zOs/SQL/CATSTMTA
$#@
call sqlConnect dbof
$;
$>.fEdit(':v')
$<=[
-- 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 = 'OA1P';
with p as
( -- select newest packages --------------------------------------------
select location, collid, name, contoken
from sysibm.sysPackage n
-- select here |
where location = '' and name in ('YCDSGET'
,'YCDX011'
,'YCDX031'
,'YCIG600'
,'YDIEURE'
,'YDIT001'
,'YDIT002'
,'YDIT003'
,'YDIT004'
,'YGMSTEU'
,'YGMTXIO'
,'YOE2BU '
,'YOE2GET'
,'YOOAIE3'
,'YSVEUTV'
,'YSVG502'
,'YSVIDAT'
,'YVVSQLE'
,'YWPRCBX'
,'YWPR1B2'
,'YWPR2D1'
,'YWPR7HX'
,'YWUPARM'
,'YWUPUT '
,'YWUSTEU'
,'YYCRCXE'
)
and pcTimestamp = (select max(pcTimestamp)
from sysibm.sysPackage a
where a.location = n.location
and a.collid = n.collid and a.name = n.name
)
)
, g as
( -- g 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)
and (location, collid, name, contoken) in
(select * from p)
group by location, collid, name, contoken,
stmtNo, stmtNoI
)
, stT (lv, sq, sLen, tLen, tx,
location, collid, name, contoken, stmtNo, stmtNoI,
seqMin, seqMax, explainable
) as
( -- recursion do concatenate stmt text --------------------------------
-- 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 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)
order by location, collid, name, contoken, stmtNo, stmtNoI
$] call sqlStmts
$#out 20120411 14:05:03
$#out 20120411 14:04:49
*** run error ***
SQLCODE = -440: NO AUTHORIZED FUNCTION NAMED FOSC2I4 HAVING
COMPATIBLE ARGUMENTS WAS FOUND
stmt = execSql prepare s11 into :M.SQL.11.D from :src
with into :M.SQL.11.D = M.SQL.11.D
from :src = with p as
$#out 20120411 14:04:41
*** run error ***
tsoAlloc rc 12 for alloc dd(CAT1) NEW recfm(V b) lrecl(32756) block(32760) mgmt
$#out 20120411 13:44:11
*** run error ***
tsoAlloc rc 12 for alloc dd(CAT1) NEW recfm(V b) lrecl(32756) block(32760) mgmt
$#out 20120411 13:44:04
*** run error ***
SQLCODE = -440: NO AUTHORIZED FUNCTION NAMED FOSC2I4 HAVING
COMPATIBLE ARGUMENTS WAS FOUND
stmt = execSql prepare s11 into :M.SQL.11.D from :src
with into :M.SQL.11.D = M.SQL.11.D
from :src = with p as
$#out 20120411 13:43:48
*** run error ***
SQLCODE = -924: DB2 CONNECTION INTERNAL ERROR, 00000002,
0000000C, 00F30006
stmt = connect DBXF
$#out 20120411 13:43:45
*** run error ***
SQLCODE = -924: DB2 CONNECTION INTERNAL ERROR, 00000002,
0000000C, 00F30006
stmt = connect DBXF
$#out 20120411 13:40:34
$#out 20120411 13:40:26
*** run error ***
tsoAlloc rc 12 for alloc dd(CAT1) NEW recfm(V b) lrecl(32756) block(32760) mgmt
$#out 20120411 13:40:08
*** run error ***
SQLCODE = -440: NO AUTHORIZED FUNCTION NAMED FOSC2I4 HAVING
COMPATIBLE ARGUMENTS WAS FOUND
stmt = execSql prepare s11 into :M.SQL.11.D from :src
with into :M.SQL.11.D = M.SQL.11.D
from :src = with p as
$#out 20120411 13:39:58
*** run error ***
tsoAlloc rc 12 for alloc dd(CAT1) NEW recfm(V b) lrecl(32756) block(32760) mgmt
$#out 20120411 13:39:51
*** run error ***
SQLCODE = -440: NO AUTHORIZED FUNCTION NAMED FOSC2I4 HAVING
COMPATIBLE ARGUMENTS WAS FOUND
stmt = execSql prepare s11 into :M.SQL.11.D from :src
with into :M.SQL.11.D = M.SQL.11.D
from :src = with p as
$#out 20120307 13:38:24
$#out 20120307 13:35:51
$#out 20120307 13:31:10
$#out 20120307 13:30:58