zOs/SQL/STPRDRS
$#@
$<=[
set current sqlid = 's100447' ;
drop procedure A540769.resP1;
drop procedure A540769.resP2;
drop procedure A540769.DRSP1;
drop procedure A540769.DRSP2;
drop procedure gdb9998.resP1;
drop procedure gdb9998.resP2;
drop procedure gdb9998.DRSP1;
drop procedure gdb9998.DRSP2;
terminator } ;
create procedure gdb9998.DRSP1(in name varchar(20))
reads sql data dynamic result sets 3
begin
declare vrs varchar(20);
declare paC int;
declare eof int default 0;
declare sqlCode int default 0;
declare prCu cursor with return for
select version, parm_count
from sysibm.sysRoutines r
where r.name = DRSP1.name;
declare paCu cursor with return for
select *
from sysibm.sysParms p
join sysibm.sysRoutines r
on p.SCHEMA = r.SCHEMA
and p.SPECIFICNAME = r.SPECIFICNAME
and p.ROUTINETYPE = r.ROUTINETYPE
and p.VERSION = r.VERSION
where r.name = DRSP1.name and r.active = 'Y'
order by ordinal;
-- open paCu; -- um resignal im Handler zu testen
begin
declare continue handler for sqlException begin
if sqlCode = -502 then
resignal sqlstate 'HHI01'
set message_text = 'sqlcode '
|| strip(char(sqlCode))
|| ' opening prCu 1';
end if;
end;
open prCu;
end;
begin
declare continue handler for sqlException begin
declare old int;
set old = sqlCode;
if old = -502 then
resignal sqlstate 'HHI02'
set message_text = 'sqlcode '
|| strip(char(sqlCode))
|| ' old ' || strip(char(old))
|| ' opening paCu 2';
end if;
end;
open paCu;
end;
return;
end
}
--- macht noch nichts|
create procedure gdb9998.DRSP2(in name varchar(20),out out varchar(500))
reads sql data
begin
declare vrs varchar(20);
declare paC int;
declare eof int default 0;
declare sqlCode int default 0;
declare prCu cursor for
select version, parm_count
from sysibm.sysRoutines r
where r.name = DRSP2.name;
set out = 'begin';
open prCu;
set out = out || ' open';
begin
declare exit handler for not found
set out = out || ' notFound=' || strip(char(sqlCode));
loop
fetch prCu into vrs, paC;
set out = out || ' =' || strip(vrs);
end loop;
end;
set out = out || ' endLoop';
close prCu;
set out = out || ' close';
end
}
terminator ; }
call gdb9998.DRSP1(FETP1 );
call gdb9998.DRSP1(VERSP1);
call gdb9998.DRSP1(xxxYY );
--call gdb9998.DRSP2(FETP1 );
--call gdb9998.DRSP2(VERSP1);
--call gdb9998.DRSP2(xxxYY );
commit;
$] call sqlStmtsOpt
$#out 20110610 14:21:40
sqlCode 0: set current sqlid = 's100447'
sqlCode -204: drop procedure A540769.resP1
sqlCode -204: drop procedure A540769.resP2
sqlCode -204: drop procedure A540769.DRSP1
sqlCode -204: drop procedure A540769.DRSP2
sqlCode -204: drop procedure gdb9998.resP1
sqlCode -204: drop procedure gdb9998.resP2
sqlCode 0: drop procedure gdb9998.DRSP1
sqlCode 0: drop procedure gdb9998.DRSP2
sqlCode 0: create procedure gdb9998.DRSP1(in name varchar(20)) reads sql d...
sqlCode 0: create procedure gdb9998.DRSP2(in name varchar(20),out out varc...
*** run error ***
sqlCode SQLCODE not numeric
sqlCode SQLCODE sqlState=SQLSTATE
errMC=SQLERRMC
warnings= 0=SQLWARN.0 1=SQLWARN.1 2=SQLWARN.2 3=SQLWARN.3 4=SQLWARN.4 5=SQLW
errD.1=SQLERRD.1 2=SQLERRD.2 3=SQLERRD.3
errD.4=SQLERRD.4 5=SQLERRD.5 6=SQLERRD.6
stmt = execSql call GDB9998.DRSP1 using descriptor :M.SQLSTMTCALL
with M.SQLSTMTCALL = M.SQLSTMTCALL
$#out 20110608 14:34:51
sqlCode 0: set current sqlid = 's100447'
sqlCode -204: drop procedure A540769.resP1
sqlCode -204: drop procedure A540769.resP2
sqlCode 0: drop procedure A540769.DRSP1
sqlCode 0: drop procedure A540769.DRSP2
sqlCode -204: drop procedure gdb9998.resP1
sqlCode -204: drop procedure gdb9998.resP2
sqlCode -204: drop procedure gdb9998.DRSP1
sqlCode -204: drop procedure gdb9998.DRSP2
sqlCode 0: create procedure gdb9998.DRSP1(in name varchar(20)) ...
sqlCode 0: create procedure gdb9998.DRSP2(in name varchar(20),out out v...
--- called GDB9998.DRSP1, sqlCode 466
parm 1 i NAME = FETP1
dynamic result set 1 PRCU locator=1
dynamic result set 2 PACU locator=2
--- begin of dynamic result set 1=PRCU of GDB9998.DRSP1
VE T
V1 2
--- 1 rows fetched from dynamic result set 1=PRCU of GDB9998.DRSP1
--- begin of dynamic result set 2=PACU of GDB9998.DRSP1
SCHEMA OWNER NAME SPECI R C PARM NEID R L TYPESC TYPENAM EID D L T O GTH E S
A540769 s100447 FETP1 FETP1 P N NAME 1443 P 1 SYSIBM VARCHAR 449 0 N N 0 20 0 S
A540769 s100447 FETP1 FETP1 P N OUT 1443 O 2 SYSIBM VARCHAR 449 0 N N 0 500 0 S
--- 2 rows fetched from dynamic result set 2=PACU of GDB9998.DRSP1
sqlCode 466: call gdb9998.DRSP1(FETP1 )
--- called GDB9998.DRSP1, sqlCode 466
parm 1 i NAME = VERSP1
dynamic result set 1 PRCU locator=3
dynamic result set 2 PACU locator=4
--- begin of dynamic result set 1=PRCU of GDB9998.DRSP1
VE T
V1 1
V2 1
--- 2 rows fetched from dynamic result set 1=PRCU of GDB9998.DRSP1
--- begin of dynamic result set 2=PACU of GDB9998.DRSP1
SCHEMA OWNER NAME SPECIF R C PA NEID R L TYPESC TYPE EID D L T O H E S SID
A540769 s100447 VERSP1 VERSP1 P N VE 1437 O 1 SYSIBM CHAR 453 0 N N 0 4 0 S 500
--- 1 rows fetched from dynamic result set 2=PACU of GDB9998.DRSP1
sqlCode 466: call gdb9998.DRSP1(VERSP1)
--- called GDB9998.DRSP1, sqlCode 466
parm 1 i NAME = xxxYY
dynamic result set 1 PRCU locator=5
dynamic result set 2 PACU locator=6
--- begin of dynamic result set 1=PRCU of GDB9998.DRSP1
--- 0 rows fetched from dynamic result set 1=PRCU of GDB9998.DRSP1
--- begin of dynamic result set 2=PACU of GDB9998.DRSP1
--- 0 rows fetched from dynamic result set 2=PACU of GDB9998.DRSP1
sqlCode 466: call gdb9998.DRSP1(xxxYY )
sqlCode 0: commit
$#out 20110603 18:26:42
sqlCode 0: set current sqlid = 's100447'
sqlCode -204: drop procedure gdb9998.resP1
sqlCode -204: drop procedure gdb9998.resP2
sqlCode 0: drop procedure gdb9998.DRSP1
sqlCode 0: drop procedure gdb9998.DRSP2
sqlCode 0: create procedure gdb9998.DRSP1(in name varchar(20)) ...
sqlCode 0: create procedure gdb9998.DRSP2(in name varchar(20),out out v...
--- called gdb9998.DRSP1, sqlCode 466
parm 1 i NAME = FETP1
dynamic result set 1 PRCU locator=1
dynamic result set 2 PACU locator=2
--- begin of dynamic result set 1=PRCU of gdb9998.DRSP1
VE T
V1 2
--- 1 rows fetched from dynamic result set 1=PRCU of gdb9998.DRSP1
--- begin of dynamic result set 2=PACU of gdb9998.DRSP1
SCHEMA OWNER NAME SPECI R C PARM NEID R L TYPESC TYPENAM EID D L T O GTH E S
gdb9998 s100447 FETP1 FETP1 P N NAME 1443 P 1 SYSIBM VARCHAR 449 0 N N 0 20 0 S
gdb9998 s100447 FETP1 FETP1 P N OUT 1443 O 2 SYSIBM VARCHAR 449 0 N N 0 500 0 S
--- 2 rows fetched from dynamic result set 2=PACU of gdb9998.DRSP1
sqlCode 466: call gdb9998.DRSP1(FETP1 )
--- called gdb9998.DRSP1, sqlCode 466
parm 1 i NAME = VERSP1
dynamic result set 1 PRCU locator=3
dynamic result set 2 PACU locator=4
--- begin of dynamic result set 1=PRCU of gdb9998.DRSP1
VE T
V1 1
V2 1
--- 2 rows fetched from dynamic result set 1=PRCU of gdb9998.DRSP1
--- begin of dynamic result set 2=PACU of gdb9998.DRSP1
SCHEMA OWNER NAME SPECIF R C PA NEID R L TYPESC TYPE EID D L T O H E S SID
gdb9998 s100447 VERSP1 VERSP1 P N VE 1437 O 1 SYSIBM CHAR 453 0 N N 0 4 0 S 500
--- 1 rows fetched from dynamic result set 2=PACU of gdb9998.DRSP1
sqlCode 466: call gdb9998.DRSP1(VERSP1)
--- called gdb9998.DRSP1, sqlCode 466
parm 1 i NAME = xxxYY
dynamic result set 1 PRCU locator=5
dynamic result set 2 PACU locator=6
--- begin of dynamic result set 1=PRCU of gdb9998.DRSP1
--- 0 rows fetched from dynamic result set 1=PRCU of gdb9998.DRSP1
--- begin of dynamic result set 2=PACU of gdb9998.DRSP1
--- 0 rows fetched from dynamic result set 2=PACU of gdb9998.DRSP1
sqlCode 466: call gdb9998.DRSP1(xxxYY )
sqlCode 0: commit
$#out 20110603 18:25:47
sqlCode 0: set current sqlid = 's100447'
sqlCode 0: drop procedure gdb9998.resP1
sqlCode 0: drop procedure gdb9998.resP2
sqlCode -204: drop procedure gdb9998.DRSP1
sqlCode -204: drop procedure gdb9998.DRSP2
sqlCode 0: create procedure gdb9998.DRSP1(in name varchar(20)) ...
sqlCode 0: create procedure gdb9998.DRSP2(in name varchar(20),out out v...
*** run error ***
SQLCODE = -438, SQLSTATE = HHI02, UNKNOWN SQLSTATE TYPE FROM SQL
ROUTINE TOKENS sqlcode 0 old -502 opening paCu 2
stmt = execSql call gdb9998.DRSP1 using descriptor :M.SQLSTMTCALL
with M.SQLSTMTCALL = M.SQLSTMTCALL
$#out 20110603 18:01:13