• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Passing an Entire Result Set as a Parameter, Part 2

    October 21, 2009 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:

    Account

    Debit

    Credit

    100020

    0

    1007.95

    100030

    1007.95

    0

    Here’s a sample of how the RPG program would be invoked by DB2 as a table function in a query:

    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
    

    Of course, in a client/server environment the XML data would be passed as a parameter to the table function or to a stored procedure that invoked the table function instead of having hard-coded text as illustrated above.

    Here is a simple stored procedure that accepts the XML data as a CLOB parameter and uses the GetJrnDta UDTF to convert the XML data into rows:

    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; '
    

    Before I end, there are a few things to note about the RPG function:

    • When passing large XML strings into a stored procedure, it may be quicker to immediately dump the data into a DB2 temporary table for subsequent processing rather than invoking the table function multiple times because table functions are relatively slow.
    • I wrote the RPG program the “easy way” by assuming the XML data would be 64K or less on a V5R4 system or 16MB or less on a V6R1 (a.k.a. 6.1) i/OS system (each maximum being the respective maximum RPG character variable lengths.) The XML-SAX operation code allows for processing larger XML documents piece by piece but the programming is more intricate. With the V5R4 program however, the original requirement of allowing up to 9,999 rows be passed back is not permissible using my “el-cheap-o” method, due to the 64K string length barrier.

    Finally, passing XML data in a CLOB variable is an excellent way to pass and process a set of rows in a stored procedure. Thanks to the XML-INTO operation code, RPG is a great language choice for easily parsing XML into a useable form. Any errors encountered while parsing the XML data can be handled by the RPG program. Further, when creating a table function, using XML and RPG allows the data to be returned to DB2 as a strongly typed result set.

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

    RELATED STORY

    Passing an Entire Result Set as a Parameter: Part 1



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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Infor:  Visit the first System i Virtual Conference hosted by Infor and IBM. View on-demand Webinar.
    CCSS:  Need Pro-Active Management of Your IBM® i Server? We can help.
    Patrick Townsend Security Solutions:  Get a customized state privacy law compliance report

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Kronos Acquires Time and Attendance Software from Paychex IBM Rolls Up an i 6.1.1 Dot Release

    Leave a Reply Cancel reply

Volume 9, Number 38 -- October 21, 2009
THIS ISSUE SPONSORED BY:

Halcyon Software
ProData Computer Services
Twin Data

Table of Contents

  • Prompting CL in Run SQL Scripts
  • Publish Result Sets Using Web Services and IWS
  • Admin Alert: The Ins and Outs of IBM Business Partners
  • Getting the Message, Part 2
  • Passing an Entire Result Set as a Parameter, Part 2
  • Setting Up SNTP Time Synchronization on an i5/OS Box

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle