Generating reorg and runstats scripts for DB2 performance tuning

Problem(Abstract)

You have multiple tables in your database that have changed extensively and you are experiencing a performance degradation. Reorg and runstats are recommended on these tables. You can create reorg and runstats scripts to make DB2® performance tuning easy.

Resolving the problem

To generate and run reorg and runstats scripts:

1. Log in as the DB2 administrator, and connect to the database.
   
   
2. To create a script for reorg table, run the following command on one line:
   db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/;/g' > /tmp/reorg_tbl.ddl
   
   
3. To create a script for reorg index, run the following command on one line:
   db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG INDEXES ALL FOR TABLE /g' | sed 's/$/;/g' > /tmp/reorg_idx.ddl
   
   
4. To create a script for runstats, run the following command on one line:
   db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS on TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL;/g' > /tmp/runstats_tbl.ddl
   
   
5. Run reorg against tables and indexes, then runstats by entering the following lines:
   db2 -tvf /tmp/reorg_tbl.ddl
   db2 -tvf /tmp/reorg_idx.ddl
   db2 -tvf /tmp/runstats_tbl.ddl
   
   
6. Ignore the following message if you receive it: 
   SQL2212N The specified table is a view. The Reorganize Table utility cannot be run against a view.
   
   
7. To capture the message printed on screen, you can pipe the standard output into a file, such as with the following command:
   db2 -tvf /tmp/runstats_tbl.ddl > filename.log 2>&1
   
   NOTE: The commands in step 2, 3 and 4 will not run on windows system.

The following method can also be used to reorg all the tables in a database on Linux, Unix and Windows. 

1. Log in as the DB2 administrator, and connect to the database.
   
2. To create a script for reorg table, run the following command on one line:
   
   db2 -x "select 'REORG TABLE ' ||rtrim (tabschema)|| '.' || rtrim (tabname) || ' ;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'" > reorg.out
   
3. Run reorg against tables entering the following line:
   db2 -tvf reorg.out

NOTE: The where clause in step 2 can be altered to include other tables in the reorg.out script. 
It is recommended to runstats on any table that has been reorged. 
Also, these commands may not work on all versions and levels of Unix and Linux.

http://www-01.ibm.com/support/docview.wss?uid=swg21444174&myns=swgimgmt&mynp=OCSSEPGG&mync=E