DB2 tables performance optimization scripts

The script below is used to optimize all tables in a database automatically.

Hint: the script is a modified version from a script by Portal support and Portal info centre to optimize Portal database performance

db2 connect to SAMPLE
db2 -x -r "runstats.db2" "select rtrim(concat('reorg table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),''))))) from syscat.tables where type='T' and TABSCHEMA not like 'SYS%' "
db2 -x -r "runstats.db2" "select rtrim(concat('reorg INDEXES all for table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),''))))) from syscat.tables where type='T' and TABSCHEMA not like 'SYS%' "
db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T' and TABSCHEMA not like 'SYS%' "
db2 connect reset

Important consideration:
The default behavior for reorg table command to organize data physically on disk using the tables primary key if no cluster index is available. Sometimes this is not the best way to organize a table. For example, your table consists of auto generated primary key and another column for department ID and another one for employees. If your application most of the time generate queries for employees in a certain department then the best approach to optimize this table physically is to create a cluster index based on department ID. This will lead to fewer IO requests to load the part of the table content satisfying your query into memory.

If you don't understand how the application works, then it is better to remove the reorg table command from script above.