What? You haven't used a UDF before? No worries. There's nothing to it.

Recently, one of our programmers came to me with a question about retrieving some information from our iSeries. He was using a business intelligence (BI) tool to analyze picking errors across several warehouses, and he needed a way of retrieving the warehouse area for a specific warehouse and item location. Unfortunately, that information was not in a table in our system, but rather was calculated by an RPGLE program and returned as a parameter when the program was called with the warehouse ID and the item location. Because the BI tool used SQL to retrieve information from our iSeries, I suggested that he consider creating an SQL User-Defined Function (UDF) to return the warehouse area. With this method, the warehouse area can be returned as a value by any high-level language, such as PHP or Java, or any tool that uses SQL to access data on the system.

If you're primarily an RPG programmer and haven't created or used an SQL UDF before, it might be a little intimidating the first time you look at the SQL CREATE FUNCTION command. That's because SQL is a standard database language that can be used with many different operating systems, high-level languages, and database implementations, so the CREATE FUNCTION command has a number of parameters to accommodate them. For the purposes of this article, however, we're going to assume that we have an RPG or RPGLE program that's called to perform a specific task and that it returns a single value. In SQL UDF terminology, we are creating an external UDF that returns a scalar value.

Once you have identified an RPG program that returns a single value, what steps do you need to take to create an SQL UDF? That depends on whether you're starting with a program or service program and the type of interface you want to create. If you're starting with a service program, then you have a choice of the type of interface you want to use when the program is called, or in SQL terminology, the PARAMETER STYLE. The two basic types I'll discuss here for RPG programs are GENERAL and SQL. Ironically, GENERAL refers to a very specific program implementation, which is an RPGLE Service Program containing a RETURN (result) statement, while SQL refers to a more general interface that can be used with both programs and service programs. Let's look at the GENERAL style first, since it's the easiest to implement.

In the example above, where we're trying to find the warehouse area based on a warehouse number and bin location, the service program would look like this:

     H NOMAIN

     ****************************************************************

     * Program.....: AREACHEK                                       *

     * Purpose.....: Determine warehouse area                       *

     ****************************************************************    

     D getarea       pr            3

     D whse_                      2   const

     D binloc_                      5   const

     P getarea        B                   Export

     D getarea       pi            3

     D whse                      2   const

     D binloc                      5   const

     D whsearea     s             3  

     /free

     // Do some work here to take the warehouse number and bin

     // location and determine the warehouse area

      return (whsearea);

     /end-free

     P                 E

The good thing about starting with the preceding service program that returns a value is that no programming changes are required to define an interface for SQL to call the UDF, and the CREATE FUNCTION statement can refer directly to the service program. But before we move on to the CREATE FUNCTION statement, let's look at what needs to be done in the case of an RPGLE program.

Instead of a service program, let's assume you have an RPGLE program similar to the one shown below that returns the warehouse area as a parameter:

     ****************************************************************

   * Program.....: AREACHK                                       *

     * Purpose.....: Determine warehouse area                       *

     ****************************************************************

     D areacheck     pr                 EXTPGM('AREACHEK')

     D whse_                      2

     D binloc_                      5

     D whsearea_                  3

     D areacheck     pi

     D whse                       2

     D binloc                    5

     D whsearea                  3

     /free

     // Do some work here to take the warehouse number and bin

     // location and determine the warehouse area

     // Set whsearea = warehouse area

      *inlr = *on;

     /end-free

Again you have a choice. Either the program can be converted to a service program as in the first example with a RETURN (value), or the SQL parameter style interface can be used. Since we've discussed the service program interface, let's look at what needs to be done to this program to be called using the SQL interface. The SQL interface consists of the following required parameters in this order:

1. Input parameters: One or more to be specified for the function
2. A result parameter
3. One or more indicator variables for the input parameters (one for each input parameter). These are set by DB2 and are used to indicate null (-1) or not null (0).
4. An indicator variable for the result parameter. A negative value indicates a null result.
5. A CHAR(5) output parameter for SQLSTATE
6. A VARCHAR(517) input parameter for the fully qualified function name
7. A VARCHAR(128) input parameter for the specific name of the function
8. A VARCHAR(1000) output parameter for the message text

Several other optional parameters can be used but will not be discussed here. Now let's look at the program after the interface modifications have been made.

     ****************************************************************

     * Program.....: AREACHK                                       *

     * Purpose.....: Determine warehouse area                       *

     ****************************************************************

     D areacheck     pr                 EXTPGM('AREACHEK')

     D whse_                      2   const

     D binloc_                    5   const

     D whsearea_                  3

     D in_whse_                  5i 0 const

     D in_binloc_                5i 0 const

     D in_whsearea_              5i 0

     D sqlsterr_                  5

     D fctname_                517   varying

     D spcname_                128   varying

     D msgtext_                1000   varying

     D areacheck     pi

     D inwhse                    2   const

     D inbinloc                  5   const

     D whsearea                  3

     D in_whse                    5i 0 const

     D in_binloc                  5i 0 const

     D in_whsearea                5i 0

     D sqlsterr                  5

     D fctname                  517   varying

     D spcname                  128   varying

     D msgtext                  1000   varying

     /free

     // Do some work here to take the warehouse number and bin

     // location and determine the warehouse area

     // Set whsearea = warehouse area

      if notfound;

         sqlsterr = '59999';

         msgtest = 'Warehouse area not found';

      ENDIF;

      *inlr = *on;

     /end-free

