Function Signatures and Parameter Matching

Hey, Howard:

We've been using an ILE RPG procedure to return cost information from our product master file. The procedure returns a 13,6 decimal cost using 15 char product, 1 char standard (Base, Current, etc.), 1 char type (Mat, Lab, Ovr, Tot), and 1 char level (This, Prev, or Both) as input. This has been working very well for us in RPG. The input parameters are all defined as CONST.

Recently, we've been looking at using this procedure in a view so that we can integrate this costing routine into our queries. We thought we could turn our procedure into a function something like this:

CREATE FUNCTION PCGETCST
       (PRODNO CHAR(15),
        STANDRD CHAR(1),
        MATTYPE CHAR(1),
        PRILEVEL CHAR(1)) 
       RETURNS DECIMAL(13,6)
       ....
       EXTERNAL NAME 'MYLIB/PC(PCGETCST)'

This created a nice function that didn't work, because PCGETCST was not found when called like this:

SELECT PRDNO, PCGETCST(PRDNO,'B','T','B') FROM PRODMSTR

This stumped me for quite a while for two reasons. First, the function really does not exist as an object (WRKOBJ *ALL/pcgetcst only showed the original module), even though it was created. I finally figured out that creating functions isn't like creating programs. But you can check if they exist by using table SYSFUNCS. Second, I was calling it basically the same way I called it from our RPGLE programs, and it works fine in those programs. This is when I figured out overloading and conversion of data types had some effect on finding it.

So next I tried creating a function like this:

CREATE FUNCTION PCGETCST 
       (PRODNO VARCHAR(15),
        STANDRD VARCHAR(1),
        MATTYPE VARCHAR(1),
        PRILEVEL VARCHAR(1)) 
RETURNS DECIMAL(13,6)
....
EXTERNAL NAME 'MYLIB/PC(PCGETCST)'

This also created a function, but when called it always returned zero (indicating a problem in the program). Further debugging (which wasn't very much fun, either) showed a 2-byte garbage field in front of my product number (and probably in front of the other parameters). As you maybe can tell, I'm not sure about the representation of the various data types. Ascertaining that the two garbage bytes might represent a length, I finally duped my procedure and created a new one using CONST VARYING parameters. Voila! Success at last.

After all that, my question is this: Did I solve this the only way I can, or is there a way that I can use the existing procedure to create this function?

--Mike

What a cool question, Mike. Thanks for asking. This brings up a few points I want to make about user-defined functions (UDFs) in DB2.

A UDF is simply an entry in the table SYSFUNCS and possibly one or more entries in the SYSPARMS table that identify the arguments to the function. When you attempt to use a UDF, DB2 searches the SYSFUNCS table to find a function with the name that you are using. Then DB2 ensures that the parameter signatures of the function match the parameters you are passing. In your first definition, you are defining the parameters as CHAR, but DB2 sees literal strings as data type VARCHAR. Therefore, DB2 does not find the function.

This select would have probably worked:

SELECT PRDNO, 
PCGETCST( CHAR(PRDNO,15), 
    CHAR('B',1), 
    CHAR('T',1), 
    CHAR('B',1))
       FROM PRODMSTR

or using CAST

Select prdno,PCGETCST( CAST(PRDNO as char(15)),
     CAST('B' as char(1)),
            CAST('T' as char(1)),
     CAST('B' as char(1))) 
FROM PRODMSTR

The reason why it would have worked is that you are casting the data types to the type that DB2 expects for your function definition, thereby making the function signatures match. Note that if PRDNO is a CHAR(15) field, the CAST and CHAR functions are not required, because DB2 will see that the field matches the function definition.

Also, remember that you can have more than one function called PCGETCST that takes the same number of arguments. Which one is used depends on the function signature--the data types and the number of arguments--that you are passing to the UDF.

In your second example, you declared the function with VARCHAR data types; the function signatures matched and therefore DB2 attempted to execute the external program. However, since your program was expecting CHAR data and was actually being passed VARCHAR, those mysterious two bytes in the beginning of the data, the program returned 0 indicating an error.

What I always do is make my data types go to the universal type. For example, CHAR data types can be passed to VARCHAR function declarations. DECIMAL, ZONED, PACKED, INTEGER, SMALLINT, and FLOAT can all be passed as DOUBLE, as all numbers can convert to double. This saves you from the data type conversion issues, because DB2 accepts the data no matter what the base type. The problem is that you then have to rewrite the RPG code to accept VARCHAR values when it is expecting CHAR values.

You have three choices in how to proceed. You can do this conversion to the program, thereby creating this second copy that accepts VARCHAR data and then needs to be maintained along with the original program. Or you can write a simple UDF that accepts the VARCHAR values, casts to CHAR values, and then calls the program as a stored procedure using the CALL statement. The CALL can return the data to a variable in the user-defined function, which can then return the data to the calling statement. Or you can issue the cast or CHAR statements, as I have shown in my example statement, thereby leaving your original program unmodified. If you want to see an example of a user-defined function calling a stored procedure, go to the resources section of my Web site: www.sqlthing.com

One last note on finding the function: Always remember that DB2 uses the value of the SQL PATH when searching for a user-defined function. The SQL PATH can be your library list, or it can be the list of libraries that you assigned when creating an ODBC or a JDBC data source/connection string. If you are sure a function exists and you are attempting to call it with the proper parameters, try issuing a SET CURRENT FUNCTION PATH = MYLIB, OTHERLIB, SECLIB; statement. This causes the current function path to be set to the libraries indicated. When DB2 searches the SYSFUNCS table for a function, it only searches the libraries that are in your PATH. If the function exists and the signature matches, but the library the function is created in is not in your path variable, then DB2 will not find it.

I think putting functions in views is one of the ways that you really can enforce business rules in your database. Your pricing example is a great implementation of moving business logic to a table and thereby forcing application programs to use the rules. Kudos on cool code!

Howard F. Arner, Jr., is a writer and consultant working for Client Server Development in Jacksonville, Florida. He is the author of iSeries and AS/400 SQL at Work, available at www.sqlthing.com. E-mail Howard at harner@sqlthing.com or go to his Web site to find out more about user-defined functions.

Sponsored By PROFOUND LOGIC SOFTWARE

Announcing RPGsp! RPG Smart Pages (RPGsp) is an Integrated Development Environment for creating dynamic web applications. If you are a shop with strong RPG skills, you can start developing e-Business / e-Commerce or Intranet Applications IMMEDIATELY! It's easier than traditional RPG/DDS development! RPG Smart Pages (RPGsp) integrates an RPG aware WYSIWYG HTML Designer with RPG-Alive enabled Code Editing Capabilities, allowing you to create and compile dynamic Web applications as well as traditional RPGLE programs from one sophisticated GUI development environment! For a Free Trial or more information, contact Profound Logic Software at www.ProfoundLogic.com