zOs/SQL/DDLCH5
set current sqlid = 'S100447';
drop table A540769.tSels;
create global temporary table A540769.tSels
(ty char(3), qu varchar(128), nm varchar(128), gp char(3))
;
insert into A540769.tSels
select 'ts', dbName, name, ''
from sysibm.systablespace
where dbName like 'NI%'
/*?? select 'ts', dbName, name, ''
from sysibm.systablespace
where dbName = 'DGDB9998' and name like 'A40%'
union all select 't', creator, name, ''
from sysibm.systables
where creator = 'OA1A' and name like 'TMF150%' and type = 'T'
union all select 'v', creator, name, ''
from sysibm.systables
where creator = 'GDB9998' and name like 'WK947NU%'
and type = 'V'
??*/
;
-- 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;