Temporary Table

I want to create and populate a temporary table into a DB2 database from
a Java web application. This is all fine and good and works. However, I
would then like to reference the temporary table from a Stored Procedure
from the same connection.

Is this possible? Am I able to pass in the name of the temporary table
and use it in a Stored Procedure? If so can you give some simple coded
examples? If not then what other options do I have (possibly a 'fake'
temporary table)?

Cheers,

Jack

Jack Higgs wrote:
> I want to create and populate a temporary table into a DB2 database from
> a Java web application. This is all fine and good and works. However, I
> would then like to reference the temporary table from a Stored Procedure
> from the same connection.
> Is this possible? Am I able to pass in the name of the temporary table
> and use it in a Stored Procedure? If so can you give some simple coded
> examples? If not then what other options do I have (possibly a 'fake'
> temporary table)?
Which platform are you on? All DB2's support DECLAREd GLOBAL TEMPORARY
TABLEs (DGTT) which allow you to declare a temporary table that can be
seen by any nested stored procedure while it's definition is private to
the connection.
In order to create a procedure that uses a DGTT that DGTT needs to exist
at the time you create the procedure. As a best practice I recommend
that all DGTT are declared inside of soem sort of "initialize" procedure
which you call before doing any DDL which depends on their existence
and after you log on with your application.

DB2 for zOS also supports CREATEd GLOBAL TEMPORARY TABLEs which are just
like the declared ones except that their definitions are stored in the
information schema therefore every connection (and CREATE PROCEUDURE
statement) can see the same definition even though the content is
private to the session (You can assume that in a future version for DB2
for LUW this variation will be added as well).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab