Simplify your SQL SELECT statements, provide an interface to pass parameters to create dynamic tables, automatically log access to tables, and create a single point of maintenance for the SQL code you use repeatedly.

In my previous article, "Reuse Your RPG Code with SQL User-Defined Functions," I discussed how you could reuse RPG business logic in SQL by taking an RPG program or service program that returns a single value and then using the SQL CREATE FUNCTION command to make the business logic available to any high-level language (HLL), such as PHP or Java, or any tool that uses SQL to access data on your system. There are times, however, when you might want a function that returns not just a single value but a set of values or even a table. In that case, you could consider using a User-Defined Table Function (UDTF).

Like its name implies, the UDTF returns a table (file) consisting of a row or set of rows that can then be referenced in the FROM clause of an SQL Select statement. While a UDF allows a programmer to reuse RPG business logic and make that logic available through SQL to other programming languages, a UDTF is more likely to be useful in simplifying your SQL statements, consolidating your code in a single place, or creating a table out of data that is not stored in a relational database.

As with a UDF, a UDTF can be written using either an SQL Procedural Language (SPL) or an HLL such as RPGLE, C, or Java. I'm going to start with some examples using SPL, since I find it simpler and more intuitive because SQL does the interface work for you. I'll save the RPGLE examples for another article. In these examples, I'm going to use the table shown below consisting of an employee number, department, and name.

In our first example, let's create a UDTF that takes as input the department number and returns the employee number, department, and name.

CREATE FUNCTION DSPEMPLY(dept INTEGER)

   RETURNS TABLE (Number INTEGER, Departmt INTEGER, Name VARCHAR(30))

   LANGUAGE SQL

   BEGIN

     RETURN

       SELECT EMPNUM, EMPDPT, EMPNAM

       FROM mylib/EMPLOYEE

       WHERE EMPDPT = dept;

   END

I like to think of a CREATE FUNCTION command as consisting of four parts. The first part, CREATE FUNCTION, names the function and follows with any input parameters. The second, RETURNS TABLE, specifies the columns that are being returned and their data types. The third part is the set of keywords and parameters describing the function's attributes. In this case, the only one specified is LANGUAGE SQL, which lets the compiler know the function will be written in SPL. This is not to be confused with the UDF parameter PARAMETER STYLE SQL, which refers to the calling interface of the external program. Finally, the fourth section is the BEGIN…END section that contains the SPL, and within the BEGIN…END section is a RETURN statement with the data elements that correspond to the columns specified in the RETURNS TABLE statement.

Creating the function can be done by using the SQL statement CREATE FUNCTION in STRSQL, creating a source member with the CREATE FUNCTION statement in it, and executing the RUNSQLSTM command. Alternatively, you could use the wizard in the System i Navigator, which can be very helpful with drop-downs on data types and SQL statements you can use in SPL if you are not familiar with them. Creating the function registers its name, parameters, and return values with the database.

Now that the function has been created, let's use it in a SELECT statement to display the employees from Department 17 and order them by Name as follows:

SELECT * from TABLE(dspemply(17)) as T order by Name

The TABLE keyword lets SQL know that a table function is being invoked, and the name of the function and any parameters are enclosed in parentheses. Since this is a dynamic table, it requires a name, and I've called it "T." The output is shown below.

Figure 1: This is the output from the Select statement.

With the simple example above, you probably wouldn't go to the trouble of creating a UDTF, so let's enhance our function to create a new table from our original one in order to separate the employee's first name and last name into their own columns that we'll call FirstName and LastName.

CREATE FUNCTION DSPEMPFL(dept INTEGER)

   RETURNS TABLE (Number INTEGER, Departmt INTEGER,

                   FirstName VARCHAR(30), LastName VARCHAR(30))

   LANGUAGE SQL

   BEGIN

     RETURN

       SELECT empnum, empdpt,

       (substring(empnam,1,

         (locate(' ',empnam)-1))) as FirstName,

         substring(empnam,(locate(' ',empnam)+1),(length(empnam)

         - length(substring(empnam,1,(locate(' ',empnam)-1))))) as LastName

       FROM mylib/EMPLOYEE

       WHERE empdpt = dept;

   END

