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

Declared Global Temporary Table in multiple Stored Procedures

P: n/a
Hi,

As I understand it, Declared Global Temporary Tables (DGTTs) have a
scope that is session/connection based.
Using the same connection, I have discovered that if I declare a DGTT
in one stored procedure, then I can't create a second stored procedure
that uses the DGTT, as the DGTT is not "visible". The only way around
this is to use dynamic SQL in the second stored procedure. In this way
(using dynamic sql) i can create and use the DGTT across multiple
stored procedures.

Is there perhaps a way to make DGTT visible across multiple stored
procedures using static sql, or is this an inherint limitation of
DGTTs.

Cheers
Otto

Feb 16 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Otto Carl Marte wrote:
Hi,

As I understand it, Declared Global Temporary Tables (DGTTs) have a
scope that is session/connection based.
Using the same connection, I have discovered that if I declare a DGTT
in one stored procedure, then I can't create a second stored procedure
that uses the DGTT, as the DGTT is not "visible". The only way around
this is to use dynamic SQL in the second stored procedure. In this way
(using dynamic sql) i can create and use the DGTT across multiple
stored procedures.

Is there perhaps a way to make DGTT visible across multiple stored
procedures using static sql, or is this an inherint limitation of
DGTTs.
The limitation is this:
To create a procedure that uses a DGTT the table must be declare before
CREATE PROCEDURE is run OR it must be declared within the CREATE
PROCEDURE statement itself. The reason is that DB2 needs to be able to
distinguish between variables and columns which share the same namespace
in the SQL/PSM standard.

My preferred code-convention for x-procedure DGTT usage is to declare
all temps in a dedicated "init" procedure.
Then before you run any DDL using the temps you CALL INIT()
That way all your temps are centralized and no mix up can happne.
Of course when you connect you also call init().

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 16 '07 #2

P: n/a
Thanks Serge. That is definitely an option.
Feb 16 '07 #3

P: n/a
Thanks Serge, this works well, and definitely solves our problem.
Feb 28 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.