Dynamically generate reorg script

Creating appropriate reorg script for UDB can be challenging. A little research made me right this sql that will generate the reorg script from the reorgchk utilities  :

CALL SYSPROC.REORGCHK_TB_STATS(‘S’,’MYSCHEMA’);
select * from session.TB_stats where reorg like ‘%*%’;
select coalesce (‘reorg table ‘ || rtrim(a.tabschema) || ‘.’ ||
rtrim(a.tabname) || ‘ index ‘ || coalesce(max(case indextype when ‘CLUS’
then rtrim(indschema) || ‘.’ || rtrim(indname) end), max(case uniquerule
when ‘P’ then rtrim(indschema) || ‘.’ || rtrim(indname) end), max(case iid
when 1 then rtrim(indschema) || ‘.’ || rtrim(indname) end)) || ‘ inplace;’,
‘reorg table ‘ || rtrim(a.tabschema) || ‘.’ || rtrim(a.tabname) || ‘
inplace;’) from syscat.tables a left outer join syscat.indexes b on
a.tabschema = b.tabschema and a.tabname = b.tabname where
rtrim(a.tabschema) || ‘.’ || rtrim(a.tabname) in (select
rtrim(table_schema)||’.’||rtrim(table_name) from session.TB_stats where
reorg like ‘%*%’) group by a.tabschema, a.tabname;

select ‘runstats on table ‘||rtrim(table_schema)||’.’||rtrim(table_name)||
‘ with distribution on key columns and indexes all;’
from session.TB_stats where reorg like ‘%*%’;

CALL SYSPROC.REORGCHK_IX_STATS(‘S’,’MYSCHEMA’);
select ‘reorg indexes all for table
‘||rtrim(TABLE_SCHEMA)||’.’||TABLE_NAME||’ allow write access;’
from session.IX_stats where reorg like ‘%*%’;

 

The select generates the db2 script required to run the reorg.