DB2 Reorg and Run stats commands – life saver

Below are some life saver commands for DB2 admin for reorg and subsequent runstat –

Reorganising tables::
This process helps in bringing together of widely separated data of a big table of DB2 where we running a OLTP intrinsic database. This process has to be done once a week. The following command in db2 will create a custom reorg script file for a full database.
—->db2 “select ‘reorg table ‘ || rtrim(tabschema) || ‘.’ || rtrim(tabname) || ‘;’ from syscat.tables where tabschema=’WWW’ ” > reorg.out

We can execute the the contents of reorg.out by the following command.

—->db2 -tvf reorg.out

Running stats on tables::

This process helps to update DB2 system tables after reorg command. The following command in db2 will create a custom runstats script file for a full database.

——>db2 “select ‘runstats on table ‘ || rtrim(tabschema) || ‘.’ || rtrim(tabname) || ‘ with distribution and detailed indexes all;’ from syscat.tables where tabschema=’WWW'” > runstats.out

——>db2 -tvf runstats.out

Reorg check::

This gives a reorg statistics….

———>db2 reorgchk on table all > reorgchk.out

Reorganising indexes::

After running reorg, you run reorgcheck. If you find * in the last column of the any index, then run reorg for that index like…

—–> db2 reorg table www.wf_flow_level index www.ef_flow_lvl_pk