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.
