DB2 Scripts

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.out

•  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