zOs/SQL/DDLCH6
set current sqlid = 'S100447';
set current path oa1t;
--drop table A540769.tSels;
create global temporary table A540769.tSels
(ty char(3), qu varchar(128), nm varchar(128), gp char(3))
;
create view A540769.vDdlCheck as
with ts as
(
select 'ts' ty, dbName qu, name nm
, case when gp = '' then left(dbName, 2) || substr(dbName, 7, 1)
else gp end gp
, 'ts:' || strip(dbName) || '.' || strip(name) pa
, ts.*
from sysibm.sysTablespace ts join A540769.tSels s
on s.ty = 'ts' and s.qu = ts.dbName and s.nm = ts.name
union all select 'ts' ty, ts.dbName qu, ts.name nm
, case when gp = '' then left(t.dbName,2)||substr(t.dbName, 7, 1)
else gp end gp
, 't:' || strip(t.creator) || '.' || strip(t.name)
|| 'ts:' || strip(t.dbName) || '.' || strip(t.tsname) pa
, ts.*
from sysibm.sysTables t join A540769.tSels s
on s.ty = 't' and s.qu = t.creator and s.nm = t.name
join sysibm.sysTablespace ts
on ts.dbName = t.dbname and ts.name = t.tsname
)
, tp as
(
select 'tp' ty, qu, nm, gp, pa
, tp.*
from ts join sysibm.sysTablePart tp
on ts.qu = tp.dbName and ts.nm = tp.tsName
)
, t as
(
select 't' ty, t.creator qu, t.name nm, gp
, pa || ' t:' || strip(t.creator) || '.' || strip(t.name) pa
, t.*
from ts join sysibm.sysTables t
on t.dbName = ts.qu and t.tsName = ts.nm
and t.type not in ('A', 'V')
)
, c as
(
select 'c' ty, c.tbName qu, c.name nm, gp
, pa || ' c:' || strip(c.name) pa
, c.*
from t join sysibm.sysColumns c
on qu = c.tbCreator and nm = c.tbName
)
, i as
(
select 'i' ty, ix.creator qu, ix.name nm, gp
, pa || ' i:' || strip(ix.creator) ||'.'|| strip(ix.name) pa
, ix.*
from t join sysibm.sysIndexes ix
on qu = ix.tbCreator and nm = ix.tbName
union all select 'i' ty, ix.creator qu, ix.name nm
, case when gp='' then left(ix.dbName,2)||substr(ix.dbName, 7,1)
else gp end gp
, 'i:' || strip(creator) || '.' || strip(name) pa
, ix.*
from sysibm.sysIndexes ix join A540769.tSels s
on s.ty = 'i' and ix.creator = s.qu and ix.name = s.nm
)
, ip as
(
select 'ip' ty, qu, nm, gp, pa
, ip.*
from i join sysibm.sysIndexPart ip
on ip.ixCreator = qu and ip.ixName = nm
)
, v1 (ty, qu, nm, gp, tCr, pa, lv) as
(
select ty, qu, nm, gp, creator, pa, 0
from t
union all select s.*, '', 'v:' || strip(s.qu) ||'.'|| strip(s.nm), 0
from A540769.tSels s where ty = 'v'
union all select dType, dCreator, dName, gp, tCr
, case when length(pa) < 70 then pa
else left(pa, 65) || '.....' end
|| ' v:' || strip(dCreator) || '.' || strip(dName)
, lv+1
from v1 join sysibm.sysViewDep d
on d.bCreator = v1.qu and d.bName = v1.nm
where lv < 100
)
, v as
( select 'v' ty, qu, nm, gp, tCr, pa
from v1
where ty in('v', 'V')
)
, eTs (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'p', 'type', 'simple', ''
from ts
where partitions = 0 and segSize = 0
union all select ty, qu, nm, pa, 'p', 'type', 'partition by growth',''
from ts
where maxPartitions > 0
union all select ty, qu, nm, pa, 'p', 'type', 'universal', ''
from ts
where partitions > 0 and segSize > 0
union all select ty, qu, nm, pa, 'p', 'tables', char(nTables), '1'
from ts
where nTables <> 1
union all select ty, qu, nm, pa, 'p', 'partitions',
char(partitions), '<=512'
from ts
where partitions > 512
union all select ty, qu, nm, pa, 'o', 'bufferPool', bPool, 'bp2...'
from ts
where bPool not in('BP2', 'BP8K1', 'BP16K1', 'BP32K')
union all select ty, qu, nm, pa, 'p', 'encoding',
encoding_scheme, 'EBCDIC'
from ts
where encoding_scheme <> 'E'
union all select ty, qu, nm, pa, 'o', 'close', closeRule, 'Y'
from ts
where closeRule <> 'Y'
union all select ty, qu, nm, pa, 'oq', 'dssize', char(dsSize), '16GB'
from ts
where dssize <> 16777216 and partitions <> 0
union all select ty, qu, nm, pa, 'o', 'erase', eraseRule, 'N'
from ts
where eraseRule <> 'N'
union all select ty, qu, nm, pa, 'p', 'implicit', implicit, 'N'
from ts
where implicit <> 'N'
union all select ty, qu, nm, pa, 'o', 'lockmax',
char(lockmax), 'system'
from ts
where lockMax <> -1
union all select ty, qu, nm, pa, 'oq', 'locksize', lockRule, 'any'
from ts
where lockRule <> 'A'
union all select ty, qu, nm, pa, 'o', 'logged', log, 'Y'
from ts
where log <> 'Y'
union all select ty, qu, nm, pa, 'oq', 'maxrows', char(maxrows),'256'
from ts
where segsize not in (0, 64)
union all select ty, qu, nm, pa, 'pq', 'membercluster',
member_cluster, ' '
from ts
where member_cluster <> ' '
union all select ty, qu, nm, pa, 'o', 'segsize', char(segsize), '64'
from ts
where segsize not in (0, 64)
)
, eTP (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'o', 'compress', compress, 'Y'
from tp
where tp.compress <> 'Y'
union all select ty, qu, nm, pa, 'oq', 'storName',storName,'GSMS?'
from tp
where (left(gp, 2) <> 'XB' and storName <> 'GSMS')
or (left(gp, 2) = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select ty, qu, nm, pa, 'o', 'priQty', char(pQty), '-1'
from tp
where pqty <> -1
union all select ty, qu, nm, pa, 'o', 'secQty', char(sQty), '-1'
from tp
where sqty <> -1
union all select ty, qu, nm, pa, 'oq', 'freePage',
char(freepage), '0'
from tp
where freepage <> 0
union all select ty, qu, nm, pa, 'oq', 'pctFree',
char(pctFree), '10'
from tp
where pctFree <> 10
union all select ty, qu, nm, pa, 'o', 'gbpCache',
gbpCache, 'changed'
from tp
where gbpCache <> ' '
)
, eT (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'n', 'creator', qu,
'oa1' || substr(gp, 3, 1) || '%'
from t
where not (left(qu, 4) = 'OA1' || substr(gp, 3, 1)
or (left(gp, 2) = 'VV' and qu = 'VDPS2'))
union all select ty, qu, nm, pa, 'n', 'name', nm,
't' || left(gp,2) || case when left(gp, 2) = 'VV' then ''
else substr(tsName,2,3) end || '%'
from t
where not (left(nm, 6) = 'T' || left(gp,2) || substr(tsName, 2, 3)
or (left(gp, 2) = 'VV' and left(nm, 3) = 'TVV'))
union all select ty, qu, nm, pa, 'p', 'tableStatus',
case when tableStatus = 'L' then 'pauxilary index oder table fehlt'
when tableStatus = 'P' then 'pprimary index fehlt'
when tableStatus = 'R' then 'pindex auf Row ID fehlt'
when tableStatus = 'U' then 'pindex auf unique key fehlt'
when tableStatus = 'V' then 'pFehler interne ViewDarstellung'
else 'ptableStatus=' || tableStatus
end, 'ok'
from t
where tablestatus <> ' '
union all select ty, qu, nm, pa, 'p', 'append', append, 'N'
from t
where append <> 'N'
union all select ty, qu, nm, pa, 'p', 'audit', auditing, 'no'
from t
where auditing <> ' '
union all select ty, qu, nm, pa, 'p', 'dataCapture',
dataCapture, 'no'
from t
where dataCapture <> ' '
union all select ty, qu, nm, pa, 'o', 'restrict on drop',
clustertype, 'Y'
from t
where clusterType <> 'Y'
union all select ty, qu, nm, pa, 'p', 'Clustering Index', 'none',
'required if any indexes'
from t
where (select max(clustering) from sysibm.sysIndexes i
where i.tbCreator = t.qu and i.tbName = t.nm)
not in ('Y', case when 1=1 then 'x' else null end)
union all select ty, qu, nm, pa, 'p', 'partitioning',
'indexed based', 'table based'
from t join sysibm.sysTablespace ts
on t.dbName = ts.dbName and t.tsName = ts.name
and ts.partitions > 0 and partKeyColNum < 1
union all select ty, qu, nm, pa, 'a', 'primary key constraint',
'none', 'adviced'
from t
where keyObid <> 0
)
, eC (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'p', 'colType', colType,
'only CS allowed'
from c
where coltype not in('INTEGER', 'SMALLINT', 'FLOAT', 'CHAR'
, 'VARCHAR', 'DECIMAL', 'DATE', 'TIME', 'TIMESTMP'
, 'ROWID', 'BIGINT', 'BINARY')
union all select ty, qu, nm, pa, 'p', 'on update', 'used', 'no'
from c
where default in ('E','F')
union all select ty, qu, nm, pa, 'p', 'hidden', hidden, 'N'
from c
where hidden <> 'N'
union all select ty, qu, nm, pa, 'p', 'period', period, 'no'
from c
where period <> ' '
)
, eI (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'n', 'creator', qu, tbCreator
from i
where creator <> tbCreator
union all select ty, qu, nm, pa, 'n', 'name', nm,
'i' || substr(tbName, 2, 5) || '%'
from i
where not (left(nm, 6) = 'I' || substr(tbName, 2, 5)
or qu = 'VDPS2')
union all select ty, qu, nm, pa, 'p', 'extensionType',
case when ix_extension_type = 'S' then 'index on expression'
else ix_extension_type end, 'no'
end
from i
where ix_extension_type <> ''
union all select ty, qu, nm, pa, 'o', 'bufferpool', bPool, 'BP1'
from i
where bPool <> 'BP1'
union all select ty, qu, nm, pa, 'o', 'close', closeRule, 'Y'
from i
where closeRule <> 'Y'
union all select ty, qu, nm, pa, 'o', 'compress', compress, 'N'
from i
where compress <> 'N'
union all select ty, qu, nm, pa, 'o', 'copy', copy, 'N'
from i
where copy <> 'N'
union all select ty, qu, nm, pa, 'o', 'erase', eraseRule, 'N'
from i
where eraseRule <> 'N'
union all select ty, qu, nm, pa, 'p', 'include columns', 'yes', 'no'
from i
where unique_count <> 0
union all select ty, qu, nm, pa, 'oq', 'padded', padded, 'N'
from i
where padded not in (' ', 'N')
union all select ty, qu, nm, pa, 'a', 'partitioned', 'no', 'yes'
from i join sysibm.sysTables t
on i.tbCreator = t.creator and i.tbName = t.name
join sysibm.sysTableSpace ts
on ts.dbName = t.dbName and t.tsName = ts.name
and ts.partitions > 0
join sysibm.sysIndexPart ip
on ip.ixCreator = i.creator and ip.ixName = i.name
and ip.partition = 1
union all select ty, qu, nm, pa,
case when piecesize between 1 and 2097152 then 'pq' else 'iq' end,
'piecesize', char(piecesize), '2GB'
from i
where piecesize <> 2097152
)
, eIP (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'oq', 'storName',
storName, 'GSMS?'
from ip
where ( left(gp, 2) <> 'XB' and storName <> 'GSMS')
or (left(gp, 2) = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select ty, qu, nm, pa, 'o', 'priQty', char(pQty), '-1'
from ip
where pqty <> -1
union all select ty, qu, nm, pa, 'o', 'secQty', char(sQty), '-1'
from ip
where sqty <> -1
union all select ty, qu, nm, pa, 'oq', 'freePage', char(freePage), '0'
from ip
where freepage <> 0
union all select ty, qu, nm, pa, 'oq', 'pctFree', char(pctFree), '10'
from ip
where pctFree <> 10
union all select ty, qu, nm, pa, 'o', 'gbpCache', gbpCache, 'changed'
from ip
where gbpCache <> ' '
)
, eV (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'n', 'creator', qu,
'OA1' || substr(gp, 3, 1) || '%'
from v
where not( (tCr <> '' and tCr = qu)
or ((tCr = '' or left(gp, 2) = 'VV')
and left(qu, 4) = 'OA1' || substr(gp, 3, 1)))
union all select ty, qu, nm, pa, 'n', 'name',nm, 'V'||left(gp, 2)||'%'
from v
where left(nm, 3) <> 'V' || left(gp, 2)
)
, eU1 as
(
select * from eTs
union all select * from eTp
group by ty, qu, nm, cat, att, val, std, pa
union all select * from eT
union all select * from eC
union all select * from eI
union all select * from eIp
group by ty, qu, nm, cat, att, val, std, pa
union all select * from eV
)
, eU (ty, qu, nm, cat, err, pa) as
(
select ty, qu, nm, cat
, att || '='
|| case when length(val) = 0 then ''''''
when val = '' then ''' '''
else strip(val)
end
|| ', not std=' || std, pa
from eU1
)
, rt (ty, qu, nm, pa, rows, space) as
(
select ty, qu, nm, min(pa) pa
, real(sum(totalRows)) rows
, sum(ts.pgSize * real(r.nActive) * 1024) space
from ts left join sysibm.sysTablespaceStats r
on r.dbName = ts.dbName and r.name = ts.name
and r.dbid = ts.dbid and r.psid = ts.psid
group by ty, qu, nm
)
, ri (ty, qu, nm, pa, rows, space) as
(
select ty, qu, nm, min(pa)
, real(sum(totalEntries))
, sum(case when i.pgSize = 4096 then 4
else i.pgSize end
* real(r.nActive) * 1024)
from i left join sysibm.sysIndexspaceStats r
on r.creator= i.creator and r.name = i.name
and r.dbid = i.dbid and r.isobid = i.isobid
group by ty, qu, nm
)
, u1 (ty, qu, nm, cat, err, pa) as
(
select * from eU
union all select ty, qu, nm, 'ir'
, ' rows=' || fosFmtE7(rows)
|| ', space=' || fosFmtE7(space) || 'B'
, pa
from (select * from rt union all select * from ri) y
union all select ty, qu, nm, 'io', '', pa from t
union all select ty, qu, nm, 'io', '', pa from v
)
, u (ty, qu, nm, cat, err, pa) as
(
select ty, substr(qu, 1, 8), substr(nm, 1, 16), cat, err, pa
from u1
order by pa, err, cat
)
select * from u
;
insert into A540769.tSels
select 'ts', dbName, name, ''
from sysibm.systablespace
where dbName like 'NI%'
union all select 't', creator, name, ''
from sysibm.systables
where creator like 'OA1%' and name like 'TMF%' and type = 'T'
/* union all select 'ts', dbName, name, ''
from sysibm.systablespace
where dbName = 'DGDB9998' and name like 'A40%'
union all select 'v', creator, name, ''
from sysibm.systables
where creator = 'GDB9998' and name like 'WK947NU%'
and type = 'V' */
;
select count(*), ty, cat, err, min(pa), max(pa)
from A540769.vDDLCheck
where cat not like 'i%'
group by cat, err, ty
order by cat, err, ty
;
select * from A540769.vDDLCheck order by pa, err
;x;
-- select * from A540769.tSels
--;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 3 for
with ts as
(
select 'ts' ty, dbName qu, name nm
, case when gp = '' then left(dbName, 2) || substr(dbName, 7, 1)
else gp end gp
, 'ts:' || strip(dbName) || '.' || strip(name) pa
, ts.*
from sysibm.sysTablespace ts join A540769.tSels s
on s.ty = 'ts' and s.qu = ts.dbName and s.nm = ts.name
union all select 'ts' ty, ts.dbName qu, ts.name nm
, case when gp = '' then left(t.dbName,2)||substr(t.dbName, 7, 1)
else gp end gp
, 't:' || strip(t.creator) || '.' || strip(t.name)
|| 'ts:' || strip(t.dbName) || '.' || strip(t.tsname) pa
, ts.*
from sysibm.sysTables t join A540769.tSels s
on s.ty = 't' and s.qu = t.creator and s.nm = t.name
join sysibm.sysTablespace ts
on ts.dbName = t.dbname and ts.name = t.tsname
)
, tp as
(
select 'tp' ty, qu, nm, gp, pa
, tp.*
from ts join sysibm.sysTablePart tp
on ts.qu = tp.dbName and ts.nm = tp.tsName
)
, t as
(
select 't' ty, t.creator qu, t.name nm, gp
, pa || ' t:' || strip(t.creator) || '.' || strip(t.name) pa
, t.*
from ts join sysibm.sysTables t
on t.dbName = ts.qu and t.tsName = ts.nm
and t.type not in ('A', 'V')
)
, c as
(
select 'c' ty, c.tbName qu, c.name nm, gp
, pa || ' c:' || strip(c.name) pa
, c.*
from t join sysibm.sysColumns c
on qu = c.tbCreator and nm = c.tbName
)
, i as
(
select 'i' ty, ix.creator qu, ix.name nm, gp
, pa || ' i:' || strip(ix.creator) ||'.'|| strip(ix.name) pa
, ix.*
from t join sysibm.sysIndexes ix
on qu = ix.tbCreator and nm = ix.tbName
union all select 'i' ty, ix.creator qu, ix.name nm
, case when gp='' then left(ix.dbName,2)||substr(ix.dbName, 7,1)
else gp end gp
, 'i:' || strip(creator) || '.' || strip(name) pa
, ix.*
from sysibm.sysIndexes ix join A540769.tSels s
on s.ty = 'i' and ix.creator = s.qu and ix.name = s.nm
)
, ip as
(
select 'ip' ty, qu, nm, gp, pa
, ip.*
from i join sysibm.sysIndexPart ip
on ip.ixCreator = qu and ip.ixName = nm
)
, v1 (ty, qu, nm, gp, tCr, pa, lv) as
(
select ty, qu, nm, gp, creator, pa, 0
from t
union all select s.*, '', 'v:' || strip(s.qu) ||'.'|| strip(s.nm), 0
from A540769.tSels s where ty = 'v'
union all select dType, dCreator, dName, gp, tCr
, case when length(pa) < 70 then pa
else left(pa, 65) || '.....' end
|| ' v:' || strip(dCreator) || '.' || strip(dName)
, lv+1
from v1 join sysibm.sysViewDep d
on d.bCreator = v1.qu and d.bName = v1.nm
where lv < 100
)
, v as
( select 'v' ty, qu, nm, gp, tCr, pa
from v1
where ty in('v', 'V')
)
, u as
(
select ty, qu, nm, pa from ts
union all select ty, qu, nm, pa from t
union all select ty, qu, nm, pa from i
union all select ty, qu, nm, pa from v
)
, eTs (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa, 'psimple TableSpace'
from ts
where partitions = 0 and segSize = 0
union all select ty, qu, nm, gp, pa, 'ppartition by growth'
from ts
where maxPartitions > 0
union all select ty, qu, nm, gp, pa, 'puniversal TableSpace'
from ts
where partitions > 0 and segSize > 0
union all select ty, qu, nm, gp, pa,
'ptables=' || strip(char(nTables)) || ', <> 1'
from ts
where nTables <> 1
union all select ty, qu, nm, gp, pa,
'partitions=' || strip(char(partitions)) || ', > 512'
from ts
where partitions > 512
union all select ty, qu, nm, gp, pa, 'obufferPool=' || bPool
from ts
where bPool not in('BP2', 'BP8K1', 'BP16K1', 'BP32K')
union all select ty, qu, nm, gp, pa,
'pEncoding=' || encoding_scheme || ', not EBCDIC'
from ts
where encoding_scheme <> 'E'
union all select ty, qu, nm, gp, pa,
'oclose=' || closeRule || ', not Y'
from ts
where closeRule <> 'Y'
union all select ty, qu, nm, gp, pa,
'odssize=' || strip(char(dsSize)) || ' tables, not 16GB'
from ts
where nTables <> 1
union all select ty, qu, nm, gp, pa, 'oerase=' || eraseRule
from ts
where eraseRule <> 'N'
union all select ty, qu, nm, gp, pa, 'pimplicit=' || implicit
from ts
where implicit <> 'N'
union all select ty, qu, nm, gp, pa,
'olockmax=' || strip(char(lockmax)) || ', not system'
from ts
where lockMax <> -1
union all select ty, qu, nm, gp, pa,
'olockSize=' || strip(char(lockRule)) || ', not any, page'
from ts
where lockRule not in ('A', 'P', 'L')
union all select ty, qu, nm, gp, pa, 'onot Logged'
from ts
where log <> 'Y'
union all select ty, qu, nm, gp, pa,
'omaxrows=' || strip(char(maxrows)) || ', not 256'
from ts
where segsize not in (0, 64)
union all select ty, qu, nm, gp, pa, 'pmembercluster'
from ts
where member_cluster <> ' '
union all select ty, qu, nm, gp, pa,
'osegsize=' || strip(char(segsize)) || ', not 64'
from ts
where segsize not in (0, 64)
)
, eTP (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa, 'ocompress=' || compress
from tp
where tp.compress <> 'Y'
union all select ty, qu, nm, gp, pa, 'ostorName not GSMS?'
from tp
where (left(gp, 2) <> 'XB' and storName <> 'GSMS')
or (left(gp, 2) = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select ty, qu, nm, gp, pa, 'opriQty <> -1'
from tp
where pqty <> -1
union all select ty, qu, nm, gp, pa, 'osecQty <> -1'
from tp
where sqty <> -1
union all select ty, qu, nm, gp, pa,
'ofreepage=' || strip(char(freepage)) || ', not 0'
from tp
where freepage <> 0
union all select ty, qu, nm, gp, pa,
'opctFree=' || strip(char(pctFree)) || ', > 20'
from tp
where pctFree > 20
union all select ty, qu, nm, gp, pa,
'ogbpCache=' || gbpCache || ', not changed'
from tp
where gbpCache <> ' '
)
, eT (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa,
'ncreator not like oa1' || substr(gp, 3, 1) || '%'
from t
where not (left(qu, 4) = 'OA1' || substr(gp, 3, 1)
or (left(gp, 2) = 'VV' and qu = 'VDPS2'))
union all select ty, qu, nm, gp, pa
, 'nname not like t' || left(gp,2)
|| case when left(gp, 2) = 'VV' then ''
else substr(tsName,2,3) end || '%'
from t
where not (left(nm, 6) = 'T' || left(gp,2) || substr(tsName, 2, 3)
or (left(gp, 2) = 'VV' and left(nm, 3) = 'TVV'))
union all select ty, qu, nm, gp, pa,
case when tableStatus = 'L' then 'pauxilary index oder table fehlt'
when tableStatus = 'P' then 'pprimary index fehlt'
when tableStatus = 'R' then 'pindex auf Row ID fehlt'
when tableStatus = 'U' then 'pindex auf unique key fehlt'
when tableStatus = 'V' then 'pFehler interne ViewDarstellung'
else 'ptableStatus=' || tableStatus
end
from t
where tablestatus <> ' '
union all select ty, qu, nm, gp, pa, 'pappend=' || append
from t
where append <> 'N'
union all select ty, qu, nm, gp, pa, 'paudit=' || auditing
from t
where auditing <> ' '
union all select ty, qu, nm, gp, pa, 'pdataCapture' || dataCapture
from t
where dataCapture <> ' '
union all select ty, qu, nm, gp, pa, 'odrop not restricted'
from t
where clusterType <> 'Y'
union all select ty, qu, nm, gp, pa, 'pno Clustering Index'
from t
where (select max(clustering) from sysibm.sysIndexes i
where i.tbCreator = t.qu and i.tbName = t.nm)
not in ('Y', case when 1=1 then 'x' else null end)
union all select ty, qu, nm, gp, pa, 'pindexed based partitioning'
from t join sysibm.sysTablespace ts
on t.dbName = ts.dbName and t.tsName = ts.name
and ts.partitions > 0 and partKeyColNum < 1
union all select ty, qu, nm, gp, pa, 'ano primary key constraint'
from t
where keyObid <> 0
)
, eC (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa, 'pcolType=' || colType
from c
where coltype not in('INTEGER', 'SMALLINT', 'FLOAT', 'CHAR'
, 'VARCHAR', 'DECIMAL', 'DATE', 'TIME', 'TIMESTMP'
, 'ROWID', 'BIGINT', 'BINARY')
union all select ty, qu, nm, gp, pa, 'pon update'
from c
where default in ('E','F')
union all select ty, qu, nm, gp, pa, 'phidden=' || hidden
from c
where hidden <> 'N'
union all select ty, qu, nm, gp, pa, 'pperiod=' || period
from c
where period <> ' '
)
, eI (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa,
'ncreator <> tbCreator=' || tbCreator
from i
where creator <> tbCreator
union all select ty, qu, nm, gp, pa,
'nname not like i' || substr(tbName, 2, 5) || '%'
from i
where not (left(nm, 6) = 'I' || substr(tbName, 2, 5)
or qu = 'VDPS2')
union all select ty, qu, nm, gp, pa,
case when ix_extension_type = 'S' then 'pindex on expression'
else 'ix_extension_type=' || ix_extension_type
end
from i
where ix_extension_type <> ''
union all select ty, qu, nm, gp, pa, 'obufferpool=' || bPool
from i
where bPool <> 'BP1'
union all select ty, qu, nm, gp, pa, 'oclose=' || closeRule
from i
where closeRule <> 'Y'
union all select ty, qu, nm, gp, pa, 'ocompress=' || compress
from i
where compress <> 'N'
union all select ty, qu, nm, gp, pa, 'ocopy=' || copy
from i
where copy <> 'N'
union all select ty, qu, nm, gp, pa, 'oerase=' || eraseRule
from i
where eraseRule <> 'N'
union all select ty, qu, nm, gp, pa, 'include columns'
from i
where unique_count <> 0
union all select ty, qu, nm, gp, pa, 'opadded=' || padded
from i
where padded not in (' ', 'N')
union all select ty, qu, nm, gp, pa, 'anot partitioned'
from i join sysibm.sysTables t
on i.tbCreator = t.creator and i.tbName = t.name
join sysibm.sysTableSpace ts
on ts.dbName = t.dbName and t.tsName = ts.name
and ts.partitions > 0
join sysibm.sysIndexPart ip
on ip.ixCreator = i.creator and ip.ixName = i.name
and ip.partition = 1
union all select ty, qu, nm, gp, pa
, 'ppiecesize=' || strip(char(piecesize))
from i
where piecesize <> 0 and piecesize < 2097152
)
, eIP (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa, 'ostorName not GSMS?'
from ip
where ( left(gp, 2) <> 'XB' and storName <> 'GSMS')
or (left(gp, 2) = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select ty, qu, nm, gp, pa, 'opriQty <> -1'
from ip
where pqty <> -1
union all select ty, qu, nm, gp, pa, 'osecQty <> -1'
from ip
where sqty <> -1
union all select ty, qu, nm, gp, pa,
'ofreepage=' || strip(char(freepage)) || ', not 0'
from ip
where freepage <> 0
union all select ty, qu, nm, gp, pa,
'opctFree=' || strip(char(pctFree)) || ', > 20'
from ip
where pctFree > 20
union all select ty, qu, nm, gp, pa,
'ogbpCache=' || gbpCache || ', not changed'
from ip
where gbpCache <> ' '
)
, eV (ty, qu, nm, gp, pa, err) as
(
select ty, qu, nm, gp, pa, 'ncreator'
from v
where not( (tCr <> '' and tCr = qu)
or ((tCr = '' or left(gp, 2) = 'VV')
and left(qu, 4) = 'OA1' || substr(gp, 3, 1)))
union all select ty, qu, nm, gp, pa, 'nname'
from v
where left(nm, 3) <> 'V' || left(gp, 2)
)
, eU as
(
select * from eTs
union all select * from eTp
group by ty, qu, nm, gp, err, pa
union all select * from eT
union all select * from eC
union all select * from eI
union all select * from eIp
group by ty, qu, nm, gp, err, pa
union all select * from eV
)
, r as
(
select ty, substr(qu, 1, 12) qu, substr(nm, 1, 16) nm,
substr(err, 1, 1) cat, substr(gp, 1, 3) gp,
substr(err, 2, 40) err, pa
from eU
)
-- select * from r order by pa ;x;
select count(*), ty, cat, min(err), max(err)
, min(pa), max(pa)
from r
group by ty, cat, left(err, 10)
order by 3, 2, 4
;
select * from plan_view1
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by --collid, progName, applName, explain_time,
queryNo, qBlockNo, planno,
stage, predNo
with ur
;
rollback
;x;
-- where db = 'DGDB9998' -- and ts like 'A97%'
order by pa, err
;
select ty, substr(qu, 1, 12) qu, substr(nm, 1, 16) nm,
substr(err, 1, 1) cat, substr(err, 2, 40) err, pa
from eU
where db like 'MF%' -- and ts like 'A97%'
-- where db = 'DGDB9998' -- and ts like 'A97%'
order by pa, err
;
select * from v
where db like 'VV24%' and ts like 'VDPS168%'
order by qu, nm, pa
;x;