• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Pipelined User-Defined Table Functions In DB2 For i

    May 5, 2015 Michael Sansoterra

    Pipes are a fantastic invention as they are useful for conveying a substance in an efficient and well directed manner. Whether used for transporting sewage, carrying oil, or even bottling a duck (if you’re old enough to remember “The High and the Flighty” Foghorn Leghorn cartoon featuring the Ace Novelty company’s “Pipe Full of Fun Kit #7”); our world wouldn’t be the same without them.

    Not to be left out, starting in IBM i 7.1 TR9 and IBM i 7.2 TR1, DB2 for i now offers a “PIPE” statement that is used to quickly populate rows in a user-defined table function’s (UDTF) result set.

    Creating a pipelined function is simple and very similar to creating a non-pipelined SQL table function. For a simple example, say you had a suite of related global variables that are referenced often throughout an application’s code base:

    CREATE VARIABLE PRDLIB.ENVIRONMENT  VARCHAR(20)  DEFAULT 'Production';
    CREATE VARIABLE PRDLIB.PRIMARY_OUTQ VARCHAR(10)  DEFAULT 'PRT01';
    CREATE VARIABLE PRDLIB.PDF_FOLDER   VARCHAR(128) DEFAULT '/Production/PDFs';
    CREATE VARIABLE PRDLIB.DEFAULT_COMPANY SMALLINT  DEFAULT 1;
    

    A typical approach to making these variables easily accessible in an application is to create a table function that will grab them all at once and return them in a single row:

    CREATE OR REPLACE FUNCTION PRDLIB.ENVIRONMENT_VARIABLES()
    RETURNS TABLE (
    ENVIRONMENT VARCHAR(20),
    PRIMARY_OUTQ VARCHAR(10),
    PDF_FOLDER VARCHAR(128),
    DEFAULT_COMPANY SMALLINT)
    CARDINALITY 1
    DETERMINISTIC
    LANGUAGE SQL
    BEGIN
        RETURN
        SELECT * 
          FROM (VALUES(
                ENVIRONMENT,PRIMARY_OUTQ,PDF_FOLDER,DEFAULT_COMPANY)
               ) ENV;
    END
    

    The new PIPE statement provides an alternative way to code this:

    CREATE OR REPLACE FUNCTION PRDLIB.ENVIRONMENT_VARIABLES_PIPE()
    RETURNS TABLE (
    ENVIRONMENT VARCHAR(20),
    PRIMARY_OUTQ VARCHAR(10),
    PDF_FOLDER VARCHAR(128),
    DEFAULT_COMPANY SMALLINT)
    CARDINALITY 1
    DETERMINISTIC
    LANGUAGE SQL
    BEGIN
        PIPE (ENVIRONMENT,PRIMARY_OUTQ,PDF_FOLDER,DEFAULT_COMPANY);
        RETURN;
    END
    

    As you can see, PIPE simply inserts a row into the table function’s result set. It’s obvious that the number of values and data types passed in the PIPE statement must be compatible with the UDTF’s return table definition.

    So what advantage does the PIPE statement offer DB2 coders? First, the syntax is easy to understand. Second, PIPE is advantageous in situations when variables (not table data), are the driving force behind the UDTF’s data. Without the benefit of PIPE, variable data needs to be preserved in a temp table or included in a SELECT statement. (If a UDTF will make use of a standard query from existing table(s), there is probably no need to use a pipelined function.)

    The following COUNTER table function illustrates the benefit of PIPE by returning an arbitrary number of integer values:

    CREATE OR REPLACE FUNCTION PRDLIB.COUNTER(@ROWS INT)
    RETURNS TABLE (
    ROW_ID INT)
    LANGUAGE SQL
    CARDINALITY 50
    DETERMINISTIC
    BEGIN
        DECLARE @I INT DEFAULT 0;
        WHILE @I>@ROWS DO
            SET @I=@I+1;
            PIPE (@I);
        END WHILE;
        RETURN;
    END;
    

    Invoking the pipelined UDTF:

    SELECT * FROM TABLE(PRDLIB.COUNTER(5)) COUNTER;
    

    Which returns the following result set:

    ROW_ID

    1

    2

    3

    4

    5

    This COUNTER UDTF can also be implemented with a recursive common table expression, but in my opinion the pipelined function is easier to follow.

    As always, specify the cardinality value when creating a UDTF to give DB2 an idea of the average number of rows it can expect to receive.

    If you’re thinking that writing a pipelined SQL UDTF is similar to writing an external UDTF you are correct. They both insert one row at a time into the UDTF result set. Finally, like PIPE, external UDTFs can only return variables as row data.

    For a final example, the following ParseData table function is a pipelined function that will split text data based on a given delimiter and return the result as rows. As you can see each new row is sent directly through the PIPE in lieu of dumping them into a temporary table first.

    CREATE OR REPLACE FUNCTION QGPL.ParseData(
    @Data CLOB(1M), @Delimiter VARCHAR(12))
    RETURNS TABLE (Row_Id INTEGER,FieldData varchar(2048))
    LANGUAGE SQL
    BEGIN
        DECLARE @START INTEGER DEFAULT 1 NOT NULL;
        DECLARE @END1  INTEGER DEFAULT 0 NOT NULL;
        DECLARE @ROWID INTEGER DEFAULT 0 NOT NULL;
        DECLARE @FIELD VARCHAR(2048);
    
        IF @Data IS NULL THEN
            RETURN;
        END IF;
    
        SET @END1=LOCATE(@Delimiter,@Data);
        WHILE @END1>0 DO
            SET @FIELD=SUBSTRING(@Data,@START,@END1-@START);
            SET @ROWID=@ROWID+1;
            PIPE (@ROWID,@FIELD);
            SET @START=@END1+LENGTH(@Delimiter);
            SET @END1=LOCATE(@Delimiter,@Data,@END1+LENGTH(@Delimiter));
        END WHILE;
    
        SET @FIELD=SUBSTRING(@Data, @START,LENGTH(@Data)-@START+1);
        SET @ROWID=@ROWID+1;
        PIPE (@ROWID,@FIELD);
        RETURN;
    END
    

    Here is an example of the ParseData function’s usage:

    SELECT * 
      FROM TABLE(QGPL.ParseData('Pipelined|Functions|Are|Great','|')) x;
    

    And output:

    ROW_ID

    FIELDDATA

    1

    Pipelined

    2

    Functions

    3

    Are

    4

    Great

    Since there is a similarity between pipelined SQL table functions and external table functions, I might as well address a misgiving I had about external UDTFs that IBM‘s Scott Forstie cleared up for me. Until recently, based on early external UDTF experience in V5R2, I thought that all external UDTFs would create a temporary (QTEMP) table under the covers and were therefore relatively slow. Scott responded that the temporary table issue is only true for external UDTFs processed through the CQE but not the SQE. Most SQL statements are now processed by the SQE so this is unlikely to be an issue.

    As for performance, pipelined functions can be beneficial compared to a similar UDTF written using a temporary table. As Forstie says, “Even if a small temporary table is fully contained within memory, there is performance advantage for the PIPE statement since the overhead of library management, file management, and catalog management can be avoided.” The performance benefit will largely depend on how often the function is invoked.

    Pipelined UDTFs are an asset to DB2 for i developers because they can potentially perform quicker, they are easier to code and they bring IBM i devs another feature that is available in other DB2 versions.

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

    REFERENCES

    Pipelined Table Functions

    Pipe Control Statement

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Manta Technologies

    The Leader in IBM i Education!
    Need training on anything i?
    Manta is all you need.

    Spring Sale! Save 20% off any Manta Combination Package, including the complete IBM i Training Library. Now through April 30.

    130 courses and competency exams on:
    · IBM i operations
    · System Management and Security
    · IBM i Programming Tools
    · Programming in RPG, COBOL, CL, Java
    · Web Development
    · SQL, DB2, Query

    Product features:
    · Runs in every popular browser
    · Available 24/7/365
    · Free Student Reference Guides
    · Free Student Administration
    · Concurrent User License
    · Built-In IBM i Simulator

    You can download our 200-page catalog and take sample sessions at MantaTech.com.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Remain Software:  Take control of your software modernization process with TD/OMS and X-Analysis
    ProData Computer Services:  SQL/Pro 5.0 - New & Improved! Download today!
    United Computer Group:  VAULT400 BaaS delivers secure cloud backup and DR solutions

    An Open Letter To IBM From A Developer Technology Refresh Highlighted By Development Languages And Native Flash Storage

    One thought on “Pipelined User-Defined Table Functions In DB2 For i”

    • Kevin T says:
      October 7, 2018 at 2:22 am

      Great article. Really helpful for getting up to speed with DB2. Note that the COUNTER example has a bug. Should be @I < @ROWS

      Reply

    Leave a Reply Cancel reply

Volume 15, Number 09 -- May 5, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
PowerTech
United Computer Group, Inc.

Table of Contents

  • Pipelined User-Defined Table Functions In DB2 For i
  • Formatting Dates with SQL, Take 2
  • Ruby And Existing Databases

Content archive

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

Recent Posts

  • LANSA Developing Business Intelligence Tool
  • Blazing The Trail For VTL In The Cloud
  • Data De-Dupe Gives VTL Customers More Options
  • Four Hundred Monitor, March 29
  • The Big Spending On IT Security Is Only Going To Get Bigger
  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13

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 © 2023 IT Jungle