Select *
  From Table(ListUDTF('PROJ1|PROJ2|PROJ3','|',9999))
       As ProjectList

ItemNo	Item
1	PROJ1
2	PROJ2
3	PROJ3

The first parameter in the table function is a delimited list. The second parameter is the delimiter character(s), and the third parameter is the maximum number of rows the table function should return.

This temporary result set can be joined to a project master table as follows:

Select ProjectMaster.*
  From Table(ListUDTF('PROJ1|PROJ2|PROJ3','|',9999))
       As ProjectList
  Join ProjectMaster 
    On ProjectMaster.ProjectId=ProjectList.Item

Putting this statement in the context of a DB2 SQL stored procedure that returns a result set to the caller, we get code that looks something like this:

Create Procedure ProjectList
(@Projects IN VarChar(1000))
Language SQL
Result Sets 1
Set Option Commit=*None, UsrPrf=*Owner, DatFmt=*ISO
Begin

Declare Project_Cursor Cursor 
With Return To Caller For
Select ProjectMaster.*
  From Table(ListUDTF(@Projects,'|',9999))
       As ProjectList
  Join ProjectMaster 
    On ProjectMaster.ProjectId=ProjectList.Item;

Open Project_Cursor;

End;

Of course, you may want to enhance this procedure and add an ORDER BY clause or additional parameters to restrict the query's results even further.

A .NET program (or any other language) can easily accommodate this scenario by creating a delimited list of project IDs in a string variable and passing this variable as a parameter to the stored procedure.

In a nutshell, that's how I like to tackle the issue of passing a variable number of data items.

A second dimension to your issue involves performance. SQL statements usually perform better when they're pre-compiled. But allowing for a variable number of parameters often requires using a dynamic query rather than a pre-compiled query. Nevertheless, there are a few things you can do to try to tweak performance.

Option 1: Add a Cardinality Value to the Table Function Definition

The CREATE FUNCTION statement used to define the LISTUDTF table function (as presented in the Delimited List Processing in SQL tip) omits the CARDINALITY option. CARDINALITY is used to give DB2 a hint as to how many rows it can expect the user-defined table function to return. Normally DB2 can examine row count statistics on physical files, indexes, etc., to formulate a plan on how it should retrieve the data. But how will DB2 know how many rows to expect from an RPG program without an explicit hint?

Create Function xxxxx/ListUDTF
             (parmList     VarChar(24576),
              parmDelim    VarChar(10),
              parmMaxItems Integer)
Returns Table(ItemNo Integer,
              Item VarChar(128))
External Name 'xxxxx/LISTUDTFR(PROCESSLIST)'
Language RPGLE
Disallow Parallel
No SQL
Parameter Style DB2SQL
Deterministic
CARDINALITY 12

In this illustration, when ListUDTF is used in a query, DB2 makes its plan based on an estimate of 12 rows being returned, on average, from the table function. CARDINALITY is not meant to be a hard and fast number, but specifying the average number of rows to be returned is meant to help DB2.

Be careful, though, because ListUDTF is an all-purpose function and could return drastically different row counts depending on the application. If this is the case, you can create the function multiple times (using slightly different names) for different cardinalities you may need.

In the case of a small number of rows, specifying a cardinality value may not help much. But it is one more piece of information that DB2 can use to formulate an execution plan for the query.

Option 2: Build a Procedure With a Pre-Defined Number of Parameters

Another way to tackle the performance problem is to build a stored procedure with the largest anticipated parameter count needed. For example, if 10 project numbers is the most you'll ever need, then code the procedure as follows:

Create Procedure ProjectList (
@Project1  IN VarChar(10),
@Project2  IN VarChar(10),
@Project3  IN VarChar(10),
@Project4  IN VarChar(10),
@Project5  IN VarChar(10),
@Project6  IN VarChar(10),
@Project7  IN VarChar(10),
@Project8  IN VarChar(10),
@Project9  IN VarChar(10),
@Project10 IN VarChar(10))
Language SQL
Result Sets 1
Set Option Commit=*None, UsrPrf=*Owner, DatFmt=*ISO
Begin

Declare Project_Cursor Cursor 
With Return To Caller For
Select ProjectMaster.*
  From ProjectMaster 
 Where ProjectId In (@Project1,@Project2,@Project3,
                     @Project4,@Project5,@Project6,
                     @Project7,@Project8,@Project9,
                     @Project10);

Open Project_Cursor;

End;

If ProjectMaster has a unique index or primary key on the ProjectId column, this technique will get the best performance, with the caveat that needing additional parameters will require changes in both the client and server environments. In other words, getting the best performance means telling the pre-compiler what it can expect beforehand and all programs involved need to conform to these expectations. In this case, though, I'd favor the versatility of the dynamic list approach rather than striving for maximum performance with pre-determined parameters.

In summary, the need to pass a variable number of parameters (or better, a variable number of related data elements) is really equivalent to the need to pass a dynamically sized array parameter. SQL doesn't have array processing capabilities, but array processing can be emulated using a contrived list of values in a delimited string along with a table function like the ListUDTF function presented here.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments to Mike via the IT Jungle Contact page.

RELATED STORIES

Advanced DB2 for i Data Access Techniques with .NET

Delimited List Processing in SQL

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot