By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

Passing data from one stored procedure to another

P: n/a
Hi all,
I have one stored procedure where I defined 3 temporary tables, which
i return back to java. I want to have 3 separate sub procedures which
would go after the data and fill out these temporary tables.

Question: Is it possible to
a) Pass a pointer to the temporary table from one procedure and have
it filled out in the other procedure, then return controll to the
caller ?

OR

b) return a result set from a sub procedure to the caller, and insert
it to the temporary table.

I'm sure it is possible, I just can't find any information about it in
IBM documentation.
Operating system : AS400, Database : DB2

Thanks,
Roman Prigozhin
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Roman Prigozhin wrote:
Hi all,
I have one stored procedure where I defined 3 temporary tables, which
i return back to java. I want to have 3 separate sub procedures which
would go after the data and fill out these temporary tables.

Question: Is it possible to
a) Pass a pointer to the temporary table from one procedure and have
it filled out in the other procedure, then return controll to the
caller ?

OR

b) return a result set from a sub procedure to the caller, and insert
it to the temporary table.

I'm sure it is possible, I just can't find any information about it in
IBM documentation.
Operating system : AS400, Database : DB2

Thanks,
Roman Prigozhin


Assuming that the AS400 DB2 works as UDB for LUW:
1. Defined temporary tables are unique to the thread in which they are
defined. Assuming no intermediate commits or appropriate definition
parameters, the tables defined in the main procedure should be available
to the sub procedures.

2. The sub-procedures will not compile unless the temporary table exists
at build time. The LUW workaround is to define the temporary table,
before building the procedure, in the SESSION schema and use a fully
qualified table name in the sub-procedure. The defined table in the main
procedure will be used instead of the pre-defined one at execute time.
The logic for the main procedure would be:
Define the temporary tables
Call the sub-procedures
Open select cursors to retrieve the temporary table data
return to invoker

Check the AS400 manuals for the equivalent mechanism.

Phil Sherman

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.