User-defined functions are flexible and easy to create. Add them to your development toolkit to create powerful new solutions for your company.

Many of you are quite familiar with DDS and the ability to define physical and logical database files. For instance, if we have a physical file MYFILE defined as shown below, we can create logical files that provide a subset of the physical file fields, map physical fields to logical fields through operators such as CONCAT and SST, map physical data types to other types, etc.:

                R MYRCD                                              

                  NAME          30          TEXT('Company Name')     

                  XCNTRY         1          TEXT('Xfer Century')     

                  XMONTH         2          TEXT('Xfer Month')       

                  XYEAR          2          TEXT('Xfer Year')        

                  XDAY           2          TEXT('Xfer Day')         

For example, the logical file MYFILELF shown below maps the alphanumeric field Name to a Unicode data type and concatenates the fields XCntry, XYear, XMonth, and XDay to a XDate logical field in CYYMMDD format.

                R MYRCD                     PFILE(MYFILE)            

                  NAME            G         CCSID(1200)             

                  XDATE                     CONCAT(XCNTRY XYEAR -   

                                                   XMONTH XDAY)        

We can, however, do much, much more by combining the power of database with a high-level language such as RPG.

Let's say we have a user who needs to query MYFILE to determine the number of days that have passed since the Xfer (Transfer) date for each company. For demonstration purposes, we'll load four records into MYFILE using a tool such as the Data File Utility (DFU). These are the record contents:

NAME                            XCNTRY  XMONTH  XYEAR  XDAY

Company 1                         1       06     08     01

Company 2                         1       05     08     15

Company 3                         1       05     08     20

Company 4                         0       11     07     02      

Using the IBM Query product 5722-QU1, we can calculate the number of days since the transfer date using the following

result fields and field selection criteria:

Selected files                                                      

   ID     File           Library       Member        Record Format   

   T01    MYFILE         VINING        *FIRST        MYRCD           

Result fields                                   

  Name        Expression                        

  TIMESTAMP   '0001-01-01-00.00.00.00000' ||    

              XCntry                            

  CENTURY     DIGITS(MICROSECOND(Timestamp)     

              + 19)                             

  DATE        CHAR(DATE(SUBSTR(Century,11,2) || 

              XYear || '-' || XMonth || '-' ||  

              XDay), ISO)                       

  DAYS        DAYS(CURRENT(DATE)) - DAYS(Date)

Ordering of selected fields

Field           Sort      Ascending/  Break  Field       

Name            Priority  Descending  Level  Text        

NAME                                         Company Name

DAYS                                                         

The result fields TimeStamp and Century are used to convert the alphanumeric XCntry field to a numeric field and to then add the value 19 so that we can determine the century (19xx, 20xx, etc). The result field Date is used to create a date data type field so that we can use the DAYS function to determine the number of days between the current date and the transfer date. The value of the result field Days is that number of days. For reporting purposes, we are displaying/printing the company name and the number of days between the current date and the transfer date.

 NAME                                   DAYS

Company 1                                15

Company 2                                32

Company 3                                27

Company 4                            36,752

There are other ways to calculate the number of days, but the approach used is fairly representative of what is needed on a V5R4 system. And while it's somewhat straightforward, I would certainly not want to explain this process to a typical end user. Most of the effort involved is in converting the four alphanumeric fields XCntry, XYear, XMonth, and XDay into the date field Date. It would simplify life greatly if we could provide the user with a view of MYFILE that simply provided a Date field already mapped to a date data type, and that's what we're going to do.

Within the database is the capability to create user-defined functions (UDFs). For the purpose of this article, UDFs are essentially the ability to have a user exit program called by the system when a particular field is used within a view. UDFs are considered part of the Structured Query Language (SQL), but it is not necessary to have the DB2 Query Manager and SQL Development Toolkit product 5722-ST1 installed on your system. The only products used in this article to create UDFs are the IBM i operating system and the ILE RPG compiler. For those of you who are SQL knowledgeable, even the RPG compiler is not needed. This article, however, is being published in RPG Developer and will demonstrate how to use UDFs and RPG without having to learn SQL (or at least not much SQL).

