zOs/SQL/XBCOUNT

-- xb: elar test meta data
--    xb tables missing in txbc003
--    bad unloads in txbc021? or unloads missing in txbc003?

--- global table fuer Partitionen, stage, segment --------------------
declare global temporary table session.xbPa
   ( cr char(8), tb char(25), db char(8), ts char(8), pa smallInt
   , stage char(2), seg char(3), stoAr char(3), fam char(25)
   ) on commit preserve rows
;
create unique index session.xbPaIx on session.xbPa (db,ts, pa)
;
insert into session.xbPa
  select t.creator, t.name, t.dbName, t.tsName
       , r.partNumber, r.stage, r.storageArea, r.segment
       , r.objectFamily
    FROM sysibm.systables t
      join  BUA.TXBI003 R
        on t.name between 'XB' || left(r.storageArea, 3)
                               || left(r.segment, 3)
                      and 'XB' || left(r.storageArea, 3)
                               || left(r.segment, 3) || x'FFFFFF'
            and substr(t.name, 3, 3) = r.storageArea
            and substr(t.name, 6, 3) = r.segment
  where t.creator = 'BUA'
        and t.name like 'XB%'
  with cs
;
commit
;
declare global temporary table session.xbUnl
   ( db char(8), ts char(8), pa smallint, unl char(44), sf char(1)
   ) on commit preserve rows;
create unique index session.unlIx on session.xbUnl (db,ts, pa)
                                  include (unl)
   ;
insert into session.xbUnl
    select substr(earess, 4, 8)  db
          , substr(earess, 13
              , min(8, locate('.', earess || '.', 13) - 13))  ts
          , partNumber pa, eaRess, '1'
      from BUA.TXBC021 t
      where EYRESS =  5000 and ESRESS =  0
;
commit
;
insert into session.xbUnl
    select substr(earess, 4, 8)  db
          , substr(earess, 13
              , min(8, locate('.', earess || '.', 13) - 13))  ts
          , partNumber pa, eaRess, 's'
      from BUA.TXBC021s t
      where EYRESS =  5000 and ESRESS =  0
;
commit
;
with e as
(
   select db, ts, pa, sf, unl
         , case when unl not like 'XB.XB%'
                       then 'unl not XB.XB% '
                when locate('.', unl, 4) <> 12
                       then 'unl db len '
                when locate('.', unl, 13) not between 14 and 21
                       then 'unl ts len '
                when locate('.' || db || '.', unl) < 3
                       then 'unl locate .db.'
                when locate('.' || strip(ts) || '.', unl) < 3
                       then 'unl locate .ts.'
                when locate('0' || strip(char(pa)) || '.', unl) < 3
                       then 'unl locate 0pa.'
                when not exists ( select 1
                      from session.xbPa p
                      where p.db = u.db and p.ts = u.ts and p.pa = u.pa
                      ) then 'no xbPa'
                else '' end err
       from session.xbUnl u
)
select db, ts, pa, err
    from e
    where err <> ''
    with ur
;
select char(dbName, 8) db, char(tsName, 8) ts
      , char(strip(creator) || '.' || name, 40) tb
    from sysibm.sysTables t
    where dbName like 'XB%' and not exists ( select 1
         from session.xbPa n
         where t.dbName = n.db and t.tsName = n.ts
         )
    with ur