zOs/SQL/CATPKDEP

--- 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 d as
(  select dLocation, dCollid, dName, dContoken, dType
     from sysibm.syspackdep d
     where dType not in ('O', 'P') and
      ( (bQualifier = 'OA1P'
           and ( (bName = 'TTG503A1' and bType in ('G', 'M', 'P', 'T'))
              or (bName = 'TTG501A1' and bType in ('G', 'M', 'P', 'T'))
              or (bName like 'TMF%'  and bType in ('G', 'M', 'P', 'T'))
              or (bName = 'VTG501A1' and bType = 'V')
              or (bName = 'VTG501A1' and bType = 'V')
              or (bName = 'VTG503A1' and bType = 'V') ) ) )
   union all select dLocation, dCollid, dName, dContoken, dType
     from sysibm.syspackdep d
     where dType not in ('O', 'P') and
        bQualifier = 'TG01A1P'
           and (  (bName = 'A051H' and bType in ('P', 'R'))
               or (bName = 'A051A' and bType in ('P', 'R')))
)
, p as
(
  select collid, name, version, type, dType
      , p.validate || p.isolation || p.valid || p.operative vivo
      , p.contoken
      , p.timestamp
      , p.lastUsed
      , case when lastUsed  > current date - 10 days then 'lastUse'
             when timestamp > current timestamp -7 days then 'creTst'
             when not exists (select 1
            from sysibm.syspackage r
            where r.location = p.location and r.collid = p.collid
                and r.name = p.name
                and r.timestamp > p.timestamp
                and r.timestamp <= current timestamp - 7 days)
            then 'new7' else 'no' end doReb
    from d join sysibm.sysPackage p
    on dLocation = location and dCollid = collid
       and dName = name and dConToken = conToken
)
select doReb, lastUsed, timestamp
    , strip(collid) || '.' || strip(name) || ':' || strip(version)
    , p.*
  from p
order by collid, name, timestamp desc
;
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
;;;;
;x;
with d as
(  select dLocation, dCollid, dName, dContoken, dType
     from sysibm.syspackdep d
     where dType not in ('O', 'P') and
        (bQualifier = 'OA1P'
           and ( (bName = 'TTG503A1' and bType in ('G', 'M', 'P', 'R'))
              or (bName = 'TTG501A1' and bType in ('G', 'M', 'P', 'R'))
              or (bName = 'VTG501A1' and bType = 'V')
              or (bName = 'VTG501A1' and bType = 'V')
              or (bName = 'VTG503A1' and bType = 'V') ) )
   group by dLocation, dCollid, dName, dContoken, dType
)
, p as
(
  select collid, name, version, type, dType
      , p.validate || p.isolation || p.valid || p.operative vivo
      , p.contoken
      , p.timestamp
      , p.lastUsed
      , value((select count(*)
            from sysibm.syspackage r
            where r.location = p.location and r.collid = p.collid
                and r.name = p.name
                and r.timestamp > p.timestamp
                and r.timestamp <= current timestamp - 7 days),0) nw7
    from d join sysibm.sysPackage p
    on dLocation = location and dCollid = collid
       and dName = name and dConToken = conToken
)
select * from p
order by 1, 2, 3, 4
;x;
select char(', ''' || strip(pk.NAME) || '''', 12) pkg
  , max(pcTimestamp) pc
  from sysIbm.sysPackage pk
  where  (pk.location, pk.collid, pk.name, pk.conToken) in
  (select pkd.dLocation, pkd.dCollid, pkd.dName, pkd.dConToken
    from sysIbm.sysPackDep pkd
    where  (pkd.bQualifier, pkd.bName) in
    (select t.creator, t.name
      from sysIbm.sysTables t
      where t.type not in ('A', 'V') and  t.name like 'TNG005A1%'
  ) )
  group by pk.name
  order by pk.name
;x;
select distinct p.collid, p.Name, p.version, p.type
      , p.validate || p.isolation || p.valid || p.operative vivo

/*      value((select count(*)
     from sysibm.syspackage r
       where r.location = p.location and r.collid = p.collid
        and r.name = p.name
        and r.timestamp > p.timestamp
        and r.timestamp < current timestamp - 7 days),0)   */
   from sysibm.syspackdep d join sysibm.syspackage p' ,
    'on p.location = d.dLocation and p.collid = d.dCollid' ,
       'and p.name = d.dName and  p.conToken = d.dConToken' ,
select char(', ''' || strip(pk.NAME) || '''', 12) pkg
  , left('-- ' || char(max(pcTimestamp)), 19) pc
  , max(lastUsed) lastUsed
  from sysIbm.sysPackage pk
  where  (pk.location, pk.collid, pk.name, pk.conToken) in
  (select pkd.dLocation, pkd.dCollid, pkd.dName, pkd.dConToken
    from sysIbm.sysPackDep pkd
    where  (pkd.bQualifier, pkd.bName) in
    (select t.creator, t.name
      from sysIbm.sysTables t
      where t.type in ('T') and t.creator = 'OA1P'
          and t.name in ('TDI002A1'
             --         ,'TWQX14A1'
             --         ,'TWQX13A1'
             --         ,'TWQ143A1'
                        )
  ) )
  group by pk.name
  order by pk.name