Re: Creating a SQL UDF

On 27 Sep 2013 05:29, Robert Mullis wrote:

I was using the OPDESC to retrieve the current length of the varying
parm passed in using CEEGSI. I removed that and replaced it with the
%LEN BIF and now it at least calls my procedure. Unfortunately when
my procedure returns, I get this cryptic message from SQL (I am
testing using STRSQL):

Function error X'0306' in machine instruction. Internal dump
identifier (ID) 0100A758.

The invocation effected a VL02000306 [VLIC log 0200 0306}. The following request would spool the dump [though probably not much use to hardly anyone but the IBM support\development]::
PRTINTDTA *DMP DMPID(0100A758)

There is nothing else in my joblog, but this message.

Seems improbable. And FWiW the "this message" has an identifier... MCH3203. The partial symptom is msgMCH3203 rcX0306, but for lack of the spooled joblog, but there is missing context; i.e.. the failing LIC program name and instruction and the program\procedure\statement that invoked that LIC method are in the [spooled] joblog. Plus there may have been additional spool data produced, in association with the failure; e.g. a QPDSPJOB and\or QPSRVDMP.

This is how I defined my UDF:

Create Function RMULLIS/RmvNonAlph(VarChar(1000),VarChar(1))
Returns VarChar(1000)
Language RPGLE
External Name 'RMULLIS/SYSRVUTL(REMOVE_NON_ALPHANUMERIC)'
Deterministic
No SQL
Parameter Style General
Allow Parallel
No External Action

According to the prototype in the OP, presuming only the OPDESC keyword was removed, the CREATE FUNCTION is not valid; i.e. the UDF can not be defined that way, because that does declarative for the SQL interface to the program does not match the actual program. Either the "wrapper" or the "sourced function" [mentioned in prior replies in this thread] must be utilized to enable the VARCHAR(1) as the second parameter using the PARAMETER STYLE GENERAL... or the program must be changed besides omitting the OPDESC keyword, to declare the second parameter as /varying/ character data.

Any ideas on what might cause this error?

If there is a parameter mismatch [as alluded just above seems possibly to be the case per no explicit mention of the required code change], then that incorrect definition may be what gives rise to the LIC error. There is a limited ability of the SQL to ensure validity; i.e. the user is almost fully entrusted with [responsible for] making the proper declarative to define the interface between the SQL and their program.