By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

Stored procedures using declared temporary tables: please help!

P: n/a
Hi, we are currently implementing an application running on DB2 V7 on
Z/OS using largely COBOL stored procedures, managed using WLM.

Some of these stored procedures declared global temporary tables, which
are declared with ON COMMIT DELETE ROWS to perform work on and then
return these temporary tables to the client (which is a message driven
EJB connecting via DB2 connect). The client reads the data in the result
sets, creates some XML to be sent back to the front end and then commits
the transaction. At this point, I would expect the temporary tables to
be destroyed and the threads to be returned to the WLM thread pool for
re-use by a subsequent request.

This doesn't appear to be the case. According to a number of articles,
the client is expected to destroy the temporary table itself, otherwise
the threads hang around. Our experience in practice is that if we do
destroy the temporary table from the EJB we get a noticable gain in
performance (twice as fast!) over a sample of 200 executions. Our guess
is that performance deteriorates since WLM dowgrades the priority of the
long-running thread when it gets re-used (could be wrong, but it's our
best guess). We do also notice that there are some threads hanging
around for hours.

This isn't a huge problem, and we can do this if we need to; however, it
doesn't sit very well. It appears to be a violation of the general
principle that it should be the same piece of code that allocates
resource that should take responsibility for destroying it. That and I
can't personally see why DB2 wouldn't just destroy the temporary table
and return the threads to the thread pool on commit (assuming you
haven't declared the table as ON COMMIT RETAIN ROWS, or used a WITH HOLD
cursor).

If any of you DB2 experts out there are able to confirm or deny the
above I would greatly appreciate it, as I would rather not take the step
to 'manually' delete the temporary tables if I can avoid it.

Many thanks, Keith.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I can't comment on DB2 z/OS performance problems, but ON COMMIT DELETE
_ROWS_ will only delete teh rows. It will not drop the temp table.
You could try to DROP the temp after opening the cursor.
With luck (meaning I don't remember) DB2 marks the tabel as drop
pending. If no luck your cursor gets closed :-(

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
I can't comment on DB2 z/OS performance problems, but ON COMMIT DELETE
_ROWS_ will only delete teh rows. It will not drop the temp table.
You could try to DROP the temp after opening the cursor.
With luck (meaning I don't remember) DB2 marks the tabel as drop
pending. If no luck your cursor gets closed :-(

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.