Prev: db2 install questions
Next: Replacing multiple statements with a MERGE
From: scv1977 on 25 Jun 2010 02:48
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)?
From: Serge Rielau on 25 Jun 2010 08:24
On 6/25/2010 2:48 AM, scv1977 wrote:
> 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)?
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

 | 
Pages: 1
Prev: db2 install questions
Next: Replacing multiple statements with a MERGE