Re: Debugging an SQL Function

After getting the debug going, please start a new\separate message thread for the error handling concerns. Responses inline.

Regards, Chuck

eftimios pantzopoulos wrote:

<<SNIP re: working SQL stored procedure example>>

I put the following function script through the same RUNSQLSTM as
above. Again this is a hack to prove a concept, and A00720 won’t
be the final name of the function! Is Language RPGLE? I thought
Language had to refer to the language used in the root code? Have
I made some fundamental mistake in the script?:

<code>

DROP FUNCTION EVEDEV/A00720
;
CREATE FUNCTION EVEDEV/A00720
( VARCHAR(2)
, VARCHAR(3), , NUMERIC(7,0),
, DECIMAL(8,0),
, DECIMAL(3,0)
) RETURNS CHAR(78) CAST FROM VARCHAR(78)
-- SET OPTION DBGVIEW= *SOURCE
LANGUAGE RPGLE DETERMINISTIC NO SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION ALLOW PARALLEL
PARAMETER STYLE GENERAL
--SIMPLE CALL
EXTERNAL NAME 'EVEDEV/#CMSVP(GETCLMAGEXS)'
RUNSQLSTM SRCFILE(EVEDEV/QRPGSRC) SRCMBR(a00720)
COMMIT(*NONE) ERRLVL(30) DBGVIEW(*SOURCE)

</code>

The above is an "External User Defined Function" for SQL which is not the same as a "SQL User Defined Function". The SQL UDF is written in the SQL, not in another language like RPGLE or even SQLRPGLE; i.e. the source must indicate "LANGUAGE SQL". That the SET statement had to be commented was a very subtle indication [SQL0199] that there was no compile since an SQLanguage statement was not allowed; not very obvious without having the necessary understanding of the difference between External & LANGUAGE SQL UDFs :-!

I get nothing in the job log. The spool file tells me the
function was created:

SQL7998 0 1 Position 1 DROP FUNCTION statement complete.
SQL7997 0 3 Position 1 Function A00720 was created in EVEDEV.
<<SNIP end of listing>>

This is because an External [SQL] UDF is effectively just an entry in the SQL catalog [query SYSFUNCS in QSYS2] as a redirect to an [existing] external object; external to the SQL, and an existing RPGLE [service] program object would be updated with SQL details, such that PRTSQLINF issued against that named-as-External object would show the SQL details.

The named source member with CREATE FUNCTION contains only text for creating the catalog entry rather than any text lines which are actual language source statements that would be compiled into an executable. So there is no "compile" from any "language" source, thus there is also no QSQDSRC source member created from which an SQL UDF is then created. The executable object was [or should have been] created previously, from its own language statement source.

Because the object is not an SQL object, the manner of debug would be done the same as for any other non-SQL program object debug. If you use STRDBG, STRISDB, System debugger, or whatever for your other RPGLE programs, just do the same thing with the stored procedure. Note however that DISALLOW PARALLEL is probably best specified on the request to create the function, and that the debug environment is for threaded [even in interactive] activity [maybe not with NOT FENCED specified], so best to debug the program as a "serviced job"; i.e. where the servicing\debugging job is separate from the job that is both running the SQL and being debugged.

Trying to implement Birgitta’s approach:
Never having debugged an SQL script though Ops navigator I of course went to my browser. I found the following guide on
debugging SQL scripts through Ops Navigator. I hope that’s what
Birgitta intended:

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

I am not clear on purpose here. Regardless...

I got as far as step 3, but failed on: CL: CRTDUPOBJ QAQQINI QSYS *FILE MYLIB *NO; As I don’t have authority under my QPGMR sign on.
QAQQINI is present in two libraries and is owned as follows:
Lib: QSYS Owner=QSYS
Lib: QUSRSYS Owner=QSECOFR
Is this the way it should be?

As expected. The object is only available for read access; i.e. available for reference, but not available for copy\duplicating. The object must be duplicated to maintain the triggers; i.e. a QAQQINI not "duplicated" using CRTDUPOBJ will not be functional for reference by the CHGQRYA QRYOPTLIB().

The OS ships with only the copy in QSYS. If there is a copy in QUSRSYS [I personally recommend *not* to do so], the data within effects system-scoped initialization defaults.

Anything with the QQ INI file should be unrelated to either issue of getting debug going or getting the error handling established.?