zOs/SQL/CATSTMT0

-- test view for catStmt: show more debug infos
--
set current sqlid = 'S100447';
set current path = 'OA1A';
drop   view A540769.CATSTMT;
create view A540769.CATSTMT as
--     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||
with stG as
( select location, collid, name, contoken, stmtNo, stmtNoI,
        min(seqNo) seqMin, max(seqNo) seqMax
    from sysibm.sysPackStmt
          -- if all 0 internal (pseudo) statements => ignore
    where (seqno <> 0 or stmtNo <> 0 or stmtNoI <> 0)
          -- select here |
        and location = '' and collid = 'MF'
    group by location, collid, name, contoken,
             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,
       seqMin, seqMax, explainable
) as
(
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
    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.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, 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.CATSTMT
    where     seqMax - seqMin > 1
    order by location, collid, name, contoken, sq
;