zOs/SQL/KE5480B
delete from A540769.plan_table
;
declare global temporary table
tab (tab_text3 char(4) not null, tab_code char(20) not null,
tab_type2 integer not null, tab_type1 integer not null)
;
--create unique index session.tab
-- on session.tab (tab_code asc, tab_type2 asc, tab_type1 asc,
-- tab_text3 asc)
--;
explain plan set queryno = 55 for
insert into session.tab
(
select V843.TAB_TEXT3, V843.TAB_CODE, V843.TAB_TYPE2, V843B.TAB_TYPE1
from oa1p.VKS843A1V V843, oa1p.VKS843A1V V843B
where V843.TAB_ART =
'PRODUCT_ID' AND
V843B.TAB_ART = 'APPLICATION_IDENT' AND
V843B.TAB_TEXT3 = V843.TAB_TEXT3
)
;
explain plan set queryno = 99 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, tab.TAB_TEXT3, V858.VALID_TO_DATE,
V859.VALID_TO_DATE, V859.APPLICATION_TYPE
FROM oa1p.VKS831A1V V831,
oa1p.VKS833A1V V833, oa1p.VKS835A1V V835
, session.tab tab,
oa1p.VKS858A1V V858, oa1p.VKS859A1V V859
WHERE V833.PARTNER_KEY >= ? AND
V833.PARTNER_TYPE = 3
AND V831.PARTNER_KEY = V833.PARTNER_KEY AND
V835.PARTNER_KEY = V833.PARTNER_KEY AND V835.PARTNER_TYPE = 3 and
V835.CONTRACT_KEY = V833.CONTRACT_KEY
AND V858.PARTNER_KEY =
V833.PARTNER_KEY AND V859.IDENT_ID = V858.IDENT_ID
AND tab.TAB_CODE = V835.PRODUCT_ID AND tab.TAB_TYPE2 =
V858.IDENT_TYPE
AND V859.APPLICATION_TYPE =
tab.TAB_TYPE1
AND ( V833.LAST_UPDATE_TIME > ?
or V835.LAST_UPDATE_TIME > ?
OR V858.VALID_TO_DATE BETWEEN ? AND ?
or V859.VALID_TO_DATE BETWEEN ? AND ?
OR V859.VALID_FROM_DATE >= ?
)
ORDER BY V833.PARTNER_KEY, V831.PARTNER_ID, V858.IDENT_ID,
tab.TAB_TEXT3, V859.APPLICATION_TYPE, V858.VALID_TO_DATE,
V859.VALID_TO_DATE WITH UR
;
explain plan set queryno = 111 for
with u as
(
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3,
V833.LAST_UPDATE_TIME v833last,
V835.LAST_UPDATE_TIME v835Last,
V858.VALID_TO_DATE v858validTo,
V859.VALID_From_DATE v859validFrom,
V859.VALID_TO_DATE v859validTo, V859.APPLICATION_TYPE
FROM oa1p.VKS831A1V V831,
oa1p.VKS833A1V V833, oa1p.VKS835A1V V835
, oa1p.VKS843A1V V843, oa1p.VKS843A1V V843B,
oa1p.VKS858A1V V858, oa1p.VKS859A1V V859
WHERE V833.PARTNER_KEY >= ? AND
V833.PARTNER_TYPE = 3
AND V831.PARTNER_KEY = V833.PARTNER_KEY AND
V835.PARTNER_KEY = V833.PARTNER_KEY AND V835.PARTNER_TYPE = 3 and
V835.CONTRACT_KEY = V833.CONTRACT_KEY
AND V858.PARTNER_KEY =
V833.PARTNER_KEY AND V859.IDENT_ID = V858.IDENT_ID AND V843.TAB_ART =
'PRODUCT_ID' AND V843.TAB_CODE = V835.PRODUCT_ID AND V843.TAB_TYPE2 =
V858.IDENT_TYPE AND V843B.TAB_ART = 'APPLICATION_IDENT' AND
V843B.TAB_TEXT3 = V843.TAB_TEXT3 AND V859.APPLICATION_TYPE =
V843B.TAB_TYPE1
)
select * from u where V833LAST > ?
union all select * from u where V835LAST > ?
union all select * from u where V858VALIDTO BETWEEN ? AND ?
union all select * from u where V859VALIDTO BETWEEN ? AND ?
or V859VALIDFrom >= ?
ORDER BY PARTNER_KEY, PARTNER_ID, IDENT_ID,
TAB_TEXT3, APPLICATION_TYPE, V858VALIDTO,
V859VALIDTO WITH UR
;
explain plan set queryno = 222 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE,
V859.VALID_TO_DATE, V859.APPLICATION_TYPE
FROM oa1p.VKS831A1V V831,
oa1p.VKS833A1V V833, oa1p.VKS835A1V V835
, oa1p.VKS843A1V V843, oa1p.VKS843A1V V843B,
oa1p.VKS858A1V V858, oa1p.VKS859A1V V859
WHERE V833.PARTNER_KEY >= ? AND
V833.PARTNER_TYPE = 3
AND V831.PARTNER_KEY = V833.PARTNER_KEY AND
V835.PARTNER_KEY = V833.PARTNER_KEY AND V835.PARTNER_TYPE = 3 and
V835.CONTRACT_KEY = V833.CONTRACT_KEY
AND V858.PARTNER_KEY =
V833.PARTNER_KEY AND V859.IDENT_ID = V858.IDENT_ID AND V843.TAB_ART =
'PRODUCT_ID' AND V843.TAB_CODE = V835.PRODUCT_ID AND V843.TAB_TYPE2 =
V858.IDENT_TYPE AND V843B.TAB_ART = 'APPLICATION_IDENT' AND
V843B.TAB_TEXT3 = V843.TAB_TEXT3 AND V859.APPLICATION_TYPE =
V843B.TAB_TYPE1
AND ( V833.LAST_UPDATE_TIME > ?
or V835.LAST_UPDATE_TIME > ?
OR V858.VALID_TO_DATE BETWEEN ? AND ?
or V859.VALID_TO_DATE BETWEEN ? AND ?
OR V859.VALID_FROM_DATE >= ?
)
ORDER BY V833.PARTNER_KEY, V831.PARTNER_ID, V858.IDENT_ID,
V843.TAB_TEXT3, V859.APPLICATION_TYPE, V858.VALID_TO_DATE,
V859.VALID_TO_DATE WITH UR
;
explain plan set queryno = 333 for
with a as
(
SELECT DISTINCT V833.PARTNER_KEY
,V833.PARTNER_TYPE, V833.CONTRACT_KEY, V833.LAST_UPDATE_TIME
,V835.PRODUCT_ID, V835.LAST_UPDATE_TIME V835LAST
FROM
oa1p.VKS833A1V V833, oa1p.VKS835A1V V835
WHERE V833.PARTNER_KEY >= ? AND
V833.PARTNER_TYPE = 3
and V835.PARTNER_KEY = V833.PARTNER_KEY AND V835.PARTNER_TYPE = 3 AND
V835.CONTRACT_KEY = V833.CONTRACT_KEY
)
SELECT DISTINCT a.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE,
V859.VALID_TO_DATE, V859.APPLICATION_TYPE
FROM oa1p.VKS831A1V V831,
a
, oa1p.VKS843A1V V843, oa1p.VKS843A1V V843B,
oa1p.VKS858A1V V858, oa1p.VKS859A1V V859
WHERE
V831.PARTNER_KEY = a.PARTNER_KEY
AND V858.PARTNER_KEY =
a.PARTNER_KEY AND V859.IDENT_ID = V858.IDENT_ID AND V843.TAB_ART =
'PRODUCT_ID' AND V843.TAB_CODE = a.PRODUCT_ID AND V843.TAB_TYPE2 =
V858.IDENT_TYPE AND V843B.TAB_ART = 'APPLICATION_IDENT' AND
V843B.TAB_TEXT3 = V843.TAB_TEXT3 AND V859.APPLICATION_TYPE =
V843B.TAB_TYPE1
AND ( a.LAST_UPDATE_TIME > ?
or a.V835LAST > ?
OR V858.VALID_TO_DATE BETWEEN ? AND ?
or V859.VALID_TO_DATE BETWEEN ? AND ?
OR V859.VALID_FROM_DATE >= ?
)
ORDER BY a.PARTNER_KEY, V831.PARTNER_ID, V858.IDENT_ID,
V843.TAB_TEXT3, V859.APPLICATION_TYPE, V858.VALID_TO_DATE,
V859.VALID_TO_DATE WITH UR
;
select * from plan_view5a
-- where queryno in ( 149, 222)
-- order by applname, progname, queryNo, qblockno, planno
with ur
;
rollback
;
x
select min(partner_key), max(partner_key)
from oa1p.VKS833A1V
with ur
;
x