zOs/SQL/HUNGER
--- 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 = 2 for
SELECT distinct
v858.ident_id AS UserId
,cast(';' as char(1))
,u831.partner_id AS USER_CIF
,cast(';' as char(1))
,SUBSTR(U100.CD100452,9,5) AS U_CUST_OFFICE
,cast(';' as char(1))
,u831.DOMIZIL AS USER_DOM
,cast(';' as char(1))
,U831.CUSTOMER_SEGMENT AS USER_SEGM
,cast(';' as char(1))
,o831.partner_id AS OWNER_CIF
,cast(';' as char(1))
,SUBSTR(U100.CD100452,9,5) AS O_CUST_OFFICE
,cast(';' as char(1))
,O831.DOMIZIL AS OWNER_DOM
,cast(';' as char(1))
,O831.CUSTOMER_SEGMENT AS OWNER_SEGM
,cast(';' as char(1))
,v832.ext_contract_id AS CNTRACT_ID
,cast(';' as char(1))
,v843.tab_text1 AS PROCUCT
,cast(';' as char(1))
,v835.service_key AS SERVICE
,cast(';' as char(1))
,S843.tab_text1 AS SERVICE_TEXT
,cast(';' as char(1))
,V859.SECID1_LAST_LOGON AS LAST_LOGON_SEC1
,cast(';' as char(1))
,V859.SECID2_LAST_LOGON AS LAST_LOGON_SEC2
,cast(';' as char(1))
,CAST((CASE
WHEN((V859.SECID1_LAST_LOGON> V859.SECID2_LAST_LOGON
AND V859.SECID1_LAST_LOGON^='9999-12-31-23.59.59.999999')
OR V859.SECID2_LAST_LOGON='9999-12-31-23.59.59.999999')
THEN V859.SECID1_LAST_LOGON
ELSE V859.SECID2_LAST_LOGON
END) AS CHAR(26)) lastLogon
FROM
oa1t.VKS831A1V O831
,oa1t.VKS831A1V U831
,oa1t.VKS832A1V V832
,oa1t.VKS833A1V V833
,oa1t.VKS835A1V V835
,oa1t.VKS858A1V V858
,oa1t.VKS859A1V V859
,oa1t.VKS843A1V V843
,oa1t.VKS843A1V S843
,oa1t.TCD100A1 U100
,oa1t.TCD100A1 O100
WHERE
V835.PRODUCT_ID IN (
'0095',
'0125',
'0141',
'0148',
'0149',
'0150',
'0151',
'0183',
'0191',
'0192',
'0193' )
and v835.partner_type = 3
AND V835.CONTRACT_KEY = V832.CONTRACT_KEY
AND V835.CONTRACT_KEY = V833.CONTRACT_KEY
and v833.partner_type = 3
AND V833.partner_key = u831.partner_key
AND U831.partner_ID = U100.CD100001
AND V835.arrang_partner_key = O831.partner_key
AND O831.partner_ID = O100.CD100001
and v833.partner_key = V858.partner_key
AND V858.ident_id = V859.ident_id
AND V859.application_type = 15
AND V843.tab_art = 'PRODUCT_ID'
AND V843.tab_code = V835.PRODUCT_ID
AND S843.tab_art = 'SERVICE_ID'
AND S843.tab_code = V835.SERVICE_KEY
AND S843.TAB_LANGUAGE_CODE = '001'
and current date < o831.valid_to_date
and current date < u831.valid_to_date
and current date < v832.valid_to_date
and current date < v833.valid_to_date
and current date < v858.valid_to_date
and current date < v859.valid_to_date
and current date between v835.valid_from_date and
v835.valid_to_date
ORDER BY u831.partner_id
WITH UR;
explain plan set queryno = 3 for
SELECT
v858.ident_id
,CHAR(DECIMAL(COUNT(distinct V841.ACTIVITY_TSTAMP),10,0))
FROM
oa1t.VKS831A1V O831
,oa1t.VKS831A1V U831
,oa1t.VKS832A1V V832
,oa1t.VKS833A1V V833
,oa1t.VKS835A1V V835
,oa1t.VKS858A1V V858
,oa1t.VKS859A1V V859
,oa1t.VKS841H1V V841
WHERE
V835.PRODUCT_ID IN (
'0095',
'0125',
'0141',
'0148',
'0149',
'0150',
'0151',
'0183',
'0191',
'0192',
'0193' )
and v835.partner_type = 3
AND V835.CONTRACT_KEY = V832.CONTRACT_KEY
AND V835.CONTRACT_KEY = V833.CONTRACT_KEY
and v833.partner_type = 3
AND V833.partner_key = u831.partner_key
AND V835.arrang_partner_key = O831.partner_key
and v833.partner_key = V858.partner_key
AND V858.ident_id = V859.ident_id
AND V859.application_type = 15
AND V858.IDENT_ID = V841.LOG_KEY
AND V841.APPLICATION_TYPE = 15
AND V841.LOG_CODE = '0000'
and current date < o831.valid_to_date
and current date < u831.valid_to_date
and current date < v832.valid_to_date
and current date < v833.valid_to_date
and current date < v858.valid_to_date
and current date < v859.valid_to_date
and current date between v835.valid_from_date and
v835.valid_to_date
GROUP BY V858.ident_id
WITH UR;
explain plan set queryno = 23 for
with b as
(
SELECT
v858.ident_id
,CHAR(DECIMAL(COUNT(distinct V841.ACTIVITY_TSTAMP),10,0))
countActTst
FROM
oa1t.VKS831A1V O831
,oa1t.VKS831A1V U831
,oa1t.VKS832A1V V832
,oa1t.VKS833A1V V833
,oa1t.VKS835A1V V835
,oa1t.VKS858A1V V858
,oa1t.VKS859A1V V859
,oa1t.VKS841H1V V841
WHERE
V835.PRODUCT_ID IN (
'0095',
'0125',
'0141',
'0148',
'0149',
'0150',
'0151',
'0183',
'0191',
'0192',
'0193' )
and v835.partner_type = 3
AND V835.CONTRACT_KEY = V832.CONTRACT_KEY
AND V835.CONTRACT_KEY = V833.CONTRACT_KEY
and v833.partner_type = 3
AND V833.partner_key = u831.partner_key
AND V835.arrang_partner_key = O831.partner_key
and v833.partner_key = V858.partner_key
AND V858.ident_id = V859.ident_id
AND V859.application_type = 15
AND V858.IDENT_ID = V841.LOG_KEY
AND V841.APPLICATION_TYPE = 15
AND V841.LOG_CODE = '0000'
and current date < o831.valid_to_date
and current date < u831.valid_to_date
and current date < v832.valid_to_date
and current date < v833.valid_to_date
and current date < v858.valid_to_date
and current date < v859.valid_to_date
and current date between v835.valid_from_date and
v835.valid_to_date
GROUP BY V858.ident_id
)
SELECT distinct
v858.ident_id AS UserId
,cast(';' as char(1))
,u831.partner_id AS USER_CIF
,cast(';' as char(1))
,SUBSTR(U100.CD100452,9,5) AS U_CUST_OFFICE
,cast(';' as char(1))
,u831.DOMIZIL AS USER_DOM
,cast(';' as char(1))
,U831.CUSTOMER_SEGMENT AS USER_SEGM
,cast(';' as char(1))
,o831.partner_id AS OWNER_CIF
,cast(';' as char(1))
,SUBSTR(U100.CD100452,9,5) AS O_CUST_OFFICE
,cast(';' as char(1))
,O831.DOMIZIL AS OWNER_DOM
,cast(';' as char(1))
,O831.CUSTOMER_SEGMENT AS OWNER_SEGM
,cast(';' as char(1))
,v832.ext_contract_id AS CNTRACT_ID
,cast(';' as char(1))
,v843.tab_text1 AS PROCUCT
,cast(';' as char(1))
,v835.service_key AS SERVICE
,cast(';' as char(1))
,S843.tab_text1 AS SERVICE_TEXT
,cast(';' as char(1))
,V859.SECID1_LAST_LOGON AS LAST_LOGON_SEC1
,cast(';' as char(1))
,V859.SECID2_LAST_LOGON AS LAST_LOGON_SEC2
,cast(';' as char(1))
,CAST((CASE
WHEN((V859.SECID1_LAST_LOGON> V859.SECID2_LAST_LOGON
AND V859.SECID1_LAST_LOGON^='9999-12-31-23.59.59.999999')
OR V859.SECID2_LAST_LOGON='9999-12-31-23.59.59.999999')
THEN V859.SECID1_LAST_LOGON
ELSE V859.SECID2_LAST_LOGON
END) AS CHAR(26)) lastLogon
, b.countActTst
FROM
oa1t.VKS831A1V O831
,oa1t.VKS831A1V U831
,oa1t.VKS832A1V V832
,oa1t.VKS833A1V V833
,oa1t.VKS835A1V V835
,oa1t.VKS858A1V V858
,oa1t.VKS859A1V V859
,oa1t.VKS843A1V V843
,oa1t.VKS843A1V S843
,oa1t.TCD100A1 U100
,oa1t.TCD100A1 O100
,b
WHERE
b.ident_id = v858.ident_id
and V835.PRODUCT_ID IN (
'0095',
'0125',
'0141',
'0148',
'0149',
'0150',
'0151',
'0183',
'0191',
'0192',
'0193' )
and v835.partner_type = 3
AND V835.CONTRACT_KEY = V832.CONTRACT_KEY
AND V835.CONTRACT_KEY = V833.CONTRACT_KEY
and v833.partner_type = 3
AND V833.partner_key = u831.partner_key
AND U831.partner_ID = U100.CD100001
AND V835.arrang_partner_key = O831.partner_key
AND O831.partner_ID = O100.CD100001
and v833.partner_key = V858.partner_key
AND V858.ident_id = V859.ident_id
AND V859.application_type = 15
AND V843.tab_art = 'PRODUCT_ID'
AND V843.tab_code = V835.PRODUCT_ID
AND S843.tab_art = 'SERVICE_ID'
AND S843.tab_code = V835.SERVICE_KEY
AND S843.TAB_LANGUAGE_CODE = '001'
and current date < o831.valid_to_date
and current date < u831.valid_to_date
and current date < v832.valid_to_date
and current date < v833.valid_to_date
and current date < v858.valid_to_date
and current date < v859.valid_to_date
and current date between v835.valid_from_date and
v835.valid_to_date
ORDER BY u831.partner_id
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
;;;;