zOs/SQL/EXPLAIKS

select hex(applname), p.*
    from cmnbatch.dsn_predicat_table p
    where explain_time = '2011-11-02-21.55.44.046535'
;
--- versionen, compiles und explains eines packages -------------------
select *
    from cmnbatch.plan_view0
    where   col          = 'KE'
        and prg          = 'YKEA028'
    order by pcOrd desc, biTi desc
    with ur
;
select *
    from cmnbatch.plan_view1
 -- from cmnbatch.plan_view2
 -- from cmnbatch.plan_view2Det  ----> index fehlt
    where     collid     = 'KE'
        and progName     = 'YKEA028'
        and bind_time in ('2008-11-13-13.08.55.317001'
                         ,'2011-11-02-21.55.44.046535'
                         )
    order by collid, progName, version, bind_time,
             queryno, qBlockNo, planno, mixopSeq
    with ur
;
select *
    from cmnbatch.plan_viewPred  ----> index fehlt
    where     collid     = 'KE'
        and progName     = 'YKEA028'
        and bind_time in ('2008-11-13-13.08.55.317001'
                         ,'2011-11-02-21.55.44.046535'
                         )
    order by applName, collid, progName, bind_time,
             queryNo, qBlockNo, planno, mixOpSeq,
             stage, predNo
    with ur
;;;;;;;;;;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
explain plan set queryno = 3   for
   SELECT
       DISTINCT A832. EXT_CONTRACT_ID,
       A833. CONTRACT_KEY,
       A833. PARTNER_KEY,
       A833. PARTNER_TYPE,
       A833. LE,
       A833. BU,
       A833. CONTRACT_COLL,
       A833. STATE,
       A833. LOCK_FROM_DATE,
       A833. LOCK_TO_DATE,
       A833. VALID_FROM_DATE,
       A833. VALID_TO_DATE,
       A833. LAST_UPDATE_TIME,
       A833. LAST_UPDATE_ID,
       A833. CONTROL_TIME,
       A833. COMMENT
   FROM
       oa1t.VKS832A1V B832
   JOIN
       oa1t.VKS833A1V B833
           ON B832. CONTRACT_KEY=B833. CONTRACT_KEY
   JOIN
       oa1t.VKS833A1V A833
           ON B833. CONTRACT_COLL=A833. CONTRACT_COLL
   JOIN
       oa1t.VKS832A1V A832
           ON A833. CONTRACT_KEY=A832. CONTRACT_KEY
   WHERE
       B832. EXT_CONTRACT_ID>=?
       AND B832. EXT_CONTRACT_ID<=?
       AND A833. PARTNER_TYPE>=?
       AND A833. PARTNER_TYPE<=?
       AND A833. VALID_FROM_DATE<=?
       AND A833. VALID_TO_DATE>=?
       AND A832. VALID_FROM_DATE<=?
       AND A832. VALID_TO_DATE>=? FOR FETCH ONLY
;
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
;;;;
select count(*), KS833060
    from oa1t.TKS833A1
    group by KS833060
    order by 1 desc
    with ur
    fetch first 100 rows only
;;;