zOs/SQL/KE54805
set current sqlId = 'A540769';
delete from A540769.plan_table
;
explain plan set queryno = 111 for
SELECT DISTINCT V833.PARTNER_KEY
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
AND ( V833.LAST_UPDATE_TIME > ?
or V835.LAST_UPDATE_TIME > ?
)
ORDER BY V833.PARTNER_KEY
WITH UR
;
explain plan set queryno = 200 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 = 290 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 = 295 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
;
explain plan set queryno = 300 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE aa,
V859.VALID_TO_DATE bb, 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 > current timestamp - 7 days
or V835.LAST_UPDATE_TIME > current timestamp - 7 days
OR V858.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
or V859.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
OR V859.VALID_FROM_DATE >= current date - 7 days
)
;
explain plan set queryno = 304 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE aa,
V859.VALID_TO_DATE bb, 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 >= 'a' 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 > current timestamp - 7 days
or V835.LAST_UPDATE_TIME > current timestamp - 7 days
OR V858.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
or V859.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
OR V859.VALID_FROM_DATE >= current date - 7 days
)
;
explain plan set queryno = 308 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE aa,
V859.VALID_TO_DATE bb, 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 >= 'A' 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 > current timestamp - 7 days
or V835.LAST_UPDATE_TIME > current timestamp - 7 days
OR V858.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
or V859.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
OR V859.VALID_FROM_DATE >= current date - 7 days
)
;
explain plan set queryno = 310 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE aa,
V859.VALID_TO_DATE bb, 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 >= '0' 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 > current timestamp - 7 days
or V835.LAST_UPDATE_TIME > current timestamp - 7 days
OR V858.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
or V859.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
OR V859.VALID_FROM_DATE >= current date - 7 days
)
;
explain plan set queryno = 315 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE aa,
V859.VALID_TO_DATE bb, 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 >= '5' 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 > current timestamp - 7 days
or V835.LAST_UPDATE_TIME > current timestamp - 7 days
OR V858.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
or V859.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
OR V859.VALID_FROM_DATE >= current date - 7 days
)
;
explain plan set queryno = 319 for
SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, V843.TAB_TEXT3, V858.VALID_TO_DATE aa,
V859.VALID_TO_DATE bb, 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 >= '9' 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 > current timestamp - 7 days
or V835.LAST_UPDATE_TIME > current timestamp - 7 days
OR V858.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
or V859.VALID_TO_DATE BETWEEN current date - 7 days
and current date + 7 days
OR V859.VALID_FROM_DATE >= current date - 7 days
)
;
select * from plan_view5a
-- where queryno in ( 149, 222)
-- order by applname, progname, queryNo, qblockno, planno
union select * from cmnbatch.plan_view5a
where progName = 'KE5480'
-- and version = '2008-08-07-18.52.54.033048'
-- and collid = 'HP3'
and (
bind_time = '2008-08-07-18.52.54.033048'
and queryno = 1613
or bind_time = '2008-11-15-00.48.01.023700'
and queryno = 531
)
order by applname, progname, queryNo, timestamp ,
qblockno, planno
;
rollback
;
x
select left(V858.PARTNER_KEY, 1), count(*)
from oa1p.VKS858A1V V858
group by left(V858.PARTNER_KEY, 1)
order by 1
with ur
;
x