zOs/SQL/EXPLAIVV
set current path = OA1T;
set current sqlid = 'A540769';
delete from plan_table;
delete from dsn_filter_table;
delete from dsn_predicat_table;
delete from dsn_detcost_table;
delete from dsn_query_table;
EXPLAIN ALL SET QUERYNO=300 FOR
SELECT SYMBOLCSFI,EVENTFUNCTIONTYPE,EFFECTIVEDATE,PROVIDERID,
SYMBOLCH,SYMBOLISIN,CSEXDATE,PAYMENTSTATUSTYPE,VALIDATIONTYPE,
RECORDDATE,ASSEMBLYDATE,DESCRIPTION_E,DESCRIPTION_F,DESCRIPTION_G,
DESCRIPTION_I,DESCRIPTION_N,PHYSICALSECURITYNO,INTERESTCALCTYPE,
ENTITLEBEGINDATE,ENTITLEENDDATE,ENTITLETIMENO,ENTITLETIMEUNITTP,
IRREGULARCPNTYPE,DIVIOCCURENCETYPE,DIVIDENDTYPE,HOSTLINKID,
BUSINESSTYPE,TYPEX,CSEVENTSTATUSTYPE,CSDESCRIPTION,
INFORMATIONSOURCE,ORGANISATIONALUNIT,USERID,USERTELEPHONENUM,
HASNOTIFICATION,ISVALIDRANGEEXDEPO,ISVALIDRANGEPRESEN,CSEVENTID
FROM OA1T.VVV_IDS_STD842_V02 A WHERE A.CATEGORYVDPS
BETWEEN '1' AND '99' AND A.CATEGORYVDPS NOT IN ('7','18') AND
A.STATUSTYPE <> 5 AND A.ROWSTATUS = 1 AND A.INFOCUSTYPE IN (1,2,3)
AND A.EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38,89,90,91)
WITH UR FOR FETCH ONLY
;
select * from plan_view1
-- where queryNo = 322
order by applname, progname, queryNo, qblockno, planno, mixopseq
;
select * from plan_view2
-- where queryNo = 322
order by applname, progname, queryNo, qblockno, planno, mixopseq
;
select * from plan_view2Det
-- where queryNo = 322
order by applname, progname, queryNo, qblockno, planno, mixopseq
;
select *
from plan_viewPred
order by collid, progName, applName, explain_time,
queryNo, qBlockNo, planno, mixOpSeqNo,
stage, orderNo
; rollback ;x;
with r as
(
select instrumentid,
row_number() over (order by instrumentid) row
from VDPS2.VTINSTRFLATDATA
)
select *
from r
where mod(row, 4000000) = 0
with ur
;;;;
select count(*), min(instrumentid), max(instrumentid)
from VDPS2.VTINSTRFLATDATA
group by floor(real(instrumentid/10000000))
order by min(instrumentid)
with ur
;;;;
select count(*), min(instrumentid), max(instrumentid)
from VDPS2.VTINSTRFLATDATA
group by floor(real(instrumentid/10000000))
order by min(instrumentid)
with ur
;;;;
select mc_o, substr(accessName, 1, 25),
k.colSeq, substr(k.colName, 1, 25), k.ordering,
v.*
from cmnbatch.plan_view1 v
LEft join sysibm.sysKeys k
on k.ixCreator in ('VDPS2', 'OA1T')
and k.ixName = v.accessName
where progName = 'YVVZZ19'
and bind_time >= '2011-08-12-23.47.31.260807'
and queryno = 376
order by collid, progName, applName, bind_time desc,
queryNo, qBlockNo, planno
, k.colSeq
;;;;
set current path = oA1P;
set current sqlid = 'A540769';
delete from plan_table;
delete from dsn_filter_table;
delete from dsn_predicat_table;
delete from dsn_detcost_table;
delete from dsn_query_table;
EXPLAIN ALL SET QUERYNO=300 FOR
SELECT SYMBOLCSFI,EVENTFUNCTIONTYPE,EFFECTIVEDATE,PROVIDERID,SYMBOLCH,
SYMBOLISIN,CSEXDATE,PAYMENTSTATUSTYPE,VALIDATIONTYPE,RECORDDATE,
ASSEMBLYDATE,DESCRIPTION_E,DESCRIPTION_F,DESCRIPTION_G,DESCRIPTION_I,
DESCRIPTION_N,PHYSICALSECURITYNO,INTERESTCALCTYPE,ENTITLEBEGINDATE,
ENTITLEENDDATE,ENTITLETIMENO,ENTITLETIMEUNITTP,IRREGULARCPNTYPE,
DIVIOCCURENCETYPE,DIVIDENDTYPE,HOSTLINKID,BUSINESSTYPE,TYPEX,
CSEVENTSTATUSTYPE,CSDESCRIPTION,INFORMATIONSOURCE,ORGANISATIONALUNIT,
USERID,USERTELEPHONENUM,HASNOTIFICATION,ISVALIDRANGEEXDEPO,
ISVALIDRANGEPRESEN
FROM oA1P.VVV_IDS_STD842
WHERE CATEGORYVDPS BETWEEN '1' AND '99'
AND CATEGORYVDPS NOT IN ('7','18')
AND STATUSTYPE <> 5
AND ROWSTATUS = 1
AND INFOCUSTYPE IN (1,2,3)
AND EVENTFUNCTIONTYPE
IN (6,7,10,11,15,20,32,33,36,37,38)
WITH UR FOR FETCH ONLY
;
EXPLAIN ALL SET QUERYNO=311 FOR
SELECT SYMBOLCSFI,EVENTFUNCTIONTYPE,EFFECTIVEDATE,PROVIDERID,SYMBOLCH,
SYMBOLISIN,CSEXDATE,PAYMENTSTATUSTYPE,VALIDATIONTYPE,RECORDDATE,
ASSEMBLYDATE,DESCRIPTION_E,DESCRIPTION_F,DESCRIPTION_G,DESCRIPTION_I,
DESCRIPTION_N,PHYSICALSECURITYNO,INTERESTCALCTYPE,ENTITLEBEGINDATE,
ENTITLEENDDATE,ENTITLETIMENO,ENTITLETIMEUNITTP,IRREGULARCPNTYPE,
DIVIOCCURENCETYPE,DIVIDENDTYPE,HOSTLINKID,BUSINESSTYPE,TYPEX,
CSEVENTSTATUSTYPE,CSDESCRIPTION,INFORMATIONSOURCE,
ORGANISATIONALUNIT,USERID,USERTELEPHONENUM,HASNOTIFICATION,
ISVALIDRANGEEXDEPO,ISVALIDRANGEPRESEN FROM oA1P.VVV_IDS_STD842
WHERE oA1P.VVV_IDS_STD842.CATEGORYVDPS BETWEEN '1' AND '99'
AND oA1P.VVV_IDS_STD842.CATEGORYVDPS NOT IN ('7','18')
AND oA1P.VVV_IDS_STD842.INSTRUMENTUPDATETIMESTAMP >= '2011041306000000'
AND oA1P.VVV_IDS_STD842.INSTRUMENTUPDATETIMESTAMP < '2011041410562200'
AND oA1P.VVV_IDS_STD842.INFOCUSTYPE IN (1,2,3)
AND oA1P.VVV_IDS_STD842.EVENTFUNCTIONTYPE IN
(6,7,10,11,15,20,32,33,36,37,38)
with UR for fetch only
;
EXPLAIN ALL SET QUERYNO=322 FOR
SELECT SYMBOLCSFI,EVENTFUNCTIONTYPE,EFFECTIVEDATE,PROVIDERID,
CFAMOUNTTYPE,SYMBOLCH,SYMBOLISIN,PAYMENTSTATUSTYPE,ACTUALAMOUNT,
ACTUALEXERCISETP,AMOUNTQUOTATIONTP,DRAWDATE,MAXAMORTIZQUOTETP,
ENTITLEDPERSONTP,REASONTYPE,TERMCHANGETYPE,MAXNOTICEPERIOD,
MINNOTICEPERIOD,NOTICEPERIODUNITP,NOMINALAFTERREDUCT,
NOMINALREDUCTION,REPAYCURRENCY_I1,REPAYCURRENCY_I2,
REPAYCURRENCY_WS80,PLANNEDPAYRATE,SETTLESTYLETYPE,
PLANNEDPAYAMOUNT,PLANNEDPAYINSTR_I1,PLANNEDPAYINSTR_I2,
PLANNEDPAYINSTR_WS80,PLANNEDBEGINDATE,PLANNEDENDDATE,
PAYAMOUNT,PAYINSTRUMENT_I1,PAYINSTRUMENT_I2,
PAYINSTRUMENT_WS80,PAYRATE FROM oA1P.VVV_IDS_STD856
WHERE oA1P.VVV_IDS_STD856.CATEGORYVDPS BETWEEN '1' AND '99'
AND oA1P.VVV_IDS_STD856.CATEGORYVDPS NOT IN ('7','18')
AND oA1P.VVV_IDS_STD856.INSTRUMENTUPDATETIMESTAMP >= '2011041306000000'
AND oA1P.VVV_IDS_STD856.INSTRUMENTUPDATETIMESTAMP < '2011041410562200'
AND oA1P.VVV_IDS_STD856.EVENTFUNCTIONTYPE IN (2,3,9,17,26,39)
AND oA1P.VVV_IDS_STD856.PROVIDERTYPE = 1
WITH UR FOR FETCH ONLY ;
EXPLAIN ALL SET QUERYNO=801 FOR
select count(*) from sysibm.sysTables
where tsName = 'A001' ;
EXPLAIN ALL SET QUERYNO=802 FOR
select count(*) from sysibm.sysTables
where case when tsName = 'A001' then 1 else 0 end = 1;
select * from plan_view1
-- where queryNo = 322
order by applname, progname, queryNo, qblockno, planno, mixopseq
;
select * from plan_view2
-- where queryNo = 322
order by applname, progname, queryNo, qblockno, planno, mixopseq
;
select * from plan_view2Det
-- where queryNo = 322
order by applname, progname, queryNo, qblockno, planno, mixopseq
;
select *
from plan_viewPred
order by collid, progName, applName, explain_time,
queryNo, qBlockNo, planno, mixOpSeqNo,
stage, orderNo
;
commit;
;;;;
select count(*), INSTRUMENTID
, INSTRUMENTEVENTNO
from vdps2. VTCASHFLOWAMOUNT
group by INSTRUMENTID
, INSTRUMENTEVENTNO
order by 1 desc
with ur
;
;;;;
rollback
EXPLAIN ALL SET QUERYNO=300 FOR
SELECT T1.INSTRUMENTID, T1.SYMBOLCSFI, T2.EVENTFUNCTIONTYPE,
T2.EFFECTIVEDATE, T1.SYMBOLCH, T1.SYMBOLISIN,
T1.CATEGORYVDPS, T1.ROWSTATUS, T1.STATUSTYPE,
T1.INSTRUMENTUPDATETIMESTAMP, T2.CSEXDATE,
T2.PAYMENTSTATUSTYPE, T2.VALIDATIONTYPE, T2.RECORDDATE,
T2.PROVIDERTYPE, (CASE
WHEN T2.PROVIDER3ID IS NULL THEN T2.PROVIDER1ID ELSE T2.
PROVIDER3ID END) AS PROVIDERID, T2.ASSEMBLYDATE,
T2.DESCRIPTION_E, T2.DESCRIPTION_F, T2.DESCRIPTION_G,
T2.DESCRIPTION_I, T2.DESCRIPTION_N, T2.PHYSICALSECURITYNO,
T2.IDSYMBOL HOSTLINKID, T2.BUSINESSTYPE, T2.TYPEX,
T2.CSEVENTSTATUSTYPE, T2.CSDESCRIPTION,
T2.INFORMATIONSOURCE, T2.ORGANISATIONALUNIT, T2.USERID,
T2.USERTELEPHONENUM, T2.HASNOTIFICATION,
T2.ISVALIDRANGEEXDEPO, T2.ISVALIDRANGEPRESEN,
T2.INFOCUSTYPE, T3.INTERESTCALCTYPE, T3.ENTITLEBEGINDATE,
T3.ENTITLEENDDATE, T3.ENTITLETIMENO, T3.ENTITLETIMEUNITTP,
T3.IRREGULARCPNTYPE, T3.DIVIOCCURENCETYPE, T3.DIVIDENDTYPE
FROM VDPS2.VTINSTRUMENTEVENT T2
INNER JOIN
VDPS2.VTINSTRFLATSYMBOLS T1
ON T1.INSTRUMENTID = T2.INSTRUMENTID
INNER JOIN
VDPS2.VTCASHFLOW T3
ON T3.INSTRUMENTID = T2.INSTRUMENTID AND T3.
INSTRUMENTEVENTNO = T2.INSTRUMENTEVENTNO
WHERE T2.INFOCUSTYPE IN (1,2,3) AND
T2.EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38) AND
DAYS(CURRENT DATE) -
DAYS(DATE(T1.INSTRUMENTUPDATETIMESTAMP)) <= 1 AND
T1.CATEGORYVDPS BETWEEN '1' AND '99' AND
T1.CATEGORYVDPS NOT IN ('7','18')
WITH UR FOR FETCH ONLY
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback
;x;
explain plan set queryno = 2 for
SELECT * FROM oA1P.VVV_IDS_STD842
WHERE INFOCUSTYPE IN (1,2,3)
AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
;
explain plan set queryno = 1 for
SELECT * FROM oA1P.VVV_IDS_STD843
WHERE INFOCUSTYPE IN (1,2,3)
AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
SELECT * FROM oA1P.VVV_IDS_STD844
WHERE INFOCUSTYPE IN (1,2,3)
AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
select *
from oA1P.TWP703A101
where
left(wp70302e, 5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 1 for
select *
from oA1P.TWP703A101
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 2 for
select *
from oA1P.TWP703A102
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 3 for
select *
from oA1P.TWP703A103
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 4 for
select *
from oA1P.TWP703A104
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 5 for
select *
from oA1P.TWP703A105
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 6 for
select *
from oA1P.TWP703A106
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
explain plan set queryno = 7 for
select *
from oA1P.TWP703A107
where
substr(wp70302e,1,5) in ('BFTBB','WPSPM') and
wp70302q >= ? and
wp70302q <= ? and
wp70306 in (? ,?)
;
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 = 'MF01A1P' 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 oA1P.vpw210a1V
group by auftrags_nummer, pm_id
order by 1 desc
;
--select count(*) from oA1P.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 oA1P.VNI600A101A C
, oA1P.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 oA1P.VNI600A101A C
WHERE C.CTRLID = 'NI6220R'
)
SELECT T.* FROM NI600
,oA1P.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
;