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

Hi Chuck,

Thanks for the insight; I'll try debugging from another session tomorrow. I'd debugged it before from the same session, but got tired of putting in the EV variable-name over and over, so I threw in the DSPLYs; I'll likely also try you suggestion of DSPLY to a named msgq. And thanks for catching the missing "L" on the link.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>/

On 11/11/2013 12:26 PM, CRPence wrote:

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.