zOs/SQL/EXPLAIRM

--- versionen, compiles und explains eines packages -------------------
select *
    from cmnbatch.plan_view0
    where   collid       = 'RM'
        and prog         = 'YRMVER'
    order by pcBi  desc, bind_Time desc
    with ur
;
--- ausgewählte Explains analysieren
select *
    from cmnbatch.plan_view1
 -- from cmnbatch.plan_view2
 -- from cmnbatch.plan_view2Det
    where     collid     = 'RM'
        and progName     = 'YRMVER'
        and bind_time  in ('2012-03-17-19.03.04.705650'
                          ,'2012-06-25-07.56.26.829556'
                          )
    order by collid, progName, version,
        queryno,
        bind_time,
        qBlockNo, planno, mixopSeq
    with ur
;
--- 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 = 887 for
   -- DECLARE KEYDIRTYPECURS CURSOR FOR
                                  SELECT RM01001, RM01002, RM01003,
RM01004, RM01005, RM01006, RM01007, RM01008, RM01009, RM01010, RM01011,
RM01012, RM01013, RM01014, RM01015, RM01016, RM01017, RM01018
FROM oa1t.TRM010A1
WHERE (RM01011 = ? OR RM01014 = ?) AND RM01015 IN (?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND RM01003 <= ? AND RM01007 + 0 seconds >
?
;
    select *
        from cmnbatch.plan_view2Det  ----> index fehlt
        where     collid     = 'MF'
            and progName     = 'MF7010'
            and bind_time    = '2011-08-30-14.14.59.234601'
        order by collid, progName, version, bind_time,
                 queryno, qBlockNo, planno, mixopSeq
        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
;;;;
--- versionen, compiles und explains eines packages -------------------
select *
    from cmnbatch.plan_view0
    where   collid       = 'RM'
        and prog         = 'YRMVER'
    order by pcBi  desc, bind_Time desc
    with ur
;
--- ausgewählte Explains analysieren
select *
    from cmnbatch.plan_view1
 -- from cmnbatch.plan_view2
 -- from cmnbatch.plan_view2Det
    where     collid     = 'RM'
        and progName     = 'YRMVER'
        and bind_time  in ('2012-05-20-01.48.59.380110'
                          ,'2011-02-10-10.54.30.980394'
                          )
    order by collid, progName, version,
        queryno,
        bind_time,
        qBlockNo, planno, mixopSeq
    with ur
; x;
select *
    from cmnbatch.plan_viewPred
    where     collid     = 'MF'
        and progName     = 'MF7010'
        and bind_time    = '2011-08-30-14.14.59.234601'
    order by collid, progName, applName, bind_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;