Creating User Defined Functions to make my SQL statements easier

The old ERP application I have to work with holds the "dates" in its files as seven long packed numeric fields, called *CYMD format. When I extract data from these files using SQL I always want to convert these "dates" to real dates, which I can the use for calculations, etc. I am tired of using the following SQL code to convert these fields to dates:

SELECT DATE(TIMESTAMP_FORMAT(CHAR(CYMD_DATE + 19000000),'YYYYMMDD')) FROM TESTFILE

And if the value in the numeric field is not compatible with a date the Select statement fails, and I have to fix the data with a best guess, and try again. It would just be so much easier if I could make the equivalent of a subprocedure to do this for me, then all I would have to do is call the "subprocedure" with the value to convert and it would return a valid date.

Fortunately DB2 for i offers me this functionality, which is called a "User Defined Function", or UDF for short. I can create an UDF in SQL or other languages, including RPG, to, in this example, convert the number to real date, and back again. In this article I am going to focus on two examples:

1. FromCYMD – Converts the packed numeric into a date
2. ToCYMD – Converts a date to the packed numeric

SELECT FROMCYMD(STRDTE) FROM TESTFILE SELECT TOCYMD(START_DATE) FROM QTEMP.TABLE1

I am going to write the code for my UDFs in RPG as I prefer the ways it handles dates and it allows me to handle any errors easily. I am going to write the RPG code as subprocedures, which I will bind into a service program. I could write this as two programs, but I just find it easier to do as subprocedures. I can also have both of the procedures for the FromCYMD and ToCYMD in the same source member, which, in my opinion, makes them easier to find.

Let me start with the RPG code for FromCYMD:

01 ctl-opt nomain ; 02 dcl-proc FromCYMD export ; 03 dcl-pi FromCYMD date ; 04 inCymd packed(7) const ; 05 end-pi ; 06 dcl-s outDate date ; 07 test(de) *cymd inCymd ; 08 if (%error) ; 09 outDate = *loval ; 10 else ; 11 outDate = %date(inCymd:*cymd) ; 12 endif ; 13 return outDate ; 14 end-proc ;

I go into how to code subprocedures in more detail than I am here in the post More about subprocedures, therefore, I am only going to give a high level description of how to code a subprocedure, and concentrate on the code within it.

I do not want either of these subprocedures to produce an error. So rather than erroring when, in FromCYMD, an invalid numeric representation of a date is passed, or, in ToCYMD, a date is passed that is outside of the range handled by the *CYMD date type, my code will put a "error" value into the returned variable. Later when I use the data I will know that the value passed could not be successfully converted.

Line 1: This helps the compile determine that this source member contains only subprocedures, as there is not a main procedure.

Line 2: The DCL_PROC indicates the start of the procedure FromCYMD. It is flagged for export so it can be used by external programs and UDFs.

Lines 3 – 5: This is the subprocedure interface definition. On line 3 I have the subprocedure name followed by the word DATE. This indicates that this subprocedure returns a date to whatever called it. Line 4 is the one parameter that is passed to this subprocedure, a 7 long packed numeric variable.

line 6: I need to define a date field to receive the data from the input field.

Line 7: Before I try and convert the incoming number to a date I need to make sure that this number is a valid *CYMD formatted date.

Lines 8 and 9: If the number is not valid I am going to move *LOVAL to the date field. This will move the date January 1 0001, 01/01/0001, to the variable outDate.

Line 11: If the value in the passed parameter is a valid representation of a date I am converting it to a date using the %DATE built in function.

Line 13: The RETURN followed by the date field returns its value to whatever called this subprocedure.

Line 14: End of the FromCYMD subprocedure.

Next comes the code for the subprocedure to convert a date to a 7 long packed numeric. As I have this in the same member as the previous subprocedure the line numbers continue from the example code above.

15 dcl-proc ToCYMD export ; 16 dcl-pi ToCYMD packed(7) ; 17 inDate date const ; 18 end-pi ; 19 dcl-s outCymd packed(7) ; 20 monitor ; 21 outCymd = %dec(inDate:*cymd) ; 22 on-error ; 23 outCymd = -1 ; 24 endmon ; 25 return outCymd ; 26 end-proc ;

Line 15: Start of the subprocedure ToCYMD.

Lines 16 – 18: As this subprocedure is the opposite of the previous, this one returns a seven long packed value, line 16, and receives a date, line 17.

Line 19: I need to have a variable defined to hold the packed value I have converted.