Since the employee's last name is contained in its own column, we can now select the employees from department 17 and easily order them by their last name.

SELECT * from table(dspempfl(17)) as T order by LastName

By putting the logic for separating the names in a UDTF, the SQL used to separate them is consolidated in one function, the SELECT statement using the new table is simplified, and a convenient way is provided for someone not as well-versed in SQL to access this table. The results are shown in the figure below.

Figure 2: The new results separate first name from last name.

So far, I've just used a basic SELECT statement in the body of the SPL to return a set of values. SPL is much more powerful, allowing programming logic to test conditions and to insert, modify, or delete rows and columns in other tables. For example, let's assume that you want to create an audit for every time a program uses the function to access the employees of department 17. To keep things simple, we'll make the modifications to the first function we created. The table LOGFILE will record each access with a row consisting of the timestamp, the user, and department 17. The result is shown below.

CREATE FUNCTION DSPEMPLY(dept INTEGER)

   RETURNS TABLE (Number INTEGER, Departmt INTEGER, Name VARCHAR(30))

   LANGUAGE SQL

   MODIFIES SQL DATA

   BEGIN

   IF dept = 17 THEN

     INSERT INTO mylib/LOGFILE (tstamp, cuser, dep)

         VALUES(CURRENT_TIMESTAMP, USER, '017');

         END IF;

     RETURN

       SELECT EMPNUM, EMPDPT, EMPNAM

       FROM mylib/EMPLOYEE

       WHERE EMPDPT = dept;

   END

Notice that since we are performing an INSERT, we are modifying an SQL table, so the keyword MODIFIES SQL DATA has been added to the keyword list. In my previous article, I mentioned several other keywords that can be used with UDFs, and those keywords can be used with UDTFs as well. One keyword that is unique for table functions is the keyword CARDINALITY, which is followed by an integer. This keyword indicates the expected number of rows to be returned by the table function for optimization purposes. For example, in our function above, if it was known that no department contained more than 10 employees, a cardinality of 10 could be specified as shown below.

   LANGUAGE SQL

   MODIFIES SQL DATA

   CARDINALITY 10

For a complete listing of the keywords and how they are used, see the IBM i Database SQL Programming Guide at the IBM Infocenter Web site.

Summing It Up

If you haven't previously been familiar with SQL UDTFs or haven't had an occasion to use one, I attempted in the above examples to illustrate that they are not that difficult or mysterious. In fact, they are a great way to simplify the complexity of SQL SELECT statements in your coding, to provide a simple interface that allows passing parameters to create dynamic tables, to automatically log access to tables, and to provide a single point of maintenance for the SQL code that you use repeatedly in your programs.

For more on SQL User-Defined Functions, consider the IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries or the DB2 for i SQL Reference at the IBM Infocenter.

Jim Staton is Vice President for Information Technology at Mutual Distributing Company, the largest alcohol beverage distributing company in North Carolina.

 

While Jim never worked with IBM midrange systems at IBM, one of his first programming jobs was developing software for an IBM System/3 Model 6 for his family's business. He continued to develop RPG applications for the business in his spare time on a System/32, System/34, System/36, and eventually the AS/400.

 

In 1978, Jim joined IBM at the Research Triangle Park in North Carolina, where he worked with computer communications and protocol standardization. As a manager at IBM, he was responsible for IBM's work with the IEEE 802.x committees to complete the standards for the 802.2-5 protocols for local area networks. In 1985, Jim joined the IBM European Networking Center in Heidelberg, Germany, where he managed the development of IBM's first prototype implementing the CCITT X400 Standard, which were the rules governing the first universal email system. While at IBM, Jim was awarded two patents in communication protocols as well as an Outstanding Technical Achievement Award for his work with X.400 Message Handling System. He also coauthored several articles on computer communications for the IBM Systems Journal.

 

Jim has been a speaker at a number of conferences on a variety of topics, including communications protocols, supply chain management, mobile applications, and business intelligence.

 

Jim graduated from Ohio State University in 1978 with an MS degree in Computer Science.

 

 

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL