Re: Debugging an SQL Function

eftimios pantzopoulos wrote:

I've been experimenting with SQL procedures and functions, and
have discovered how to debug
procedures after reading this link:

http://www-01.ibm.com/support/docview.wss?uid=nas197633f5d0d583c0f86256a1e007624f5

I was able to confirm that copies of the sql statements had been
placed into QSQDSRC as was described in the article. I used the
DBGVIEW(*SOURCE) parameter in the RUNSQLSTM statement as per the
above link.

FWiW both DBGVIEW(*SOURCE) and OUTPUT(*LIST) are noted.

I now want to do the same thing with a function I've written, but
when I create the function (which interfaces to an RPGLE service
program sub-procedure), again with DBGVIEW(*SOURCE), I am unable
to put it into debug, and I noticed that there was no source
member in QSQDSRC for the function I created.

The messages logged in the joblog should show the completion for ADDPFM to QSQDSRC in the library of the function. For example:

RUNSQLSTM SRCFILE(QCLSRC) SRCMBR(P1) COMMIT(*NONE) NAMING(*SYS)
ERRLVL(30) /* OUTPUT(*LIST) */ DBGVIEW(*SOURCE)
File QSQLSRC in library QTEMP already exists.
Member P1 already exists in file QSQLSRC in library QTEMP.
File QSQDSRC created in library MYLIB.
File QSQDSRC in library MYLIB changed.
Member P1 added to file QSQDSRC in MYLIB.
Member P1 added to file QSQLT00160 in QTEMP.
Module P1 was created in library MYLIB on 03/19/10 at 05:03:15.
Service program P1 created in library MYLIB.
Object P1 in MYLIB type *MODULE deleted.

I get the following message when I attempted to use STRDBG:
CPF1912 Program CLMAGEXS in library EVEDEV cannot be accessed.

That message indicates STRDBG PGM(EVEDEV/CLMAGEXS) was issued, but the UDF is in a Service Program; i.e. STRDBG SRVPGM(EVEDEV/CLMAGEXS) was presumably the intended invocation.?

All the documentation I've read implies that debug can be used with procedures, functions, and triggers. Should I expect that the function code be generated to QSQDSRC just like the
procedure?

Maybe the only issue for *SRVPGM vs *PGM is the problem, but per the interactive joblog details that I included above, the source member was generated [for my request]. Did the joblog look like my snippet? Were the spooled files for the RUNSQLSTM and compile listing(s) verified to have completed with success; and that the requests were not been rolled back [per default COMMIT(*CHG)]?

What I'm actually trying to do is issue an SQL error from the function if it determines that the return value from the sub-procedure denotes that an error has occurred (byte 1 will be
'1'). I've found the SIGNAL command and that works in a test procedure I created, so now I just need to know how to obtain the
sub-procedure return value from within the SQL function so that an error condition can be transposed to an SQL error with the
SIGNAL.

The scenario is a CREATE FUNCTION for LANGUAGE SQL.?.?

Any advice for this would be appreciated as well.

HTH.

Regards, Chuck