Line 20: I am using a MONITOR to capture any error that may happen when I convert the date to the packed numeric. This will only happen when the date is outside the range of dates supported by the *CYMD format, January 1 1900 - December 31 2899. If you are unfamiliar with how this operation code works see MONITOR for errors in RPG.

Line 21: Using the Convert to decimal built in function, %DEC, I convert the date to a number, and I do need to specify what date format I want the decimal to be given to the result variable.

Line 22: If the number used in the %DEC is outside of the range of allowed *CYMD dates an error occurs. By using the ON-ERROR stops the error from causing the program to fail.

Line 23: This line is only executed if an error happened. I am moving -1 to the numeric field so I will later know that the date could not be converted to *CYMD.

Line 25: The value in outCymd is returned to whatever called this subprocedure.

Line 26: This is the end of the ToCYMD subprocedure.

This source member, CVTCYMD, is compiled into a module using the CRTRPGMOD command, or option 15 in PDM. I then us the Create Service Program command, CRTSRVPGM to create a service program of the same name.

CRTRPGMOD MODULE(MYLIB/CVTCYMD) SRCFILE(MYLIB/DEVSRC) CRTSRVPGM SRVPGM(MYLIB/CVTCYMD) EXPORT(*ALL)

Now I need to define my two SQL functions. I could do this using STRSQL, in a CL program using the RUNSQL, or in a SQLRPGLE program. But I prefer to put the code into a source member, and then compile with the Run SQL Statement command, RUNSQLSTM. I have included the definitions of both of these functions in the same source member:

01 CREATE OR REPLACE FUNCTION MYLIB.FROMCYMD (inCymd DECIMAL(7,0)) 02 RETURNS DATE 03 LANGUAGE RPGLE 04 DETERMINISTIC 05 NO SQL 06 EXTERNAL NAME 'MYLIB/CVTCYMD(FROMCYMD)' 07 PARAMETER STYLE GENERAL 08 PROGRAM TYPE SUB ; 09 CREATE OR REPLACE FUNCTION MYLIB.TOCYMD (inDate DATE) 10 RETURNS DECIMAL(7,0) 11 LANGUAGE RPGLE 12 DETERMINISTIC 13 NO SQL 14 EXTERNAL NAME 'MYLIB/CVTCYMD(TOCYMD)' 15 PARAMETER STYLE GENERAL 16 PROGRAM TYPE SUB ;

line 1 and 9: As anyone familiar with SQL can guess to create a function I use the CREATE FUNCTION. As I am working on an IBM i with 7.3 I can use CREATE OR REPLACE, those of you on some older release may find that does not work. You will have to DROP FUNCTION before having the CREATE FUNCTION.

Line 1: My function is called FROMCYMD, it is located in MYLIB, and I need to define the parameter it is passed, inCymd.

Line 2: This function returned a date type variable.

Line 3: This function is written in RPGLE.

Line 4: DETERMINISTIC means that if it called with the same incoming parameter more than once it somehow "remembers" the value to return. The opposite, NOT DETERMINISTIC, is when the code within the function needs to be executed every time. As a value of date does not change I am using DETERMINISTIC.

Line 5: The code within the function does not use any SQL.

Line 6: This is where I define where the code for the function is to be found. This is given in the format: library/service program(subprocedure).

Line 7: This is the simplest way to pass parameters in and out of the function. Only the defined incoming parameter, line 1, and the outgoing parameter, line 2, are returned to the SQL statement. I will go into the alternative parameter style later in this post.

Line 8: As the function uses a subprocedure I need to give the program type of SUB.

The code for the toCYMD is basically the same, except for:

Line 9: Different function name and incoming parameter, this time it is a date.

Line 10: This function returns a seven long packed variable.

Line 14: And of course it uses a different subprocedure.

Now I can use these two functions in my SQL statements:

SELECT MYLIB.FROMCYMD(STRDTE),STRDTE FROM MYLIB.TESTFILE FROMCYMD ( STRDTE ) STRDTE 10/01/2008 1081001 04/17/2010 1100417 01/01/0001 0 01/01/0001 1160231 01/01/0001 9999999

If the function is not in my library list I can qualify it with the library name.

Notice how the third, fourth, and fifth numbers returned 01/01/0001 as they are not valid dates.

If I had output the data from the above Select into a table and then used the ToCYMD function I would get:

SELECT TOCYMD(START_DATE),START_DATE FROM QTEMP.TABLE TOCYMD ( STR_DATE ) STR_DATE 1081001 10/01/2008 1100417 04/17/2010 1- 01/01/0001 1- 01/01/0001 1- 01/01/0001

The third, fourth, and fifth rows are all -1 as the date 01/01/0001 is outside the range of acceptable dates for the *CYMD format.

This works great for me, as I do not want an error message if the number is not a date or vice versa. But what happens if I want an error that is meaningful. I can do that by using the PARAMETER STYLE SQL in the CREATE FUNCTION statement:

01 CREATE OR REPLACE FUNCTION MYLIB.FROMCYMD2 (inCymd DECIMAL(7,0)) 02 RETURNS DATE 03 LANGUAGE RPGLE 04 DETERMINISTIC 05 NO SQL 06 EXTERNAL NAME 'MYLIB/CVTCYMD2(FROMCYMD2)' 07 PARAMETER STYLE SQL 08 PROGRAM TYPE SUB ;

This function is very similar to FromCymd, except…

Line 1: This function is called FROMCYMD2, and expects to be passed a seven long packed numeric.

Line 6: This uses a different service program and subprocedure.

Line7: This is the significant difference. This time I cam using the SQL parameter. What does that mean? We have to look at the RPG to understand.

01 ctl-opt nomain ; 02 dcl-proc FromCYMD2 export ; 03 dcl-pi FromCYMD2 ; 04 inCymd packed(7) const ; 05 outDate date ; 06 NullinCymd int(5) const ; 07 NulloutDate int(5) ; 08 SQLState char(5) ; 09 F1 varchar(517) ; 10 F2 varchar(128) ; 11 SQLMessage varchar(70) ; 12 end-pi ; 13 test(de) *cymd inCymd ; 14 if (%error) ; 15 outDate = *loval ; 16 SQLState = 'U0000' ; 17 SQLMessage = 'CYMD is not a valid date' ; 18 else ; 19 outDate = %date(inCymd:*cymd) ; 20 endif ; 21 return ; 22 end-proc ;

By using the SQL parameters I have a parameter interface that includes both the incoming and returned variables.

Line 4: This is the incoming packed numeric.

Line 5: This is the returned date.

line 6: Null indicator for the incoming packed numeric.

Line 7: Null indicator for the returned date.

Line 8: This is the SQL State value I can return to whatever called this function.

Lines 9 and 10: I just ignore these. These are the "Fully qualified function name" and the "Specific name", neither of which I care about.

Line 11: This where I can put the text for the SQL state.

Lines 14 – 17: Now when the date validation fails I can move my own value to the SQL state field, line 16, and my own message to the message variable, line 17.

Line 21: Now when this subprocedure ends I want to just RETURN and all of the parameters in the subprocedure interface are returned.

So what happens now when I use a Select statement with an invalid "date".

First using the STRSQL interface:

SELECT FROMCYMD2(0) FROM TESTFILE Query cannot be run. See lower level messages.

When I look in the job log I do see an error message with my SQL state and message within it:

Additional Message Information Message ID . . . . . . : CPF503E Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Date sent . . . . . . : DD/DD/DD Time sent . . . . . . : TT:TT:TT Message . . . . : User-defined function error on member TESTFILE. Cause . . . . . : An error occurred while invoking user-defined function FROMCYMD2 in library MYLIB. The error occurred while invoking the associated external program or service program CVTCYMD2 in library MYLIB, program entry point or external name FROMCYMD2, specific name FROMCYMD2. The error occurred on member TESTFILE file TESTFILE in library MYLIB. The error code is 1. The error codes and their meanings follow: 1 -- The external program or service program returned SQLSTATE U0000. The text message returned from the program is: CYMD is not a valid date .

I can use this function in a SQLRPGLE program:

01 dcl-s wkDate date ; 02 exec sql SELECT FROMCYMD2(STRDTE) INTO :wkDate FROM WA046P ; 03 dump(a) ;

If I look in the program dump, produced by the DUMP(A), on line 3, I can see SQL state and message:

SQLCA DS SQLERM CHAR(70) ' *N *N FROMCYMD2 CVTCYMD2 MYLIB CYMD is not a valid date ' SQLSTATE CHAR(5) 'U0000' SQLSTT CHAR(5) 'U0000'

 

I have covered making making functions using SQL in a separate post, as this page is too long as it is.

 

You can learn more about the CREATE FUNCTION statement from the IBM website here.

 

This article was written for IBM i 7.3, and should work for earlier releases too.