SQLCODE -204 due to deletion of Declare Global Temp Table



IBM's flagship relational database management system

SQLCODE -204 due to deletion of Declare Global Temp Table

Postby upendra_water » Sun Dec 19, 2010 1:55 am

Hi,

We are facing some problems in our project due to deletion of Declare Global Temporary Table.
The logic used is as follows:
We execute a stored procedure "A" which creates a Temp table.
This Temp table is used by a next stored procedure "B" and it returns data to the front end.
The temp table is used by 1 more stored procedure "C" and it returns data to front end.

Before starting third stored procedure "C" the Temp table created by "A" is getting dropped. So we get SQLCODE -204 in third stored procedure "C".
I believe the table is getting dropped because the execution time of each SP is high and so it is getting dropped meanwhile. Is my understanding correct?
Is there any way to avoid this table from getting dropped?
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQLCODE -204 due to deletion of Declare Global Temp Table

 

Re: SQLCODE -204 due to deletion of Declare Global Temp Table

Postby dick scherrer » Sun Dec 19, 2010 10:28 am

Hello,

You might consider having a "permanant" work table rather than every time thru creating a new table . . .

Periodically (every day or week) this table could be emptied if/as needed.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: SQLCODE -204 due to deletion of Declare Global Temp Table

Postby upendra_water » Mon Dec 20, 2010 11:50 pm

Hi Dick,

Creating permanent table is the first option we thought to resolve this issue. But I have a few doubts.
1) These stored procedures will be called from .NET front end by hundreds of users on a daily basis. What issues permanent table may create is still unknown.
2) Rows inserted by one user may impact the results of another user etc.
3) Housekeeping jobs (IC, RUNSTAT, REORG) need to be scheduled on Daily basis.
So, we again dropped this idea and started looking for ways to retain temp tables. So is there any way to avoid temp tables from getting dropped?
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQLCODE -204 due to deletion of Declare Global Temp Table

Postby dick scherrer » Tue Dec 21, 2010 2:40 am

Hello,

Done properly, i expect the "permanant" table will perform better than the multiple temporary tables. . .

1. There should be no issues if done correctly. . .

2. I suspect that there are multiple tables that contain multiple entries for different users without gatting confused/impacted. . .

3. Not necessarily. . . This may be needed daily, but possibly not. Even if it does require housecleaning daily, it will only be a few minutes. Once set up, the job(s) can be scheduled and not need daily attention by "someone".

One of my clients long ago did this for several thousand users/customers who did many interactions every day. . .

Also, our work tables were pre-defined and we did not delete/insert rows typically. Existing rows were updated which saved lots of database activity.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: SQLCODE -204 due to deletion of Declare Global Temp Table

Postby upendra_water » Tue Dec 21, 2010 3:24 am

Hi Dick,

Thanks for the response. Can you please explain first 2 points?
1) What is mean by 'done correctly'?
2) I didn't understand the 2nd point. If it is a permanent table, there will be only single table, right?
Also can you please help me to understand why Declare Global Temporary Table is getting dropped in my program. Is it a common problem with DGTT? What is the remedy for it?
Appreciate your help on this!!
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SQLCODE -204 due to deletion of Declare Global Temp Table

Postby dick scherrer » Tue Dec 21, 2010 9:51 am

Hello,

Also can you please help me to understand why Declare Global Temporary Table is getting dropped in my program. Is it a common problem with DGTT? What is the remedy for it?
No, i can't explain this. If you continue with the temp table, you may need to some diagnostic code to determine under which circumstances this happens. One place to start mikght be to make sure that every sqlcode is checked for every sql issued.

"Done correctly" means that the work table be designed to accomodate what is needed without introducing unneeded complexity or performance degraders.

Yes, there will be only one table for this. From the little i understand of the process, the key to the table would be the user/customer id and a sequence number (one for each entry into the table). I suspect that you will only want a given id to have one process active at any one time.

Part of defining a new user/customer to the system would be to create the work entries in this table.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post