We are going to create a UDF (or program) that accepts four parameters--Xcntry, XMonth, XDay, and XYear--and returns a date data type field. To create the UDF, we will use the following CREATE FUNCTION statement:

create function vining/Get_Date                                 

  (char(1), char(2), char(2), char(2)) 

  returns date                                                   

  language rpgle                                                

  deterministic                                                 

  no sql                                                        

  not fenced;                                                    

For now, don't worry about how we'll run this statement. Let's just look at what the statement contains.

We are creating a function named Get_Date, which will be in the library VINING. The Get_Date function, an ILE RPG program we will create shortly, will be passed four parameters--a 1-byte character field followed by three 2-byte character fields. These parameters correspond to XCntry, XMonth, XDay, and XYear, respectively. I'll point out that we could have included the field names in this parameter list (XCntry char(1), XMonth char(2), etc.), but I'm trying to hold the information in the statement to a minimum. (For full documentation on the CREATE FUNCTION statement, see the SQL Reference manual.) Rather than a program, Get_Date could also be implemented as an entry point within a service program. We're using a program approach in order to minimize the lines of code and the number of compilation steps.

The Get_Date function will return one parameter, which is a date data type. The function is written in ILE RPG, is deterministic (meaning that it will always return the same result if called with the same input parameters), contains no imbedded SQL statements, and is not fenced (meaning that it can run in the same thread as the database caller of the function, which may not be the initial thread where the Query is running). The Not Fenced clause is not strictly necessary for this article, but, as it provides better performance than the alternative, Fenced, I chose to include it. Several other parameters for the CREATE FUNCTION statement are being defaulted, and you may want to look at the SQL Reference manual for details on these other options.

The system will call our Get_Date program with more than just the four parameters discussed above. The minimum parameters that will be passed are described below, where N is the number of input parameters defined (four in our example) on the CREATE FUNCTION statement.

Required Parameters:

Our Get_Date program will be called with 14 parameters. The first four will be the values for XCntry, XMonth, XDay, and XYear as each record is processed. The fifth parameter will be the output parameter where Get_Date will return the date value calculated from the input parameters.

The next four parameters (six through nine) will each be 2-byte signed integers representing null indicators for the four respective input parameters (XCntry, XMonth, XDay, and XYear). If the indicator value is -1, then the corresponding input parameter value is NULL. If the indicator value is 0, then the corresponding input parameter is not NULL. The next, tenth, parameter will be a 2-byte signed integer output allowing Get_Date to indicate whether or not the returned date field is NULL. As with the input parameter indicators, -1 indicates that the result is NULL.

The next parameter, Status, can be used by the Get_Date function to pass error-related information back to the i5/OS database (and from there to the Query). A value of all zeroes, which is the default value, indicates that no error was encountered by Get_Date. Several possible error values can be returned and can be found here. The valid error values are those starting with either '01' for warnings or '38' for exceptions. Qualified function name, the 12th parameter, will be the name of the function (VINING.GET_DATE for our example), Specific function name, the next parameter, will be GET_DATE, and the last parameter, Message text, is an output parameter where Get_Date can provide textual information related to any error the program reported in the Status parameter.

This is the ILE RPG source for Get_Date:

dGet_Date         pr                  extpgm('GET_DATE')     

d Century                        1                           

d Month                          2                           

d Day                            2                           

d Year                           2                           

d Date                            d                          

d CenturyInd                     5i 0                        

d MonthInd                       5i 0                        

d DayInd                         5i 0                        

d YearInd                        5i 0                        

d DateInd                        5i 0                        

d SQLState                       5                            

d FuncName                     139    varying                

d SpecificName                 128    varying                

d MsgText                       70    varying                

dGet_Date         pi                                      

d Century                        1                        

d Month                          2                        

d Day                            2                         

d Year                           2                        

d Date                            d                       

d CenturyInd                     5i 0                     

d MonthInd                       5i 0                     

d DayInd                         5i 0                     

d YearInd                        5i 0                     

d DateInd                        5i 0                     

d SQLState                       5                        

d FuncName                     139    varying             

d SpecificName                 128    varying             

