IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 18 replies
  • Latest Post - ‏2018-02-19T15:48:01Z by Junius
Junius
Junius
31 Posts

Pinned topic RPGLE service pgm UDF not created

‏2017-12-15T18:36:43Z | creation problem rpg rpgle udf

We're trying to create our first RPG service program UDF.  We've created many SQL UDFs without a problem.

When we run the CREATE FUNCTION statement it runs successfully and the job log indicates the object was created in the library specified, AND the function is listed in the DB2 system tables, BUT .... the object doesn't exist anywhere ! ????  So when we try to run it we get a "function not found", of course.

We are on 7.1 TR11 and here is the source code:

CREATE or replace FUNCTION zzsteveb/NXTAVLDT_F(@comp char(2),@locn numeric(2,0),@SKU char(7), @ret_date numeric(8,0))

RETURNS numeric(8)

LANGUAGE rpgle

NO SQL

external name 'MOD2000/NXTAVLDTS(NXT_AVL_DT)'

PARAMETER STYLE general

program type sub ;

 

Did we do something wrong or is this a bug?

Thanks,

Jay

 

 

Updated on 2017-12-15T18:37:31Z at 2017-12-15T18:37:31Z by Junius
  • Junius
    Junius
    31 Posts
    ACCEPTED ANSWER

    Re: RPGLE service pgm UDF not created

    ‏2018-01-09T16:21:22Z  
    • Phil5471
    • ‏2018-01-09T14:29:35Z

    The last screen shot shows the function parameter attributes are varchar, integer, varchar and integer.  Brigitta stated early on that the parameters may need to be cast to match the attributes of the program parameters.  How are the parameters defined in program NXTAVLDTS procedure NXT_AVL_DT?

     An external SQL function, language RPGLE, i.e. other than SQL, does not create a new program object.  A SQL function based on a SQL statement, language SQL, will create a service program object based on generated C source code.  In both cases the function will appear in Navigator's functions list.

    Hello Phil,

    Thank you for clearing up the confusion about the external object issue when the function is based on a service program.

    Also, you were correct to point out the parameter casting issue.  Both of the following statements worked:


    select ZZSTEVEB.NXTAVLDT_F(  Cast('02' as Char(2)),  Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0))  ) from sysibm/sysdummy1;

    values( ZZSTEVEB.NXTAVLDT_F(  Cast('02' as Char(2)),  Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0))  )  );

     

    Thank you Phil (and Birgitta & Tom),

    Jay

  • B.Hauser
    B.Hauser
    320 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-16T16:22:35Z  

    How did you try to call your function, i.e. how did you pass your parameter for testing? Hardcoded? for example as 'AB' for the first parameter?

    Try to cast the passed parameters to the expected data types.

    UDFs can be overloaded, i.e. multiple UDFs with the same name but different parameter definitions can coexist within the same schema/library.

    If you pass 'AB' it is interpreted as VARCHAR. If you parameter is defined as CHAR and VARCHAR is passed, the UDF is not found because of the different data types.

    Also I you pass numeric values without decimal point, these values are interpreted as Integer and integer and Numeric are also different data types.

    Birgitta

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-21T01:47:00Z  
    • B.Hauser
    • ‏2017-12-16T16:22:35Z

    How did you try to call your function, i.e. how did you pass your parameter for testing? Hardcoded? for example as 'AB' for the first parameter?

    Try to cast the passed parameters to the expected data types.

    UDFs can be overloaded, i.e. multiple UDFs with the same name but different parameter definitions can coexist within the same schema/library.

    If you pass 'AB' it is interpreted as VARCHAR. If you parameter is defined as CHAR and VARCHAR is passed, the UDF is not found because of the different data types.

    Also I you pass numeric values without decimal point, these values are interpreted as Integer and integer and Numeric are also different data types.

    Birgitta

    Hi Birgitta,

    I don't think it's a parameter issue.  I'm familiar with the problems related to parameter types and overloading.  I've created several overloaded SQL UDF functions.

    I'm confused by the fact we can not find the object in any library when we look for it using WRKOBJ or something similar.  When we create a regular SQL UDF we see the object in the library we specified (when creating the UDF) with a type of "*SRVPGM" and an attribute of "CLE".  That's not the case when we create the RPGLE UDF.  We can't find any object  in any library with a name anywhere similar to the UDF name or the "external" name.

    I would think we should be able to see the object somewhere?

    Thanks,

    Jay

  • B.Hauser
    B.Hauser
    320 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-21T10:07:16Z  
    • Junius
    • ‏2017-12-21T01:47:00Z

    Hi Birgitta,

    I don't think it's a parameter issue.  I'm familiar with the problems related to parameter types and overloading.  I've created several overloaded SQL UDF functions.

    I'm confused by the fact we can not find the object in any library when we look for it using WRKOBJ or something similar.  When we create a regular SQL UDF we see the object in the library we specified (when creating the UDF) with a type of "*SRVPGM" and an attribute of "CLE".  That's not the case when we create the RPGLE UDF.  We can't find any object  in any library with a name anywhere similar to the UDF name or the "external" name.

    I would think we should be able to see the object somewhere?

    Thanks,

    Jay

    Are you able to see the UDF in ACS --> Schemas --> Functions?

    If so what is the value in the column program for the UDF? You should see something like 'YOURSCHEMA/PGMNAME(FNCNAME)'.

    You need to check whether there is a Program or Service-Program with this name in the displayed schema.

     

    Birgitta

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-21T18:22:51Z  
    • B.Hauser
    • ‏2017-12-21T10:07:16Z

    Are you able to see the UDF in ACS --> Schemas --> Functions?

    If so what is the value in the column program for the UDF? You should see something like 'YOURSCHEMA/PGMNAME(FNCNAME)'.

    You need to check whether there is a Program or Service-Program with this name in the displayed schema.

     

    Birgitta

    Yes, the UDF can be found in ACS. I've attached a screen shot of the ACS properties window for that UDF.  We know DB2 thinks it exists.  Is that good enough?

    The RPG being referenced is a service program and we set the "program type" parameter to "sub".

    Does the system not create an actual object since this is basically an SQL wrapper (pointer) to an existing service program?

    What should the SQL syntax look like to invoke this function?  We tried    " select zzsteveb/NXTAVLDT_F('02',12,'12F45XX',20171219) from sysibm/sysdummy1 "  and that got us the "not found" message.

    Thanks again.

    Jay

    Attachments

  • B.Hauser
    B.Hauser
    320 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-21T18:40:32Z  

    As said before, you need to cast your parameters to the expected data types!

     

    Values(ZZSTEVEB/NXTVALDT_F(Cast('02' as Char(2)), Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0)));

     

    If you pass '02' it interpreted as VARCHAR (even though if it includes all defined parameters). For SQL (UDFs) CHAR and VARCHAR are different data types and there is no UDF defined with VARCHAR data types!

    Numeric values passed without decimal point are interpreted as INTEGER, and again INTEGER and NUMERIC are different data types.

    So because of the parameters passed with data types that defer from the parameter definition, the function cannot be found.

    Birgitta

     

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-21T23:48:06Z  
    • B.Hauser
    • ‏2017-12-21T18:40:32Z

    As said before, you need to cast your parameters to the expected data types!

     

    Values(ZZSTEVEB/NXTVALDT_F(Cast('02' as Char(2)), Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0)));

     

    If you pass '02' it interpreted as VARCHAR (even though if it includes all defined parameters). For SQL (UDFs) CHAR and VARCHAR are different data types and there is no UDF defined with VARCHAR data types!

    Numeric values passed without decimal point are interpreted as INTEGER, and again INTEGER and NUMERIC are different data types.

    So because of the parameters passed with data types that defer from the parameter definition, the function cannot be found.

    Birgitta

     

    Well...still no luck.  I've attached a screen shot so you can verify I'm doing it correctly.

    By the way, thank you for the clarification of how DB2 sees literal vales in this scenario.  I would not have guessed that they would be seen as VARCHAR & INT.

    I'm still curious to know whether there should be an object or not. 

    Attachments

  • TomLiotta
    TomLiotta
    7 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-25T01:42:21Z  
    • Junius
    • ‏2017-12-21T23:48:06Z

    Well...still no luck.  I've attached a screen shot so you can verify I'm doing it correctly.

    By the way, thank you for the clarification of how DB2 sees literal vales in this scenario.  I would not have guessed that they would be seen as VARCHAR & INT.

    I'm still curious to know whether there should be an object or not. 

    The error shows as "NXTVALDT_F in *LIBL ... was not found."

    What is *LIBL for that job? Does it include ZZSTEVEB? How did you determine that ZZSTEVEB is in *LIBL for that job?

    If you qualify it as ZZSTEVEB.NXTVALDT_F(), what error do you see?

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2017-12-26T18:58:48Z  
    • TomLiotta
    • ‏2017-12-25T01:42:21Z

    The error shows as "NXTVALDT_F in *LIBL ... was not found."

    What is *LIBL for that job? Does it include ZZSTEVEB? How did you determine that ZZSTEVEB is in *LIBL for that job?

    If you qualify it as ZZSTEVEB.NXTVALDT_F(), what error do you see?

    Hi Tom,

    When we qualify the UDF name with the library (ZZSTEVEB) we get this message:

    Message: [SQL0206] Column or global variable ZZSTEVEB not found. "

    I even tried the " SET PATH ZZSTEVEB"   statement, which still resulted in the "not found" error when I try to invoke the UDF.

     

    Also, just to make sure there's no confusion, there is no object by that name in the library call ZZSTEVEB..  I've attached a screen shot showing all objects in that library starting with letter N.

    The only place in our system that we can find evidence of the existence of this UDF is in the system tables like QSYS2/SYSFUNCS.  I don't know if this is normal for RPG service program UDFs.

     

    Thanks for trying to help Tom,

    Jay

  • TomLiotta
    TomLiotta
    7 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-05T07:25:39Z  
    • Junius
    • ‏2017-12-26T18:58:48Z

    Hi Tom,

    When we qualify the UDF name with the library (ZZSTEVEB) we get this message:

    Message: [SQL0206] Column or global variable ZZSTEVEB not found. "

    I even tried the " SET PATH ZZSTEVEB"   statement, which still resulted in the "not found" error when I try to invoke the UDF.

     

    Also, just to make sure there's no confusion, there is no object by that name in the library call ZZSTEVEB..  I've attached a screen shot showing all objects in that library starting with letter N.

    The only place in our system that we can find evidence of the existence of this UDF is in the system tables like QSYS2/SYSFUNCS.  I don't know if this is normal for RPG service program UDFs.

     

    Thanks for trying to help Tom,

    Jay

    When we qualify the UDF name with the library (ZZSTEVEB) we get this message:

    "  Message: [SQL0206] Column or global variable ZZSTEVEB not found. "

    In this case, please show the exact SQL statement that results in that error. Also, provide the name and version of the client that runs the statement.

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-05T15:13:37Z  
    • TomLiotta
    • ‏2018-01-05T07:25:39Z

    When we qualify the UDF name with the library (ZZSTEVEB) we get this message:

    "  Message: [SQL0206] Column or global variable ZZSTEVEB not found. "

    In this case, please show the exact SQL statement that results in that error. Also, provide the name and version of the client that runs the statement.

    Hi Tom,

    Here's the statement we're trying to run...

    select zzsteveb/NXTAVLDT_F('02',12,'12F45XX',20171219) from sysibm/sysdummy1

    And I've attached a screen shot with the ACS client information along with the statement and result, just to make sure there's no confusion here.

    Thanks,

    Jay

  • B.Hauser
    B.Hauser
    320 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-06T12:11:08Z  
    • Junius
    • ‏2018-01-05T15:13:37Z

    Hi Tom,

    Here's the statement we're trying to run...

    select zzsteveb/NXTAVLDT_F('02',12,'12F45XX',20171219) from sysibm/sysdummy1

    And I've attached a screen shot with the ACS client information along with the statement and result, just to make sure there's no confusion here.

    Thanks,

    Jay

    If you want to qualify a UDF in the SELECT Line of an SELECT-Statement, you need to separate Schema and UDF with a period (even if you are working with System Naming Conventions).

    The slash is interpreted as divide.

    I can't remember when the rule that schema and object can be separated with a period instead of a slash for system naming conventions was introduced, but it should be available for release 7.1 (and higher).

    Before there was no way to qualify a UDF in the SELECT-Line when system naming conventions were used (with SQL naming conventions there was never a problem)

    Birgitta

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-08T16:33:53Z  
    • B.Hauser
    • ‏2018-01-06T12:11:08Z

    If you want to qualify a UDF in the SELECT Line of an SELECT-Statement, you need to separate Schema and UDF with a period (even if you are working with System Naming Conventions).

    The slash is interpreted as divide.

    I can't remember when the rule that schema and object can be separated with a period instead of a slash for system naming conventions was introduced, but it should be available for release 7.1 (and higher).

    Before there was no way to qualify a UDF in the SELECT-Line when system naming conventions were used (with SQL naming conventions there was never a problem)

    Birgitta

    Hi Birgitta,

    I tried with the period versus the slash and I'm back to the original problem, i.e. the function is not found.  :-(

    What else might I be missing here?  I still understand how this can ever be found when there's no object with that name (even though there's an entry in the DB2 system tables).

    Jay

  • Phil5471
    Phil5471
    5 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-09T14:29:35Z  
    • Junius
    • ‏2018-01-08T16:33:53Z

    Hi Birgitta,

    I tried with the period versus the slash and I'm back to the original problem, i.e. the function is not found.  :-(

    What else might I be missing here?  I still understand how this can ever be found when there's no object with that name (even though there's an entry in the DB2 system tables).

    Jay

    The last screen shot shows the function parameter attributes are varchar, integer, varchar and integer.  Brigitta stated early on that the parameters may need to be cast to match the attributes of the program parameters.  How are the parameters defined in program NXTAVLDTS procedure NXT_AVL_DT?

     An external SQL function, language RPGLE, i.e. other than SQL, does not create a new program object.  A SQL function based on a SQL statement, language SQL, will create a service program object based on generated C source code.  In both cases the function will appear in Navigator's functions list.

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-09T16:21:22Z  
    • Phil5471
    • ‏2018-01-09T14:29:35Z

    The last screen shot shows the function parameter attributes are varchar, integer, varchar and integer.  Brigitta stated early on that the parameters may need to be cast to match the attributes of the program parameters.  How are the parameters defined in program NXTAVLDTS procedure NXT_AVL_DT?

     An external SQL function, language RPGLE, i.e. other than SQL, does not create a new program object.  A SQL function based on a SQL statement, language SQL, will create a service program object based on generated C source code.  In both cases the function will appear in Navigator's functions list.

    Hello Phil,

    Thank you for clearing up the confusion about the external object issue when the function is based on a service program.

    Also, you were correct to point out the parameter casting issue.  Both of the following statements worked:


    select ZZSTEVEB.NXTAVLDT_F(  Cast('02' as Char(2)),  Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0))  ) from sysibm/sysdummy1;

    values( ZZSTEVEB.NXTAVLDT_F(  Cast('02' as Char(2)),  Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0))  )  );

     

    Thank you Phil (and Birgitta & Tom),

    Jay

  • Phil5471
    Phil5471
    5 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-09T17:22:28Z  

    It's a pain to require parameter casting to the data type of an external procedure.  If a developer or user forgets to cast then someone could spend a lot of time looking for a non-existent problem.  Right? :-) 

    In this case I'd create a SQL based function with the same name and a specific name of something like NXTAVLDT_F_CAST.  This new function would receive the four parameters as varchar, integer, varchar and integer and return the numeric(8) value.  The statement would be:

    begin

      return

      values( ZZSTEVEB.NXTAVLDT_F(  Cast('02' as Char(2)),  Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0))  )  );

    end;

    (The statement may need tweaking. I use this method for table functions, i.e. "select... from table(..." rather than "values...".  Haven't used it with scalar functions.)

    With the new function is created you could use your original statement: select zzsteveb/NXTAVLDT_F('02',12,'12F45XX',20171219) from sysibm/sysdummy1

    Another option is to add a new procedure to the service program that receives varchar, integer, varchar, integer parameters and executes the original procedure to retrieve the numeric value.  Then recreate SQL function using the new procedure.

    If I'm providing suggestions that you already understand I apologize.

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-01-10T00:06:49Z  
    • Phil5471
    • ‏2018-01-09T17:22:28Z

    It's a pain to require parameter casting to the data type of an external procedure.  If a developer or user forgets to cast then someone could spend a lot of time looking for a non-existent problem.  Right? :-) 

    In this case I'd create a SQL based function with the same name and a specific name of something like NXTAVLDT_F_CAST.  This new function would receive the four parameters as varchar, integer, varchar and integer and return the numeric(8) value.  The statement would be:

    begin

      return

      values( ZZSTEVEB.NXTAVLDT_F(  Cast('02' as Char(2)),  Cast(12 as Numeric(2, 0)), Cast('12F45XX' as Char(7)), Cast(20171219 as Numeric(7, 0))  )  );

    end;

    (The statement may need tweaking. I use this method for table functions, i.e. "select... from table(..." rather than "values...".  Haven't used it with scalar functions.)

    With the new function is created you could use your original statement: select zzsteveb/NXTAVLDT_F('02',12,'12F45XX',20171219) from sysibm/sysdummy1

    Another option is to add a new procedure to the service program that receives varchar, integer, varchar, integer parameters and executes the original procedure to retrieve the numeric value.  Then recreate SQL function using the new procedure.

    If I'm providing suggestions that you already understand I apologize.

    Thank you Phil, for the suggestions.   This function will be used in the SELECT statement where the parameters will be actual columns versus literals. We should have probably written the test something like this...

    With test_data as (
    select cast('02' as char(2)) as cmp,
             cast('12' as numeric(2,0)) as loc,
             cast('12345  ' as char(7)) as sku,
             cast('20171217' as numeric(8,0)) as wkdate
     from sysibm/sysdummy1
     )
     select zzsteveb.nxtavldt_f(cmp,loc,sku,wkdate)
     from test_data;

     

    Using that format to insure the parameter are properly cast, plus using the period in qualifying the function name might have done the trick.

     

    Thanks,

    Jay

  • Cliff Dowell
    Cliff Dowell
    6 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-02-19T15:16:47Z  

    Hi Jay,

    I haven't seen it stated here (forgive me if I missed it) but external functions and procedures using RPG program calls do not create an object in the literal (read as "O/S level", wrkobj accessible) sense. It will show in sysfuncs and iNav under functions of your target library. Think of it as more of an SQL recipe for a call to a non-SQL object. 
    As far as testing it, when I build any procedure or function, I open an SQL session in iACS (IBM i Access Client Solutions) and create variables that are the same data types as my parameters [Create or replace variable pWHS numeric(2,0); Create or replace variable pDspDte DATE;]. Then, I set those variables with test values [Set pWHS = 05;Set pDspDte = date('2017-12-18'); ] (minus all the [brackets]). I have found this to simplify the process and makes testing different parameter scenarios pretty easy.
    You can also change the LIBL in the SQL session as you would at the i command line with CL commands; just precede with [CL:] such as - [cl:ADDLIBLE LIB(mylib) POSITION(*BEFORE ThatLib); ] (again, minus the [brackets])
    One last thing I'll do is test a function with a Values clause - [values(mylib.myfunc(pWHS, pDspDte));  ]
    Again, I apologize if any of this was already covered.
    Good luck!
    Cliff

  • Junius
    Junius
    31 Posts

    Re: RPGLE service pgm UDF not created

    ‏2018-02-19T15:48:01Z  

    Hi Jay,

    I haven't seen it stated here (forgive me if I missed it) but external functions and procedures using RPG program calls do not create an object in the literal (read as "O/S level", wrkobj accessible) sense. It will show in sysfuncs and iNav under functions of your target library. Think of it as more of an SQL recipe for a call to a non-SQL object. 
    As far as testing it, when I build any procedure or function, I open an SQL session in iACS (IBM i Access Client Solutions) and create variables that are the same data types as my parameters [Create or replace variable pWHS numeric(2,0); Create or replace variable pDspDte DATE;]. Then, I set those variables with test values [Set pWHS = 05;Set pDspDte = date('2017-12-18'); ] (minus all the [brackets]). I have found this to simplify the process and makes testing different parameter scenarios pretty easy.
    You can also change the LIBL in the SQL session as you would at the i command line with CL commands; just precede with [CL:] such as - [cl:ADDLIBLE LIB(mylib) POSITION(*BEFORE ThatLib); ] (again, minus the [brackets])
    One last thing I'll do is test a function with a Values clause - [values(mylib.myfunc(pWHS, pDspDte));  ]
    Again, I apologize if any of this was already covered.
    Good luck!
    Cliff

    Hi Cliff,

    Thank you for the reply.  Most of what you said had already been said, although what you covered in one reply took many replies (above) to get that same information, especially the point about external procedures not creating a real object on the system.  The main problem was us testing with literal values which were being interpreted as integer values.  Had we done something like you mentioned, i.e. use properly defined variables, we wouldn't have wasted so much time trying to figure out the problem.

    Thanks for the reply.

    Jay