zOs/SQL/EXPLAINZ
select * from cmnbatch.plan_view1
where progName = 'NZ5820'
and version = 'ZVHI000058'
order by applname, progname, queryNo, qblockno, planno
;
select * from cmnbatch.plan_table
where progName = 'NZ5820'
and version = 'ZVHI000058'
order by applname, progname, queryNo, qblockno, planno
;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
select *
from oa1p.TNZ240A1
where
NZ240003 > ? || '' and NZ240003 < ? || ''
and NZ240036 <= ?
;
explain plan set queryno = 2 for
select *
from oa1p.TNZ240A1
where
NZ240003 > ? and NZ240003 < ?
and NZ240036 <= ?
;
explain plan set queryno = 4 for
select *
from oa1p.TNZ240A1
where
NZ240003 between ? and ?
and NZ240036 <= ?
optimize for 1 rows
;
explain plan set queryno = 5 for
select *
from oa1p.TNZ240A1
where
NZ240003 between x'003544' and x'003545'
and NZ240036 <= ?
;
explain plan set queryno = 6 for
select *
from oa1p.TNZ240A1
where
NZ240003 between x'0036' and x'0055FFFF'
and NZ240036 <= ?
;
explain plan set queryno = 7 for
select *
from oa1p.TNZ240A1
where
NZ240003 > x'0036' and NZ240003 <x'0055FFFF'
and NZ240036 <= ?
;
select * from plan_view1
order by applname, progname, queryNo, qblockno, planno
;
select * from plan_table
order by applname, progname, queryNo, qblockno, planno
;
rollback
X'0035
X'0039
X'003D
X'0041
X'0045
X'0049
X'004D
X'0051
X'0055
;x;
select count(*), accesstype, page_range
from cmnbatch.plan_table
group by accesstype, page_range
order by accesstype, page_range
with ur
;;;
select * from s100447.plan_view1
where progName in ('YWPLXL3') and collid = 'A540769'
-- and bind_time = '2010-07-18-22.01.54.199935'
order by applname, progname, queryNo, qblockno, planno,
bind_time, tname
;x;
select * from cmnbatch.dsn_statemnt_table
where progName in ('YWPLXL3')
and explain_time = '2010-07-18-22.01.54.199935'
-- order by applname, progname, queryNo, qblockno, planno
;x;
select '4', count(*), sum(case when substr(wp70302e,1,5)
in ('BFTBB','WPSPM') then 1 else 0 end)
from oa1p.TWP703A104
union all select '5', count(*), sum(case when substr(wp70302e,1,5)
in ('BFTBB','WPSPM') then 1 else 0 end)
from oa1p.TWP703A105
with ur
;;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
SELECT * FROM A540769.VMFNVEXT
WHERE MF150023 > '038 ' AND MF150023 <= '052 '
WITH UR
;
explain plan set queryno = 2 for
SELECT * FROM A540769.VMFNVPART
WHERE MF150023 > '052 ' AND MF150023 <= '057 '
;
explain plan set queryno = 3 for
select t.*, i.mf150Hash
from oa1p.tmf150a1 t, A540769.tmfnvPart i
where t.mf150013 = i.mf150013
and t.mf150023 = i.mf150023
and i.part = 1
order by i.MF150023 ASC,
i.MF150013 DESC
;
explain plan set queryno = 4 for
select t.*, i.mf150Hash
from oa1p.tmf150a1 t, A540769.tmfnvPart i
where t.mf150013 = i.mf150013
and t.mf150023 = i.mf150023
and t.MF150082 = ' '
and i.part = 1
order by i.MF150023 ASC,
i.MF150013 DESC
;
--explain plan set queryno = 7 for
--ect s.*
-- from tstRts.tsIxStats S
-- where db like 'MF%' and ts like 'A1%1%' and part in (0,1,5,7,9)
-- order by db, ts, part
--
--ect current timestamp from sysibm.sysdummy1;
--lect s.*
-- from tstRts.tsIxStats S
-- where db = 'MF01A1P' and ts like 'A1%1%' and part in (0,1,5,7,9)
-- order by db , ts -- , part
--
--lect current timestamp from sysibm.sysdummy1;
--lect s.*
-- from tstRts.tsIxStats S
-- where db like 'MF0%' and ts like 'A1%1%' and part in (0,1,5,7,9)
-- order by db , ts -- , part
--
select current timestamp from sysibm.sysdummy1;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
select * from A540769.plan_view5
where progName in ('SV5003', 'SV5390', 'SV8240')
order by applname, progname, queryNo, qblockno, planno
;x;
select * from cmnbatch.plan_view2
where progName in ('FI5700') and version = 'BKUR000319'
order by applname, progname, queryNo, qblockno, planno
;x;
-- select * from tstRts.TrtsReoTSException;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
select s.*
from tstRts.VRtsReoTSSchwelle s
where db = 'MF01A1T' and ts like 'A1%1%' and part in (0,1,5,7,9)
order by db , ts -- , part
;
explain plan set queryno = 7 for
select s.*
from tstRts.tsIxStats S
where db like 'MF%' and ts like 'A1%1%' and part in (0,1,5,7,9)
order by db, ts, part
;
select current timestamp from sysibm.sysdummy1;
select s.*
from tstRts.tsIxStats S
where db = 'MF01A1P' and ts like 'A1%1%' and part in (0,1,5,7,9)
order by db , ts -- , part
;
select current timestamp from sysibm.sysdummy1;
select s.*
from tstRts.tsIxStats S
where db like 'MF0%' and ts like 'A1%1%' and part in (0,1,5,7,9)
order by db , ts -- , part
;
select current timestamp from sysibm.sysdummy1;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
xxx
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for -- mit concat
with c as
( select mf150023 cif, mf150013 dat, count(*) cnt
from oa1p.tmf150A1
where mf150023 like '000%'
group by mf150023 , mf150013
) , h as
( select
smallint(mod(
int(
translate(
translate('08642' , hex(substr(cif , 8, 5)), '1234567890'),
'024579', 'ABCDEF'))
+ month(dat) ,11113)) h
, cnt
from c
)
, g as
( select sum(cnt) c
from h
group by h
)
, p as
( select sum(cnt) c
from h
group by floor(h/111.14)
)
select 'h', min(c) "min", max(c) "max", count(*) "count", sum(c) "sum"
from g
group by floor(log10(c) * 6)
union all
select 'p', min(c) "min", max(c) "max", count(*) "count", sum(c) "sum"
from p
group by floor(log10(c) * 6)
order by 1, 2
with ur
;
explain plan set queryno = 5 for -- mit concat
with h as
( select mf150023 cif,
smallint(mod(
int(
translate(
translate('08642' , hex(substr(mf150023 , 8, 5)), '1234567890'),
'024579', 'ABCDEF')), 9973)) t
from oa1p.tmf150A1
-- where cif like '000%'
)
, g as
( select t, count(*) c
from h
group by t
)
select sum(c), min(c), max(c), count(*) "dist c"
from g
group by floor(log10(c) * 6)
with ur
;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
select * from cmnBatch.plan_view5
where progName = 'YDG129'
order by applname, progname, queryNo, qblockno, planno
;
x
select * from cmnBatch.plan_view5
where progName = 'MF8500' -- and version = 'MFR0000203'
order by applname, progname, queryNo, qblockno, planno
;
x
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for -- mit concat
SELECT RM01001,RM01002,RM01003,RM01004,RM01005,RM01006,
RM01007,RM01008,RM01009,RM01010,RM01011,RM01012,
RM01013,RM01014,RM01015,RM01016,RM01017,RM01018
FROM oa1p.TRM010A1
WHERE ? BETWEEN RM01002 AND RM01006
AND RM01015 || '' IN ('RT001','RT002','RT004',
'RT010','RT015','RT018','RT201')
ORDER BY RM01015, RM01011, RM01014, RM01002
FOR FETCH ONLY;
explain plan set queryno = 3 for -- mit or
SELECT RM01001,RM01002,RM01003,RM01004,RM01005,RM01006,
RM01007,RM01008,RM01009,RM01010,RM01011,RM01012,
RM01013,RM01014,RM01015,RM01016,RM01017,RM01018
FROM oa1p.TRM010A1
WHERE ? BETWEEN RM01002 AND RM01006
AND (RM01015 IN('RT001','RT002','RT004',
'RT010','RT015','RT018','RT201')
or 1 = 0
)
ORDER BY RM01015, RM01011, RM01014, RM01002
FOR FETCH ONLY;
explain plan set queryno = 9 for -- neu ohne or
SELECT *
FROM oa1p.TRM010A1
WHERE ? BETWEEN RM01002 AND RM01006
AND RM01015 IN('RT001','RT002','RT004',
'RT010','RT015','RT018','RT201')
ORDER BY RM01015, RM01011, RM01014, RM01002
FOR FETCH ONLY;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
x
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for -- alt schnell
Select MF150023,MF150067,
sum(Case when MF150055 = 'Z' then DEC(MF150019,18,3) else 0 end),
sum(Case when MF150055 = 'A' then DEC(MF150019,18,3) else 0 end)
FROM oa1p.TMF150A1
WHERE MF150066 = ' A965367'
AND MF150013 BETWEEN '01.01.2009' and '30.11.2009'
AND MF150085 BETWEEN 10 AND 89
AND MF150084 BETWEEN 10 AND 89
GROUP BY MF150023,MF150067
with ur
;
explain plan set queryno = 9 for -- neu langsam
Select MF150023,MF150067,MF150019A,
sum(Case when MF150055 = 'Z' then DEC(MF150019,18,3) else 0 end),
sum(Case when MF150055 = 'A' then DEC(MF150019,18,3) else 0 end)
FROM oa1p.TMF150A1
WHERE MF150066 = ' A965367'
AND MF150013 BETWEEN '01.01.2009' and '30.11.2009'
AND MF150085 BETWEEN 10 AND 89
AND MF150084 BETWEEN 10 AND 89
GROUP BY MF150023,MF150067,MF150019A
with ur;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
x
delete from plan_table;
explain plan set queryno = 3 for
SELECT SV363012
FROM OA1P.TSV361A1 JOIN oa1p.TSV363A1
ON SV361003=SV363001
WHERE SV361002=1
AND SV361008=2
AND SV361009= ?
AND(SV363007='1' OR SV363007='2')
AND(ABS(SV363010 - '01.01.2009')<30 OR ABS(SV363011
- date('01.01.2009'))<30)
ORDER BY SV363010 DESC,SV363011 DESC
;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
set current sqlid = 'A540769';
select count(*), SV363001
FROM OA1P.TSV363A1
group by SV363001
ORDER BY 1 DESC
WITH UR
;
x
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
select *
from a540769.vrtsReoTs
where dbName like 'MF%'
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
set current sqlid = 'A540769';
----- query 1532 YXEBV2E, 30.6.09
----- Kumar Sandeep will sequenz TKS835, TKS833, TKS843, TKS843
----- seit einem Jahr bind TKS843, TKS835, TKS833, TKS843
-----
----- 3: partner_*=? in 833 und join : original
explain plan set queryno = 3 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V833.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V833.PARTNER_TYPE = 3 AND V833.PARTNER_KEY = V835.PARTNER_KEY AND
V833.CONTRACT_KEY = V835.CONTRACT_KEY AND V833.PARTNER_TYPE =
V835.PARTNER_TYPE AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
----- 5: partner_*=? in 835 und join
explain plan set queryno = 5 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V835.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V835.PARTNER_TYPE = 3 AND V833.PARTNER_KEY = V835.PARTNER_KEY AND
V833.CONTRACT_KEY = V835.CONTRACT_KEY AND V833.PARTNER_TYPE =
V835.PARTNER_TYPE AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
----- 35: partner_*=? in 833 und 835 und join: fake filtering
explain plan set queryno = 35 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V833.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V833.PARTNER_TYPE = 3
and V835.PARTNER_KEY = ? AND V835.PARTNER_TYPE = 3
AND V833.PARTNER_KEY = V835.PARTNER_KEY AND
V833.CONTRACT_KEY = V835.CONTRACT_KEY AND V833.PARTNER_TYPE =
V835.PARTNER_TYPE AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
----- 359: partner_*=? in 833 und 835 NO join
explain plan set queryno = 359 for
SELECT 1
FROM oA1P.VKS833A1V V833, oA1P.VKS835A1V V835, oA1P.VKS843A1V V843S,
oA1P.VKS843A1V V843O
WHERE V833.PARTNER_KEY = ? AND V833.CONTRACT_COLL = ?
AND V833.PARTNER_TYPE = 3
and V835.PARTNER_KEY = ? AND V835.PARTNER_TYPE = 3
and V833.CONTRACT_KEY = V835.CONTRACT_KEY
AND V835.ARRANGEMENT_TYPE = ? AND
V835.ARRANGEMENT_ID = ? AND V835.SERVICE_KEY = V843S.TAB_CODE AND
V843S.TAB_ART = 'SERVICE_ID' AND V843O.TAB_ART = 'OTHER_CONDITION' AND
V843O.TAB_CODE = V843S.TAB_TEXT3 AND V843O.BU = ? AND CURRENT DATE
BETWEEN V835.VALID_FROM_DATE AND V835.VALID_TO_DATE FETCH FIRST ROW ONLY
;
select * from plan_view5a
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
select * from cmnbatch.plan_view5a
where progname = 'WI8710'
and version in ('WS8B000786', 'WS8B000325')
and queryno in ( 241)
order by applname, progname, queryNo, timestamp, qblockno, planno
;
x
x
select * from CMNbatch.plan_view5
where queryno between 142 and 147
and progname = 'YCI017C'
order by applname, progname, timestamp, queryNo, qblockno, planno
;
x
explain plan set queryno = 111 for
select * from cmnbatch.plan_view5
-- where progname in ('XBIM33')
where progname in ('XBIM31', 'XBIM32', 'XBIM33', 'XBIM34' ,
'XBIM35', 'XBIM42')
order by applname, progname, queryNo, qblockno, planno
;
explain plan set queryno = 222 for
select * from cmnbatch.plan_view5ss
-- where progname in ('XBIM33')
where progname in ('XBIM31', 'XBIM32', 'XBIM33', 'XBIM34' ,
'XBIM35', 'XBIM42')
order by applname, progname, queryNo, qblockno, planno
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
set current sqlid = 'A540769';
explain plan set queryno = 111 for
UPDATE bua.TXBH111
SET ESSTATE = 3 WHERE EAUUIDMQ IN (SELECT
EAUUIDMQ FROM bua.TXBH111 WHERE EAUUIDHT = '?' )
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
set current sqlId = 'A540769';
delete from A540769.plan_table
;
explain plan set queryno = 111 for
select count(*), jobname, max(partition), min(partition)
from sysibm.syscopy c, sysibm.systablepart p
where c.dbName = p.dbName and c.tsName = p.tsname
and c.dsNum in (0, p.partition)
group by jobName
order by 1 desc
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;
xelect *
from CMNBATCH.plan_view5
where progName = 'DBWK3'
order by applname, progname, queryNo, qblockno, planno
;
xelect count(*), auftrags_nummer, pm_id
from oa1t.vpw210a1V
group by auftrags_nummer, pm_id
order by 1 desc
;
--select count(*) from oa1t.TSN100A1
-- with ur
-- ;
select *
from CMNBATCH.plan_view5
where progName = 'YPW2KPI' and queryNo = 4235
order by applname, progname, queryNo, qblockno, planno
;
x
set current sqlid = 'A540769'
;
delete from plan_table where queryno in (111, 222)
;
select current timestamp from sysibm.sysdummy1
;
explain plan set queryno = 111 for
SELECT T.* FROM oa1t.VNI600A101A C
, oa1t.VNI203A101A T
WHERE T.PERENDDT >= DATE(C.CTRLTIMESTMPFROM)
AND C.CTRLID = 'NI6220R'
;
select current timestamp from sysibm.sysdummy1
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
explain plan set queryno = 222 for
WITH NI600 (PERENDDT) AS
(SELECT DATE(C.CTRLTIMESTMPFROM) AS ENDDT
FROM oa1t.VNI600A101A C
WHERE C.CTRLID = 'NI6220R'
)
SELECT T.* FROM NI600
,oa1t.VNI203A101A T
WHERE T.PERENDDT >= NI600.PERENDDT
;
select * from plan_view5a
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
select * from plan_view5
where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback
;
x
select *
from cmnbatch.plan_view5
where progName = 'YRPNIMP' and queryNo = 866
order by applname, progname, queryNo, qblockno, planno
;
x
d.bType, p.collid, p.Name, p.version,
p.conToken, p.timeStamp, p.type,
p.validate, p.isolation, p.valid, p.operative,
p.owner, p.qualifier
m sysibm.syspackdep d join sysibm.syspackage p
on p.location = d.dLocation and p.collid = d.dCollid
and p.name = d.dName and p.conToken = d.dConToken 2.22.129867'
re
(bQualifier, bName, bType) in
( select dbName, name, 'R'
from sysIbm.sysTablespace
where dbName in ('NF01A1A', 'NF02A1A', 'NF03A1A')
)
from gdb0283.vnz240a1v
where SearchValue01 = ?
-- dec(SearchValue01,15,3) = ?
and CreateDateTime < ?
and dec(SearchValue02,15,3) >= ?
and dec(SearchValue02,15,3) <= ?
and Objtype ^= 'PORDSPLT'
order by CreateDateTime
fetch first 101 rows only
optimize for 101 rows
with ur
;
select * from plan_view5A
where queryno = 999
order by applname, progname, queryNo, qblockno, planno
;
rollback
;
select * from plan_table
order by applname, progname, queryNo, qblockno, planno
;
rollback
;