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