How to run REORG, REORGCHK and RUNSTAT on all tables in a database

Queries to execute REORG, REORGCHK and RUNSTAT on all tables in a database

Write the following scripts in a .out file, then execute it by issuing the db2 command with option -tvf.

• Reorg for all tables

db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';'from syscat.tables where type = 'T' " > reorg.out

db2 -tvf  reorg.out

•  Reorgchk for all tables

db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' from \
syscat.tables where type = 'T' " > reorgchk.out

db2 -tvf reorgchk.ou

• Runstats for all tables

db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' and indexes all;'from \
syscat.tables where type = 'T' " > runstats.out

db2 -tvf runstats.out

Executing reorg, reorgcheck and runstats for all tables from one specific tablespace.

• Reorg for all tables from one specifc tablespace

db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' \
from syscat.tables where type = 'T' and tbspace='<tablespace_name>'" > reorg.out 

db2 -tvf  reorg.out

• Reorgchk for all tables from one specifc tablespace

db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' from \
syscat.tables where type = 'T' and tbspace='<tablespace_name>'" > reorgchk.out

db2 -tvf reorgchk.ou

• Runstats for all tables from one specifc tablespace

db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' and indexes all;'from \
syscat.tables where type = 'T' " > runstas.out

db2 -tvf runstats.out