zOs/SQL/TECSVUNB

insert into oa1p.tqz005TecSvUnload
with sU (area, n, seg, pa, stage, sTb, hkTS) as
(
  select storageArea, storageArea_N, segment, partNumber, stage
      , 'i', LASTHKTS
    from  BUA.TXBI003 R
  union all select '?', enStorAr, right('000' || enSeg, 3), 1, '-a'
      , 'a', cast(null as timestamp)
    from bua.txba201
)
, sC (area, areaN, areaC, seg, pa, stage, sTb, hkTs) as
(               /* xba201 may contain storArea Numbers
                   missing in xbi003, thus calculate area from areaN */
  select area, n
       , case when n <= 999 then right('000' || n, 3)
              when n <= 35657                /*  1296 = 36**2 */
                then substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                           , (n + 10998) / 1296 + 1, 1)
                  || substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                           , mod(n + 10998, 1296) / 36 + 1, 1)
                  || substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                           , mod(n + 10998, 36) + 1, 1)
         end
       , seg, pa, stage, sTb, hkTS
    from sU
)
, seg (seg, t8, pa, stage, sTb, hkTS, err) as
(
  select areaC || seg, 'XB' || areaC || seg, pa, max(stage)
      , max(sTb), max(hkTS)
      , case when min(areaN) <> max(areaN)
                 then 'areaC=' || areaC || ' >1 Nums'
                               || min(sTb) || '-' || max(sTb)
             when max(case when area <> '?' and area <> areaC
                        then 1 else 0 end) = 1
                  then max(case when area <> '?' and area <> areaC
                        then 'area=' || area || ' <> calc=' || areaC
                        else '' end)
             when sum(case when sTb = 'i' then 1 else 0 end) > 1
                 then '>1 xbi003' || areaC || seg || ' ' || pa
             when sum(case when sTb = 'a' then 1 else 0 end) > 1
                 then '>1 xba201' || areaC || seg || ' ' || pa
             else '' end
    from sC
    group by areaC, seg, pa
)
, uU as
(
  select substr(earess, 4, 8)  db
        , substr(earess, 13
            , min(8, locate('.', earess || '.', 13) - 13))  ts
        , partNumber pa, eaRess, 'c' sTb
    from BUA.TXBC021 t
    where EYRESS =  5000 and ESRESS =  0
  union all select substr(earess, 4, 8)  db
        , substr(earess, 13
            , min(8, locate('.', earess || '.', 13) - 13))  ts
        , partNumber pa, eaRess, 's' sTb
    from BUA.TXBC021s t
    where EYRESS =  5000 and ESRESS =  0
)
, unl (db, ts, pa, unl, sTb, err) as
(
  select db, ts, pa, max(eaRess) eaRess, max(sTb)
      , case when count(*) <> 1
                 then 'duplicates ' || min(sTb) || '-' || max(sTb)
        else max(
            case when earess not like 'XB.XB%' then 'eaRess not XB.XB%'
                 when locate('.', earess, 4) <> 12 then 'eaRess db len'
                 when locate('.', earess, 13) not between 14 and 21
                    then 'eaRess ts len'
            else '' end) end err
    from uU
    group by db, ts, pa
)
, tp (db, ts, pa, t8, err) as
(
  select t.dbName, t.tsName, partition, max(left(t.name, 8))
      , case when max(left(t.name, 8)) <> min(left(t.name, 8))
           then 'multipleTables' else '' end
    from sysibm.sysTables t
      join sysibm.sysTablePart p on
         t.dbName = p.dbName and t.tsName = p.tsName
    where t.dbName like 'XB%'
        and t.type not in ('A', 'V')
    group by t.dbName, t.tsName, partition
)
, j (db, ts, pa, stage, unl, pun, sTb, hkts, err) as
(
  select value(tp.db, unl.db, '?noDB')
      , value(tp.ts, unl.ts, seg.seg, '?noTS')
      , value(tp.pa, unl.pa, seg.pa, -999)
      , case when seg.stage is not null then seg.stage
             when tp.ts like '%WWW%' then '-w'
             else '-m' end
      , value(unl.unl, '')
      , case when unl is null or unl = '' then ''
             when locate_in_string(unl, '.', -1, 2) < 1 then ''
             when substr(unl, locate_in_string(unl, '.', -1, 2) + 1
                            , locate_in_string(unl, '.', -1, 1)
                            - locate_in_string(unl, '.', -1, 2) - 1)
                  <> 'SYSREC' then ''
             else left(unl, locate_in_string(unl, '.', -1, 2) )
                || 'SYSPCH'
             end
      , value(seg.sTb, '') || value(unl.sTb, '')
      , hkTS
      , case when tp.db is null then 'notInDB2 ' else '' end
      ||case when seg.stage not in
                 ('RW', 'CL', 'UL', 'DL', '-m', '-a', '-w', '-r')
                 then 'badStage=' || stage || ' '
             when seg.stage in ('UL', 'DL') and unl is null or unl=''
                 then 'noUnload '
             when seg.stage not in ('CL', 'DL', 'UL') and unl <> ''
                 then 'UnloadInBadStage '
             else '' end
      ||case when seg.stage is not null then seg.err || ' '
             when tp.ts like '%WWW%' then ''
             else 'notIn xbi003/xba201 ' end
      ||case when unl is null or unl = '' then ''
             when locate_in_string(unl, '.', -1, 2) < 1 then 'unl<2q '
             when substr(unl, locate_in_string(unl, '.', -1, 2) + 1
                            , locate_in_string(unl, '.', -1, 1)
                            - locate_in_string(unl, '.', -1, 2) - 1)
                  <> 'SYSREC' then 'sysrecNotInUnl '
             else ''
             end
      || value(unl.err, '') || value(tp.err, '')
                             /* missing in TXBI003? correct stage? */
    from tp
      full outer join seg
          on tp.t8 = seg.t8 and tp.pa = seg.pa
      full outer join unl
          on tp.db = unl.db and tp.ts = unl.ts  and tp.pa = unl.pa
)
select db, ts, pa, stage
      , '1111-11-11-11.11.11.111111'
      , value(sTb, '')
      , value(hkts, '1111-11-11-11.11.11.111111')
      , unl
      , '1111-11-11-11.11.11.111111'
      , pun
      , ''
      , err
    from j