Reorg and runstats in a ksh script

You can reorg and runstats in a single ksh script. Install this script as a crontab to run every day during off-peak hours to get maximum performance in DB2. Change @HOME@ to your DB2 home dir and @SCHEMA@ to your database schema name.

This script will work on all major Unix servers. It will basically update statistics for all the tables for a given schema. This will improve performance for insert, update, delete and select statements. It has been tested on version 7.2.

 #!/bin/ksh 
 # Source the db2 environment variables. 
 
 . @HOME@/sqllib/db2profile 
  
 LIST1=/tmp/tempreorg1 
 LIST2=/tmp/tempreorg2 
 LOG=@HOME@/schema/utilities/reorg.log 
 
 { 
 rm $LIST1 $LIST2 2>> $LOG 
 
 DATABASE=$1 
 # 
 db2 -v "connect to ${DATABASE}" 
 
 # 
 # get a list of tables 
 db2 "select tabname from syscat.tables where 
 tabschema='@SCHEMA@' and type='T'">$LIST1 
 if [ $? -ne 0 ] 
 then 
 print "an error occurred on connect" 
 exit 1 
 fi 
 # 
 # delete the first three lines 
 # delete the line that contains "record" 
 # 
 sed '1,3d' $LIST1 |sed '/record(s)/d' > $LIST2 
 # 
 # 
 print "begin reorg" 
 # 
 for i in `cat $LIST2` 
 { 
 # print reorg table $i 
 db2 -v "reorg table @SCHEMA@.$i" 
 if [ $? -ne 0 ] 
 then 
 print "An error occurred in reorg" 
 exit 1 
 fi 
 
 db2 -v "runstats on table @SCHEMA@.$i with distribution and detailed indexes all" 
 if [ $? -ne 0 ] 
 then 
 print "an error occurred in runstats" 
 exit 1 
 fi 
 
 } 
 print "reorg SUCCESSFUL" 
 rm $LIST1 $LIST2 
 } > $LOG 

Reader Feedback

Jean P. writes: Excellent. Just one note. To eliminate the need to remove the header and footer with an extra step (sed '1,3d' $LIST1 |sed '/record(s)/d' > $LIST2) you could run the DB2 command with the -x option:

db2 -x "select tabname from syscat.tables where tabschema='@SCHEMA@' and 
type='T'">$LIST1

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free DB2 tips and scripts.
• Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
• Best Web Links: DB2 tips, tutorials, and scripts from around the Web.