IBM Mainframe Forum

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?

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.

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?

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.

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!!

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.