zOs/SQL/EXPLAICM

--- 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 DISTINCT ROLE_KEY FROM SSXX28Q0.SCM_ORG_ROLE
WHERE ROLE_TYPE = 'CMN-ACF-RULE'
AND ROLE_KEY_TYPE LIKE 'USER%'
AND ROLE_PID = 'F325756'
UNION SELECT DISTINCT ROLE_KEY FROM SSXX28Q0.SCM_ORG_ROLE
WHERE ROLE_TYPE = 'CMN-ACF-RULE'
AND ROLE_KEY_TYPE LIKE 'ORG%'
AND ROLE_PID IN ('K','KG','KGO','KGOU','KGOU3','KGOU32','KGOU322')
;
explain plan set queryno = 5   for
SELECT DISTINCT ROLE_KEY FROM SSXX28QS.SCM_ORG_ROLE
WHERE ROLE_TYPE = 'CMN-ACF-RULE'
AND ROLE_KEY_TYPE LIKE 'USER%'
AND ROLE_PID = 'F325756'
UNION SELECT DISTINCT ROLE_KEY FROM SSXX28QS.SCM_ORG_ROLE
WHERE ROLE_TYPE = 'CMN-ACF-RULE'
AND ROLE_KEY_TYPE LIKE 'ORG%'
AND ROLE_PID IN ('K','KG','KGO','KGOU','KGOU3','KGOU32','KGOU322')
;
explain plan set queryno = 13  for
SELECT DISTINCT ROLE_KEY FROM SSXX28Q0.SCM_ORG_ROLE
WHERE  '' || ROLE_TYPE = 'CMN-ACF-RULE'
      AND ((ROLE_KEY_TYPE LIKE 'USER%'
        AND ROLE_PID = 'F325756'
      ) or
        ( ROLE_KEY_TYPE LIKE 'ORG%'
        AND ROLE_PID
          IN ('K','KG','KGO','KGOU','KGOU3','KGOU32','KGOU322')
      ))
;
explain plan set queryno = 14  for
SELECT DISTINCT ROLE_KEY FROM SSXX28Q0.SCM_ORG_ROLE r
where (role_type, role_key_type, role_key)
in ( select role_type, role_key_type, role_key
       FROM SSXX28Q0.SCM_ORG_ROLE s
       WHERE ROLE_TYPE = 'CMN-ACF-RULE'
         AND ((ROLE_KEY_TYPE LIKE 'USER%'
         AND ROLE_PID = 'F325756'
         ) or
         ( ROLE_KEY_TYPE LIKE 'ORG%'
           AND ROLE_PID
           IN ('K','KG','KGO','KGOU','KGOU3','KGOU32','KGOU322')
      )))
;
select * from  plan_view1
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by collid, progName, explain_time,
             queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
    with ur
;
rollback
;;;;
select count(*), role_type, min(role_key_type)
                          , max(role_key_type)
     from SSXX28QS.SCM_ORG_ROLE
     group by role_type, left(role_key_type, 3)
     order by 1 desc
     fetch first 100 rows only
     with ur
;x;