Calling a program from a UDF

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 by the program on the fly. I know I can do this in Oracle, can I do it with DB2 on the AS/400? -- Dave.

Dear Dave,

Yes, you can do this by using the magic of user-defined functions and stored procedures. 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. Lets do a simple example procedure and user-defined function to illustrate the points.

First, lets take the following example procedure. The procedure is one I defined in my book, iSeries and AS/400 SQL at Work, which is 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 is meant to identify 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 PSM, (Persistent Stored Modules), 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 SQLBOOK . SEQCTRL 
          WHERE SEQID = REQKEY ;
 OPEN C1 ;
 FETCH C1 INTO RETVAL ;
 CLOSE C1 ;
 UPDATE sqlbook.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);

So, 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:

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, lets 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 SQLBOOK / GETSEQ ( MYSTRING , R ) ; 
 RETURN R ; 
END

Some things to note about the function are that 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, note that the function takes it’s argument as a VARCHAR(10) and not a CHAR(10). This is so that you can 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 T4(‘PO) FROM <somtable>

Finally, note that 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.

Lets 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:

This is what is 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 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 the 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/your economical order quantity program results. 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-