Re: Simple UDTF only gets called with OPEN, FETCH events

On 11-Nov-2013 11:03 -0800, Peter Dow wrote:

<<SNIP>>

My code is at http://code.midrange.com/399002eaaa.htm.

The link [without the extra period] failed with a 404 error.

Ahhh... I just tried the following, and that works:
http://code.midrange.com/399002eaaa.html

FWiW: I noticed in that link, the DROP FUNCTION syntax is using the by-parameter versus DROP SPECIFIC FUNCTION, so be sure there is not any issues with overloading that routine name.

My other comments, inline below, reflect not having seen the code... which I have yet to look at, other than a quick look at the CREATE FUNCTION.

The interesting thing is that my UDTF only gets called with a call
type of -1 (open) and 0 (fetch), but never 1 (close).

I tried creating the function without the NO FINAL CALL, which
theoretically means my UDTF should have been called with a call type
of -2 (startup) and 2 (final) as well as the open/fetch/close, but
still only got called on open and fetch. I have a counter that's
incremented each time the function is called, and put in DSPLYs like
this

0045.00 dsply ('STARTUP: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' '
0046.00 + %editc(Counter:'L') + ' '
0047.00 + %editc(parmIn:'L'));

to see what was happening and ran it from the green screen STRSQL just
to keep it simple and this is what I got:
<<SNIP>>

Anyone have an idea what's going on? Is it because I'm calling it
from STRSQL?

Use of DSPLY [which defaults sending] to the *EXT external message queue as a debug technique is likely not a good idea for a UDF; scalar functions for sure, and I am confident also TABLE functions. Possibly naming *SYSOPR or another message queue [as the second argument of the DSPLY], then DSPMSG to view the effects afterward, is a better choice. Or just use actual debug to interrogate the value of the variables and the program flow [e.g. breakpoints], from a separate session, rather than in the same session; i.e. use STRSRVJOB and STRDBG [and DSPMODSRC].

The 5250 is not thread-safe, and SQL UDF processing is implemented using threads. Thus what is made visible by the workstation function at the display, may not properly reflect what was actually sent. This is the same reason that debugging of the invocation of the UDTF needs to be done from a separate session... because the debugging which must communicate with the user via information to\from the display-station where the UDF runs, is unpredictable for the input\output to that WS.

It occurred to me that maybe the close and final calls happen when I
sign off, so I did a SIGNOFF *LIST, but the resulting job log did not
show anything after the last FETCH.

For interactive SQL, the close would typically be upon F3=Exit, from the report. But if the cursor can pseudo-close, then when the cursor was closed. The final call too, if not run under isolation, else AFaIK after COMMIT or ROLLBACK to complete the transaction. If either is due to signoff, then it is possible that the external message queue is no longer available when the ODP is closed or transaction is canceled by the EOJ processing.