Reorg and runstats in a ksh script
You can reorg and runstats in a single 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.