zOs/SQL/CATPKGIX

                   -- index Zugriffe pro Package auf TMF150A1

with v as          -- packages die unsere Tabelle benutzen
(                  -- mit neuestem PreCompile pro Package
select dLocation loc, dCollid col, dName pkg,
       substr(max(char(p.pcTimeStamp) || p.version), 27) ver
    from sysibm.syspackdep d
        left join sysibm.sysPackage p
        on                   p.location = d.dLocation
                         and p.Collid = d.dCollid
                         and p.name = d.dName
                         and p.conToken = d.dConToken
                         and p.owner = 'CMNBATCH'
    where -- bType in ('P', 'R')
            bQualifier = 'OA1T' and bName = 'TMF150A1'
      group by dLocation, dCollid, dName
)
, b as             -- neusten bind pro package
(
select v.*, (select max(e.bind_time)
                 from cmnbatch.plan_table e
                 where  e.COLLID    = v.col
                    and e.PROGNAME  = v.pkg
                    and e. VERSION   = v.ver
            ) biTi
    from v
)
                   -- explain rows dieser pacakges MIT unserer tabelle
                   -- groupiert pro index
select substr(col, 1, 8) col, substr(pkg, 1, 8) pkg,
       substr(ver, 1, 20) vers,
       TYPE,   ACCESS,  INDEX,
       min(mc_o) MC_O, max(mc_o) MC_O, count(*) count,
       biTi bindTime
    from b
        left join cmnBatch.plan_view1 v
        on      COLLID    = col
            and PROGNAME  = pkg
            and VERSION   = ver
            and BIND_TIME = biTi
            and table     = 'TMF150A1'
    group by loc, col, pkg, ver, biTi, TYPE,   ACCESS,  INDEX
    order by loc, col, pkg, ver, biTi, TYPE,   ACCESS,  INDEX
    with ur
;;;
-- neuer Versuch, ohne group mit fetch first
select substr(strip(location) || '.' || strip(collid)
           || '.' || strip(name) || ':' || strip(version), 1, 30),
           pcTimestamp, p.*
    from sysibm.sysPackage p
    where (name like 'MF%' or name like 'YMF%' or name like 'YYMF%')
    and (collid, contoken) in
        ( select collid, contoken
              from sysibm.sysPackage c
              where c.location = p.location
                and c.name     = p.name
              order by pcTimestamp desc, pcTimestamp desc,
                       collid asc
              fetch first row only
        )
    order by location, collid, name, version
;,