zOs/SQL/CATPKD2

-- packDep: index ==> table
with d as
(  select d.dLocation, d.dCollid, d.dName, d.dContoken, d.dType, d.bType
       , i.creator cr, i.name tb,             l.bType lbTy
       , p.valid, p.operative
     from sysibm.syspackdep d
       join sysibm.sysindexes i
         on d.bQualifier = i.creator and d.bName = i.name
       left join sysibm.syspackdep l
         on d.dLocation = l.dLocation and d.dCollid = l.dCollid
           and d.dName = l.dName and d.dContoken = l.dContoken
           and d.dType = l.dType
           and l.bQualifier = i.tbcreator and l.bName = i.tbName
       left join sysibm.syspackage p
         on d.dLocation = p.Location and d.dCollid = p.Collid
           and d.dName = p.Name and d.dContoken = p.Contoken
     where d.dType not in ('O', 'P')
      -- and d.dLocation = '' and d.dCollid like 'M%'

)
select substr(strip(cr) || '.' || strip(tb), 1, 20)
          , bType, dType, lbTy, valid, operative
     , strip(dCollid) || '.' || strip(dName)
          || ':' || strip(hex(dContoken))
   from d
   where lbTy is null and valid = 'Y'
                      -- and tTy <> 'G'
fetch first 100 rows only
;x;
-- packDep: ts ==> table
with d as
(  select d.dLocation, d.dCollid, d.dName, d.dContoken, d.dType, d.bType
       , t.creator cr, t.name tb, t.type tTy, l.bType lbTy
       , p.valid, p.operative, s.nTables
     from sysibm.syspackdep d join sysibm.sysTables t
         on d.bQualifier = t.dbName  and d.bName = t.tsName
           and t.type not in ('A', 'V')
       left join sysibm.systablespace s
         on t.dbName = s.dbName and t.tsName = s.name
       left join sysibm.syspackdep l
         on d.dLocation = l.dLocation and d.dCollid = l.dCollid
           and d.dName = l.dName and d.dContoken = l.dContoken
           and d.dType = l.dType
           and l.bQualifier = t.creator and l.bName = t.Name
       left join sysibm.syspackage p
         on d.dLocation = p.Location and d.dCollid = p.Collid
           and d.dName = p.Name and d.dContoken = p.Contoken
     where d.dType not in ('O', 'P')
      --   and d.dLocation = '' and d.dCollid like 'N%'

)
select substr(strip(cr) || '.' || strip(tb), 1, 20)
     , tTy, bType, dType, lbTy, valid, operative, nTables
     , strip(dCollid) || '.' || strip(dName)
          || ':' || strip(hex(dContoken))
   from d
   where lbTy is null and valid = 'Y'  and nTables <= 1
                      and tTy <> 'G'
fetch first 100 rows only
;x;
-- packDep: table ==> TS
with d as
(  select d.dLocation, d.dCollid, d.dName, d.dContoken, d.dType, d.bType
       , t.creator cr, t.name tb, t.type tTy, l.bType lbTy
       , p.valid, p.operative
     from sysibm.syspackdep d join sysibm.sysTables t
         on d.bQualifier = t.creator and d.bName = t.name
           and t.type not in ('A', 'V')
       left join sysibm.syspackdep l
         on d.dLocation = l.dLocation and d.dCollid = l.dCollid
           and d.dName = l.dName and d.dContoken = l.dContoken
           and d.dType = l.dType
           and l.bQualifier = t.dbName and l.bName = t.tsName
       left join sysibm.syspackage p
         on d.dLocation = p.Location and d.dCollid = p.Collid
           and d.dName = p.Name and d.dContoken = p.Contoken
     where d.dType not in ('O', 'P')
           and d.dLocation = '' and d.dCollid =    'NJ'

)
select substr(strip(cr) || '.' || strip(tb), 1, 20)
     , tTy, bType, dType, lbTy, valid, operative
     , strip(dCollid) || '.' || strip(dName)
          || ':' || strip(hex(dContoken))
   from d
   where lbTy is null and valid = 'Y' and bType <> 'G'
fetch first 100 rows only
;x;
with d as
(  select d.dLocation, d.dCollid, d.dName, d.dContoken, d.dType, d.bType
       , t.creator cr, t.name tb, t.type tTy, l.bType lbTy
       , p.valid, p.operative
     from sysibm.syspackdep d join sysibm.sysTables t
         on d.bQualifier = t.creator and d.bName = t.name
           and t.type not in ('A', 'V')
       left join sysibm.syspackdep l
         on d.dLocation = l.dLocation and d.dCollid = l.dCollid
           and d.dName = l.dName and d.dContoken = l.dContoken
           and d.dType = l.dType
           and l.bQualifier = t.dbName and l.bName = t.tsName
       left join sysibm.syspackage p
         on d.dLocation = p.Location and d.dCollid = p.Collid
           and d.dName = p.Name and d.dContoken = p.Contoken
     where d.dType not in ('O', 'P')
           and d.dLocation = '' and d.dCollid =    'NJ'

)
select substr(strip(cr) || '.' || strip(tb), 1, 20)
     , tTy, bType, dType, lbTy, valid, operative
     , strip(dCollid) || '.' || strip(dName)
          || ':' || strip(hex(dContoken))
   from d
   where lbTy is null and valid = 'Y' and bType <> 'G'
fetch first 100 rows only
;x;
              or (bName = 'VTG501A1' and bType = 'V')
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') ) )
   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
      , 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
;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