zOs/SQL/CATSTMTB

-- view catStmTb: extract sql statement text from sysPackStmt
--     for newest Package (pcTimestamp from sysPackage)
--     for all packages using a table (as of sysPackDep)
--
-- attention see catStmt, use WSH catStmtC
--
set current sqlid = 'S100447';
set current path = 'OA1A';
drop   view A540769.catStmTb;
create view A540769.catStmTb as
with dg as
(     -- group all packages using our tables,
      -- and select newest pcTimeSt
 select dLocation, dCollid, dName,
         max(char(pcTimeStamp) || dContoken) tc
     from sysibm.syspackdep
     left join sysibm.syspackage
         on dLocation = location
         and dCollid = collid and dName = name and dConToken = conToken
     where -- bType in ('P', 'R')
             bQualifier = 'OA1A'
             and bName in ('TMF150A1', 'TMF150H1')
       group by dLocation, dCollid, dName
 )
 , dn as
 (    -- extrace pcTimeStamp and conToken
 select dLocation, dCollid, dName,
        substr(tc, 27) dconToken,
        timestamp(left(tc, 26)) pcTimestamp
     from dg
)
--     group statemets of the selected packages
--          and find range of seqNo for each stmt
--
--            attention: where clause cannot be moved from
--                             seed of recursive view stT,
--                because optimizer doesn't optimize it as needed|
--
--     stG groups sysPackStmt rows into statments
--          each stmt has a range of seqNo, neither gaps nor overlaps
--     stmtNo and stmtNoI are often equal, but not always||
, stG as
( select location, collid, name, contoken, stmtNo, stmtNoI, pcTimestamp,
        min(seqNo) seqMin, max(seqNo) seqMax
    from sysibm.sysPackStmt, dn
          -- if all 0 internal (pseudo) statements => ignore
    where (seqno <> 0 or stmtNo <> 0 or stmtNoI <> 0)
          -- select here |
        and location = dLocation and collid = dCollid
        and name = dName and conToken = dContoken
    group by location, collid, name, contoken, pcTimeStamp,
             stmtNo, stmtNoI
)
-- now use recursive stT to concatenate text junks up to 29000 chars
, stT (lv, sq, sLen, tLen, tx,
       location, collid, name, contoken, stmtNo, stmtNoI,
       pcTimeStamp, seqMin, seqMax, explainable
) as
(     -- recursion seed are selected statements with first text chunk
select  0, s.seqNo sq, fosC2I4(substr(stmt, 1, 4)) sLen,
                      fosC2I4(substr(stmt, 5, 4)) tLen,
        varchar(substr(stmt, 9, min(length(stmt)-8,
              int(fosC2I4(substr(stmt, 5, 4))))), 29000) tx,
        g.*, s.explainable
    from stG g, sysibm.sysPackStmt s
    where s.location = g.location and s.collid = g.collid
        and s.name = g.name and s.contoken = g.contoken
        and s.seqNo = g.seqMin
union all  -- concatenate next text junk
    select lv+1,
           s.seqNo, sLen, tLen, varchar(tx || substr(stmt, 1,
            int(min(length(stmt), t.tLen-length(tx), 29000-length(tx)
         ))),  29000),
         t.location, t.collid, t.name, t.contoken,
         t.stmtNo, t.stmtNoI, t.pcTimeStamp,
         t.seqMin, t.seqMax,
                        t.explainable
    from stT t, sysibm.sysPackStmt s
    where s.location = t.location and s.collid = t.collid
        and s.name = t.name and s.contoken = t.contoken
        and s.seqNo = t.sq+1
        and t.sq < t.seqMax and t.lv < 100000
        and length(t.tx) < t.tLen and length(t.tx) < 29000
)
select  location, collid, name, contoken, pcTimestamp,
        stmtNo, stmtNoI,
        CASE WHEN left(tx, 1) >=  'a' then tx
             else  cast(CAST(tx AS VARCHAR(29000) CCSID 1208)
                     AS VARCHAR(29000) CCSID EBCDIC)
        end tc,
        lv, sq, sLen, tLen,
        seqMin, seqMax,
        explainable , substr(tx, 1, 1) t1
    from stT
-- use only longest text of each statement
    where length(tx) in (tLen, 29000)
;
select * from A540769.catStmTb
--  where     seqMax - seqMin > 1
    order by location, collid, name, contoken, sq
;