zOs/SQL/CATCOLDI
set current path oa1p;
with t0 as
(
select *
from sysibm.sysTables
where creator = 'QZ91CRS' and name = 'TQZ91WK911K1'
)
, t (cr, tb, tbCard ) as
(
select creator, name, cardF
from t0
)
, ti (cols, col, colGroup, type, cardF, freqF, val, tb, cr, tbCard) as
(
select 0, '*table runSta', '', 'tRun', tbCard, -1, '', t.* from t
union select 0, '*table RTS', '', 'tRTS'
, sum(real(totalRows)), -1, '', t.creator, t.name
, min(s.partitions)
from t0 t
join sysibm.sysTableSpace s
on t.dbName = s.dbName and t.tsName = s.name
left join sysibm.sysTableSpaceStats r
on t.dbName = r.dbName and t.tsName = r.name
and t.dbid = r.dbid and s.psid =r.psid
group by t.creator, t.name, t.cardf
)
, c (cols, col, colGroup, type, cardF, freqF, val, tb, cr, tbCard) as
(
select 1, name, hex(colNo), 'Col'
, colCardF, -1, '', t.*
from t join sysibm.sysColumns c
on c.tbCreator = t.cr and c.tbName = t.tb
)
, d (cols, col, colGroup, type, cardF, freqF, val, tb, cr, tbCard) as
(
select numColumns, name, hex(colGroupColNo), type, cardF, frequencyF
, case when type = 'H'
then quantileNo || ':'
|| hex(case when length(strip(lowValue, t))<50
then strip(lowValue, t)
else left(lowValue, 50) end)
|| ' - '
|| hex(case when length(strip(highValue, t))<50
then strip(highValue, t)
else left(highValue, 50) end)
when type = 'F'
then hex(case when length(strip(colValue, t))<100
then strip(colValue, t)
else left(colValue, 100) end)
else ''
end
, t.*
from sysibm.sysColDist d join t
on d.tbOwner = t.cr and d.tbName = t.tb
)
, e as
(
select cols
, case when d.cols = 1 then d.col
else (select c.col from c
where c.cr = d.cr and c.tb = d.tb
and c.colGroup = right(d.colGroup, 4))
end col
, case when d.cols <> 1 then d.colGroup
else (select c.colGroup from c
where c.cr = d.cr and c.tb = d.tb
and c.col = d.col)
end colGroup
, type, cardf, freqF, val
, tb, cr, tbCard
from d
)
, o1 as
(
select * from c
union all select e.* from e
union all select * from ti
)
, o2 (cols, col, colGroup, type, cardF, freqF, val, tb, cr, tbCard) as
(
select cols, col, colGroup,type
, case when cardf <= 0 then null else cardf end
, case when freqf < 0 then null else freqf end
, val, tb, cr
, case when tbCard < 0 then null else tbCard end
from o1
)
, o3 (cols, col, colGroup, type, cardF, freqF, val, tb, cr, avgRows) as
(
select cols, col, colGroup, type, cardF, freqF, val, tb, cr
, case when type in ('C', 'COL') then tbCard / cardF
when type in ('F', 'N') then tbCard * freqF
when type in ('tRTS') then tbCard end
from o2
)
select smallint(cols) cols
, substr(col, 1, 12)
, type
, substr(fosFmte7(cardf), 1, 7) cardinF
, substr(fosFmte7(freqf), 1, 7) frequeF
, substr(fosFmte7(avgRows), 1, 7) avgRows
, 'x' || colGroup colGroup, val, cr, tb
from o3
order by cr, tb, colGroup || x'0000', type, cardf Desc, freqF desc
; x;
select smallint(cols) cols
, substr(col, 1, 12)
, type
, substr(fosFmte7(cardf), 1, 7) cardinF
, substr(fosFmte7(freqf), 1, 7) frequeF
, substr(fosFmte7(case when tbCard < 0 then null
when type in ('C', 'Col') and cardF > 0 then tbCard / cardF
when type in ('F', 'N') and freqF >= 0 then tbCard * freqF
else null end), 1, 7) avgRows
, colGroup, val, cr, tb
from o
order by cr, tb, colGroup || x'0000', type, cardf Desc, freqF desc
; x;
;x;
union all select 1, name, '', 'Col'
, colCardF, tbName, tbCreator
from sysibm.sysColumns
where (tbCreator, tbName) in (select * from t)
)
select * from d
;
set current path oa1p;
--- 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 t (cr, tb) as
(
select creator, name
from sysibm.sysTables
where creator = 'OA1P' and name = 'TBP205A1'
)
, d (cols, nam1, colGroup, type, cardF, tb, cr) as
( select numColumns, name, colGroupColNo, type, cardF
, tbName, tbOwner
from sysibm.sysColDist
where (tbOwner, tbName) in (select * from t)
union all select 1, name, '', 'Col'
, colCardF, tbName, tbCreator
from sysibm.sysColumns
where (tbCreator, tbName) in (select * from t)
)
select * from d
;
explain plan set queryno = 7 for
with d (cols, nam1, colGroup, type, cardF, tb, cr) as
( select numColumns, name, colGroupColNo, type, cardF
, tbName, tbOwner
from sysibm.sysColDist
union all select 1, name, '', 'Col'
, colCardF, tbName, tbCreator
from sysibm.sysColumns
)
select * from d
where cr = 'OA1P' and tb = 'TBP205A1'
;
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
;;;;
with t (cr, tb) as
(
select 'OA1P', 'TBP205A1' from sysibm.sysDummy1
)
, d (cols, nam1, colGroup, type, cardF, tb, cr) as
( select numColumns, name, colGroupColNo, type, cardF
, tbName, tbOwner
from sysibm.sysColDist
where (tbOwner, tbName) in (select * from t)
union all select 1, name, '', 'Col'
, colCardF, tbName, tbCreator
from sysibm.sysColumns
where (tbCreator, tbName) in (select * from t)
)
select * from d
;x;
,
case when numColumns = 1 then name
else ( select name || '>' || hex(colNo) || ':' || varchar(colNo)
from sysibm.sysColumns c
where c.tbCreator = cd.tbOwner
and c.tbName = cd.tbName
and hex(c.colNo) = right('FFFF'||hex(cd.colGroupColNo),4)
)
end lastCol
, type, cardF, hex(colgroupcolno) colGroup
, tbOwner cr, tbName tb
from sysibm.sysColDist cd
-- where type = 'C'
union all select 1 cols
, name || '>' || hex(colNo) || ':' || varchar(colNo) lastCol
, 'col' type
, colCardF cardF, hex(colNo) colGroup
, tbCreator cr, tbName tb
from sysibm.sysColumns
)
,ith c1 as
( select numColumns cols,
case when numColumns = 1 then name
else ( select name || '>' || hex(colNo) || ':' || varchar(colNo)
from sysibm.sysColumns c
where c.tbCreator = cd.tbOwner
and c.tbName = cd.tbName
and hex(c.colNo) = right('FFFF'||hex(cd.colGroupColNo),4)
)
end lastCol
, type, cardF, hex(colgroupcolno) colGroup
, tbOwner cr, tbName tb
from sysibm.sysColDist cd
-- where type = 'C'
union all select 1 cols
, name || '>' || hex(colNo) || ':' || varchar(colNo) lastCol
, 'col' type
, colCardF cardF, hex(colNo) colGroup
, tbCreator cr, tbName tb
from sysibm.sysColumns
)
select smallint(cols) cols
, substr(lastCol, 1, 24)
, type
, fosFmte7(cardf), colGroup, cr, tb
from c1
where cr = 'OA1P' and tb = 'TBP205A1'
order by cr, tb, type, colGroup || x'0000'
; x;
???????
select
( select substr(name || '>' || hex(colNo) || ':' || varchar(colNo)
, 1, 24)
from sysibm.sysColumns c
where c.tbCreator = cd.tbOwner
and c.tbName = cd.tbName
and hex(c.colNo) = right('FFFF'||hex(cd.colGroupColNo),4)
) "lastCol"
, cardF, hex(colgroupcolno)
, name
, cd.*
from sysibm.sysColDist cd
where tbowner = 'OA1T' and tbName = 'TRP303A2'
and type = 'C'
-- and colValue like x'000000' || '%'
order by tbOwner, tbName,
colGroupColNo || x'0000'
;x;
select hex(colValue), hex(colgroupcolno), hex(lowvalue), c.*
from sysibm.sysColDist c
where tbowner = 'OA1T' and tbName = 'TRP303A2'
and type = 'C'
-- and colValue like x'000000' || '%'
;x;
select count(*), tbOwner, tbName, name, type, COLGROUPCOLNO,QUANTILENO
-- ,colvalue -- , lowvalue, hex(colValue)
from sysibm.sysColDist c
-- where tbowner = 'OA1T' and tbName = 'TCK051A1'
group by tbOwner, tbName, name, type, COLGROUPCOLNO,QUANTILENO
-- ,colvalue -- , lowvalue
order by 1 desc
fetch first 100 rows only
with ur
;;;
select char(strip(tbOwner)||'.'||strip(tbName)||'.'||strip(name), 40),
frequency, frequencyf, cardf,
numColumns, hex(COLGROUPCOLNO), c.*
where tbOwner = 'A540769' and tbName = 'TTR009A1'
;
select colNo, name
from sysibm.sysColumns
where tbCreator = 'A540769' and tbName = 'TTR009A1'