Problem with Global Temporary Table

[chettiar]

I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerServiceTypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

I am able to compile the proceudre.

But when I try to compile procedure B which is referencing the
temporary table in procedure A, I get the error.
"SESSION.TEMP" is an undefined name.

In procedure B all I am trying to do is
INSERT INTO session.temp VALUES ('MS', 0);
Can some one please help

[Knut Stolze]

chettiar wrote:

I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerServiceTypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

I am able to compile the proceudre.

But when I try to compile procedure B which is referencing the
temporary table in procedure A, I get the error.
"SESSION.TEMP" is an undefined name.

Try to add this in procedure B before the temp table is used:

IF 1 = 0 THEN
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerServiceTypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;
END IF

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

chettiar wrote:

I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerServiceTypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

I am able to compile the proceudre.

But when I try to compile procedure B which is referencing the
temporary table in procedure A, I get the error.
"SESSION.TEMP" is an undefined name.

In procedure B all I am trying to do is
INSERT INTO session.temp VALUES ('MS', 0);

You have two choices:
Use dynamic SQL to break teh static dependency.
DECLARE the temp table before running the CREATE PROCEDURE statement.
DB2 needs to have all DDL objects defined to do proper name and function
resolution.

I recommend to customers to concentrate all temp table defintion that
are used across multiple procedures to have a dedicated DDL procedure.
Before creating any procedure you simply CALL the DDL procedures and you
know you got what you need.
Also when executing the app you CALL that same procedure at the right time.

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