In DB2 LUW 8.2, if a SP is called dynamically, the elapsed time of the
entire stored procedure shows up accurately in the Snapshot for Dynamic SQL,
even when it includes a Cursor with Return. When I say elapsed time, I am
referring to the entire SP elapsed time, not the elapsed time of individual
statements within the SP (many of which are static SQL and would not be
included in a Snapshot for Dynamic SQL).

But in 9.5+ (not sure about 9.1), the elapsed time of a dynamically called
SP in a Snapshot for Dynamic SQL only includes the point up to (but not
including) when the Cursor with Return is opened and DB2 processes the
result set at the end of the SP (which can be a big chunk of time in many
SP's).

I assume there is some reason for this? It would be nice if it worked like
8.2 so we can easily identify slow running SP's being called dynamically
from java apps, and then dig deeper into the SP itself for each individual
SQL statement only if needed (if the total elapsed time of the SP is too
slow).

On 8/24/2010 12:56 AM, Mark A wrote:
In DB2 9 there was a lot of SP perf work being done. This included
tossing the client-runtime out of the PSM API.
With that "buffering" was also tossed (because it wasn't needed anymore...)
Buffering is the filling up of the comm buffer with multiple rows on the
first fetch fetch so that subsequent fetches are done locally.
What you probably saw in DB2 8.2 was the time it took to fill that
buffer on open.
In DB2 9.5 this doesn't happen anymore. Instead the buffer will be
filled (i.e. the query partially executed) on the first fetch from the
client.

Either way you never had a guarantee that the cost to execute the entire
cursor was included.

What the snapshot correctly showed and still shows is the elapsed time
until the CALL finishes..

Cheers
Serge

PS: I give this theory a 90% chance of being correct :-)
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

"Serge Rielau" <srielau (AT) ca (DOT) ibm.com> wrote

Maybe no guarantee in 8.2, but it was pretty close. I can tell by measuring
the elapsed to run the SP in a local shell script vs. what Snapshot for
Dynamic SQL says was elapsed time (a little higher in the shell script to
account for comm back to local client and may fetching all the rows like you
suggested).

But the major part of the cost of the cursor was the open and first fetch.
At least it was enough info to know whether the SP had a performance problem
without looking at each individual SQL statement.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab