zOs/SQL/EXPLAIWI
--- versionen, compiles und explains eines packages -------------------
select *
from cmnbatch.plan_view0
where collid = 'WI'
and prog = 'YWIESOQ'
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 = 'WI'
and progName = 'YWIESOQ'
and bind_time = '2014-04-20-03.53.16.805962'
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;x;
select *
from cmnbatch.plan_viewPred
where collid = 'MF'
and progName = 'MF7010'
and bind_time = '2011-08-30-14.14.59.234601'
order by collid, progName, applName, bind_time,
queryNo, qBlockNo, planno,
stage, predNo
with ur
;
select count(*), WI10500
from oa1p.tWI105A1004
group by WI10500
order by 1 desc
fetch first 1000 rows only
with ur
;x;
select count(*), WI10500, WI10501T, WI10501D, WI10501L, WI10501G
from oa1p.tWI105A1004
group by WI10500, WI10501T, WI10501D, WI10501L, WI10501G
order by 1 desc
fetch first 1000 rows only
with ur
;x;
select count(*), WI10501T, WI10501D, WI10501L, WI10501G
from oa1p.tWI105A1004
group by WI10501T, WI10501D, WI10501L, WI10501G
order by 1 desc
fetch first 1000 rows only
with ur
;x;
--- 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
SELECT
DISTINCT WI10304G ,
WI10501V ,
WI10501B ,
WI10302CIF ,
WI10302BK ,
WI10300 ,
WI10302DA ,
WI10500V ,
WI103D150301 ,
WI10505 ,
WI10518C ,
WI103D150305 ,
WI10305 ,
WI10307G ,
WI10307A
FROM
oa1p.TWI103A1003
JOIN
oa1p.TWI105A1003
ON WI10500 = WI10300
AND WI10501T = WI10301T
AND WI10501D = WI10301D
AND WI10501L = WI10301L
AND WI10501G = WI10301G
WHERE
WI10301T = ?
AND WI10301D = ?
AND WI10301L = ?
AND WI10301G = ?
AND WI10301GK = '01'
ORDER BY
WI10501V ,
WI10501B WITH UR
;
explain plan set queryno = 4 for
SELECT
DISTINCT WI10304G ,
WI10501V ,
WI10501B ,
WI10302CIF ,
WI10302BK ,
WI10300 ,
WI10302DA ,
WI10500V ,
WI103D150301 ,
WI10505 ,
WI10518C ,
WI103D150305 ,
WI10305 ,
WI10307G ,
WI10307A
FROM
oa1p.TWI103A1004
JOIN
oa1p.TWI105A1004
ON WI10500 = WI10300
AND WI10501T = WI10301T
AND WI10501D = WI10301D
AND WI10501L = WI10301L
AND WI10501G = WI10301G
WHERE
WI10301T = ?
AND WI10301D = ?
AND WI10301L = ?
AND WI10301G = ?
AND WI10301GK = '01'
ORDER BY
WI10501V ,
WI10501B WITH UR
;
select * from plan_view1
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;rollback
;x;
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 = 'WI'
and prog = 'WI8710'
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 = 'WI'
and progName = 'WI8710'
and bind_time in ('2012-11-10-00.11.55.137885'
,'2012-11-09-20.22.41.252382'
,'2012-08-11-00.28.08.942942'
)
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;;;;;;;
set current schema cmnbatch;
-- versionen, Compiles und Binds pro Programm
with e as
(
select count(*) c, progName pg, version vr, collid co, bind_time bt,
sum(case when opthint = '' then 0 else 1 end) cOpt
from cmnBatch.plan_table
group by progName, version, collid, bind_time
)
select substr(e.pg, 1, 8) pg, date(p.pcTimeStamp) "preComp",
date(e.bt) "bind", c "#plan_t", cOpt "#hints", vr,
e.*, p.*
from e
full outer join sysibm.sysPackage p
on e.co = p.collid and e.pg = p.name and e.vr = p.version
where e.pg = 'WI8710'
order by e.pg, p.pcTimeStamp desc, e.bt desc
with ur
;
select * from cmnBatch.plan_view1
where progName in ('WI8710') -- and collid = 'WP'
-- and bind_time in ('2011-04-12-14.09.51.446149'
-- ,'2011-04-11-17.07.54.101644'
-- ,'2011-02-11-23.34.35.249197'
-- )
and opthint <> ''
-- and version = 'DBH000012659B91C5F'
and queryno = 269
and qBlockNo = 1 and planNo = 1
order by bind_time desc,
applname, progname, queryNo, qblockno, planno, mixOpSeq
;;;
set current sqlid = 'A540769';
select * from cmnbatch.plan_view2
where progname = 'WI8710'
-- and version in ('WS8B000786', 'WS8B000325')
-- and queryno in ( 241)
and opthint <> ''
order by applname, progname, queryNo, timestamp, qblockno, planno
;
;;;
--- 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
SELECT DISTINCT C.WI10001T,
C.WI10001D, C.WI10001L, C.WI10001G, C.WI10001V, C.WI10001B, C.WI10002
FROM oa1p.TWI100A1 C INNER JOIN
(SELECT A.WI11701T AS TERM, A.WI11701D AS
ADATE, A.WI11701L AS ALNR, A.WI11701G AS GANR, A.WI11701V AS VVNR,
A.WI11701B AS VBNR FROM oa1p.TWI117A1 A INNER JOIN
(SELECT MAX (WI11701I) AS
MAXI, B.WI11701T AS TERM, B.WI11701D AS ADATE, B.WI11701L AS ALNR,
B.WI11701G AS GANR, B.WI11701V AS VVNR, B.WI11701B AS VBNR FROM
oa1p.TWI117A1 B
WHERE B.WI11701R = 'B' GROUP BY B.WI11701T, B.WI11701D,
B.WI11701L, B.WI11701G, B.WI11701V, B.WI11701B) AS TEMPTAB ON
A.WI11701T = TEMPTAB.TERM AND A.WI11701D = TEMPTAB.ADATE AND A.WI11701L
= TEMPTAB.ALNR AND (A.WI11701G = TEMPTAB.GANR OR A.WI11701G = '') AND
(A.WI11701V = TEMPTAB.VVNR OR A.WI11701V = '') AND (A.WI11701B =
TEMPTAB.VBNR OR A.WI11701B = '') AND A.WI11701I = TEMPTAB.MAXI WHERE
A.WI11703 = 'Y') AS TEMPTAB2 ON TEMPTAB2.TERM = C.WI10001T AND
TEMPTAB2.ADATE = C.WI10001D AND TEMPTAB2.ALNR = C.WI10001L AND
(TEMPTAB2.GANR = C.WI10001G OR TEMPTAB2.GANR = '') AND (TEMPTAB2.VVNR =
C.WI10001V OR TEMPTAB2.VVNR = '') AND (TEMPTAB2.VBNR = C.WI10001B OR
TEMPTAB2.VBNR = '') FOR FETCH ONLY WITH UR
;
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
;;;;
set current sqlid = 'A540769';
delete from A540769.plan_table;
explain plan set queryno = 0 for
DELETE
FROM oa1p.TWI020A1
WHERE WI02040T < TIMESTAMP(CURRENT TIMESTAMP - 21 DAYS)
AND WI02001S IN ('D','P','R')
;
explain plan set queryno = 5 for
select count(*), min(WI02025d)
FROM oa1p.TWI020A1
WHERE WI02040T < TIMESTAMP(CURRENT TIMESTAMP - 21 DAYS)
AND WI02001S IN ('D','P','R')
;
select * from plan_view1
order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
rollback
;
select current timestamp from sysibm.sysDummy1;
select count(*), min(WI02025d)
FROM oa1p.TWI020A1
WHERE WI02040T < TIMESTAMP(CURRENT TIMESTAMP - 21 DAYS)
AND WI02001S IN ('D','P','R')
with ur
;
select current timestamp from sysibm.sysDummy1;
;;;
delete from A540769.plan_table;
insert into A540769.plan_table
select * from cmnbatch.plan_Table
where collid = 'WI'
and progName = 'WI8710'
and bind_time = '2012-08-11-00.28.08.942942'
and hint_used = 'HBD1'
;
update A540769.plan_table
set queryno = 224, version = 'WS8I000685'
, bind_time = '2012-11-09-23.00.00' , opthint = 'HBD1'
, hint_used = ''
;
select * from A540769.plan_View1
where collid = 'WI'
and progName = 'WI8710'
and bind_time = '2012-11-09-23.00.00'
;
delete from cmnBatch.plan_table
where collid = 'WI'
and progName = 'WI8710'
and bind_time = '2012-11-09-23.00.00'
;
insert into cmnbatch.plan_table
select * from A540769.plan_Table
;
commit
;