Passing an Entire Result Set as a Parameter, Part 2

Published: October 21, 2009

by Michael Sansoterra

Note: The code accompanying this article is available for download here.

In Part 1 of this series, I discussed a generic method of passing a delimited set of text records in CSV (comma separated variable) format into a single stored procedure variable. Once the stored procedure had this text data, a generic Java user-defined table function (UDTF) was used to parse the text data and convert it to a row set that could be by any number of standard DB2 for i statements, such as a SELECT or INSERT. So with a little sweat, it is indeed possible to send multiple rows of data to a stored procedure for processing. Don't forget, this discussion of passing result sets is done in the context of client/server programming.

While adequate, this generic Java based approach suffered from a few problems:

• All data columns within the CSV file were represented as VARCHAR(400). Columns with less data were wasting space while columns requiring a larger column size would require a slightly modified function definition.
• All columns were generically labeled as Field1, Field2, etc.
• Eighty columns were always returned even if they weren't needed. If you needed over 80 columns, you were out of luck.
• Starting a Java Virtual Machine to run the function negatively impacted performance.

On the plus side, this Java UDTF could handle the majority of scenarios dealing with CSV data without changing any of the underlying code. The same cannot be said for the RPG/XML technique I will demonstrate in this tip. However, the good news is that the major performance and data typing problems mentioned above do not apply with this technique.

This tip will require the i/OS level to be at V5R4 or higher because it uses RPG's XML-INTO operation code. (For users stuck on a back-leveled OS, I did implement this technique many years back on a V5R2 system using the 5733XT1 XML Toolkit. However, it's a lot more work than using the native RPG XML support.) This tip also requires the 5722ST1 SQL Development Kit to be installed because we will be using a little embedded SQL.

The objective in this tip is still the same: we want to pass an entire set of rows in a single parameter to a stored procedure where the parameter is converted into a set of rows that can be used by DB2 for i SQL statements. This time, instead of using the CSV text format we'll use XML. For the sake of illustration, we'll stick with implementing the solution using an RPG UTDF, although an RPG program using this same technique could also serve just as well as a standalone stored procedure with the advantage of better performance.

In the first tip, the sample data had three pieces of information: GL Account Number, Debit value, and Credit value. We can represent the same information as an XML definition as follows:

<JrnlLines>
<JrnlLine>
    <Account>100020</Account>
    <Debit>0</Debit>
    <Credit>1007.95</Credit>
</JrnlLine>
<JrnlLine>
    <Account>100030</Account>
    <Debit>1007.95</Debit>
    <Credit>0</Credit>
</JrnlLine>
</JrnlLines>

Assuming the client-side program can package XML data in this manner, it's a snap to have RPG read the XML into a data structure array. With the XML-INTO op-code, the XML text is shredded and put into a data structure array with one C-Spec!

RPG program GETJRNDTA acts as a UDTF to transform an XML fragment into a result set with these steps:

1. Receive a large character parameter as a LOB locator (a locator is a special SQL "pointer" that references a large object type such as a BLOB, CLOB, or DBCLOB).
2. Take the XML data chunk by chunk from the CLOB and load it into an RPG string variable.
3. Let the RPG XML-INTO program convert the XML data into a data structure array.
4. Extract the data from the data structure one row at a time and give it back to DB2.
5. Clean up the locator when finished.

Once the RPG program GETJRNDTA is compiled, it must be registered for use with SQL using the CREATE FUNCTION statement as follows:

Create Function xxxxx/GETJRNDTA
(XMLData Clob(2M) As Locator)   /* Use Clob(64K) for V5R4 */
Returns Table
(Account Char(20), 
 Debit   Dec(9,2),
 Credit  Dec(9,2))
External
Language RPGLE
Disallow Parallel
Modifies SQL Data
Parameter Style DB2SQL
Not Fenced

In case you're not familiar with UDTF functions, they are simply a special program that gathers data in a tabular (row and column) format for use with DB2. In this case, RPG table function GetJrnDta will take the above XML fragment and convert it to a set of rows:

Select * 
  From Table(GetJrnDta('
<JrnlLines>
<JrnlLine>
    <Account>100020</Account>
    <Debit>0</Debit>
    <Credit>1007.95</Credit>
</JrnlLine>
<JrnlLine>
    <Account>100030</Account>
    <Debit>1007.95</Debit>
    <Credit>0</Credit>
</JrnlLine>
</JrnlLines>')) JournalEntries

Create Procedure xxxxx/ProcessJournalEntry
(XMLData In CLOB(2M))
Language SQL
Set Option Commit=*None, UsrPrf=*Owner, DatFmt=*ISO
Begin
    Declare Debits  Dec(11,2) Not Null Default 0;
    Declare Credits Dec(11,2) Not Null Default 0;

    Select Sum(Debit),Sum(Credit)
      Into Debits, Credits
      From Table(GetJrnDta(XMLData)) BalanceCheck;

    If Debits<>Credits Then
        Signal SQLSTATE '38I01'
           Set Message_Text='Debits Must Equal Credits';
    End If;

-	- Do more stuff here...
    Insert Into QTEMP.JournalEntry
    Select *
      From Table(GetJrnDta(XMLData)) JournalData;

End; '