Re: Stored Proc execution times, front to back

On 14-Oct-2016 12:19 -0500, Needles,Stephen J wrote:

I need to be able to find out how long it takes to execute a stored
procedure, all the way through the open and consumption its cursors.

While in iNav...

In the program that is the stored proc, is an SQL statement. When I
test the statement alone, I can route its output to a table and clock
the whole process. But when I try to do the same thing using the
stored procedure, I am not allowed.

Even if somewhat directly possible, the comparisons between the lone statement [presumed to be a query SELECT done in the procedure; as a declared cursor], and the /same/ query done external to the routine, are likely enough different to preclude use of the results of that testing for performance measurements because they are not /same-enough/.

Does anyone have an idea that can allow me to do something like:

Insert into testable (call storedProc(parameter))

This would include the creation (inside the storedProc) and
consumption of the cursor (the INSERT portion) and iNav should be
able to tell how long it took to run.

Encapsulate the work done by the call storedProc(parameter) in a User Defined Table Function (UDTF or Table UDF). I recall an External stored procedure can access the result set of another. An SQL routine [such as a UDTF] I expect should be able to do that too, using RESULT SET LOCATORS, I always meant to code up an example of an SQL stored procedure consuming the result set of another but never have best I can recall. For external routines, that is done using the SQLTYPE of RESULT_SET_LOCATOR, but similarly I have never coded such a program, although I have a link saved to an article:
[DB2 for i: Process Stored Procedure Result Sets as Cursors; Published: August 25, 2010; by Michael Sansoterra](http://www.itjungle.com/fhg/fhg082510-story02.html)

A snippet of an example for a SQL stored procedure referencing the result set of another can be found here:
[DB2 for i 7.1: More Than Meets the Eye; by Kent Milligan; 26 April 2010]
(http://www.mcpressonline.com/database/db2/db2-for-i-71-more-than-meets-the-eye.html)