db2 Shared CGTT(Global Temp Table) Data ploblem?

[scv1977]

Hi
I have a question about Shared CGTT(Global Temp Table).
In one session you can share data
Can I share data(In the SESSION.TMP_PROTESTTAB) with 2SP in one
session?
For example,I have 2SP(Stored Procedure)
The SP code is the following:

1) SP1
CREATE OR REPLACE PROCEDURE dbo."TMP_TESTTB1"
(
V_CompanySeq INTEGER,
V_TableName VARCHAR(100)
)
SPECIFIC dbo."TMP_TESTTB1"
LANGUAGE SQL
BEGIN
DECLARE b int DEFAULT 7;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_PROTESTTAB
(
IDX_NO INT ,
TableSeq INT

) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO SESSION.TMP_PROTESTTAB (IDX_NO, TableSeq) values (b, b);

CALL dbo."TMP_TESTTB2"(1, 2);
END;

2) SP2
CREATE OR REPLACE PROCEDURE dbo."TMP_TESTTB2"
(
CompanySeq INTEGER,
TableName VARCHAR(100)
)
SPECIFIC dbo."TMP_TESTTB2"
LANGUAGE SQL
BEGIN
DECLARE a INT DEFAULT 5;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_PROTESTTAB
(
IDX_NO INT ,
TableSeq INT
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

BEGIN
DECLARE S_Temp_Cursor0 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT * FROM SESSION."TMP_PROTESTTAB";
Open S_Temp_Cursor0;
END;
END

--->I want to get the following result!!
db2 "call dbo.TMP_TESTTB1(1,1)"

Result Set 1
--------------

IDX_NO TABLESEQ
----------- -----------
1 1

1 Record Complete.

But The result is following....

Result Set 1
--------------

IDX_NO TABLESEQ
----------- -----------

0 Record Complete.

In the Two Source(SP1,SP1),Can I share the Data In the
Table(SESSION.TMP_PROTESTTAB)?

[Serge Rielau]

In your exampel you are creating two temp tables. So obviosuly that will
not work.
What you should do is to declare teh global temporary table outside teh
stored procedures (ideally after connect).
That way teh CREATE PROCEDURE DDL and teh CALL statements can see teh GTT.

If you are on DB2 9.7 I recommend you CREATE GLOBAL TEMPORARY TABLE.
That way you don't need to bothere declaring it in every connection.
DGTT are great for ad-hoc when the defintion keeps changing.
CGTT are best for static setups. Where all you want is private data sets.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[john feng]

In your particular code case, there is a simple fix:

1: swap the two lines:
...

INSERT INTO SESSION.TMP_PROTESTTAB (IDX_NO, TableSeq) values (b, b);

CALL dbo."TMP_TESTTB2"(1, 2);

...

into :

...

CALL dbo."TMP_TESTTB2"(1, 2);

INSERT INTO SESSION.TMP_PROTESTTAB (IDX_NO, TableSeq) values (b, b);

....

2. move these codes from sp2 into the end of sp1:

BEGIN
DECLARE S_Temp_Cursor0 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT * FROM SESSION."TMP_PROTESTTAB";
Open S_Temp_Cursor0;
END;

It will return the rows ( the (b,b) will not be inserted since the columns are int typed.).

But for sure, this is cheating the compiler to get it working.

Cheers!

JOHN FENG

>> On Friday, June 25, 2010 8:24 AM Serge Rielau wrote:

>> On 6/25/2010 2:48 AM, scv1977 wrote:
>> In your exampel you are creating two temp tables. So obviosuly that will
>> not work.
>> What you should do is to declare teh global temporary table outside teh
>> stored procedures (ideally after connect).
>> That way teh CREATE PROCEDURE DDL and teh CALL statements can see teh GTT.
>>
>> If you are on DB2 9.7 I recommend you CREATE GLOBAL TEMPORARY TABLE.

>> That way you do not need to bothere declaring it in every connection.

>> DGTT are great for ad-hoc when the defintion keeps changing.
>> CGTT are best for static setups. Where all you want is private data sets.
>>
>> Cheers
>> Serge
>>
>>
>> --
>> Serge Rielau
>> SQL Architect DB2 for LUW
>> IBM Toronto Lab

>> Submitted via EggHeadCafe
>> ASP.NET Drawing a chart using OWC11 - Office Web Components
>> http://www.eggheadcafe.com/tutorials/aspnet/601e9bc2-40ed-405e-b1b0-f416046b6698/aspnet-drawing-a-chart-using-owc11--office-web-components.aspx