db2mainframer
IS-IT--Management
Here is my script that I run to get the qualifing rows that need reorged but I get this crazy -209 error while trying to use the TEMPSPACE1 tablespace to do my re-org....please help
select distinct
'REORG TABLE '||rtrim(char(t.tabschema,32))
||'.'||rtrim(char(t.tabname,32))
||' INDEX '||rtrim(char(i.indschema,32))
||'.'||rtrim(char(i.indname,32))
||' USE TEMPSPACE1'
||' RUNSTATS ON TABLE '||rtrim(char(t.tabschema,32))
||'.'||rtrim(char(t.tabname,32))
||' WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE'
||'`DB2 COMMIT' as c1
from syscat.tables t
, syscat.indexes i
, syscat.indexes i2
where i.tabschema=t.tabschema
and i.tabname =t.tabname
and i2.tabschema=t.tabschema
and i2.tabname =t.tabname
and t.type ='T'
and t.tabschema not in ('SYSIBM')
and (i.indextype!='CLUS' and i.uniquerule ='P')
and i2.indextype!='CLUS'
and ( ( (t.npages>10 and i.clusterratio<95 and i.clusterfactor=-1)
or (t.npages>10 and i.clusterratio=-1 and i.clusterfactor<.95)
or ( (t.npages>10 and i.clusterratio between 0 and 94)
or (t.npages>10 and i.clusterfactor between 0 and .95)
)
)
or ((t.overflow)/(t.card+2.0) >.05
)
or (i.nleaf >10 and i.density < 80
)
or (i2.nleaf>10 and i2.density < 80
))
order by 1
;
select distinct
'REORG TABLE '||rtrim(char(t.tabschema,32))
||'.'||rtrim(char(t.tabname,32))
||' INDEX '||rtrim(char(i.indschema,32))
||'.'||rtrim(char(i.indname,32))
||' USE TEMPSPACE1'
||' RUNSTATS ON TABLE '||rtrim(char(t.tabschema,32))
||'.'||rtrim(char(t.tabname,32))
||' WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE'
||'`DB2 COMMIT' as c1
from syscat.tables t
, syscat.indexes i
, syscat.indexes i2
where i.tabschema=t.tabschema
and i.tabname =t.tabname
and i2.tabschema=t.tabschema
and i2.tabname =t.tabname
and t.type ='T'
and t.tabschema not in ('SYSIBM')
and (i.indextype!='CLUS' and i.uniquerule ='P')
and i2.indextype!='CLUS'
and ( ( (t.npages>10 and i.clusterratio<95 and i.clusterfactor=-1)
or (t.npages>10 and i.clusterratio=-1 and i.clusterfactor<.95)
or ( (t.npages>10 and i.clusterratio between 0 and 94)
or (t.npages>10 and i.clusterfactor between 0 and .95)
)
)
or ((t.overflow)/(t.card+2.0) >.05
)
or (i.nleaf >10 and i.density < 80
)
or (i2.nleaf>10 and i2.density < 80
))
order by 1
;