zOs/SQL/EXPLAICZ
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_FILTER_TABLE ;
delete from A540769.DSN_STRUCT_TABLE ;
delete from A540769.DSN_PREDICAT_TABLE ;
explain plan set queryno = 0 for
SELECT Q323.I_DCMNTN_ITEM
, Q323.I_DOCUMENT_T
, Q323.I_DCMNT_ARCHIVE_T
, Q323.D_LEGAL
FROM oa1t.VCZ431A1V Q431
, oa1t.VCZ323A1V Q323
WHERE Q431.I_OBJECT = ?
AND Q431.D_EFFECTIVE <= CURRENT DATE
AND Q431.D_END > CURRENT DATE
AND Q431.I_IDENTIFIER_T = 22
AND Q431.I_DCMNTN_ITEM = Q323.I_DCMNTN_ITEM
AND Q323.D_EFFECTIVE <= CURRENT DATE
AND Q323.D_END > CURRENT DATE
AND Q323.I_DOCUMENT_T IN (445, 446, 447, 448,
454, 455, 491, 492)
;
explain plan set queryno = 4 for
SELECT Q323.I_DCMNTN_ITEM, Q323.D_LEGAL,
Q323.D_EXPIRATION, Q323.D_EFFECTIVE, Q323.D_TIME_CREATED
FROM oa1t.VCZ431A1V Q431, oa1t.VCZ323A1V Q323
WHERE Q431.I_OBJECT = ? AND Q431.I_IDENTIFIER_T =
23 AND Q431.I_DCMNTN_ITEM = Q323.I_DCMNTN_ITEM AND Q431.D_EFFECTIVE <=
CURRENT DATE AND Q431.D_END > CURRENT DATE AND Q323.I_DOCUMENT_T = 341
AND Q323.D_EFFECTIVE <= CURRENT DATE AND Q323.D_END > CURRENT DATE AND
Q323.D_EXPIRATION >= CURRENT DATE ORDER BY Q323.D_LEGAL DESC,
Q323.D_EFFECTIVE DESC, Q323.D_TIME_CREATED DESC FOR FETCH ONLY
;
explain plan set queryno = 14 for
SELECT Q323.I_DCMNTN_ITEM, Q323.D_LEGAL,
Q323.D_EXPIRATION, Q323.D_EFFECTIVE, Q323.D_TIME_CREATED
FROM ( select q.*, smallint(341 + 0 * I_IDENTIFIER_T) kSI
from oa1t.VCZ431A1V q) Q431
join oa1t.VCZ323A1V Q323
on Q431.I_DCMNTN_ITEM = Q323.I_DCMNTN_ITEM
AND Q323.I_DOCUMENT_T = q431.kSI
WHERE Q431.I_OBJECT = ? AND Q431.I_IDENTIFIER_T =
23 AND Q431.D_EFFECTIVE <=
CURRENT DATE AND Q431.D_END > CURRENT DATE
AND Q323.D_EFFECTIVE <= CURRENT DATE AND Q323.D_END > CURRENT DATE AND
Q323.D_EXPIRATION >= CURRENT DATE ORDER BY Q323.D_LEGAL DESC,
Q323.D_EFFECTIVE DESC, Q323.D_TIME_CREATED DESC FOR FETCH ONLY
;
explain plan set queryno = 15 for
SELECT Q323.I_DCMNTN_ITEM, Q323.D_LEGAL,
Q323.D_EXPIRATION, Q323.D_EFFECTIVE, Q323.D_TIME_CREATED
from oa1t.VCZ431A1V Q431
join (select * from oa1t.VCZ323A1V
where I_DOCUMENT_T = 431) q323
on Q431.I_DCMNTN_ITEM = Q323.I_DCMNTN_ITEM
WHERE Q431.I_OBJECT = ? AND Q431.I_IDENTIFIER_T =
23 AND Q431.D_EFFECTIVE <=
CURRENT DATE AND Q431.D_END > CURRENT DATE
AND Q323.D_EFFECTIVE <= CURRENT DATE AND Q323.D_END > CURRENT DATE AND
Q323.D_EXPIRATION >= CURRENT DATE ORDER BY Q323.D_LEGAL DESC,
Q323.D_EFFECTIVE DESC, Q323.D_TIME_CREATED DESC FOR FETCH ONLY
;
explain plan set queryno = 24 for
select I_DCMNTN_ITEM, I_DCMNTN_ITEM
from oa1t.VCZ323A1V q323
where ? = Q323.I_DCMNTN_ITEM
AND Q323.I_DOCUMENT_T = ?
;
explain plan set queryno = 25 for
select CZ32301, CZ32313
from oa1t.tCZ323A1 q323
where ? = Q323.CZ32301
AND Q323.CZ32313 IN ( ?, ?, ?)
;
explain plan set queryno = 34 for
select *
from oa1t.VCZ323A1V q323
where ? = Q323.I_DCMNTN_ITEM
AND Q323.I_DOCUMENT_T = ?
;
explain plan set queryno = 35 for
select *
from oa1t.tCZ323A1 q323
where ? = Q323.CZ32301
AND Q323.CZ32313 IN ( ?, ?, ?)
;
explain plan set queryno = 36 for
select *
from oa1t.tCZ323A1 q323
where ? = Q323.CZ32301
AND Q323.CZ32313 IN ( ?, ?, ?)
AND Q323.CZ32304 >= CURRENT DATE
AND Q323.CZ32309 < CURRENT DATE
;
explain plan set queryno = 44 for
select A.*
from oa1t.tCZ323A1 A
, oa1t.tCZ323A1 B
where A.CZ32301 = B.CZ32301
AND ? = A.CZ32301
AND A.CZ32313 IN ( ?, ?, ?)
AND B.CZ32313 IN ( ?, ?, ?)
;
explain plan set queryno = 45 for
select B.*
from oa1t.tCZ323A1 A
, oa1t.tCZ323A1 B
where A.CZ32301 = B.CZ32301
AND ? = A.CZ32301
AND A.CZ32313 IN ( ?, ?, ?)
AND B.CZ32313 IN ( ?, ?, ?)
;
explain plan set queryno = 46 for
select B.*
from oa1t.VCZ323A1V A
, oa1t.VCZ323A1V B
where A.I_DCMNTN_ITEM = B.I_DCMNTN_ITEM
AND ? = A.I_DCMNTN_ITEM
AND A.I_DOCUMENT_T IN ( ?, ?, ?)
AND B.I_DOCUMENT_T IN ( ?, ?, ?)
AND B.D_EFFECTIVE <= CURRENT DATE
AND B.D_END > CURRENT DATE
;
explain plan set queryno = 47 for
select A.*
from oa1t.VCZ323A1V A
, oa1t.VCZ323A1V B
where A.I_DCMNTN_ITEM = B.I_DCMNTN_ITEM
AND ? = A.I_DCMNTN_ITEM
AND A.I_DOCUMENT_T IN ( ?, ?, ?)
AND B.I_DOCUMENT_T IN ( ?, ?, ?)
AND B.D_EFFECTIVE <= CURRENT DATE
AND B.D_END > CURRENT DATE
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_ViewPred
order by collid, progName, applName, explain_time,
queryNo, qBlockNo, planno,
stage, PREDNo
;
rollback;
;;
select * from plan_view1
order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
rollback
;;;
select * from cmnBatch.plan_view1
where progName in ('EC5492') and collid = 'EC'
-- and bind_time = '2011-02-10-10.55.40.578363'
and version = 'PFB0000070'
order by applname, collid, progname,
queryNo, qblockno, planno, mixOpSeq
;
select *
from CMNBATCH.plan_ViewPred
where progname = 'EC5492' and collid = 'EC'
and explain_time = '2011-08-11-14.41.27.996738'
order by collid, progName, applName, explain_time,
queryNo, qBlockNo, planno,
stage, orderNo
;;
-- versionen, Compiles und Binds pro Programm
with e as
(
select count(*) c, progName pg, version vr, collid co, bind_time bt
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", 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 = 'EC5492' and e.vr = 'PFB0000070'
order by e.co, e.pg, p.pcTimeStamp desc, e.bt desc
with ur
;
-- versionen, Compiles und Binds pro Programm
with e as
(
select count(*) c, progName pg, version vr, collid co, bind_time bt
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", 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 = 'YKCLOGS'
order by e.co, e.pg, p.pcTimeStamp desc, e.bt desc
with ur
;
set current sqlid = 'A540769';
delete from A540769.plan_table;
explain plan set queryno = 0 for
select *
from sysibm.sysDummy1
;
select * from plan_view1
order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
rollback
;;;
with c as
(
select count(*) c
from oa1p.tcz431a1
group by CZ43106
, CZ43108
, CZ43104
)
select count(*), c, count(*) * c
from c
group by c
order by 2 desc
with ur
;;
select count(*), CZ32313
from oa1p.tcz323a1
group by CZ32313
order by 2 asc
with ur
;;;;;;