zOs/SQL/EXPLAIBS

with cx as
(
select dbName db, tsName ts, dsNum part, instance
       , count(*) cnt
       , max(case when ICTYPE IN ('F','R','X')
                  then char(timestamp) || icType || strip(dsNum)
                  else '' end) laFu
       , max(case when ICTYPE IN ('I')
                  then char(timestamp) || icType || strip(dsNum)
                  else '' end) laInc
    from sysibm.sysCopy
    where ICTYPE IN ('F','R','X', 'I')
    group by dbName, tsName, dsNum, instance
)
, c as
(
select max(c1.laFu,  coalesce(c0.laFu,  '')) laFu,
       max(c1.laInc, coalesce(c0.laInc, '')) laInc,
       c1.db, c1.ts, c1.part, c1.instance
    from cx c1
    left join cx c0
      on c0.db = c1.db and c0.ts = c1.ts
           and c1.part <> 0 and c0.part = 0
)
select * from c
;
--- 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 ci(c, i) as
(           select 'N', 1 from sysibm.sysDummy1
  union all select 'Y', 1 from sysibm.sysDummy1
  union all select 'Y', 2 from sysibm.sysDummy1
)
, p as
(
 SELECT PT.DBNAME, pt.tsName, pt.partition, ci.i, ts.clone, ts.instance,
     case when ts.instance = i then 'base' else 'clone' end baCl,
     (  SELECT char(timestamp) || icType
          FROM  SYSIBM.SYSCOPY CP
          WHERE PT.DBNAME = CP.DBNAME
            AND PT.TSNAME = CP.TSNAME
            AND cp.dsNum in (PT.PARTITION, 0)
            AND cp.instance = ci.i
            AND  CP.ICTYPE IN ('F','R','X')
          order by timestamp desc
          fetch first 1 row only
     ) laFull,
     r.nActive,
     COPYLASTTIME,
     COPYUPDATEDPAGES,
     COPYCHANGES,
     COPYUPDATETIME
----  end   @proc selIncrCopy: select fullcopy etc. --------------------
 FROM   SYSIBM.SYSDATABASE DB
   join SYSIBM.SYSTABLESPACE TS
     on DB.NAME = PT.DBNAME
   join SYSIBM.SYSTABLEPART PT
     on DB.NAME = TS.DBNAME
       AND TS.NAME = PT.TSNAME
   join ci on ci.c=ts.clone
   left join SYSIBM.SYSTABLESpaceStats r
      on r.dbid = db.dbid
        and r.psid = ts.psid
        and r.partition = pt.partition
        and r.instance = ci.i
 WHERE  0 = 0
----  end   @proc missFUllcopies1: fehlende Fullcopies -----------------

----  begin @proc exclude ----------------------------------------------
----- begin @proc exclGen: gemeinsame excludes -------------------------
   AND NOT (PT.DBNAME like 'DSNDB%')            -- DB2 CATALOG
   AND NOT (PT.DBNAME LIKE 'DSN8%')             -- IBM TEST DB
   AND NOT (PT.DBNAME LIKE 'WKDBD%')            -- DB2 WORK DATABASE
   AND NOT (PT.DBNAME = 'DSNTESQ')              -- DB2 CATALOG CLONE
   AND NOT (PT.DBNAME LIKE 'DB2MAPP%')          -- REORG MAPPING TABLES
   AND NOT (PT.DBNAME LIKE 'DB2PLAN%')          -- explain tables
   and not translate(PT.dbName, '999999999AAAAAA', '012345678FISWXY')
           = 'DA999999'                         -- user datenbanken
   AND NOT (PT.DBNAME LIKE 'DB2ALA%')           -- marec  generated
   AND NOT (PT.DBNAME LIKE '%MAREC%')           -- marec generated
   AND NOT (PT.DBNAME LIKE 'DACME%')            -- Mail Heinz Bühler
   AND NOT (PT.DBNAME LIKE 'DGDB%')             -- PROTOTYPEN
   AND NOT (PT.DBNAME LIKE 'DGO%')              -- PROTOTYPEN
   AND NOT (PT.DBNAME LIKE '%A1X%')             -- Neue Prototypen
   AND NOT (PT.DBNAME LIKE 'DAU%')              -- Schulung Gerrit
   AND NOT (PT.DBNAME LIKE 'IDT%')              -- ibm tools
----  end   @proc exclGen: gemeinsame excludes ------------------------
   AND NOT (PT.DBNAME LIKE 'OE02%')    -- Mail Ivo Eichmann
   AND NOT (PT.DBNAME LIKE 'CSQ%')     -- M-QUEUE DATENBANK
----  end   @proc exclude ---------------------------------------------
----  end   @proc exclGen: gemeinsame excludes -------------------------

   AND NOT (PT.DBNAME = 'XC01A1P'  AND PT.TSNAME LIKE 'A2%'  )
                                       -- EOS: Armin Breyer
   AND NOT (PT.DBNAME = 'XR01A1P'  AND PT.TSNAME LIKE 'A2%'  )
                                       -- ERET: Armin Breyer
   AND NOT (PT.DBNAME = 'CSQDBOC' AND PT.TSNAME like 'TSBLOB%' )
----  end   @proc exclude ----------------------------------------------

   AND DB.TYPE NOT IN ('T','W')
----  begin @proc missFUllcopies2: fehlende Fullcopies -----------------
   AND TS.NTABLES <> 0
   AND PT.SPACEF <> -1 -- attention space is sometimes wrong|
 )
, q as
(
select case when laFull < char(current timestamp - 171 hours)
                then 'full old'
            when copyUpdateTime > current timestamp - 3 hours
                then 'no newUpd'
            when nActive * 0.1 <= COPYUPDATEDPAGES then 'full upda'
            when COPYUPDATEDPAGES <> 0 or copyChanges <> 0 then 'inc'
            else 'no changes'
            end copy,
      p.*
    from p
)
select *
    from q
    where left(copy, 2) <> 'no'
 ORDER BY DBNAME, TSNAME, PARTITION, i
 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
;;;;