When faced with making changes to the program interface to support the SQL parameter style, you might say it's just as easy to create a service program instead, and most of the time I would agree with that view. However, there are a couple of benefits when using the SQL parameter interface, and one is that a user-defined error code can be returned in the sqlsterr parameter, as well as a user-defined message in the msgtext. I've included an example here where the warehouse area could not be determined. This is one reason that you might consider using the SQL parameter style instead of the GENERAL one. Another reason is that your database supports null values, and you might pass or return a null value as part of the function arguments and want to be able to identify them in your program. The SQL interface provides indicator variables to identify null values.

Once we've determined the parameter style, it's time to create the SQL function. This can be done by using the CREATE FUNCTION SQL statement in STRSQL, creating a source module with the CREATE FUNCTION statement in it and using the RUNSQLSTMT command, or using the wizard in the System i Navigator. For our examples above, let's compare a simple create function statement for the service program using the GENERAL parameter style to the create function statement for the RPGLE program using the SQL parameter style. For the service program, our create statement is:

Create function mylib/getarea ( whse char(2), binloc char(5) )

       returns char(3)

       language rpgle

       external name 'MYLIB/AREACHECK(getarea)'

       parameter style general

       program type sub

Reading the above statement is straightforward. We're creating the SQL function getarea in the library MYLIB and passing two parameters, both consisting of character data. The function returns a three-character result. The language that the service program is written in is RPGLE, the program is located in MYLIB/AREACHECK with procedure getarea, and the program type is a procedure in a service program. Note that the external name parameter specifying the library name and program name (MYLIB/AREACHECK) must be in uppercase.

For the RPGLE program with the SQL parameter style, we have the following:

Create function mylib/getarea (whse char(2), binloc char(5) )

       returns char(3)

       language rpgle

       external name 'MYLIB/AREACHECK'

       parameter style sql

       program type main  

The only differences are referencing the program name on the external name statement instead of the procedure name for the service program, the difference in the parameter style, and the program type MAIN instead of SUB. It's important to remember that the create function statement is not creating a program object in your library, but is instead registering information about the interface of the program object in the database for SQL to use to call it.

Other Keywords on the Create Function

There are many other keywords on the Create Function command, and without discussing all of them, I will mention here a couple that could apply to an RPG or RPGLE program. The first is the DETERMINISTIC or NOT DETERMINISTIC keyword. Using the DETERMINISTIC keyword tells SQL that the same input parameters will always return the same result. This allows an SQL function to cache the result on a call and, if called again with the same parameters, use the result without executing the program. An example is a function that returns the hire date based on passing the function an employee number. Similarly, a NOT DETERMINISTIC (the default) function would be one that returns the vacation days remaining for an employee when passed the employee number. Without this keyword specified, SQL will assume the function is NOT DETERMINISTIC, so specifying DETERMINISTIC if it applies could improve performance.

Another keyword indicates whether SQL is used in the RPG or RPGLE program that makes up the function and how it is being used. In this case the options are these:

• No SQL—The program does not use SQL.
• Contains SQL—The program contains a limited number of SQL statements, none of which read or update data files.
• Reads SQL—The program reads data using, for example, an SQL Select statement (Default).
• Modifies SQL Data—The program can read and modify data using SQL.

There are a number of other keywords available, so this is just a start.

After we've executed the CREATE FUNCTION statement above, let's put our new function to use. In our system, the table INVENT contains our locations and inventory for each item in each of our warehouses, so the SQL statement

SELECT prodno, size, casloc, getarea ( char('01'), casloc )

FROM invent WHERE casloc <> ' '  

returns the warehouse area for warehouse 1 for each of the items with a case location that is not blank, as shown below.

Figure 1: The display shows the warehouse area and case location for each item.

While I've used the STRSQL command line interface and the green-screen to show the getarea function, it's important to point out that the System i Navigator has a nice interface to create, delete, and view SQL UDFs. For example, if we take a look at the getarea function that we created in my test library JBSTEST, it looks like this:

Figure 2: This is the getarea function created in test library JBSTEST.

By selecting the function and right-clicking your mouse, you get a dialog box with one of the choices being "Explain SQL". Choosing this option displays information about the function, including the CREATE statement.

Figure 3: Get information about the chosen function.

Summary

While I haven't covered every aspect of creating an SQL UDF scalar function in this brief article, I hope I've given you a good starting point for creating your own functions. So the next time one someone asks for information that is contained in the business logic of an RPG or RPGLE program, at least consider whether it makes sense to provide it through an SQL UDF. It's a great way to share your RPG business logic with programmers writing in Java, PHP, or any other language that uses SQL.

On a final note, I'd like to leave you with two other SQL statements that will be useful to you as you begin experimenting with the Create Function statement. The first is the Drop Function statement, which allows you to remove an SQL function you created from the data base registry, and, as of V6R1, a new Alter Function statement that allows you to modify an existing function.

For more on SQL User-Defined Functions, consider the IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries or the DB2 for i SQL Reference at the IBM Infocenter website.