d MsgText                       70    varying             

 /free                                                     

  Date = %date((Century + Month + Day + Year) :*CMDY0);

  *inlr = *on;                                         

  return;                                               

 /end-free                                             

As you can see, there isn't much to the program! Other than the parameter definitions, we have essentially only the one calculation using the %date built-in function of ILE RPG. The program can be created with CRTBNDRPG GET_DATE.

Now let's see how we can use Get_Date to simplify the Query we looked at earlier. First, we need to create a view (similar conceptually to a DDS defined logical file) that maps the physical file MYFILE to a view that includes our "virtual" date field. We do this with the SQL CREATE VIEW statement.

create view vining/myfiledate as select               

  Name, Get_Date(XCntry, XMonth, XDay, XYear) as Date 

  from myfile;                           

This statement creates the view MYFILEDATE in library VINING. The view includes two fields: Name and Date. Name is simply the Name field from the physical file MYFILE. Date, on the other hand, is a field returned by the Get_Date UDF with Get_Date being passed the parameters XCntry, XMonth, XDay, and XYear from MYFILE.

To use this view, from Query or even another RPG program, we use MYFILEDATE as the name of the file. To now calculate the number of days since the transfer date to the current date, we can modify our earlier Query to simply say this:

Selected files                                                     

  ID     File           Library       Member        Record Format  

  T01    MYFILEDATE     VINING        *FIRST        MYFILEDATE     

Result fields                                                      

  Name        Expression                         Column Heading    

  DAYS        days(current(date)) - days(date)                     

Ordering of selected fields                                        

  Field           Sort      Ascending/  Break  Field               

  Name            Priority  Descending  Level  Text                

  NAME                                         Company Name        

  DAYS                                                                 

And running the Query, we get this:

NAME                                   DAYS  

Company 1                                15  

Company 2                                32  

Company 3                                27  

Company 4                            36,752      

Quite a bit easier! We have basically expanded our database to now include a date data type field that is based on the physical fields found in the existing MYFILE database.

Now we get to the question "Just what is required to run the SQL statements CREATE FUNCTION and CREATE VIEW?" Fortunately, it's very simple. Create a source physical file, enter the two CREATE statements into a source member, and then run the RUNSQLSTM CL command, which will process the SQL statements. For demonstration purposes, we can use the following commands:

CRTSRCPF QSQLSRC

STRSEU QSQLSRC MYFIRSTUDF TXT

While in SEU (or a similar editor), enter the following two statements. Be careful to not miss the closing semicolon (;) for each statement:

            create function vining/Get_Date                        

              (char(1), char(2), char(2), char(2))                 

              returns date                                          

              language rpgle                                       

  deterministic                                        

  no sql                                               

  not fenced;                                           

create view vining/myfiledate as select                

  name, get_date(xcntry, xmonth, xday, xyear) as date  

  from myfile;

Exit from your editor and then run this command:

RUNSQLSTM SRCFILE(QSQLSRC) SRCMBR(MYFIRSTUDF) COMMIT(*NONE)  

If you previously entered test data into MYFILE, you are now ready to use Query and easily determine the number of days that have passed since the transfer date.

If you want to play with other versions of the Get_Date function or MYFILEDATA view, you can use the SQL DROP statement to delete the UDF and/or view. The statements would be either or both of the two below.

DROP FUNCTION GET_DATE;

DROP VIEW MYFILEDATA;

These statements can be run by again placing the statements into a source member of QSQLSRC and running the RUNSQLSTM command against that member.

The Get_Date UDF provides a very productive extension to our database. You might notice that nowhere in the CREATE FUNCTION statement or the Get_Date RPG program do we actually reference MYFILE. The Get_Date UDF can be used anytime we want to create a virtual date data type field from a physical file and the date is stored as separate fields representing the century, year, month, and day. If some of our physical files store date-related information in formats such as numeric, combined YYMMDD, etc., we can also create UDFs to cater to these other file formats. We can build these UDFs once and then reuse them many times.

Essentially, if we can calculate a value within an RPG program, then with UDFs and views we can have that value appear as if it actually exists within the database. What would be required, for instance, to eliminate the one remaining result field Days in our Query? The answer is a new UDF Get_Diff and a new view MYFILEDIFF.

