Declared Global Temporary Table in multiple Stored Procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Otto Carl Marte - 16 Feb 2007 11:07 GMT

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

Reply to this Message

Serge Rielau - 16 Feb 2007 12:27 GMT

> Hi,
>
[quoted text clipped - 10 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Otto Carl Marte - 16 Feb 2007 13:04 GMT

Thanks Serge. That is definitely an option.

Reply to this Message

Otto Carl Marte - 28 Feb 2007 11:01 GMT

Thanks Serge, this works well, and definitely solves our problem.

Reply to this Message