zOs/SQL/EXPLAIWM
select *
from cmnbatch.plan_view0
where -- collid = 'YY' and
prog in ( '?YWM51E', 'YYWM05E')
and version = 'WSIN000114'
order by pcBi desc, bind_Time desc
with ur
;
select *
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where -- collid = 'YY' and
progName in ( '?YWM51E' , 'YYWM05E')
and bind_time in('2013-08-08-12.01.22.006192'
,'1012-03-28-09.10.55.577306'
)
order by progName, version,
queryno,
bind_time desc,
qBlockNo, planno, mixopSeq
with ur
; x;
-- keys eines indexes in der form a<b>c|d<
-- a,b,c,d keys
-- <>| ascending, descending, random
with k0 (cr, ix, keys, colCount, kCnt) as
-- start selektiere Indexe
( select creator, name, -- start selektiere Indexe
cast('' as varchar(500) ccsid unicode), colCount, 0
from sysibm.sysIndexes
where creator = 'OA1T' and tbName like 'TWM005A1'
-- Rekursion: nächster key
union all select cr, ix, keys || strip(q.colName)
|| case when ordering = 'A' then '<'
when ordering = 'D' then '>'
when ordering = 'R' then '|'
else '?' || ordering || '?' end,
k0.colCount, kCnt + 1
from k0
join sysibm.sysKeys q
on q.ixCreator = k0.cr and q.ixName = k0.ix
and q.colSeq = kCnt + 1
where kCnt < 9999
)
, k as
(
select cr, ix, colCount , keys
from k0
where colCount = kCnt
)
select substr(ix, 1, 8) ix, smallint(colCount), keys
from k
order by ix
;
-- keys eines indexes in der form a<b>c|d<
-- a,b,c,d keys
-- <>| ascending, descending, random
with k0 (cr, ix, keys, colCount, kCnt) as
-- start selektiere Indexe
( select creator, name, -- start selektiere Indexe
cast('' as varchar(500) ccsid unicode), colCount, 0
from sysibm.sysIndexes
where creator = 'OA1T' and tbName like 'TWM005A1'
-- Rekursion: nächster key
union all select cr, ix, keys || strip(q.colName)
|| case when ordering = 'A' then '<'
when ordering = 'D' then '>'
when ordering = 'R' then '|'
else '?' || ordering || '?' end,
k0.colCount, kCnt + 1
from k0
join sysibm.sysKeys q
on q.ixCreator = k0.cr and q.ixName = k0.ix
and q.colSeq = kCnt + 1
where kCnt < 9999
)
, k as
(
select cr, ix, colCount , keys
from k0
where colCount = kCnt
)
select queryNo qn, substr(ix, 1, 8) ix,
mc_o || '/' || substr(strip(char(colCount)), 1, 1) "mc O i",
substr(keys, 1, 30) keys, v.*
from cmnbatch.plan_view1 v
left join k
on accessName = ix
where -- collid = 'YY'
progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-04-06-16.29.25.501534'
,'2012-04-06-16.30.46.232929'
,'2012-04-20-10.30.31.730896'
,'2012-04-20-10.34.20.522454'
)
order by progName, version,
-- mod(queryno, 100),
queryno,
bind_time desc,
qBlockNo, planno, mixopSeq
with ur
;
--- ausgewählte Explains analysieren ---dbtf---
select index, count(*), min(mc_o), max(mc_o)
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where -- collid = 'YY' and
progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-04-05-17.01.57.402640'
,'2012-03-28-09.10.55.577306'
)
group by index, bind_time
order by index, bind_time desc
with ur
;
select *
from cmnbatch.plan_view1
where -- collid = 'YY'
progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-04-05-17.01.57.402640'
,'2012-03-28-09.10.55.577306'
)
order by progName, version,
queryno,
bind_time desc,
qBlockNo, planno, mixopSeq
with ur
;
select *
from cmnbatch.plan_viewPred
where -- collid = 'YY'
progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-04-05-17.01.57.402640'
,'2012-03-28-09.10.55.577306'
)
order by progName, version,
queryNo,
bind_time desc,
qBlockNo, planno,
stage, predNo
;;;;
--- ausgewählte Explains analysieren ---dbaf---
select index, count(*), min(mc_o), max(mc_o)
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-30-14.34.00.445119'
,'2012-03-30-08.15.45.557519'
)
group by index, bind_time
order by index, bind_time desc
with ur
;
select *
from cmnbatch.plan_view1
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-30-14.34.00.445119'
,'2012-03-30-08.15.45.557519'
)
order by collid, progName, version,
queryno,
bind_time desc,
qBlockNo, planno, mixopSeq
with ur
;
select *
from cmnbatch.plan_viewPred
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-30-14.34.00.445119'
,'2012-03-30-08.15.45.557519'
)
order by collid, progName, version,
queryNo,
bind_time desc,
qBlockNo, planno,
stage, predNo
;;;;
--- 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 = 427 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
FROM oa1a.TWM005A1
WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND TWMD2 = ?
;
explain plan set queryno = 527 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
FROM oa1a.TWM005A1
WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? || '' AND TWMD2 = ?
;
explain plan set queryno = 627 for
with DSN_INLINE_opt_Hint( -- table_creator, table_name,
access_type, access_creator, access_name) AS
( values ('INDEX', 'OA1A', 'IWM005AG')
)
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
FROM oa1a.TWM005A1
WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND TWMD2 = ?
;
explain plan set queryno = 71 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
FROM oa1a.TWM005A1
WHERE
D$GUEA <= ?
AND D$GUEB >= ?
AND CSTAT = ? || ''
AND CDEFAULT || '' BETWEEN ? AND ?
AND CTYP = ?
AND CGA = ?
AND TWMD1 = ? || '' QUERYNO 064
-- WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
--CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NBU = ? AND NDEP = '' !!
--'' AND TWMD1 = '' !! '' QUERYNO 071
;
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
;;;;
--- ausgewählte Explains analysieren
select index, count(*), min(mc_o), max(mc_o)
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-28-09.10.52.339989'
,'2012-03-28-09.10.55.577306'
)
group by index
with ur
;
select *
from cmnbatch.plan_view0
where collid = 'YY'
and prog in ( 'YYWM51E' , 'YYWM05E')
order by pcBi desc, bind_Time desc
with ur
;;;
--- ausgewählte Explains analysieren
select index, count(*), min(mc_o), max(mc_o)
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-28-09.10.52.339989'
,'2012-03-28-09.10.55.577306'
)
group by index
with ur
;
select * --dbtf
from cmnbatch.plan_view1
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-28-09.10.52.339989'
,'2012-03-28-09.10.55.577306'
)
order by collid, progName, version,
queryno,
bind_time desc,
qBlockNo, planno, mixopSeq
with ur
;
select *
from cmnbatch.plan_viewPred
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-28-09.10.52.339989'
,'2012-03-28-09.10.55.577306'
)
order by collid, progName, version, bind_time,
queryNo, qBlockNo, planno,
stage, predNo
;
;;,
--- ausgewählte Explains analysieren
select index, count(*), min(mc_o), max(mc_o)
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-22-09.55.02.438577'
,'2012-03-22-09.54.58.171886'
)
group by index
with ur
;
select *
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-22-09.55.02.438577'
,'2012-03-22-09.54.58.171886'
)
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;
select *
from cmnbatch.plan_viewPred
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-22-09.55.02.438577'
,'2012-03-22-09.54.58.171886'
)
order by collid, progName, version, bind_time,
queryNo, qBlockNo, planno,
stage, predNo
;
;;,
--- 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 = 71 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
FROM oa1t.TWM005A1
WHERE
D$GUEA <= ?
AND D$GUEB >= ?
AND CSTAT = ? || ''
AND CDEFAULT || '' BETWEEN ? AND ?
AND CTYP = ?
AND CGA = ?
AND TWMD1 = ? || '' QUERYNO 064
-- WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
--CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NBU = ? AND NDEP = '' !!
--'' AND TWMD1 = '' !! '' QUERYNO 071
;
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
;;;;
--- versionen, compiles und explains eines packages -------------------
select *
from cmnbatch.plan_view0
where collid = 'YY'
and prog in ( 'YYWM51E' , 'YYWM05E')
order by pcBi desc, bind_Time desc
with ur
;
--- ausgewählte Explains analysieren
select *
from cmnbatch.plan_view1
-- from cmnbatch.plan_view2
-- from cmnbatch.plan_view2Det
where collid = 'YY'
and progName in ( 'YYWM51E' , 'YYWM05E')
and bind_time in('2012-03-22-09.55.02.438577'
,'2012-03-22-09.54.58.171886'
)
and queryno = 64
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;
select *
from cmnbatch.plan_viewPred
where collid = 'YY'
and progName = 'YYWM51E'
and bind_time = '2012-03-09-11.43.48.725773'
order by collid, progName, applName, bind_time,
queryNo, qBlockNo, planno,
stage, predNo
with ur
;;;;;
select *
from cmnbatch.dsn_filter_table
where collid = 'YY'
and progName = 'YYWM51E'
and explain_time = '2012-03-09-11.43.48.725773'
and queryno = 1
order by
queryNo, qBlockNo, planno,
stage, predNo
;
select current timestamp from sysibm.sysDummy1;
with v3 as
( select 3 keys, count(*) cnt, CKAT, nkto, d$gueb
from oa1a.twm005A1
group by CKAT, nkto, d$gueb
)
, v2 as
( select 2 keys, sum(cnt) cnt, CKAT, nkto
from v3
group by CKAT, nkto
)
, v1 as
( select 1 keys, sum(cnt) cnt, CKAT
from v2
group by CKAT
)
, v (keys, cnt, val) as
(
( select keys, cnt
, char(ckat) || ' + ' || char(nkto) || ' + ' || char(d$gueb)
from v3
order by cnt desc
fetch first 10 rows only )
union all (select keys, cnt
, char(ckat) || ' + ' || char(nkto)
from v2
order by cnt desc
fetch first 10 rows only
)
union all ( select keys, cnt
, char(ckat)
from v1
order by cnt desc
fetch first 10 rows only
) )
select *
from v
order by keys, cnt desc
with ur
;
select current timestamp from sysibm.sysDummy1;
with v (keys, cnt, val) as
( (select 3, count(*)
, char(ckat) || ' + ' || char(nkto) || ' + ' || char(d$gueb)
from oa1a.twm005A1
group by CKAT, nkto, d$gueb
order by 2 desc
fetch first 10 rows only
) union all (select 2, count(*)
, char(ckat) || ' + ' || char(nkto)
from oa1a.twm005A1
group by CKAT, nkto
order by 2 desc
fetch first 10 rows only
) union all (select 1, count(*)
, char(ckat)
from oa1a.twm005A1
group by CKAT
order by 2 desc
fetch first 10 rows only
) )
select *
from v
order by keys, cnt desc
with ur
;
select current timestamp from sysibm.sysDummy1;
;;;;
select *
from cmnBatch.plan_view1
where progName in ( 'YYWM51E') --, 'YYWM05E')
-- and version = 'WSIN000086'
and queryNo = 71
-- and optHint = 'WK1'
-- and bind_time = '2011-06-16-18.06.33.125938'
-- and bind_time in ('2011-01-19-14.34.47.972473',
-- '2010-12-17-11.16.20.037477')
-- order by index, mc_O
order by applname, progname, version desc, bind_time desc ,
queryNo, qblockno, planno, mixOpSeq
; ;;
set current sqlid = 'A540769';
--select hex(X'C2E4D9C1'), X'C2E4D9C1'
-- from sysibm.sysDummy1;;;
delete from plan_table;
explain plan for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
from
OA1T.TWM005A1 WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NBU = '' !! '' AND NDEP =
'' !! '' AND TWMD1 = ? QUERYNO 070
;
explain plan set queryno = 1070 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
from
OA1T.TWM005A1 WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NBU || '' = '' AND NDEP || ''
= '' AND TWMD1 = ? QUERYNO 070
;
explain plan set queryno = 1270 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
from
OA1T.TWM005A1 WHERE D$GUEA <= current timestamp
AND D$GUEB >= current timestamp AND CSTAT = 'A' and
CDEFAULT BETWEEN ' ' AND '9' AND
CTYP = X'C2E4D9C1'
AND NBU = '' !! '' AND NDEP =
'' !! '' AND TWMD1 = 'ab' QUERYNO 070
;
explain plan set queryno = 1370 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
from
OA1T.TWM005A1 WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NBU = '' AND NDEP
= '' AND TWMD1 = ? QUERYNO 070
;
explain plan set queryno = 9070 for
-- SQL in stmt: 3332
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG,
TGRUND, UWM005, D$GUEA, D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF,
NDEP, NBESTDEP, NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE,
CTIT, CTITLAND, NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5,
TWMD6, TWMD7, TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1,
TWM2, TWM3, TWM4, TWM5, TWM6
FROM oa1t.TWM005A1
WHERE D$GUEA <= ? AND
D$GUEB >= ? AND CSTAT BETWEEN ? AND ? AND CDEFAULT BETWEEN ? AND ?
AND CTYP = ? AND NBU = '' !! '' AND NDEP = ? AND TWMD1 = ? FOR FETCH
ONLY QUERYNO 070
;
explain plan set queryno = 9170 for
-- SQL in stmt: 3332
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG,
TGRUND, UWM005, D$GUEA, D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF,
NDEP, NBESTDEP, NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE,
CTIT, CTITLAND, NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5,
TWMD6, TWMD7, TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1,
TWM2, TWM3, TWM4, TWM5, TWM6
FROM oa1t.TWM005A1
WHERE D$GUEA <= ? AND
D$GUEB >= ? AND CSTAT BETWEEN ? AND ? AND CDEFAULT BETWEEN ? AND ?
AND CTYP = ? AND NBU = '' !! '' AND NDEP = ? || ''
AND TWMD1 = ? FOR FETCH
ONLY QUERYNO 070
;
select *
from plan_view1
order by applname, progname, queryNo, qblockno, planno
;;;
explain plan for -- set queryno = 124 for
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
from
OA1T.TWM005A1 WHERE D$GUEA <= current timestamp
AND D$GUEB >= current timestamp
AND CSTAT = 'A'
and CDEFAULT BETWEEN ' ' AND '9'
AND CTYP = X'C2E4D9C1'
AND NBU = '' !! '' AND NDEP =
'' !! '' AND TWMD1 = '0499'
;
explain plan for -- set queryno = 224 for
SELECT count(*), sum(length(strip(TGRUND)))
from
OA1T.TWM005A1 WHERE D$GUEA <= current timestamp
AND D$GUEB >= current timestamp
AND CSTAT = 'A'
and CDEFAULT BETWEEN ' ' AND '9'
AND CTYP = X'C2E4D9C1'
AND NBU = '' !! '' AND NDEP =
'' !! '' AND TWMD1 = '0499'
;
with g2 as
(
select count(*) c, twmd1 , cTyp
from OA1T.twm005a1
group by twmd1 , cTyp
) , g (c, twmd1 , cTyp) as
(
(select * from g2 order by c desc fetch first 100 rows only)
union all (select sum(c), twmd1 , '---' from g2
group by twmd1 order by 1 desc fetch first 100 rows only)
union all (select sum(c), '---', cTyp from g2
group by cTyp order by 1 desc fetch first 100 rows only)
union all select sum(c), '---', '---' from g2
)
select * from g
order by 1 desc
with ur
;;;
select current timestamp from sysibm.sysdummy1;
SELECT PERF, D$ERF, PLMUT, D$LMUT, TAUFTRG, TGRUND, UWM005, D$GUEA,
D$GUEB, CSTAT, CDEFAULT, CKAT, CTYP, NBU, NCIF, NDEP, NBESTDEP,
NBESTVAL, NBESTSEQ, CGA, CGAPH, NAUFTR, NVALOR, CWAE, CTIT, CTITLAND,
NDEPST, NDEPLAND, TWMD1, TWMD2, TWMD3, TWMD4, TWMD5, TWMD6, TWMD7,
TWMD8, IS40KEY, NKTO, CWAE2, NCIF2, CWM1, CWM2, TWM1, TWM2, TWM3, TWM4,
TWM5, TWM6
from
OA1T.TWM005A1 WHERE D$GUEA <= current timestamp
AND D$GUEB >= current timestamp AND CSTAT = 'A' and
CDEFAULT BETWEEN ' ' AND '9' AND
CTYP = X'C2E4D9C1'
AND NBU = '' !! '' AND NDEP =
'' !! '' AND TWMD1 = 'ab' QUERYNO 070
;
select current timestamp from sysibm.sysdummy1;
;;
with g2 as
(
select count(*) c, nValor, cTyp
from OA1T.twm005a1
group by nValor, cTyp
) , g (c, nValor, cTyp) as
(
(select * from g2 order by c desc fetch first 100 rows only)
union all (select sum(c), nValor, '---' from g2
group by nValor order by 1 desc fetch first 100 rows only)
union all (select sum(c), '---', cTyp from g2
group by cTyp order by 1 desc fetch first 100 rows only)
union all select sum(c), '---', '---' from g2
)
select * from g
order by 1 desc
with ur
;;;
select current timestamp from sysibm.sysDummy1;
SELECT count(*), sum(length(strip(TGRUND)))
from
OA1T.TWM005A1 WHERE D$GUEA <= current timestamp
AND D$GUEB >= current timestamp
AND CSTAT = 'A'
and CDEFAULT BETWEEN ' ' AND '9'
AND CTYP = 'BURA'
AND NBU = '' !! '' AND NDEP =
'' !! '' -- AND TWMD1 = '0499'
;
select current timestamp from sysibm.sysDummy1;
;;;
select count(*) c, uwm005
from oa1p.twm005a1
group by uwm005
order by 1 desc
fetch first 100 rows only
with ur
;;;
select count(*) c, nkto
from oa1p.twm005a1
group by nkto
order by 1 desc
fetch first 100 rows only
with ur
;;;
with g2 as y
(
select count(*) c, nCif, cKat
from oa1p.twm005a1
group by nCif, cKat
) , g (t, c, nCif, cKat) as
(
(select 'ck', g2.* from g2 order by c desc fetch first 100 rows only)
union all (select '-k', sum(c), nCif, '---' from g2
group by nCif order by 1 desc fetch first 100 rows only)
union all (select 'c-', sum(c), '---', cKat from g2
group by cKat order by 1 desc fetch first 100 rows only)
union all select '--', sum(c), '---', '---' from g2
)
select * from g
order by 1 , 2 desc
with ur
;;;
with g2 as
(
select count(*) c, nValor, cTyp
from oa1p.twm005a1
group by nValor, cTyp
) , g (c, nValor, cTyp) as
(
(select * from g2 order by c desc fetch first 100 rows only)
union all (select sum(c), nValor, '---' from g2
group by nValor order by 1 desc fetch first 100 rows only)
union all (select sum(c), '---', cTyp from g2
group by cTyp order by 1 desc fetch first 100 rows only)
union all select sum(c), '---', '---' from g2
)
select * from g
order by 1 desc
with ur
;;;
with g2 as
(
select count(*) c, nBu, cTyp
from oa1p.twm005a1
group by nBu, cTyp
) , g (c, nBu, cTyp) as
(
(select * from g2 order by c desc fetch first 100 rows only)
union all (select sum(c), nBu, '---' from g2
group by nBu order by 1 desc fetch first 100 rows only)
union all (select sum(c), '---', cTyp from g2
group by cTyp order by 1 desc fetch first 100 rows only)
union all select sum(c), '---', '---' from g2
)
select * from g
order by 1 desc
with ur
;;;
with g2 as
(
select count(*) c, nCif, ndep
from oa1p.twm005a1
group by nCif, ndep
) , g (c, nCif, ndep) as
(
(select * from g2 order by c desc fetch first 100 rows only)
union all (select sum(c), nCif, '---' from g2
group by nCif order by 1 desc fetch first 100 rows only)
union all (select sum(c), '---', nDep from g2
group by nDep order by 1 desc fetch first 100 rows only)
union all select sum(c), '---', '---' from g2
)
select * from g
order by 1 desc
with ur
;;;
with g2 as
(
select count(*) c, nCif, cStat
from oa1p.twm005a1
group by nCif, cStat
) , g (c, nCif, cStat) as
(
(select * from g2 order by c desc fetch first 100 rows only)
union all (select sum(c), nCif, '---' from g2
group by nCif order by 1 desc fetch first 100 rows only)
union all (select sum(c), '---', cStat from g2
group by cStat order by 1 desc fetch first 100 rows only)
union all select sum(c), '---', '---' from g2
)
select * from g
order by 1 desc
with ur
;;;
select * from g
;;;
select count(*), TWMD1
from oa1p.twm005a1
group by TWMD1
order by 1 desc
fetch first 100 rows only
with ur; ;
select count(*), nCif, ctyp
from oa1p.twm005a1
group by nCif, cTyp
order by 1 desc
fetch first 100 rows only
with ur;
select count(*), nCif
from oa1p.twm005a1
group by nCif
order by 1 desc
fetch first 100 rows only
with ur; ;
select count(*), nCif, ndep
from oa1p.twm005a1
group by nCif, nDep
order by 1 desc
fetch first 100 rows only
with ur; ;
select count(*), nCif
from oa1p.twm005a1
group by nCif
order by 1 desc
fetch first 100 rows only
with ur; ;
select count(distinct CTYP || nCif || ndep ) "cTyp,nCif,nDep",
count(distinct CTYP || ndep ) "cTyp, ,nDep"
from oa1p.twm005a1
-- group by CTYP, nCif, ndep
-- order by 1 desc
-- fetch first 100 rows only
with ur
;
select count(*), UWM005, CSTAT, CDEFault
from oa1p.twm005a1
group by UWM005, CSTAT, CDEFault
order by 1 desc
with ur
;;
select INDEX, count(*), min(mc_o), max(mc_o)
from cmnBatch.plan_view1
where progName = 'YYWM05E' and version = 'WSIN000086'
-- and queryNo = 24
-- and optHint = 'WK1'
and table = 'TWM005A1'
and bind_time = '2010-11-22-12.00.23.698521'
group by index
order by 1
;x
select *
from cmnBatch.plan_view1
where progName = 'YYWM51E' and version = 'WSIN000086'
-- and queryNo = 24
-- and optHint = 'WK1'
and bind_time = '2010-11-22-11.42.58.58655'
order by applname, progname, queryNo, qblockno, planno
;x
set current sqlid = 'A540769';
delete from plan_table;
insert into plan_table
select * from s100447.tOptHint
where progName = 'YYWM51E' and version = 'WSIN000086'
;
update plan_table set progName = 'YYWM05E';
delete
from s100447.tOpthint
where progName = 'YYWM05E' and version = 'WSIN000086'
;
insert into s100447.tOptHint
select * from plan_table;
delete
from cmnbatch.plan_table
where progName = 'YYWM05E' and version = 'WSIN000086'
;
insert into cmnBatch.plan_table
select * from plan_table;
commit ; x
-- and queryNo = 24
order by applname, progname, queryNo, qblockno, planno
select *
from cmnBatch.plan_view1
where progName = 'YYWM05E' and version = 'WSIN000086'
-- and queryNo = 24
order by applname, progname, queryNo, qblockno, planno
;
select *
from s100447.vOpthint_view1
where progName = 'YYWM05E' and version = 'WSIN000086'
-- and queryNo = 24
order by applname, progname, queryNo, qblockno, planno
; x
select *
from cmnbatch.plan_view1
where progName = 'YYWM51E' and version = 'WSIN000086'
and queryNo = 24
order by applname, progname, queryNo, qblockno, planno
;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 24 for
select *
from oa1p.TWM005A1
WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NVALOR = ? QUERYNO 024
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
update plan_table ---> richtige Keys setzen
set queryno = 24
, collid = 'YY'
, progname = 'YYWM51E'
, version = 'WSIN000086'
, optHint = 'WK1'
;
update plan_table ---> richtige access
set accessName = 'IWM005A9'
;
delete from cmnBatch.plan_table
where opthint = 'WK1' and progName = 'YYWM05E'
and version = 'WSIN000086'
and queryno = 24
;
insert into cmnBatch.plan_table
select * from A540769.plan_table
;
select * from cmnBatch.plan_view1
where opthint = 'WK1' and progName = 'YYWM51E'
order by applname, progname, queryNo, qblockno, planno
;
delete from s100447.tOpthint
where opthint = 'WK1' and progName = 'YYWM05E'
and version = 'WSIN000086'
and queryno = 24
;
insert into s100447.tOpthint
select * from A540769.plan_table;
--rollback;
select *
from cmnbatch.plan_view1
where progName = 'YYWM51E' and version = 'WSIN000086'
and queryNo = 15
order by applname, progname, queryNo, qblockno, planno
;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 15 for
select *
from oa1p.TWM005A1
WHERE D$GUEA <= ? AND D$GUEB >= ? AND CSTAT = ? AND
CDEFAULT BETWEEN ? AND ? AND CTYP = ? AND NBU = ? QUERYNO 015
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
update plan_table ---> richtige Keys setzen
set queryno = 15
, collid = 'YY'
, progname = 'YYWM51E'
, version = 'WSIN000086'
, optHint = 'WK1'
;
update plan_table ---> richtige access
set accessName = 'IWM005AB'
;
delete from cmnBatch.plan_table
where opthint = 'WK1' and progName = 'YYWM05E'
and version = 'WSIN000086'
and queryno = 15
;
insert into cmnBatch.plan_table
select * from A540769.plan_table
;
select * from cmnBatch.plan_view1
where opthint = 'WK1' and progName = 'YYWM51E'
order by applname, progname, queryNo, qblockno, planno
;
delete from s100447.tOpthint
where opthint = 'WK1' and progName = 'YYWM05E'
and version = 'WSIN000086'
and queryno = 15
;
insert into s100447.tOpthint
select * from A540769.plan_table;
commit;x;
rollback; x; --> commit falls es richtig kommt ein
;;
select bind_time, count(*), version
from cmnBatch.plan_table
where progName = 'YYWM51E' -- and version = 'WSIN000086'
and bind_time in ('2010-11-15-21.10.29.921543',
'2010-05-12-18.32.34.335450')
group by bind_time, version
order by 3 desc , 1 desc
;
select case when bind_time = '2010-11-15-21.10.29.921543'
then 'n' else 'o' end, v.*
from cmnBatch.plan_VIEW1 v
where progName = 'YYWM51E' -- and version = 'WSIN000086'
and bind_time in ('2010-11-15-21.10.29.921543',
'2010-05-12-18.32.34.335450')
order by applname, progname, queryNo
, 1 , qblockno, planno
; x
;x;
select bind_time, count(*), version
from cmnBatch.plan_table
where progName = 'YYWM05E' -- and version = 'WSIN000086'
-- and bind_time in ('2010-11-16-15.56.39.031094',
-- '2010-08-12-11.00.17.093367')
-- and queryNo = 21
group by bind_time, version
order by 3 desc , 1 desc
;x;
from
set current path = oa1p;
select SUBSTR(d.name, 1, 8), substr(c1.name, 1, 8),
substr(c2.name, 1, 8), substr(c3.name, 1, 8),
fosFmte7(cardf) "cardf",
numColumns, length(colGroupColNo), hex(colGroupColNo), d.*
from sysibm.syscoldist d
left join sysibm.syscolumns c1
on hex(substr(d.colGroupColNo, 1, 2))
= hex(c1.colno)
and c1.tbCreator = d.tbOwner
and c1.tbName = d.tbName
left join sysibm.syscolumns c2
on hex(substr(d.colGroupColNo, 3, 2))
= hex(c2.colno)
and c2.tbCreator = d.tbOwner
and c2.tbName = d.tbName
left join sysibm.syscolumns c3
on hex(substr(d.colGroupColNo, 5, 2))
= hex(c3.colno)
and c3.tbCreator = d.tbOwner
and c3.tbName = d.tbName
where tbOwner = 'OA1P' and d.tbName = 'TWM005A1'
and c1.name is not null
order by statstime desc
; x;
select *
from sysibm.syscolstats
where tbOwner like 'OA1P%' and tbName like 'TWM005%'
;
select *
from sysibm.syscoldist
where tbOwner like 'OA1P%' and tbName like 'TWM005%'
;
select *
from sysibm.syscoldiststats
where tbOwner like 'OA1P%' and tbName like 'TWM005%'
;
select *
from cmnBatch.plan_VIEW1
where progName = 'YYWM05E' and version = 'WSIN000086'
and queryNo = 21
order by applname, progname, queryNo, qblockno, planno
, bind_time desc
; x
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 21 for
select *
FROM oa1p.TWM005A1 WHERE D$GUEA <= ? AND
D$GUEB >= ? AND CSTAT BETWEEN ? AND ? AND CDEFAULT BETWEEN ? AND ?
AND CTYP = ? AND NCIF = ? AND NDEP = ? FOR FETCH ONLY QUERYNO 021
;
explain plan set queryno = 821 for
select *
FROM oa1p.TWM005A1 WHERE
CTYP = ? AND NCIF = ? AND NDEP = ? FOR FETCH ONLY QUERYNO 021
;
explain plan set queryno = 921 for
select *
FROM oa1p.TWM005A1 WHERE
NCIF = ? AND CTYP = ? AND NDEP = ? FOR FETCH ONLY QUERYNO 021
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
DELETE from plan_table where queryNo <> 21;
update plan_table ---> richtige Keys setzen
set queryno = 21
, collid = 'YY'
, progname = 'YYWM05E'
, version = 'WSIN000086'
, optHint = 'WK1'
;
update plan_table ---> richtige access
set accessName = 'IWM005A3'
;
delete from cmnBatch.plan_table
where opthint = 'WK1' and progName = 'YYWM05E'
and version = 'WSIN000086'
;
insert into cmnBatch.plan_table
select * from A540769.plan_table
;
select * from cmnBatch.plan_view1
where opthint = 'WK1' and progName = 'YYWM05E'
order by applname, progname, queryNo, qblockno, planno
;
delete from s100447.tOpthint
where opthint = 'WK1' and progName = 'YYWM05E'
and version = 'WSIN000086'
;
insert into s100447.tOpthint
select * from A540769.plan_table;
commit ;x;
rollback; x; --> commit falls es richtig kommt ein
;;
select *
from cmnbatch.plan_view1
where progName = 'YYWM51E' and version = 'WSIN000086'
and queryNo = 21
order by applname, progname, queryNo, qblockno, planno
; x
set current sqlid = 'A540769';
set current path = oa1p;
delete from plan_table;
explain plan set queryno = 21 for
select *
FROM oa1p.TWM005A1 WHERE D$GUEA <= ? AND
D$GUEB >= ? AND CSTAT BETWEEN ? AND ? AND CDEFAULT BETWEEN ? AND ?
AND CTYP = ? AND NCIF = ? AND NDEP = ? FOR FETCH ONLY QUERYNO 021
;
explain plan set queryno = 821 for
select *
FROM oa1p.TWM005A1 WHERE
CTYP = ? AND NCIF = ? AND NDEP = ? FOR FETCH ONLY QUERYNO 021
;
explain plan set queryno = 921 for
select *
FROM oa1p.TWM005A1 WHERE
NCIF = ? AND CTYP = ? AND NDEP = ? FOR FETCH ONLY QUERYNO 021
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback;
select SUBSTR(d.name, 1, 8), substr(c1.name, 1, 8),
substr(c2.name, 1, 8), substr(c3.name, 1, 8),
fosFmte7(cardf) "cardf",
numColumns, length(colGroupColNo), hex(colGroupColNo), d.*
from sysibm.syscoldist d
left join sysibm.syscolumns c1
on hex(substr(d.colGroupColNo, 1, 2))
= hex(c1.colno)
and c1.tbCreator = d.tbOwner
and c1.tbName = d.tbName
left join sysibm.syscolumns c2
on hex(substr(d.colGroupColNo, 3, 2))
= hex(c2.colno)
and c2.tbCreator = d.tbOwner
and c2.tbName = d.tbName
left join sysibm.syscolumns c3
on hex(substr(d.colGroupColNo, 5, 2))
= hex(c3.colno)
and c3.tbCreator = d.tbOwner
and c3.tbName = d.tbName
where tbOwner = 'OA1P' and d.tbName = 'TWM005A1'
order by statstime desc
; x;
select numColumns, length(colGroupColNo), hex(colGroupColNo), d.*
from sysibm.syscoldist d
where tbOwner = 'OA1P' and tbName = 'TWM005A1'
; x;
rollback;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
SELECT *
FROM OA1P.TWM005A1
WHERE D$GUEA <= CURRENT TIMESTAMP
AND D$GUEB >= CURRENT TIMESTAMP
AND CSTAT BETWEEN 'A' AND 'A'
AND CDEFAULT BETWEEN 'C' AND 'C'
AND CTYP = ? -- 'SPEX'
explain plan set queryno = 1 for
SELECT *
FROM OA1P.TWM005A1
WHERE
AND CSTAT BETWEEN 'A' AND 'A'
AND CDEFAULT BETWEEN 'C' AND 'C'
AND CTYP = ? -- 'SPEX'
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback;
select count(distinct CTYP || nCif || ndep ) "cTyp,nCif,nDep",
count(distinct CTYP || ndep ) "cTyp, ,nDep"
from oa1p.twm005a1
-- group by CTYP, nCif, ndep
-- order by 1 desc
-- fetch first 100 rows only
with ur
;
x;
set current sqlid = 'S100447';
update sysibm.sysColumns set
statstime = '0001-01-01-00.00.00.00000'
, colCardf = -1
where tbCreator = 'OA1P' and tbName like 'TWM005A1'
;
commit;
--delete
-- from sysibm.syscoldist
-- where tbOwner like 'OA1P%' and tbName like 'TWM005%'
--;
select *
from sysibm.syscolstats
where tbOwner like 'OA1P%' and tbName like 'TWM005%'
;
select *
from sysibm.syscoldist
where tbOwner like 'OA1P%' and tbName like 'TWM005%'
;
select *
from sysibm.syscoldiststats
where tbOwner like 'OA1P%' and tbName like 'TWM005%'
;
select *
from cmnBatch.plan_view1
where progName = 'YYWM51E' -- and version = 'WSIN000086'
-- and queryNo = 70
-- and optHint = 'WK1'
-- and bind_time = '2010-12-16-14.36.37.738425'
and (optHint <> '' or hint_Used <> '')
-- order by index, mc_O
order by applname, progname, queryNo, qblockno, planno
;x
with c as
(
select count(*) c, ctyp, nbu
from oa1t.twm005a1
where nbu <> ''
group by ctyp, nbu
)
select count(*) "ctypCnt", sum(c) tot, min(c) min, max(c) max,
min(ctyp || '-' || nbu), max(ctyp || '-' || nbu)
from c
group by floor(log10(c)*3)
order by 3 desc
with ur
;;;;;;
with c as
(
select count(*) c, ctyp
from oa1t.twm005a1
group by ctyp
)
select count(*) "ctypCnt", sum(c) tot, min(c) min, max(c) max,
min(ctyp), max(ctyp)
from c
group by floor(log10(c)*3)
order by 3 desc
with ur
;;;;;;