In this article, I will look at using a simple RPG subprocedure that converts a numeric field containing a date to an actual date field.

The RPG Side

Figure 1 shows the DDS for a simple table containing Employee ID, Name, Date Joined, and Date of Birth. The Date Joined and Date of Birth fields are defined as packed numeric data types and contain dates in ISO (YYYYMMDD) format. If there is a need to perform any sort of "date math" (e.g., calculating a person's current age or the age they were when they joined the company), there is no problem converting the dates to proper date fields in an RPG program.

Figure 1: This is the DDS of a simple table.

To make the date conversion process even easier in RPG programs, I have written a ConvertToDate subprocedure, shown in Figure 2, which converts numeric fields to date fields. The ConvertToDate subprocedure accepts a single parameter (which should be a numeric field containing a date in ISO format) and returns either the ISO date equivalent of the passed parameter (if it contains a valid date) or today's date (if the passed parameter is not a valid date). The module containing the ConvertToDate subprocedure is in a service program named DATES.

Figure 2: The ConvertToDate subprocedure converts numeric fields to date fields.

You can use the ConvertToDate subprocedure anywhere you need to treat a numeric date field (with a date in ISO format) as a proper date field. For example, using the database described in Figure 1, you would use the following code to calculate the age of an employee when he or she joined the company:

The use of ConvertToDate in programs ensures that a proper date is always used and takes care of the unlikely instance that an invalid date might exist in the database. Of course, you could add extra processing to log any errors, halt processing, or return a different default date instead of today's date.

But how can you use the ConvertToDate subprocedure in SQL?

Define an SQL Function in iSeries Navigator

Just as RPG has BIFs, SQL has scalar functions. And just as RPG allows you to define your own BIFs by writing subprocedures, SQL allows you to define your own scalar functions, referred to as user-defined functions (UDFs).

If you have an urge to jump into the deep end, you can attempt to define an SQL function using a CREATE FUNCTION command in SQL, but, for the first attempt at least, it is a lot easier to use the wizard provided in iSeries Navigator. I will return to the CREATE FUNCTION command later in this article.

To define a function using iSeries Navigator, you must add the schema (library) that will contain the function to the list of schemas. Expand Databases -> DatabaseName -> Schemas. If the required schema is not in the list, right-click on Schemas and select the option to Select Schemas to Display and add the schema to the list.

To define a function, expand the required schema, right-click on Functions, and select New -> External from the pop-up menu, as shown in Figure 3. An external SQL function indicates that the SQL function is a "wrapper" to call an external program or subprocedure.

Figure 3: An "external" SQL function calls an external program or subprocedure. (Click images to enlarge.)

The wizard to define an external SQL function consists of three tabs: General, Parameters, and External Program. Let's look at the contents of the three tabs in detail, starting with the General tab, shown in Figure 4, which identifies the function and the value (or values) it returns and provides details relating to the SQL environment for the function.

Figure 4: The General tab defines a function.
 

• Function identifies the name of the function. This is the name you will use when you want to call the function in an SQL statement. In this instance, I've given the SQL function the same name as the subprocedure: ConvertToDate.
• Description is simply a description of the function.
• Data returned to invoking statement defines the parameter returned by the called program or subprocedure. This should correspond to the value returned by the ConvertToDate subprocedure, so you define it as a date. The returned data may be a single value (as in this case), or it could be a table returned by embedded SQL; you signify which by selecting either Single Value or Table.
• Can run in parallel is selected if the function does not change data used elsewhere in the calling SQL program or procedure.
• Program does not call outside of itself (No External Action) is selected if the called procedure does not call other functions or procedures outside of itself.
• Same result returned from successive calls with identical input (Deterministic) is selected if the same result is to be returned every time the procedure is called with the same values for the parameters. This will save the values that the procedure returns. Those values are automatically returned if the procedure is called with the same parameters, thereby saving system resources.
• Attempt to run in same thread as invoking statement (Not Fenced) is selected if the called function is to share resources (such as locks) with the invoking statement. As a general rule, called functions that modify data should be Fenced (i.e., this option is not selected); otherwise, Not Fenced is preferred.
• Data Access specifies the type of SQL actions that the function performs. The available options are Contains SQL (indicates that the function changes SQL data), No SQL (indicates that the function does not read, write, or change SQL data, as is the case with ConvertToDate), Reads SQL Data (indicates that the function reads SQL data but does not write or change it), and Modify SQL Data (indicates that the function modifies SQL data but does not read it).
• Specific Name specifies a unique name for the function or procedure within the schema. This is required only if other functions or procedures with the same name happen to be within the schema.

The Parameters tab, shown in Figure 5, identifies the parameters that are passed to the function.

Figure 5: The Parameters tab identifies the parameters passed to the function.
 

• Use the Insert button to insert a parameter, and then select (by clicking on them) and change the inserted values. Since the ConvertToDate subprocedure expects a numeric field of 8,0 as input, you define the parameter accordingly.
• Parameter style indicates the format in which data is passed to the called program or subprocedure. As well as the parameters you defined above, SQL also passes a number of "fixed" parameters to your function (such as the name of the function and an SQL state code). The format of these "fixed" parameters will differ, depending on whether you select SQL, DB2 SQL or Java, and each is described in detail in the Information Center (Database -> Programming -> SQL Programming -> Routines -> Using User Defined Function -> Writing UDFs as External Functions -> Passing Arguments from DB2 to External Functions—real easy to find <g>). But the use of these "fixed" parameters is required only if you need to communicate back to the calling statement (e.g., by setting the State code) or if you need to identify which SQL function issued the call (if you defined multiple functions that called the same program or subprocedure).
• Return null on null input indicates that if the parameter is null, then the program or subprocedure is not called and a null value is returned.

The External Program tab, shown in Figure 6, identifies the program or subprocedure that is called by the function. The function may be a program or a Java method. For the purposes of this article, I'll discuss only the program.

Figure 6: The External Program tab identifies the program or subprocedure called by the function.
 

• Program identifies the program or subprocedure to be called. Do not be misled by the online help, which tells you to enter a 10-character program name; you may specify a subprocedure by entering a service program name and the name of the subprocedure in parentheses. The name of the subprocedure is case-sensitive, so ensure that it corresponds to the exported name from the service program.
• Schema identifies the library containing the service program (or program).
• Language identifies the language in which the called program or subprocedure is written. Select RPGLE for ConvertToDate.
• Type returned by program is specified if the value returned by the program or subprocedure does not correspond to the return value specified on the General tab.

Now click on the OK button to create the ConvertToDate Function.

Create Function

Earlier in the article, I mentioned that you could define a function using the CREATE FUNCTION command in SQL.

To get an idea of the format of the SQL CREATE FUNCTION command, right-click on an existing SQL function in iSeries Navigator and select Generate SQL from the pop-up menu. Figure 7 shows the SQL CREATE FUNCTION command that would be used to create the ConvertToDate function defined earlier.

Figure 7: Define a function with CREATE FUNCTION.

When defining multiple functions that are somewhat similar, it is often easier to define the first function using iSeries Navigator and then define the remaining functions using the first function as a template; simply generate the SQL, change the relevnt definitions, and run the commands.

Now let's look at a couple of examples of using the newly defined function.

Using the Function with SELECT

You can use your own user-defined functions in almost any place you can use an SQL scalar function. For example, Figure 8 shows an SQL SELECT statement that uses the ConvertToDate function and the corresponding result of running the statement. The ConvertToDate function is used to represent the Birth and Joined columns as proper dates and used again in the calculation of the Age When Joined column.

Figure 8: Use the function in a SELECT statement.

(Note to self: Stop using your date of birth in these examples).

Using the Function in a View

You can make life even easier by defining a view that already does all the hard work. Figure 9 shows the definition of the view EMPLOYEEV1, which contains the definition of columns corresponding to those defined in the SELECT statement shown in Figure 8.

Figure 9: Use the function in the definition of a view.

Now simply selecting all columns from the view EMPLOYEEV1 generates the same result set shown back in Figure 8. Do you think your users might find this useful when they are using QUERY?

A Last Word...

The ability to use existing RPG programs and subprocedures as functions within SQL can offer some intriguing possibilities in that they can provide a means of using existing application logic to provide column values.

Yet another tool to add to your ever-expanding utility belt!