Unless you live in a cave, I'm sure you've noticed that SQL is becoming more popular in iSeries shops. IBM provides a large number of built-in functions for use with SQL. These functions provide the ability to translate, convert, or in some way operate against the raw data from the files.

A function is a prewritten set of instructions that modify the data being processed, such as SUM(), which totals a column, and CHAR(), which converts certain values to character data. Sometimes, however, you come up with a need that IBM didn't cover. For example, perhaps you need a function that works as a counter, returning 1 for the first row, 2 for the second, and so on. Such a function would enable you to create sequence numbers within an SQL statement. IBM provides no such function to does this, but it does provide an example of how to write one. Unfortunately, the example is written in C. Not being a C programmer, I wanted to figure out how to do the same thing in RPG IV. I thought I'd share the code and what I learned along the way.

How Do I Create a Function?

SQL provides three ways to create functions--written in SQL; written in other host languages, such as RPG IV or COBOL; or simply modeled on an existing "source" function. Operations Navigator provides a wizard to assist in creating functions, but all you need is the ability to execute an SQL statement.

In this article, I'll show you how to create an SQL function that calls RPG IV subprocedures. The goal is to recreate one of IBM's example functions that is written in C (see the manual DB2 Universal Database for iSeries SQL Programming Concepts at the iSeries Information Center). The function is called "Counter," and it receives two parameters: the starting value of the counter and the amount by which to increment the counter for each row in the result table.

Create (or rather, "register") this function on your iSeries (see Figure 1).

Figure 1: Use this CREATE FUNCTION SQL statement to register Counter.

This type of function is referred to as an "external" function, because the code being run is written not in SQL, but in some other language of the host system. When creating an external function, the SQL CREATE FUNCTION statement does not create a new object on the system. It simply adds an entry to a control table (QSYS2/SYSFUNCS). In this case, the executable code is stored within an RPG IV service program. Therefore, this function cannot be used until the RPG IV program is created.

The CREATE FUNCTION statement contains a range of parameters that are not at all intuitive! These parameters can make using functions challenging until you understand what they do. Let's break the statement down and see what each one does.

• CREATE FUNCTION mylib/COUNTER(int,int) defines the name of the new function as "Counter" and shows that it receives two integers as input parameters.
• RETURNS INT indicates that the function returns an integer value.
• EXTERNAL NAME 'MYLIB/COUNTER(COUNTER1)' identifies the service program that contains the executable code. The subprocedure being called as a function is named within the parentheses.
• LANGUAGE RPGLE specifies that the program is written in RPG IV.
• PARAMETER STYLE DB2SQL determines what set of parameters to pass to the subprocedure.
• NO SQL simply indicates that the subprocedure does not contain additional SQL code.
• NOT DETERMINISTIC indicates that the function can return different results each time it is run, even if the input parameters remain the same.
• SCRATCHPAD 20 allocates 20 bytes of space for each instance of the function within an SQL statement.
• FINAL CALL sends an additional parameter to the subprocedure, identifying the first and last calls to the subprocedure for each instance in an SQL statement.
• DISALLOW PARALLEL requires the function calls to be performed consecutively; processing two or more simultaneously is not allowed.

Is that clear? If not, don't worry. I'll review the CREATE command again later.

What About the RPG IV Code?

The service program contains a subprocedure to perform the work of the function (see Figure 2). Additional subprocedures could be used to redefine the same functions with different parameters being passed.

Figure 2: Code for the COUNTER subprocedure.

This function uses the parameter style DB2SQL. Because FINAL CALL is specified on the CREATE statement, the input parameter FLAG is set to –1 the first time the function is called for each instance and then set to 1 on a final call to allow "clean-up" work to be done, if necessary. If FINAL CALL is not specified when creating the function, this parameter will not be passed.

On the first call, the subprocedure initializes the data in the scratchpad (INSTRUCT), and on subsequent calls, it adds the increment amount to it. The calculation specifications are rather simple. The scratchpad data is moved into a data structure. If this is the first call, the input parameter, START, is loaded into the counter field; otherwise, the input parameter INCR is added to the previous value. The new value is loaded into the output parameter and back into the scratchpad data. To call this version of the function, simply code COUNTER(x,y) in an SQL statement.

So What Does This Thing Do?

Once the service program is compiled, the function can be tested. The SQL statement Select Counter(1,1), cname from cust might yield the results shown in Figure 3. As you can see, a unique sequential number is assigned to each row of the result table.

Figure 3: SQL Select with 1 counter yields these results.

What other features could be added to improve this function? How about being able to assign just a starting value like COUNTER(101) and let the increment default to 1? Or simply let both parameters default to 1 with something like COUNTER()? That would cause the function to start at 1 and increment by 1 for each row in the result set. It is possible to configure a function to accept different parameter lists while still using the same function name. Each unique set of parameters requires another subprocedure to process it.

Because scratchpad is specified, this function can be used multiple times within the same statement, and each instance will have its own counter. A more complex SQL statement--Select Counter(1,1),cname,counter(10,10) from cust--yields the result shown in Figure 4.

Figure 4: SQL Select with 2 counters yields these results.

The first three columns use the counter function, but the counter number is determined separately for each column.

And there you have it--a working example of an RPG IV service program that is being called as a function out of SQL. Pretty cool, huh?

How Can the Same Function Accept Different Parameters?

SQL identifies functions not only by their names, but by their names and parameter lists. So each time a new set of parameters is registered, a new entry is created in the system file SYSFUNCS. To register a different parameter list for the same function, simply issue another CREATE FUNCTION statement and have it point to a different subprocedure (probably within the same service program)

Let's Review the Create Statement More Closely

A few of those odd-looking parameters on the create statement are of great importance:

SCRATCHPAD is vital to allowing this function to work. In Figure 4, the function was used more than once within the same SQL statement. How does the subprocedure know which column it's working with when it gets called? Since the data being manipulated is the data from the previous row in the result table, some mechanism is needed to keep the data accurate for each column. Scratchpad provides a block of memory for each instance of a function within an SQL statement. Each time the function is called, the appropriate memory block is passed to the subprocedure. Any data that needs to be persistent between calls, such as a counter value or a running total, can be stored within the scratchpad data. If scratchpad is not used, then the InStruct parameter must be removed from the prototypes and procedure interfaces. Scratchpad can only be used with parameter style DB2SQL.

FINAL CALL clearly identifies when the initial value of the column should be set. The FLAG parameter identifies the first call for an instance when it's value is - 1. After all the processing for that column is complete, the function is called again. This time, FLAG is set to 1. This allows the function to do any necessary clean-up work. On all other calls, FLAG is set to 0. If FINAL CALL is not used, the FLAG parameter must be removed from the prototypes and interfaces. FINAL CALL can only be used with PARAMETER STYLE DB2SQL.

PARAMETER STYLE DB2SQL identifies the format for passing parameters to the procedure. The Create command supports five parameter styles, each one with its own layout of the prototype and interface (see Figure 5).

Figure 5: The Create Function command supports alternative parameter styles.

DB2SQL offers the most flexibility for use with RPG IV, and it includes optional parameters. These optional parameters may or may not be passed, depending on which keywords you use when creating the function. DB2SQL is the only format that supports the SCRATCHPAD, FINAL CALL, and DBINFO options. See the manual DB2 Universal Database for iSeries SQL Reference at the iSeries Information Center for more information.

NOT DETERMINISTIC indicates that the results of the function can change even when the input parameters are the same. The function UPPER(), which translates lowercase characters to uppercase, is an example of a deterministic function. If the function gets called multiple times with the same data, identical results are returned for each call. For example, UPER('abc') always returns a value of 'ABC'. While GETONHAND(PART) might return a different on-hand quantity each time it is called. Defining the function as deterministic allows SQL to reuse results that may still be in cache from a previous call, thereby improving performance. For example, if the Sales History file has 100,000 records in it and you print the cost of each part with the statement SELECT PART, GETCOST(PART) FROM SALESHIST, the function GETCOST will be run 100,000 times if it is defined as NOT DETERMINISTIC. But, if it is defined as DETERMINISTIC, it might get called fewer times due to the fact that previous results for the same part could be found in the system cache. This option should only be used if the results are always the same.

What About the Service Program's Activation Group?

The service program should be compiled into either activation group *CALLER or a named activation group.

What Does All This Mean?

SQL is a fantastic tool for database manipulation. Its greatest weakness, in my opinion, is that it lacks the same level of granularity that can be found in high-level languages (HLLs) such as RPG IV. Being able to create your own SQL functions in RPG IV will enable you to provide your developers with powerful tools that greatly simplify their SQL code, or in some cases, add completely new functionality that may have been virtually impossible with the standard SQL instruction set. Add this to your tool belt and leverage the best of both SQL and RPG IV. You could be the proud author of your own SQL functions to perform tasks such as these:

• Converting dates from legacy formats to date data types
• Calculating costs
• Calculating prices
• Retrieving on-hand balances
• And many more...

You may have already learned how to embed SQL with RPG. Now, you know how to embed RPG within SQL!

Kevin Forsythe has over 18 years of experience working with the iSeries platform and its predecessors. He has been a member of the DMC team for the past nine years. Kevin's primary responsibility is providing iSeries education, but he also provides customers with project management, system design, analysis, and technical construction. In addition to his technical skills (RPG IV, CL, OS/400, SQL, FTP, Query, VB, Net.Data), Kevin possesses the ability to communicate new and complex concepts to his students. He has been the primary instructor for DMC's iSeries-based AS/Credentials training courses since 1997 and has authored courses such as Advanced ILE, SQL, Embedded SQL, Operations Navigator, and Intro to WebSphere Studio. An award-winning speaker, he has spoken at every COMMON Conference since the spring of 2000.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions