MVSFORUMS.com

JOHNCWL · Beginner Joined: 25 Feb 2004 Posts: 31 Topics: 16

HI,

I have a query on SQL stored procedure.

Stored Procedure is as follows:

kolusu · Posted: Thu Mar 18, 2004 8:34 am Post subject:

Johncwl,

You can drop the Global temporary table after the report is printed by the following statement.

Manas Biswal · Posted: Thu Mar 18, 2004 9:17 am Post subject:

JOHNCWL,

We faced exactly the same problem when we were trying to call a DB2 SP from a CICS application program. The difference however was we were only getting a duplicate GTT error when we were trying to call the SP twice within the same LUW. If we ended the transaction using a CICS RETURN(I haven't tried issuing a SYNCPOINT and trying to see whether that destroys the GTT) and thus ended the unit of work, the GTT used to get destroyed. But since we needed to call the same SP repeatatively from within the same LUW, we did exactly what Kolusu has suggested. After every CALL to the SP from our appl program and after fetching from the cursor, we dropped the GTT explicitly after issuing a CLOSE cursor. That worked for our case.
If you don't want to change your client programs, another way which I think will work is that in the beginning of your SP, code a drop of the GTT and disregard the SQLCODE of -204 in case the GTT does not exist. You can try that also.
HTH.
Regards,
Manas

JOHNCWL · Beginner Joined: 25 Feb 2004 Posts: 31 Topics: 16

But why should I drop a GTT, it will automatically drop once the application is over...

Pls expalin this ...

JOHNCWL · Beginner Joined: 25 Feb 2004 Posts: 31 Topics: 16

Thanks Ravi,

Will do the DROP explicitly.

Manas Biswal · Posted: Mon Mar 22, 2004 11:57 am Post subject:

JOHNCWL, Ravi,

I agree with JOHNCWL that the GTT should be dropped as soon as the application gets over. I was suggesting the approach as a workaround and not as the solution. Perhaps you should investigate the problem further as to why the GTT is not getting dropped when the application is getting over. We faced the problem only when we were calling the SP twice from within the same LUW (that will be analogous to the same instance of your application). I am not clear as to how you are getting the problem when your application is getting over and you are calling it afresh.
Ravi -

Manas Biswal · Posted: Thu Mar 25, 2004 2:06 pm Post subject:

Ravi,

If I understand your problem correctly, then your actual problem is not the global temporary tables, but the actual connection thread between your client and the DB2 SS on the mainframe. On the ideal side, the thread should be terminated as soon as you finish running an instance of the application. The close of the thread immediately deletes the GTT (There is nothing like any time lag). But, I guess what is happening in your case is that although you finish running your application, the thread remains open (identical to JOHNCWL's problem) and hence you are getting a -204. After 10 mins, the thread times out and deletes the GTT and you can run another instance of the application.
For my case the problem was something else. A CICS-DB2 thread (defined as a DB2ENTRY) is created when you execute the first SQL in your program and remains active until the end of the task. So, if you try to access the same stored procedure from within the same task twice, it will give you a -204 on the GTT. So, in my case there was no other option but to delete the GTT explicitly.
You can maybe have a look at the reason why your connection thread is not getting terminated.

Regards,
Manas