Coding SQL Functions in OS/400 V5R1

by Howard F. Arner Jr.

A while back, a reader sent me a query on how to convert date information from his legacy package into SQL date data types. A date was stored in his system as four DECIMAL(2,0) fields in a physical file. The fields correspond to the month, day, century, and year of the date. The century field contains a 19 for the 1900s and a 20 for years 2000 and above. The year fields contain a number representing the year, like 1 for 2001 or 99 for 1999. So the following SQL statement would work to convert these fields into an SQL date (assuming that the fields are named MO, DY, CEN, and YR):

SELECT DATE( TRIM(CHAR(MO))||'/'|| 
    TRIM(CHAR(DY))||'/'|| 
    TRIM(CHAR((CEN*100)+YR))) AS MYDATE 
FROM 

By using this simple formula, you could convert the legacy fields into a DB2 DATE data type and then be able to use all of the built-in date functions, like DAYOFWEEK and WEEK. Another advantage of the DATE data type is that there is native support for date math built into SQL, so you could use the resulting data in expressions to calculate dates or time lapsed since the event in the record.

Now, the problem with the SQL statement is that it is boring to type and looks very confusing. Also, you might use it a lot in your SQL statements, as you may need to convert several legacy fields and then compare them. One solution is to make a user-defined function that converts legacy dates into SQL dates, which we will now proceed to do.

Here is a first-run coding of the function. Make sure you change SQLTHING in the SQL to a library on your system:

CREATE FUNCTION SQLTHING.MINCRON_DATE
	(CEN DECIMAL(2,0),
	 YR DECIMAL(2,0),
	 MO DECIMAL(2,0),
	 DY DECIMAL(2,0))
	 RETURNS DATE
	 LANGUAGE SQL
	 BEGIN
	 DECLARE F_OUTPUT DATE;
	 SET F_OUTPUT = 
DATE(TRIM(CHAR(MO))||'/'||TRIM(CHAR(DY))||'/'||TRIM(CHAR((CEN*100)+YR)));
	 RETURN F_OUTPUT;
	 END

To create the function, you can use Operations Navigator Run SQL Scripts, the interactive SQL interpreter on the AS/400 (STRSQL), or my product, SQLThing (available from www.sqlthing.com). To use the Run SQL Scripts function from OpsNav, you'll need V5R1 of Client Access Express installed on your PC. Right-click on the OpsNav tree item Databases and select Run SQL Scripts. Once you've chosen a method of running your script, you simply log on to the AS/400, execute the above SQL, and--viola!--you have a user-defined function on your AS/400 (assuming that you have V5R1 and the SQL Development Kit installed on your AS/400).

Anatomy of the Function

Now let's examine each line of the above function to see what the PSM language is doing.

First, the function starts with the CREATE FUNCTION x keyword. This lets SQL know that you are about to create a user-defined function called x. In our case, x is a function called MINCRON_DATE, which lives in the library SQLTHING. Note that user-defined function names can be quite long; if they are over 10 characters, the AS/400 will generate a system name for the created program consisting of only 10 characters.

The next line starts with an open parenthesis, which indicates the start of the arguments to this function. When naming arguments to the function, you specify the argument name and data type. (In this function, I have four arguments, called CEN, YR, MO, and DY.) You then specify the data type for each. The close parenthesis, after the declaration of the DY argument, indicates the end of my argument declarations.

The RETURN DATE line indicates that the function will be returning a value of the data type DATE. Any SQL data type--or user-defined data type, for that matter--may be used for the return of data to the calling program. In this case, I want the program to calculate the date represented by the legacy fields and return an SQL date, so I specified DATE as the returned data type.

