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

Problem with Global Temporary Table

P: n/a
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

Apr 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Apr 5 '06 #2

P: n/a
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
Apr 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.