Hi ,

I am in the process of writting a stored procedure which will insert some data into the global temporary table and then i will issues a simple select to show the data on the page.However, I created the global temporary table, inserted a row into it to see if its properly created and issued select statement.

DECLARE GLOBAL TEMPORARY TABLE TESTTAB1(AGENT VARCHAR(30),CLOSECOUNT integer) on commit preserve rows;

insert into session.testtab1 values('sach',3);

select * from session.testtab1;

The result was fine.

DECLARE GLOBAL TEMPORARY TABLE TESTTAB1(AGENT VARCHAR(30),CLOSECOUNT integer) on commit preserve rows
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
insert into session.testtab1 values('sach',3);
------------------------------------------------------------------------------
insert into session.testtab1 values('sach',3)
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
select * from session.testtab1;
------------------------------------------------------------------------------
select * from session.testtab1

AGENT CLOSECOUNT
------------------------------ -----------
sach 3

1 record(s) selected.

But when I am writting into my procdure something like,

delete from session.TESTTAB1; or

INSERT INTO session.TESTTAB1 SELECT OPTR.PYUSERNAME||'('||AP.PPOP||')' "AGENT",
COUNT(DISTINCT(CASE WHEN AP.PPOP IS NOT NULL THEN AP.PZINSKEY ELSE NULL END)) "CLOSECOUNT"
FROM
AP_WORK AP,PR_OPERATORS OPTR
WHERE AP.PPOP IS NOT NULL
AND DATE(AP.PPDATE)
BETWEEN DATE(FromDateMM||'-'||FromDateDD||'-'||FromDateYY) AND DATE(ToDateMM||'-'||ToDateDD||'-'||ToDateYY)
AND AP.PPOP=OPTR.PYUSERIDENTIFIER

GROUP BY OPTR.PYUSERNAME||'('||AP.PPOP||')';

it gives me error saying TESTTAB1 is an undefined name.Acoording to what I have read,global temporary tables are created in 'session' schema by default.So why can't I access it as 'session.TESTTAB1' inside the procedure?

How can I refer to the declared global temporary table in that case?

The DGTT must exist in the session before the SP can be created with that session.

Andy

If you are on 9.7 , check if CREATED GLOBAL TEMPORARY TABLE suits your needs.

Sathyaram

You have to DECLARE Global Temporary Table inside of SP, not outside....

SP is not a subprogram, you have to know it.

Cara

The other option is to create the temp table outside the procedure and then use dynamic SQL inside the procedure itself. The thing is that the CREATE statement is analyzed and if DB2 finds a temp object and cannot guarantee that the object always exists (as is the case if the temp table is created inside the procedure), it is an error. If you can guarantee the existence in your transactions, dynamic SQL is the way to hide this from DB2.

p.s: Executing a procedure is like a sub-program. The definition of one is not.