zOs/SQL/VVREBIND
$#@
call sqlConnect dbtf
$;
$>.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 'rebind package(' || collid || '.' || name
|| '.(' || version || '))' rb, n.*
from sysibm.sysPackage n
-- select here |
where location = '' and name in ('VV6201 '
,'YVV3000'
,'YVV3210'
,'YVV3250'
,'YVV3260'
,'YVV3300'
)
and ( pcTimestamp = (select max(pcTimestamp)
from sysibm.sysPackage a
where a.location = n.location
and a.collid = n.collid and a.name = n.name
)
or pcTimestamp = (select max(pcTimestamp)
from sysibm.sysPackage a
where a.location = n.location
and a.collid = n.collid and a.name = n.name
and a.version not like '%MVS%'
)
or pcTimestamp = (select max(pcTimestamp)
from sysibm.sysPackage a
where a.location = n.location
and a.collid = n.collid and a.name = n.name
and a.version not like '%CICS%'
)
or pcTimestamp = (select max(pcTimestamp)
from sysibm.sysPackage a
where a.location = n.location
and a.collid = n.collid and a.name = n.name
and a.version not like '%CICS%'
and a.version not like '%MVS%'
) )
)
select * from p
$] call sqlStmts
$#out 20120314 12:40:05
$#out 20120411 15:17:17
$#out 20120411 15:16:38
$#out 20120411 15:14:48
$#out 20120411 15:12:45
$#out 20120411 15:10:07
$#out 20120411 15:09:46
$#out 20120411 15:07:49
$#out 20120411 15:07:35
*** run error ***
tsoAlloc rc 12 for alloc dd(CAT1) NEW recfm(V b) lrecl(32756) block(32760) mgmt
$#out 20120411 15:07:23
*** run error ***
scanErr ending Apostroph(') missing
last token scanPosition '%MVS%
pos 47 in line 53: and n.version not like '%MVS%
$#out 20120411 15:05:17
$#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