zOs/SQL/CATPKGLI

with l (pl, cx, new, old) as
(                 -- start
  select name, 0, varchar('', 500), varchar('', 500)
    from sysibm.sysPlan
    where name like 'NF5%'
  union all select l.pl, l.cx+1
      , l.new || case when collid like 'A1%' or collid like 'A2%'
                                    or collid like 'NTA%' then ''
  else ', '
      || case when p.location='' then '' else strip(location)||'.' end
      || strip(collid) || '.' || strip(name) end
      , l.old || ', '
      || case when p.location='' then '' else strip(location)||'.' end
      || strip(collid) || '.' || strip(name)
      from l
          join sysibm.sysPackList p
          on p.planName = l.pl and p.seqNo = cx+1
          where cx < 99999
)
select *
  from l a
    where cx=(select max(cx) from l r where a.pl = r.pl)
  order by pl
  with ur
;;;
select 'BIND PLAN('||strip(pl)||')'||
'   OWNER(S101869)        QUALIFIER(OA1T)+'
    ||' PKLIST('
    ||case when new = '' then '' else substr(new, 3) end new
   ,  ')+'
  from l a
    where cx=(select max(cx) from l r where a.pl = r.pl)
;;;
with s as
( select name
  , ( select count(*)
        from sysibm.sysPackList l
        where l.planName = p.name
            and (collid like 'A1%' or collid like 'A2%')
    ) a12
    from sysibm.sysplan p
)
select count(*)
      , sum(case when a12 > 0 then 1 else 0 end) "a12>0"
      , sum(case when a12 = 0 then 1 else 0 end) "a12=0"
      , sum(case when a12 = 1 then 1 else 0 end) "a12=1"
      , sum(case when a12 = 2 then 1 else 0 end) "a12=2"
      , sum(case when a12 = 3 then 1 else 0 end) "a12=3"
      , sum(case when a12 > 3 then 1 else 0 end) "a12>3"
    from s
    with ur
;;;
select collid, count(*)
    from sysibm.sysPackList
    where collid like 'A1%' or collid like 'A2%'
    group by collid
    order by collid
    with ur
;
select current server, count(*)
       , sum(case when collid = 'A1P' then 1 else 0 end) a1p
       , sum(case when collid = 'A2P' then 1 else 0 end) a2p
       , sum(case when collid = 'A1T' then 1 else 0 end) a1t
       , sum(case when collid = 'A2T' then 1 else 0 end) a2t
       , sum(case when collid = 'A1A' then 1 else 0 end) a1a
       , sum(case when collid = 'A2A' then 1 else 0 end) a2a
       , current timestamp
    from sysibm.sysPackList
    with ur
;;;
select planName, count(*)
       , max(case when collid <> 'A1P' then ''
             else substr(strip(char(seqNo)) || ': '
                      || strip(location) || '.' || strip(collid) || '.'
                      || strip(name), 1, 20) end)
       , max(case when collid <> 'A2P' then ''
             else substr(strip(char(seqNo)) || ': '
                      || strip(location) || '.' || strip(collid) || '.'
                      || strip(name), 1, 20) end)
       , max(case when collid <> 'A1T' then ''
             else substr(strip(char(seqNo)) || ': '
                      || strip(location) || '.' || strip(collid) || '.'
                      || strip(name), 1, 20) end)
       , max(case when collid <> 'A2T' then ''
             else substr(strip(char(seqNo)) || ': '
                      || strip(location) || '.' || strip(collid) || '.'
                      || strip(name), 1, 20) end)
       , max(case when collid <> 'A1A' then ''
             else substr(strip(char(seqNo)) || ': '
                      || strip(location) || '.' || strip(collid) || '.'
                      || strip(name), 1, 20) end)
       , max(case when collid <> 'A2A' then ''
             else substr(strip(char(seqNo)) || ': '
                      || strip(location) || '.' || strip(collid) || '.'
                      || strip(name), 1, 20) end)
    from sysibm.sysPackList
    where collid in ('A1A', 'A2A', 'A1T', 'A2T', 'A1P', 'A2P')
    group by planName
    with ur