Hi,

In my project a Declare Global Temporary Table is used in a stored procedure.
This DGTT is created in procedure "A" and used in procedures "B" and "C". These 3 SPs are called from dotNET application one by one i.e. first A then B then C.
Procedures "A" and "B" complete successfully but "C" fails with SQLCODE -204 because DGTT is getting deleted.
I have 2 questions:
1) Why DGTT is getting dropped.
2) Is there any way to prevent it from getting dropped?

A declared temporary table is never created - that's why it is called "declared". It needs to be declared, subsequently, in every routine that references it.

I beleive somewhere COMMIT happened.

Try to use:
Lenny

Hi,
We have already used ON COMMIT PRESERVE ROWS option while declaring TEMP table. When the number of rows in TEMP table are very few, all stored procedures execute successfully. The problem occurs only when there are thousands of rows in TEMP table. In 1 case we found that there were 12000 rows in TEMP table and the stored procedure failed i.e. "A" and "B" execute successfully but "C" fails.

Because of it's happen when global table is huge, maybe you have
sqlcode = -904 (resourse unavailabe) which you do not catch, and than sqlcode = -204.

Lenny

Hi,

We are getting SQLCODE -204 only. Please see screenshot from DB2 logs.

Attaching 1 more screenshot which shows the exact error.
It shows that the temp table is undefined.

Undefined after destroyed in SP "B"....

Hi Lenny,
That's what is puzzling me. Why this table is getting dropped in third SP?
When the number of rows are less in temp table all three SPs execute successfully and we get proper reports from front end. Only when the number of rows are huge we face this error.