Calling a Program from a UDF

August 16, 2002 Timothy Prickett Morgan

Hey, Howard:

I have a service program on my AS/400 that I want to call that evaluates data and returns a description. I want to call the program each time a record is read from a physical file when I am doing a select statement. If fact, what I want to do is create a view so that each time the user reads the records, the program is called to return the value of the specific column so that that field in the view is calculated on the fly by the program. I know I can do this in Oracle, but can I do it with DB2 on the AS/400?

— Dave

Yes, you can do this by using the magic of user-defined functions and stored procedures, Dave. What you want to do is code a stored procedure declaration for the service program so that you can call it from SQL. Then, you want to create a user-defined function that calls the stored procedure and returns its results. Let’s do a simple example procedure and user-defined function to illustrate the points.

First, let’s take the following example procedure. The procedure is one I defined in my book, “iSeries and AS/400 SQL at Work.” It’s useful for retrieving a sequence number from a table. Assume that you have a library called SQLBOOK on your AS/400 with a physical file called SEQCTRL in that library.

The file contains two fields: SEQID and VLU. The SEQID field identifies the sequence number required, like ‘WO’ for a work order number or ‘PO’ for a purchase order number. The VLU field is an integer that represents the last sequence number used. The following stored procedure, rendered in Persistent Stored Modules (PSM) language will return the next sequence number and increment the VLU field to reflect the last number used:

CREATE PROCEDURE SQLBOOK.GETSEQ
 ( IN REQKEY CHARACTER(10),
  INOUT RETVAL INTEGER )
 RESULT SETS 0
 LANGUAGE SQL NOT DETERMINISTIC 
  BEGIN DECLARE C1 CURSOR FOR 
 SELECT VLU + 1 
   FROM SEQCTRL 
   WHERE SEQID = REQKEY ;
 OPEN C1 ;
 FETCH C1 INTO RETVAL ;
 CLOSE C1 ;
 UPDATE seqctrl SET vlu = RETVAL WHERE SEQID=REQKEY;
END

Go ahead and create the SEQCTL table and insert a record to maintain PO numbers:

CREATE TABLE SQLBOOK.SEQCTRL
   (SEQID   CHAR(10) NOT NULL PRIMARY KEY,
   VLU      INTEGER NOT NULL WITH DEFAULT);

INSERT INTO SQLBOOK.SEQCTRL VALUES ('PO',50);

Now that the procedure is created and data exists for purchase order numbers, if I want a sequence number for the next purchase order I could execute the following SQL command:

CALL SQLBOOK.SEQCTRL( 'PO', :VARFIELD)

If :VARFIELD was defined as an integer variable, after the call statement it would be instantiated with the value I should use in creating my next PO, which would be 51.

Now that we have a stored procedure, let’s look at how we can code a user-defined function to call a procedure for each row returned in a select statement. The following user-defined function takes one argument, S as CHAR(10), and returns an INTEGER number. This function is how we can call the SEQCTRL procedure to get the next sequence number for a given identifier on a row-by-row basis.

CREATE function SQLBOOK.TCALL4
   (InSt VARCHAR(10))
   returns INTEGER
   LANGUAGE SQL
   modifies SQL DATA
BEGIN 
DECLARE MYSTRING CHAR ( 10 ) ; 
DECLARE R INTEGER ; 
SET MYSTRING = Inst ; 
SET R = 0 ; 
CALL GETSEQ ( MYSTRING , R ) ; 
RETURN R ; 
END

Here are some things to note about the function: It contains the statement MODIFIES SQL DATA, which tells DB2 that this function could access physical files on the AS/400 and modify their contents. If you do not add this statement, the function will not work, because the procedure that the function calls does modify the underlying table. Also,the function takes its argument as a VARCHAR(10) and not a CHAR(10). This allows you to use the function if you want to pass literal values, which DB2 will interpret as VARCHAR data not as CHAR. If you coded the function to accept CHAR(10) then the following call to the function would fail:

SELECT TCALL4('PO') FROM sometable

Finally, if we pass the function a bad identifier, because there is no error code in the stored procedure, the function will call the procedure with the bad identifier and the procedure will return a 0.

Let’s test our work. First, create the following table and add the specified data to the table:

CREATE TABLE SQLBOOK.ATEST 
   (F1 CHAR(10) NOT NULL);

INSERT INTO SQLBOOK.ATEST VALUES ('PO');
INSERT INTO SQLBOOK.ATEST VALUES ('WO');
INSERT INTO SQLBOOK.ATEST VALUES ('WO');
INSERT INTO SQLBOOK.ATEST VALUES ('PO');

Now, execute the following SQL statement:

SELECT TCALL4(F1) AS FUNRESULT,
F1 AS INPUTCOL 
FROM SQLBOOK.ATEST;

You should see the following results:

FUNRESULT      INPUTCOL
51       PO
0        WO
0        WO
52       PO

Here’s what’s happening during execution. In the first row of table ATEST the field F1 contains the value PO, so this is passed to the function TCALL4. Function TCALL4 calls the stored procedure SEQCTRL, which increments the record identified by the value PO and returns the value of the incremented record. The function TCALL4 receives the returned procedure result in its variable R and then returns that value resulting in the value expressed by the FUNRESULT column.

In the second call to the function, the value read from the table is WO, so this is what is passed. The procedure fails to find the record where SEQID is equal to WO, so it returns 0 which is the value set into variable R before the select is executed. This results in the value shown in row 2 and row 3 of the result table.

When the final row of table ATEST is read, the value of F1 is PO, so this is passed to function TCALL4. TCALL4 calls the SEQCTRL procedure, which now finds the next value for SEQID equal to PO and returns 52 after incrementing the SEQCTRL table. The function TCALL4 then returns the procedure result to DB2, which shows the result.

Finally, if you want to put this into a view, the following statement would create a view returning the results of calls to the TCALL4 function for each row in ATEST.

CREATE VIEW SQLBOOK.HVIEW (COL1, COL2) 
AS SELECT 
TCALL4(F1) AS FUNRESULT,
F1 AS INPUTCOL 
FROM SQLBOOK.ATEST

The advantage of the view is that any program, whether RPG, COBOL, C or Java, accessing this view will ensure the invocation of the program in determining the value of COL1, which is an abstraction of the call function TCALL4 with the argument of the current value of the fields F1. This is what I call way-cool stuff.

This is a trivial example of what you can accomplish when combining stored procedures with user-defined functions. Think of what you could do with this capability. For example, say you have a program that calculates the economical order quantity for a part. You could code a user-defined function that calls the program, and then code a select statement that returns the part ID and how many you should order based on current conditions. Or, say you have a program that calculates the net-present value of an item. You could use this technique to ensure that the program is called and the calculation is current as of the state of your database whenever the data in a table is accessed. This is heap-big database magic, and a great tool to add to your arsenal of tricks.

— Howard

Howard F. Arner, Jr. is the Vice President of Client Server Development, Inc., in Jacksonville, Florida, and he is the author of “iSeries and AS/400 SQL at Work.” You can purchase his book at www.sqlthing.com/books or find out more about AS/400 SQL.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search