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
