zOs/SQL/EXPLAIMF
--- versionen, compiles und explains eines packages -------------------
select *
from cmnbatch.plan_view0
where collid = 'MF'
and prog = 'MF7010'
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 = 'MF'
and progName = 'MF7010'
and bind_time = '2012-03-17-11.05.04.595094'
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;
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
;
--- 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 CHAR('@')
|| CHAR('RM') || CHAR('@')
|| CHAR('CIF') || CHAR('@')
|| CHAR('CIF_NAME') || CHAR('@')
|| CHAR('PH_A_AMT') || CHAR('@')
|| CHAR('PH_A_CTR') || CHAR('@')
|| CHAR('PH_Z_AMT') || CHAR('@')
|| CHAR('PH_Z_CTR') || CHAR('@')
|| CHAR('PH_ABS_AMT') || CHAR('@')
|| CHAR('NPH_A_AMT') || CHAR('@')
|| CHAR('NPH_A_CTR') || CHAR('@')
|| CHAR('NPH_Z_AMT') || CHAR('@')
|| CHAR('NPH_Z_CTR') || CHAR('@')
|| CHAR('TOT')
FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT
CHAR('@')
|| RM || CHAR('@')
|| CIF_NO || CHAR('@')
|| NAME || CHAR('@')
|| CHAR(DECIMAL(SUM(PHY_ABF_1),31,3)) || CHAR('@')
|| CHAR(SUM(ANZ_PHY_ABF_1)) || CHAR('@')
|| CHAR(DECIMAL(SUM(PHY_ZUF_2),31,3)) || CHAR('@')
|| CHAR(SUM(ANZ_PHY_ZUF_2)) || CHAR('@')
|| CHAR(DECIMAL(SUM(PHY_ABS_3),31,3)) || CHAR('@')
|| CHAR(DECIMAL(SUM(NPHY_ABF_4),31,3)) || CHAR('@')
|| CHAR(SUM(ANZ_NPHY_ABF_4)) || CHAR('@')
|| CHAR(DECIMAL(SUM(NPHY_ZUF_5),31,3)) || CHAR('@')
|| CHAR(SUM(ANZ_NPHY_ZUF_5)) || CHAR('@')
|| CHAR(DECIMAL(SUM(TOTAL_ABS),31,3))
FROM
(SELECT CASE(SUBSTR(C.INT_OE_FORM_PID_KV,1,8))
WHEN(' ')
THEN RTRIM(MA.GESETZLICHER_NAME) || CHAR(' ') ||
RTRIM(MA.VORNAME) || ', ' ||
LTRIM(RTRIM(C.INT_OE_FORM_PID_KV))
ELSE CHAR(' ')
END AS RM,
C.CIFNUMMER_KUNDE AS CIF_NO,
Z.KURZADRESSE_1 AS NAME,
CASE
WHEN C.TRANSACTION_TYP = 10
AND C.ZU_ABFLUSS = 'A'
THEN ABS(C.BETRAG_BUWHR)
ELSE 0
END AS PHY_ABF_1,
CASE
WHEN C.TRANSACTION_TYP = 10
AND C.ZU_ABFLUSS = 'A'
THEN 1
ELSE 0
END AS ANZ_PHY_ABF_1,
CASE
WHEN C.TRANSACTION_TYP = 10
AND C.ZU_ABFLUSS = 'Z'
THEN C.BETRAG_BUWHR
ELSE 0
END AS PHY_ZUF_2,
CASE
WHEN C.TRANSACTION_TYP = 10
AND C.ZU_ABFLUSS = 'Z'
THEN 1
ELSE 0
END AS ANZ_PHY_ZUF_2,
CASE
WHEN C.TRANSACTION_TYP = 10
THEN ABS(C.BETRAG_BUWHR)
ELSE 0
END AS PHY_ABS_3,
CASE
WHEN C.TRANSACTION_TYP = 20
AND C.ZU_ABFLUSS = 'A'
THEN ABS(C.BETRAG_BUWHR)
ELSE 0
END AS NPHY_ABF_4,
CASE
WHEN C.TRANSACTION_TYP = 20
AND C.ZU_ABFLUSS = 'A'
THEN 1
ELSE 0
END AS ANZ_NPHY_ABF_4,
CASE
WHEN C.TRANSACTION_TYP = 20
AND C.ZU_ABFLUSS = 'Z'
THEN C.BETRAG_BUWHR
ELSE 0
END AS NPHY_ZUF_5,
CASE
WHEN C.TRANSACTION_TYP = 20
AND C.ZU_ABFLUSS = 'Z'
THEN 1
ELSE 0
END AS ANZ_NPHY_ZUF_5,
ABS(C.BETRAG_BUWHR) AS TOTAL_ABS
FROM oa1t.VMF150A1V C
LEFT OUTER JOIN oa1t.VBP200A1R MA
ON SUBSTR(C.INT_OE_FORM_PID_KV,9,8) = MA.PID,
oa1t.TMF009A1 Z
WHERE C.ID_MITTELFLUSS IN
(SELECT D.ID_MITTELFLUSS
FROM oa1t.TMF160A1 D, oa1t.VMF202A1
WHERE D.TEV_DATUM = TEV_DAT_EUR)
AND C.CIFNUMMER_KUNDE = Z.CIF
AND Z.CIF_STATUS = 'L'
AND Z.OFFSHORE_CD_TRUST = '25'
AND C.RELEVANT_GROUP NOT IN (0,2)
) X
GROUP BY
RM,
CIF_NO,
NAME
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
;;;;
--- versionen, compiles und explains eines packages --------
select *
from cmnbatch.plan_view0
where collid = 'MF'
and prog = 'MF8120'
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 = 'MF'
and progName = 'MF8120'
and (opthint <> '' or hint_used <> '')
-- and bind_time in ('2012-03-06-16.53.11.688569'
-- ,'2012-03-06-16.52.29.596158'
-- ,'2011-05-12-11.57.17.372668'
-- )
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;; ;
--- 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 *
FROM oa1p.TMF401A1 A
WHERE NOT EXISTS
(SELECT 1
FROM oa1p.TMF150A1 b
WHERE A.UUID = B.MF150001 UNION ALL SELECT 1
FROM oa1p.TMF150H1 B
WHERE A.UUID = B.MF150001) WITH UR
;
explain plan set queryno = 9 for
SELECT *
FROM oa1p.TMF401A1 A
WHERE NOT EXISTS
(SELECT 1
FROM oa1p.TMF150A1 b
WHERE A.UUID = B.MF150001 ) and not exists ( SELECT 1
FROM oa1p.TMF150H1 B
WHERE A.UUID = B.MF150001) 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
;;;;
--- versionen, compiles und explains eines packages --------
select *
from cmnbatch.plan_view0
where collid = 'MF'
and prog = 'MF7930'
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 = 'MF'
and progName = 'MF7930'
-- and (opthint <> '' or hint_used <> '')
and bind_time in ('2012-03-06-16.53.11.688569'
,'2012-03-06-16.52.29.596158'
,'2011-05-12-11.57.17.372668'
)
order by collid, progName, version, bind_time,
queryno, qBlockNo, planno, mixopSeq
with ur
;; ;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND MF150001 NOT IN
(SELECT UUID FROM oa1p.TMF401A1)
UNION ALL
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
WITH UR;
explain plan set queryno = 11 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND MF150001 NOT IN
(SELECT UUID FROM oa1p.TMF401A1)
with ur ;
explain plan set queryno = 12 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
WITH UR;
explain plan set queryno = 21 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND not exists
(SELECT 1 FROM oa1p.TMF401A1 t
where f.mf150001 = t.uuid)
with ur ;
explain plan set queryno = 30 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND not exists
(SELECT 1 FROM oa1p.TMF401A1 t
where f.mf150001 = t.uuid)
UNION ALL
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
with ur ;
explain plan set queryno = 31 for
SELECT count(*), sum(day(MF150013)),CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND not exists
(SELECT 1 FROM oa1p.TMF401A1 t
where f.mf150001 = t.uuid)
UNION ALL
SELECT count(*), sum(day(MF150013)),CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
with ur ;
explain plan set queryno = 40 for
SELECT MF150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
except all
SELECT MF150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND exists
(SELECT 1 FROM oa1p.TMF401A1 t
where f.mf150001 = t.uuid)
UNION ALL
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
with ur ;
explain plan set queryno = 44 for
with w as (
SELECT MF150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
except all
SELECT MF150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150060 BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND exists
(SELECT 1 FROM oa1p.TMF401A1 t
where f.mf150001 = t.uuid)
UNION ALL
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
) select count(*), sum(day(mf150013))
from w
with ur ;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;;;,
explain plan set queryno = 22 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid
)
WITH UR;
explain plan set queryno = 27 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid
)
WITH UR;
explain plan set queryno = 30 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND '2011-08-31-23.59.59.999999'
AND MF150022 = '0015'
AND not exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid
)
UNION ALL
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
WITH UR;
explain plan set queryno = 31 for
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND not exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid
)
UNION ALL
SELECT MF150013,CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
WITH UR;
explain plan set queryno = 33 for
SELECT count(*), sum(day(MF150013)),CHAR('@')
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND not exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM + 0 days BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid || ''
)
UNION ALL
SELECT count(*), sum(day(MF150013)),CHAR('@')
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
WITH UR;
explain plan set queryno = 44 for
select count(*), sum(length(strip(MF150059))),
sum(length(strip(MF150005))),
sum(length(strip(MF150023)))
FROM oa1p.TMF150A1
;
explain plan set queryno = 55 for
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
except all
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid
)
UNION ALL
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
WITH UR
;
explain plan set queryno = 56 for
with w as (
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
except all
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1 f
WHERE MF150022 = '0015'
AND exists
(SELECT UUID FROM oa1p.TMF401A1 t
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999'
and f.mf150001 = t.uuid
)
UNION ALL
SELECT mf150001, MF150013,CHAR('@') aff
FROM oa1p.TMF150A1
WHERE MF150022 = '0015'
AND MF150001 IN
(SELECT UUID FROM oa1p.TMF401A1
WHERE BEWERTUNGSDATUM BETWEEN '2011-08-31-00.00.00.000000'
AND '2011-08-31-23.59.59.999999')
)
select count(*), sum(day(mf150013)) from w
WITH UR
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
;elete from plan_table where queryno = 1
;
select * from cmnbatch.plan_view1
-- 2010-11-12-21.45.30.666016 MFR0000223
where progname = 'MF7930' and version = 'MFR0000223'
-- and bind_time = '2010-11-12-21.45.30.666016'
and bind_time = '2010-01-10-12.41.38.311213'
-- and queryno = 775
order by applname, progname, queryNo, qblockno, planno
; x;
select * from cmnbatch.plan_view1
where progname = 'YMFC112' and version = 'MFR00002145D0FA880'
-- and bind_time = '2010-11-12-21.46.53.627557'
and queryno = 775
order by applname, progname, queryNo, qblockno, planno
, bind_time desc
; x;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
SELECT INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A,
SUM(CASE WHEN MF150019 BETWEEN 0 AND 999999999999999
THEN MF150019
ELSE 0 END) AS SUMME_Z,
SUM(CASE WHEN MF150019 BETWEEN -999999999999999 and 0
THEN MF150019
ELSE 0 END) AS SUMME_A,
0,
0
FROM oa1p.TMF150A1,oa1p.TMFOE1T1
Where MF150067 = INT_HIST_OE_BEZ
AND MF150013 BETWEEN OE_VON_DATE AND OE_Bis_Date
AND MF150013 BETWEEN '01.11.2010' AND '11.11.2010'
GROUP BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
ORdER BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
WITH UR
;
explain plan set queryno = 2 for
SELECT INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A,
SUM(CASE WHEN MF150019 BETWEEN 0 AND 999999999999999
THEN MF150019
ELSE 0 END) AS SUMME_Z,
SUM(CASE WHEN MF150019 BETWEEN -999999999999999 and 0
THEN MF150019
ELSE 0 END) AS SUMME_A,
0,
0
FROM oa1p.TMF150A1,oa1p.TMFOE1T1
Where MF150067 = INT_HIST_OE_BEZ
AND MF150013 BETWEEN max(OE_VON_DATE, ? || '')
AND min(OE_Bis_Date, ? || '')
GROUP BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
ORdER BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
WITH UR
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
delete from plan_table where queryno = 1
;
update plan_table
set queryno = 775
, collid = 'MF'
, progname = 'YMFC112'
, version = 'MFR00002145D0FA880'
, optHint = 'WK1'
;
-- update plan_table -- nein, verliert matchCols mit altem sq
-- set method = 4
-- where tName = 'TMF150A1'
--;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
delete from cmnbatch.plan_table
where opthint = 'WK1' and progName = 'YMFC112'
;
insert into cmnbatch.plan_table
select * from A540769.plan_table;
select * from cmnbatch.plan_view2
where opthint = 'WK1' and progName = 'YMFC112'
order by applname, progname, queryNo, qblockno, planno
;
commit ;
;x,
select current timestamp from sysibm.sysdummy1;
SELECT INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A,
SUM(CASE WHEN MF150019 BETWEEN 0 AND 999999999999999
THEN MF150019
ELSE 0 END) AS SUMME_Z,
SUM(CASE WHEN MF150019 BETWEEN -999999999999999 and 0
THEN MF150019
ELSE 0 END) AS SUMME_A,
0,
0
FROM oa1p.TMF150A1,oa1p.TMFOE1T1
Where MF150067 = INT_HIST_OE_BEZ
AND MF150013 BETWEEN OE_VON_DATE AND OE_Bis_Date
AND MF150013 BETWEEN '01.11.2010' AND '11.11.2010'
GROUP BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
ORdER BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
WITH UR
;
select current timestamp from sysibm.sysdummy1;
;x;
select * from cmnbatch.plan_view1
-- where progname = 'YMFC203' and version = 'MFR00002355DFB546C'
where progname = 'YMFC203' and version = 'MFR00001975C33357F'
-- and bind_time = '2010-11-12-21.46.53.627557'
order by applname, progname, queryNo, qblockno, planno
, bind_time desc
;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
SELECT COUNT(*)
FROM TABLE (
SELECT MF150001
FROM oa1p.TMF150A1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
UNION
SELECT MF150001
FROM oa1p.TMF150H1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
) AS X
WITH UR
;
explain plan set queryno = 2 for
SELECT COUNT(*)
FROM TABLE (
SELECT MF150001
FROM oa1p.TMF150A1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
UNION all
SELECT MF150001
FROM oa1p.TMF150H1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
) AS X
WITH UR
;
select * from plan_view2
order by applname, progname, queryNo, qblockno, planno
;
rollback
;x;
SELECT INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A,
SUM(CASE WHEN MF150019 BETWEEN 0 AND 999999999999999
THEN MF150019
ELSE 0 END) AS SUMME_Z,
SUM(CASE WHEN MF150019 BETWEEN -999999999999999 and 0
THEN MF150019
ELSE 0 END) AS SUMME_A,
0,
0
FROM oa1p.TMF150A1,oa1p.TMFOE1T1
Where MF150067 = INT_HIST_OE_BEZ
AND MF150013 BETWEEN OE_VON_DATE AND OE_Bis_Date
AND MF150013 BETWEEN '01.11.2010' AND '11.11.2010'
GROUP BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
ORdER BY INT_OE_BEZ,Int_Hist_OE_Bez,MF150066,
OE_VON_DATE,OE_Bis_Date,MF150019A
WITH UR
;
select current timestamp from sysibm.sysdummy1;
SELECT COUNT(*)
FROM TABLE (
SELECT MF150001
FROM oa1p.TMF150A1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
UNION
SELECT MF150001
FROM oa1p.TMF150H1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
) AS X
WITH UR
;
select current timestamp from sysibm.sysdummy1;
SELECT COUNT(*)
FROM TABLE (
SELECT MF150001
FROM oa1p.TMF150A1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
UNION all
SELECT MF150001
FROM oa1p.TMF150H1
WHERE MF150023 = '065911392784'
AND MF150055 LIKE '%'
AND MF150085 between 10 and 89
AND MF150013 BETWEEN '01.01.2008' and '10.11.2010'
AND ((MF150019 BETWEEN 100 AND 999999999999999)
OR (MF150019 BETWEEN -999999999999999 and -100))
) AS X
WITH UR
;
select current timestamp from sysibm.sysdummy1;
rollback