Pass-thru result set?

I have a procedure that takes some input parameters and returns a
result set. What I would like to do now is have a wrapper procedure
that takes some input,* processes the input, then calls the original
procedure and passes through that result set to whomever called the
wrapper procedure. I have been unsuccessful so far. Here’s some pseudo
–SQL to show what I’m trying to do:

Procedure P1 (input1, input2)

Dynamic result sets 1

Begin

*******

Declare c1 cursor with return for

******* ******* Select some data based on inputs;

Open c1;

End


Procedure P2 (input1, input2, input3)

Dynamic result sets 1

begin

Declare rs_loc RESULT_SET_LOCATOR VARYING;


<process inputs>


******* Call P1(i1, i2);

******* Associate result set locator (rs_loc) with procedure P1;

******* Allocate c2 cursor for result set rs_loc;

End


I want to be able to call P2 and see the result returned from the
internal to P1.* Logically I want c2 to be declared/allocated “with
return”, but that syntax is not supported. I tried opening c2 (which
according to the docs isn’t needed). I also tried altering the cursor
in P1 as “with return to Caller” and “with return to client” with the
former producing nothing (after calling P2) and the latter returning
SQL0423. I would think that there would be a way to do this, but can’t
seem to find much info in the docs for “allocate cursor” or any other
examples. What do I need to make this work?

There is currently no such capability.
However, if you use a CURSOR parameter you can pass result sets not only
up, level by level, but also down.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau