zOs/SQL/EXPLAIQZ

set current path oa1p;
--- 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
WITH G AS
(
  SELECT LGRDBID, lgrpsid
     , LGRPART PA
     , COUNT(*) CNT
     , MAX(TRANSLATE('20YZ-MN-DE-', LGRUCDT, 'MNDEYZ')
         || TRANSLATE('HI.MN.ST.UV', LGRUCTM, 'HIMNSTUV')) lgrUcTm
     , MAX(LGRSLRSN) lgrSLrsn
     , MAX(LGRSLRSN) lgrELrsn
    FROM SYSIBM.SYSLGRNX
    GROUP BY LGRDBID, LGRPSID, LGRPART
)
, h as
(
  select oa1p.fqzCastC2I(LGRDBID) dbID
       , oa1p.fqzCastC2I(LGRPSID) psID
       , pa
       , cnt
       , timestamp(lgrUcTm) upd
            -- max(sommer, winterzeit) - 26 leapSeconds
            -- do not use current timeZone there was a winter once
       , timestamp(substr(lgrSlrsn, 2,8)) + 7174 seconds sLrsn
       , timestamp(substr(lgrElrsn, 2,8)) + 7174 seconds eLrsn
    from g
    order by 1, 2
)
SELECT DBNAME DB, NAME TS, PA
     , max( value(slrsn, '1111-11-11-11.11.11')
          , value(elrsn, '1111-11-11-11.11.11')
          , value(upd  , '1111-11-11-11.11.11') ) endtst
     , CNT
   FROM h
     JOIN (select * from SYSIBM.SYSTABLESPACE
              order by dbid, psid) s
     ON h.dbID = S.dbID+0 AND h.psID = s.psID+0
WITH UR
;
explain plan set queryno = 7   for
 with s as
 (
   select s.DBNAME db, s.NAME TS, p.partition PA
      , value( ( select
            max( value(timestamp(substr(max(LGRSLRSN), 2, 8))
             -- max(sommer, winterzeit) - 26 leapSeconds
             -- do not use current timeZone there was a winter once
                      + 7174 seconds, '1111-11-11-11.11.11')
               , value(timestamp(substr(max(LGRELRSN), 2, 8))
                      + 7174 seconds, '1111-11-11-11.11.11')
               , value(timestamp(max(
                     TRANSLATE('20YZ-MN-DE-', LGRUCDT, 'MNDEYZ')
                  || TRANSLATE('HI.MN.ST.UV', LGRUCTM, 'HIMNSTUV')))
                      , '1111-11-11-11.11.11')
               )
        from sysibm.sysLgRnX  l
            where l.lgrdbid = oa1p.fqzCastSmall2C(s.dbid)
              and l.lgrpsid = oa1p.fqzCastSmall2c(s.psid)
              and l.lgrpart = p.partition
        ), '1111-11-11-11.11.11') endTst
     from sysibm.sysTableSpace s
       join sysibm.sysTablePart p
         on s.dbName = p.dbname and s.name = p.tsname
 )
 select *
     from s
     where endTst > '1919-01-01-00.00.00'
 WITH UR
;
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
;;;;
set current path oa1p;
with i(i) as
( select 0 from sysibm.sysDummyE
  union all select i+1 from i where i < 330
)
, j(j) as
(
  select smallint(i) from i where i < 32768
  union all select smallint(-i) from i where i <= 32768
)
select j, hex(fqzCastsmall2c(j)), fqzCastc2small(fqzCastsmall2c(j))
    from j
 -- where j <> fqzCastc2small(fqzCastsmall2c(j))
 --     or fqzCastsmall2c(j)
 --     <> fqzcastsmall2c(fqzCastc2small(fqzCastsmall2c(j)))
    order by j
; select  current timestamp from sysibm.sysDummy1;
;x;
--- versionen, compiles und explains eines packages -------------------
select *
    from cmnBatch.plan_view0
    where   collid       = 'QZ'
        and prog         = 'QZPLB'
    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     = 'QZ'
        and progName     = 'QZPLB'
        and bind_time    = '2014-04-30-11.48.30.523307'
    order by collid, progName, version, bind_time,
             queryno, qBlockNo, planno, mixopSeq
    with ur
;
select *
    from cmnbatch.plan_viewPred
    where     collid     = 'QZ'
        and progName     = 'QZPLB'
        and bind_time    = '2014-04-30-11.48.30.523307'
    order by collid, progName, applName, bind_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;