Wednesday, July 25, 2018

Using SQL UDF with RPG

using values from sql udf in rpg program

This short post comes from a discussion I was having with two other IBM i developers at my place of work. I had shown them a number of SQL User Defined Functions, UDF, I had created to make my SQL statements easier.

"That's fine," started one of the developers, "but how does that help me in RPG. Won't I have to duplicate the procedures so I can call them in RPG?"

"No", I replied, "you can call them using SQL".

At first they were skeptical. Having shown them how easy it was to do they were sold.

In the examples I showed them the UDF were calling RPG procedures. In my opinion there are just some things that RPG does better then SQL. Validating and converting dates from one format to another are a case in point.

The ERP my employer uses store the dates as seven packed numbers in CYMD format. When I create extract files for the users no-one wants to see the CYMD they want to see the date formatted for the USA. Having to do this everywhere I needed a date became a pain in the neck.

       SUBSTR(DIGITS(ADDDTE),6,2) || '/' ||
       SUBSTR(DIGITS(ADDDTE + 19000000),2,4)

The USA "date" is really character: MM/DD/YYYY

So I created my own UDF, CYMDTOUSA, to do the same thing.


My two colleagues agreed that this was a better approach, but how could they do the same thing in a RPG program.

I quickly wrote this program to show them how it could be used without using a SQL Select.

01  **free
02  dcl-s Char10 char(10) ;
03  dcl-s Packed7 packed(7) ;


05  Packed7 = 1180731 ;
06  exec sql SET :Char10 = MODLIB.CYMDTOUSA(:Packed7) ;

07  dsply ('Char10 = ' + Char10) ;

08  *inlr = *on ;

Line 1: This program is written in totally free RPG.

Lines 2 and 3: I have defined two variables. The first, line 2, is a 10 long character variable. The second, line 3, is a 7 long packed variable.

Line 4: I always include a SET OPTION in my SQL RPG programs to ensure that when the program is compiled the options will be the way I want them to be.

Line 5: Move a "date" into the packed variable.

Line 6: The SQL SET will execute the UDF, with the packed variable as the input parameter, and return a value into the character variable. Notice that the variable names all have to start with a colon ( : ).

Line 7: I have use the DSPLY operation code to show the value in Char10.

DSPLY  Char10 = 07/31/2018

There was a "light bulb" moment as my colleagues realized that they could use the UDF in their RPG programs to convert the date whenever they needed to.

If you are interested in how I made this UDF I started by creating a procedure that is added to the source member I had my other UDF procedures in, UDFMODULE.

01  **free
02  ctl-opt nomain ;

    // Convert *CYMD date (7P0) to *USA (10A)
03  dcl-proc CYMDtoUSA export ;
04    dcl-pi *n char(10) ;
05      inCymd packed(7) const ;
06    end-pi ;

07    test(de) *cymd inCymd ;
08    if (%error) ;
09      return '00/00/0000' ;
10    else ;
11      return %char(%date(inCymd:*cymd):*usa/) ;
12    endif ;
13  end-proc ;

Line 2: This control option is used on when the source member contains procedures that will be called from elsewhere.

Line 3: The start of the procedure CYMDtoUSA. It needs the EXPORT keyword as it will be called and return values to procedures and program outside of this module.

lines 4 – 6: The procedure interface. One parameter is passed to the procedure inCYMD, line 5. The way I say what is returned in on the initial procedure interface line, line 4. I have CHAR(10) at the end of the line which means that a 10 character value is returned to whatever called this procedure.

Line 7: The passed date is validated using the TEST(DE) operation code.

Line 8 and 9: If there was an error, the date is not valid, zeroes are returned.

Lines 10 and 11: If the date is valid the CYMD number is converted to a date using the %DATE built in function, which is then converted to character using the %CHAR BiF.

Line 13: The procedure ends.

I have to create a service program that contains the module, that in turn contains all of the UDF procedures.

            TEXT('UDF service program')

I put the SQL statements that create the UDF into one source member. I would add this new statement for the UDF.

    -- CONVERT *CYMD (7P0) TO *USA (10A)

                                (INCYMD DECIMAL(7,0))
02    RETURNS CHAR(10)
05    NO SQL

Line 1: My function is called CYMDTOUSA, it is in MODLIB, and I need to define the parameter that is passed, inCymd.

Line 2: This function returned a 10 character value.

Line 3: This function is written in RPGLE.

Line 4: DETERMINISTIC means that if it called with the same incoming parameter more than once it somehow "remembers" the value to return.

Line 5: The code within the function does not use any SQL.

Line 6: This is where I define where the code for the function is to be found. This is given in the format: library/service program(procedure).

Line 7: This is the simplest way to pass parameters in and out of the function. Only the defined incoming parameter, line 1, and the outgoing parameter, line 2, are returned to the SQL statement.

Line 8: As the function uses a subprocedure I need to give the program type of SUB.

I run the SQL statements in the source member by using the Run SQL Statement command, RUNSQLSTM. This create all of my UDFs, and I am ready to use them within SQL statements, like Select, or to pass the returned value to RPG, using the Set.


This article was written for IBM i 7.3, and should work for earlier releases too.


  1. I'm confused Simon. The routine is in RPG. Why ever would you use SQL to call it via an UDF when you could far more efficiently call it directly from RPG. What am I missing here?

    1. I just want to say: "Because we can!"

      As I am "dragging" people, at work, from the dark ages of RPG to more modern approaches of tackling projects showing how I can use an external function is a win in the battle I fight every day of RPG3 vs RPGLE vs SQL. Proving that there is a function that can do something simple to help, as described above, is a step in the right direction. If RPG can use the function then it must be serious code, and not discounted by the nay-sayers.

  2. Hi Simon,
    is there anyway that we can validate whether the field contains numeric data or not using SQL built in function [something similar to IS NUMERIC in COBOL/400 or TESTN in RPG]

  3. I interpret this more as a demonstration of what can be done as to what is practical. Unlike Jon I love sql :). But to use sql to leverage rpg is plain weird to me. :).

  4. (posting anonymous--the Google option didn't seem to work)

    Thanks Simon! My hunch is that Simon's intent is to educate only.

    The power of an RPG routine defined as an SQL *external* UDF is its reusability and utility. This UDF could be used in views (example) for dynamic conversion of legacy dates (SELECT CYMDTOUSA(somedate) FROM SOMETABLE). The RPG routine can certainly be called directly also.

    A UDF can also appear in a WHERE clause. If the UDF returned a date type you could do SELECT * FROM SOMETABLE WHERE UDF(somedate) BETWEEN ‘2018-01-01’ and ‘2018-07-31’.

    A UDF can also make use of complex business logic procedure (e.g., return the price of item X).

    IMHO external UDFs (and sprocs) vs.their SQL-procedure-language counterparts are the most underutilized power tools in the DB2 toolbox. The latter is kludgy, difficult to read, fraught with performance pitfalls, and accessible only via a query or sproc call. The former merely wraps a routine that is already sharable outside of DB2.

  5. I personally would let SQL find the *SRVPGM in the *LIBL via
    unless a company has a policy against it.
    This makes testing the code in different environments (development/integration/UAT/Production/ModLib) easier.



To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.