zOs/SQL/CATSTMT

-- view to extract sql statement text from sysPackStmt
--     concatenate statement texts if split in multiple junks
--          (up to 29000 bytes)
--     convert from utf8 to ebcdic if necessary
--          (we simply check first character for >='a')
-- attention:
--     where clause cannot be moved from  recursive tableExpr stT
--         because optimizer doesn't optimize it as needed|
--     DSNTIAUL gets an error on this view, and
--     SPUFI does not handle so long characters,
--     thus, use WSH catStmtW in a DSN with very long recSize
--
set current sqlid = 'S100447';
set current path = 'OA1A';
drop   view A540769.CATSTMT;
create view A540769.CATSTMT as



with stG 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||
  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
( -- now use recursive table to concatenate text junks up to 29000 chars
  -- recursion seed: first stmt junk contains lengths
select  0, s.seqNo sq, fosC2I4(substr(stmt, 1, 4)) sLen, -- total len
                      fosC2I4(substr(stmt, 5, 4)) tLen, -- textlen
        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
  -- recursion step: join next 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.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
           -- abbruch Bedingung: keinen Junks mehr
        and t.sq < t.seqMax and t.lv < 100000
           -- abbruch Bedingung: Länge erreicht

        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 (final) tupel of each statement
    where length(tx) in (tLen, 29000)
;
select * from A540769.CATSTMT
    where     seqMax - seqMin > 1
    order by location, collid, name, contoken, sq
;