Language SQL indicates that this will be an SQL function. As you may know, you can create functions in any supported language on the AS/400--such as C, RPG, or COBOL--and you can also create sourced functions. A sourced function is a function that is built upon an existing function already defined in DB2. You might use this feature to do something like redefining the DAYOFWEEK function to return character representations of the day of the week, instead of numbers. (I'll get to that in another article!)

Finally, we get to the last keyword before the guts of the function, BEGIN. BEGIN lets the SQL interpreter know that code for the user-defined function lies from this point on.

The next line of the function declares a variable called F_OUTPUT, of the data type DATE. I will be using this variable to return the calculated date value to the calling program. Note that the statement ends in a semicolon. The semicolon lets the SQL interpreter know that it has reached the end of a statement. In this manner, I could split complex statements between multiple lines of code, if I were so inclined. Each statement must be ended in a semicolon.

The next line of the function uses the SET statement to place the value of the date conversion expression into the F_OUTPUT variable.

The last line of the function (before the END statement) indicates that the function should return the value of F_OUTPUT.

Testing the Function

The function is quite useful, as long as the stored data indicates a valid date. Let's create a table and see what happens when the function is executed. Execute the following SQL statements to create the test table and add test data:

CREATE TABLE sqlthing.testdata
	(cen DECIMAL(2,0),
	 yr DECIMAL(2,0),
	 mo DECIMAL(2,0),
	 da DECIMAL(2,0),
	 good CHAR(1));
	 	
INSERT INTO sqlthing.testdata VALUES (20,1,1,1,'Y');
INSERT INTO sqlthing.testdata VALUES (20,1,1,10,'Y');

Next, execute the following statement to ensure that you have the SQL FUNCTION PATH set correctly. The SQL FUNCTION PATH is used by SQL to find user-defined functions. If you do not set the path, you will have to qualify your user-defined functions with the name of the library where they exist.

 
SET CURRENT FUNCTION PATH=SQLTHING;  

The above statement tells the SQL interpreter to look in the SQLTHING library for any user-defined functions used in an SQL statement. Now execute the following SQL statement:

SELECT MINCRON_DATE(cen,yr,mo,da),good FROM sqlthing.testdata;

This statement should yield two rows, which contain a date, the combination of the legacy fields from above, and the good flag. Next, let's add another record and see what happens to the user-defined function. Execute the following SQL statement:

INSERT INTO sqlthing.testdata VALUES (20,1,20,1,'N');

Note that 20 in the MO column is going to result in an invalid date. Try selecting the data from the table with the previous SQL Select statement and notice that SQL does not return any results. This is because of the invalid date now contained in the TESTDATA table. If you were to look at the job log (of the job that executed the select statement), you would find that an SQL System Error has occurred, which is why no data was returned. To get around the error, I need to add some exception-handling abilities to the user-defined function.

Adding Error-Handling

Error-handling can be accomplished in many ways using the PSM language. Probably the easiest is to declare a HANDLER for the error you are attempting to trap. A handler is a routine or piece of code that is invoked when a condition is met. The condition can be any SQL State that you may wish to trap. The following rewrite of the procedure has an added error handler, which allows the procedure to return a null value when a legacy date would result in an invalid date:

CREATE FUNCTION SQLTHING.MINCRON_DATE
	(CEN DECIMAL(2,0),
	 YR DECIMAL(2,0),
	 MO DECIMAL(2,0),
	 DY DECIMAL(2,0))
	 RETURNS DATE
	 LANGUAGE SQL
	 BEGIN
	 DECLARE F_OUTPUT DATE;
	 DECLARE EXIT HANDLER FOR SQLEXCEPTION
	 	 RETURN NULL;
	 SET F_OUTPUT = DATE( TRIM(CHAR(MO))||'/'|| TRIM(CHAR(DY))||'/'||      
TRIM(CHAR((CEN*100)+YR)));
     RETURN F_OUTPUT;
	 END

Note that the main difference is the addition of the DECLARE EXIT HANDLER line. DECLARE EXIT HANDLER tells SQL that you are declaring that a handler should be handled as an EXIT handler. Handlers can be tagged as CONTINUE, EXIT, or UNDO. An UNDO handler will cause a rollback of any statements or operations executed by the function. A CONTINUE handler will cause the system to ignore the condition and continue processing with the next statement. An EXIT handler causes the program to end the processing of the current segment after execution of the code in the handler body. In this case, I am declaring an EXIT handler for the condition SQLEXCEPTION. SQLEXCEPTION is a keyword that basically is a catch all for any SQL error that may happen during evaluation of my function. The next part of the handler indicates the operation that should be performed if this handler is invoked. In this case, I want to return a NULL value to the calling program.

So, basically, my handler tells the AS/400 that if it encounters an error during execution of the function, it should stop processing and return a null value. This is about the simplest type of error recovery that you can encode in a program.

Here is another example of the same function, this time using a CONTINUE handler to cause the program to set an error indicator. An IF condition then tests the value of the variable and returns the calculated date if no error has occurred, or returns NULL if an error did occur. This example just illustrates that there is always more than one way to skin the proverbial cat when programming with the PSM language.

CREATE FUNCTION SQLTHING.MINCRON_DATE
	(CEN DECIMAL(2,0),
	 YR DECIMAL(2,0),
	 MO DECIMAL(2,0),
	 DY DECIMAL(2,0))
	 RETURNS DATE
	 LANGUAGE SQL
	 BEGIN
	 DECLARE F_OUTPUT DATE;
	 DECLARE F_TEST INTEGER;
	 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	 	 SET F_TEST=1;
	 SET F_TEST=0;
	 SET F_OUTPUT = DATE( TRIM(CHAR(MO))||'/'|| TRIM(CHAR(DY))||'/'|| 
TRIM(CHAR((CEN*100)+YR)));
	 IF F_TEST=0 THEN
	 	RETURN F_OUTPUT;
	 ELSE
	 	RETURN NULL;
	 END IF;
	 END

How Does PSM Work?

Finally, I'll tell you a little bit about how PSM is implemented on the AS/400. When you issue the CREATE FUNCTION statement, the AS/400 interprets your function into a C program with embedded SQL that it places in the QTEMP library of your active job. The AS/400 then uses the CRTSQLCI (Create Structure Query Language ILE C) program to compile the generated C code into a service program object. The AS/400 then uses CRTSRVPGM to turn the object into a service program in the named library. Finally, the AS/400 makes entries into the system catalogue tables SYSROUTINES and SYSPARMS in the QSYS2 library. The SYSROUTINES table identifies all of the stored procedures and user-defined functions on your AS/400. The SYSPARMS entries identify each parameter to the function and the return parameter information.

Happy Coding!

This has been a very simple introduction to using the PSM language for defining user-defined functions. If there is an interest, I will elaborate on PSM and user-defined functions in a future article, so please send me an e-mail at harner@itjungle.com if you are interested in this topic. Otherwise, see the iSeries SQL Reference for more information on user-defined functions.

Howard F. Arner Jr. is vice president of Client Server Development, in Jacksonville, Florida, and author of iSeries and AS/400 SQL at Work. He consults with many companies and develops books and products targeted to the AS/400 community. You can reach Howard at harner@itjungle.com.