This is the CREATE FUNCTION statement to create Get_Diff:

create function vining/Get_Diff        

  (char(1), char(2), char(2), char(2)) 

  returns integer                      

  language rpgle                       

  deterministic                         

  no sql                               

  not fenced;      

Here's the RPG program Get_Diff:

dGet_Diff         pr                  extpgm('GET_DIFF')        

d Century                        1                              

d Month                          2                              

d Day                            2                              

d Year                           2                              

d Days                          10i 0                           

d CenturyInd                     5i 0                           

d MonthInd                       5i 0                           

d DayInd                         5i 0                           

d YearInd                        5i 0                           

d DaysInd                        5i 0                           

d SQLState                       5                              

d FuncName                     139    varying                   

d SpecificName                 128    varying                    

d MsgText                       70    varying                   

dGet_Diff         pi                                       

d Century                        1                          

d Month                          2                         

d Day                            2                         

d Year                           2                         

d Days                          10i 0                      

d CenturyInd                     5i 0                      

d MonthInd                       5i 0                      

d DayInd                         5i 0                      

d YearInd                        5i 0                      

d DaysInd                        5i 0                      

d SQLState                       5                         

d FuncName                     139    varying              

d SpecificName                 128    varying              

d MsgText                       70    varying              

dToday            s               d   inz(*JOB)            

 /free                                                            

  Days = %diff( Today                                            

               :(%date((Century + Month + Day + Year) :*CMDY0))  

               :*DAYS);                                           

  *inlr = *on;                                                   

  return;                                                        

 /end-free                     

Create the view MYFILEDIFF:

create view vining/myfilediff as select                     

  name, get_diff(xcntry, xmonth, xday, xyear) as days       

  from myfile;                                               

And remove the calculation from our Query:

Selected files                                                          

  ID     File           Library       Member        Record Format       

  T01    MYFILEDIFF     VINING        *FIRST        MYFILEDIFF        

Ordering of selected fields                                             

  Field           Sort      Ascending/  Break  Field                    

  Name            Priority  Descending  Level  Text                     

  NAME                                         Company Name             

  DAYS                                              

This leaves us with the same output as before, a lot less work for the end user, and elimination of the exposure that some end user might miscalculate the number of days between the current date and the transfer date.                       

NAME                                     DAYS  

Company 1                                  15  

Company 2                                  32  

Company 3                                  27  

Company 4                              36,752                       

Before we leave, there are two items I would like to point out about Get_Diff. First, the Get_Diff function is defined as returning an integer data type rather than a date data type. As we're returning the number of days, this makes sense but is a change that might be easily overlooked. Second, the Get_Diff RPG program initializes the field Today to *JOB. This is to avoid the problem of the system date rolling over to the next day if the Query happens to be running at midnight. Using *JOB, rather than *SYS, prevents the Query output from changing mid-report and leaves the function as being deterministic.

And one item about views in general: A view can be used much like a logical file, but views do have limitations. One limitation has to do with keyed access. It is impossible to associate a key (or index) with a view in the same manner as you can with a logical file. In the case of Query, this is not a major concern as Query tends to read an entire file and allows you to specify sort criteria for the output. In the case of an RPG application using standard RPG file management and reading a view, this means you can only read the view sequentially (which may or may not be arrival sequence). This may be suitable for some batch jobs but generally precludes the use of views for most interactive applications (no CHAIN by key, for instance). Using SQL, you can have an index associated with the use of a view. This consideration is not so much a limitation of UDFs as it is a consequence of how UDFs are accessed using RPG standard data management.

The IBM i has long been recognized as having a strong and productive database capability. While UDFs are not new to the i database, it's been my experience that quite a few RPG developers are unaware of the flexibility available with UDFs. Or if they are aware of UDFs, many are unaware that UDFs are available within the operating system and do not require that the SQL Toolkit be installed. As you have hopefully seen, UDFs are highly flexible, aren't difficult to create, and can be a great addition to your development toolkit when providing new solutions to your company. I hope you keep this capability in mind when working on future projects.