zOs/SQL/DDLCH3
--- 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' st, dbName sQ, name sN, 'ts' ty, dbName qu, name nm
, strip(dbName) || '.' || strip(name) pa
, ts.*
from sysibm.sysTablespace ts
union all select 't' st, t.creator sQ, t.name sN,
'ts' ty, ts.dbName qu, ts.name nm
, strip(ts.dbName) || '.' || strip(ts.name) pa
, ts.*
from sysibm.sysTables t join sysibm.sysTablespace ts
on t.dbName = ts.dbName and t.tsName = ts.name
and t.type not in ('A', 'V')
)
/*???
, tp as
(
select sT, sQ, sN, 'tp' ty, dbName qu, tsName nm, pa
, tp.*
from ts join sysibm.sysTablePart tp
on ts.qu = tp.dbName and ts.nm = tp.tsName
)
, t as
(
select sT, sQ, sN, 't' ty, t.creator qu, t.name nm
, pa || '.' || 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 sT, sQ, sN, 'c' ty, c.tbName qu, c.name nm
, pa || '.' || strip(c.name) pa
, c.*
from t join sysibm.sysColumns c
on qu = c.tbCreator and nm = c.tbName
)
, i as
(
select sT, sQ, sN, 'i' ty, ix.creator qu, ix.name nm
, pa || '.' || 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' sT, creator sQ, name sN
, 'i' ty, creator qu, name nm
, strip(creator) || '.' || strip(name) pa
, ix.*
from sysibm.sysIndexes ix
)
, ip as
(
select sT, sQ, sN, 'ip' ty, ix.creator qu, ix.name nm, pa
, ip.*
from i join sysibm.sysIndexPart ip
on ip.ixCreator = qu and ip.ixName = nm
)
, v1 (sT, sQ, sN, ty, qu, nm, pa, lv) as
(
select sT, sQ, sN, ty, qu, nm, pa, 0
from t
union all select 'v', t.creator, t.name, 'v', t.creator, t.name
, strip(t.creator) || '.' || strip(t.name) pa, 0
from sysibm.sysTables t
where type = 'V'
union all select sT, sQ, sN, dType, dCreator, dName
, case when length(pa) < 70 then pa || '.'
else left(pa, 65) || '.....' end
|| 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 sT, sQ, sN, 'v' ty, qu, nm, pa
from v1
where ty in('v', 'V')
)
??????????*/
, u as
(
select sT, sQ, sN, ty, qu, nm, pa from ts
/*???
union all select sT, sQ, sN, ty, qu, nm, pa from t
union all select sT, sQ, sN, ty, qu, nm, pa from i
union all select sT, sQ, sN, ty, qu, nm, pa from v
???*/
)
select * from u
where (sT = 'ts' and sQ = 'DA540769' and sN like 'A40%')
or (sT = 't' and sQ = 'OA1A' and sN like 'TMF150%')
or (sT = 'v' and sQ = 'GDB9998' and sN like 'WK947NUL%')
;
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;
, eTs (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa, 'psimple TableSpace'
from ts
where partitions = 0 and segSize = 0
union all select db, ts, ty, qu, nm, pa, 'ppartition by growth'
from ts
where maxPartitions > 0
union all select db, ts, ty, qu, nm, pa, 'puniversal TableSpace'
from ts
where partitions > 0 and segSize > 0
union all select db, ts, ty, qu, nm, pa,
'ptables=' || strip(char(nTables)) || ', <> 1'
from ts
where nTables <> 1
union all select db, ts, ty, qu, nm, pa,
'partitions=' || strip(char(partitions)) || ', > 512'
from ts
where partitions > 512
union all select db, ts, ty, qu, nm, pa, 'obufferPool=' || bPool
from ts
where bPool not in('BP2', 'BP8K1', 'BP16K1', 'BP32K')
union all select db, ts, ty, qu, nm, pa,
'pEncoding=' || encoding_scheme || ', not EBCDIC'
from ts
where encoding_scheme <> 'E'
union all select db, ts, ty, qu, nm, pa,
'oclose=' || closeRule || ', not Y'
from ts
where closeRule <> 'Y'
union all select db, ts, ty, qu, nm, pa,
'odssize=' || strip(char(dsSize)) || ' tables, not 16GB'
from ts
where nTables <> 1
union all select db, ts, ty, qu, nm, pa, 'oerase=' || eraseRule
from ts
where eraseRule <> 'N'
union all select db, ts, ty, qu, nm, pa, 'pimplicit=' || implicit
from ts
where implicit <> 'N'
union all select db, ts, ty, qu, nm, pa,
'olockmax=' || strip(char(lockmax)) || ', not system'
from ts
where lockMax <> -1
union all select db, ts, ty, qu, nm, pa,
'olockSize=' || strip(char(lockRule)) || ', not any, page'
from ts
where lockRule not in ('A', 'P', 'L')
union all select db, ts, ty, qu, nm, pa, 'onot Logged'
from ts
where log <> 'Y'
union all select db, ts, ty, qu, nm, pa,
'omaxrows=' || strip(char(maxrows)) || ', not 256'
from ts
where segsize not in (0, 64)
union all select db, ts, ty, qu, nm, pa, 'pmembercluster'
from ts
where member_cluster <> ' '
union all select db, ts, ty, qu, nm, pa,
'osegsize=' || strip(char(segsize)) || ', not 64'
from ts
where segsize not in (0, 64)
)
, eTP (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa, 'ocompress=' || compress
from tp
where tp.compress <> 'Y'
union all select db, ts, ty, qu, nm, pa, 'ostorName not GSMS?'
from tp
where ( left(db, 2) <> 'XB' and storName <> 'GSMS')
or (left(db, 2) = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select db, ts, ty, qu, nm, pa, 'opriQty <> -1'
from tp
where pqty <> -1
union all select db, ts, ty, qu, nm, pa, 'osecQty <> -1'
from tp
where sqty <> -1
union all select db, ts, ty, qu, nm, pa,
'ofreepage=' || strip(char(freepage)) || ', not 0'
from tp
where freepage <> 0
union all select db, ts, ty, qu, nm, pa,
'opctFree=' || strip(char(pctFree)) || ', > 20'
from tp
where pctFree > 20
union all select db, ts, ty, qu, nm, pa,
'ogbpCache=' || gbpCache || ', not changed'
from tp
where gbpCache <> ' '
)
, eT (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa,
'ncreator not like oa1' || substr(db, 7, 1) || '%'
from t
where not (left(qu, 4) = 'OA1' || substr(db, 7, 1)
or (left(db, 2) = 'VV' and qu = 'VDPS2'))
union all select db, ts, ty, qu, nm, pa,
'nname not like t' || left(db, 2) || substr(ts, 2, 3) || '%'
from t
where not (left(nm, 6) = 'T' || left(db, 2) || substr(ts, 2, 3)
or (left(db, 2) = 'VV' and left(nm, 3) = 'TVV'))
union all select db, ts, ty, qu, nm, 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 db, ts, ty, qu, nm, pa, 'pappend=' || append
from t
where append <> 'N'
union all select db, ts, ty, qu, nm, pa, 'paudit=' || auditing
from t
where auditing <> ' '
union all select db, ts, ty, qu, nm, pa, 'pdataCapture' || dataCapture
from t
where dataCapture <> ' '
union all select db, ts, ty, qu, nm, pa, 'odrop not restricted'
from t
where clusterType <> 'Y'
union all select db, ts, ty, qu, nm, 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 db, ts, ty, qu, nm, pa, 'pindexed based partitioning'
from t join sysibm.sysTablespace ts
on t.db = ts.dbName and t.ts = ts.name
and ts.partitions > 0 and partKeyColNum < 1
union all select db, ts, ty, qu, nm, pa, 'ano primary key constraint'
from t
where keyObid <> 0
)
, eC (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa, 'pcolType=' || colType
from c
where coltype not in('INTEGER', 'SMALLINT', 'FLOAT', 'CHAR'
, 'VARCHAR', 'DECIMAL', 'DATE', 'TIME', 'TIMESTMP'
, 'ROWID', 'BIGINT', 'BINARY')
union all select db, ts, ty, qu, nm, pa, 'pon update'
from c
where default in ('E','F')
union all select db, ts, ty, qu, nm, pa, 'phidden=' || hidden
from c
where hidden <> 'N'
union all select db, ts, ty, qu, nm, pa, 'pperiod=' || period
from c
where period <> ' '
)
, eI (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa,
'ncreator <> tbCreator=' || tbCreator
from i
where creator <> tbCreator
union all select db, ts, ty, qu, nm, 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 db, ts, ty, qu, nm, 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 db, ts, ty, qu, nm, pa, 'obufferpool=' || bPool
from i
where bPool <> 'BP1'
union all select db, ts, ty, qu, nm, pa, 'oclose=' || closeRule
from i
where closeRule <> 'Y'
union all select db, ts, ty, qu, nm, pa, 'ocompress=' || compress
from i
where compress <> 'N'
union all select db, ts, ty, qu, nm, pa, 'ocopy=' || copy
from i
where copy <> 'N'
union all select db, ts, ty, qu, nm, pa, 'oerase=' || eraseRule
from i
where eraseRule <> 'N'
union all select db, ts, ty, qu, nm, pa, 'include columns'
from i
where unique_count <> 0
union all select db, ts, ty, qu, nm, pa, 'opadded=' || padded
from i
where padded not in (' ', 'N')
union all select db, ts, ty, qu, nm, 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 db, ts, ty, qu, nm, pa, 'ppiecesize=' || padded
from i
where piecesize <> 0 and piecesize < 2097152
)
, eIP (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa, 'ostorName not GSMS?'
from ip
where ( left(db, 2) <> 'XB' and storName <> 'GSMS')
or (left(db, 2) = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select db, ts, ty, qu, nm, pa, 'opriQty <> -1'
from ip
where pqty <> -1
union all select db, ts, ty, qu, nm, pa, 'osecQty <> -1'
from ip
where sqty <> -1
union all select db, ts, ty, qu, nm, pa,
'ofreepage=' || strip(char(freepage)) || ', not 0'
from ip
where freepage <> 0
union all select db, ts, ty, qu, nm, pa,
'opctFree=' || strip(char(pctFree)) || ', > 20'
from ip
where pctFree > 20
union all select db, ts, ty, qu, nm, pa,
'ogbpCache=' || gbpCache || ', not changed'
from ip
where gbpCache <> ' '
)
, eV (db, ts, ty, qu, nm, pa, err) as
(
select db, ts, ty, qu, nm, pa, 'ncreator'
from v
where left(qu, 4) <> 'OA1' || substr(db, 7, 1)
union all select db, ts, ty, qu, nm, pa, 'nname'
from v
where left(nm, 3) <> 'V' || left(db, 2)
)
, eU as
(
select * from eTs
union all select * from eTp
group by ty, qu, nm, err, pa, db,ts
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, err, pa, db,ts)
union all select * from eV
)
, r as
(
select ty, substr(qu, 1, 12) qu, substr(nm, 1, 16) nm,
left(err, 1) cat, substr(err, 2) err, pa
from eU
where db like 'VV%' -- and ts like 'A97%'
)
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
;
-- where db = 'DGDB9998' -- and ts like 'A97%'
order by pa, err
;
select ty, substr(qu, 1, 12) qu, substr(nm, 1, 16) nm,
left(err, 1) cat, substr(err, 2) 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;