zOs/SQL/XBRECOV

                  -- elar: stage und unload file name
                  --       für XB Tabellen Partitionen
                  --
                  -- Achtung: Performance ist sehr heikel
                  --       diese Version braucht meist weniger als 1 min
                  --       im Gegensatz zu vielen anderen Versuchen|
                  --
with p as
( select t.creator cr, t.name tb, t.dbName db, t.tsName ts
     , p.partition pa, r.stage, r.status
     ,'XB' || R.STORAGEAREA || R.SEGMENT seg
     , R.objectFamily family
     , R.STORAGEAREA
     , (select max(timestamp) from sysibm.sysCopy c
             where c.dbName = t.dbName and c.tsName = t.tsName
                  and p.partition = c.dsNum and icType = 'F'
       ) lastCopy
   FROM sysibm.systables t
     join sysibm.sysTablePart p
       on t.dbName = p.dbName and t.tsName = p.tsName
     left join  BUA.TXBI003 R
       on t.creator = 'BUA'
         and left(t.name, 8) = 'XB'||STORAGEAREA||SEGMENT
         and p.partition = r.partNumber
)
, u as
(
  select t.*
    from BUA.TXBC021s t
    where EYRESS =  5000
        and ESRESS =  0
  union all select t.*
    from BUA.TXBC021 t
    where EYRESS =  5000
        and ESRESS =  0
)
select char(cr, 8) cr
     , char(tb, 20)  tb
     , char(db, 8) db
     , char(ts, 8) ts
     , smallint(pa) pa
     , substr(strip(stage) || '='
         || case when stage = 'RW' then 'readWri'
                 when stage = 'CL' then 'cLosed'
                 when stage = 'UL' then 'unload'
                 when stage = 'DL' then 'deleted'
                 else '???' end, 1, 10) stage
     , substr(strip(status) || '='
         || case when status = 0 then 'deleted'
                 when status = 1 then 'ReadWri'
                 when status = 2 then 'ReadOnly'
                 when status = 3 then 'locked'
                 when status = 4 then 'deleting'
                 else '???' end, 1, 10) status
     , lastCopy
     , eaRess unload, ETRESS
     , family, storageARea, seg
    from p
      left join u
        on u.eadba = p.seg
            and u.PARTNUMBER = p.pa
             -- tbSpace finden wir NUR im unloadNamen ........
            and locate('.' || strip(db) || '.' || strip(ts) || '.'
                , u.eaRess) > 0
    where -- storageArea = 'F9F'
          db = 'XBD2R001'
       -- tb = 'XBF9F003PS001001'
       -- seg = 'XBF9F003'
       -- family = 'devi.if'
       -- and stage  in ('UL', 'DL')
    order by tb, pa
    with ur