• 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
    UCG Technologies

    CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

    COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

    “Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”1

    Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

    UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

    Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

    A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

    Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

    Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS.  Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

    The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

    Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

    ucgtechnologies.com/triple-play

     800.211.8798 | info@ucgtechnologies.com

     

    1. https://theconversation.com/cyberattacks-are-on-the-rise-amid-work-from-home-how-to-protect-your-business-151268

    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

  • We Want IBM i On The Future Power E1050
  • Thoroughly Modern: Running CA 2E Applications? It’s Time To Modernize The UI
  • Guru: SELECT INTO And Arrays
  • Clearlake Re-Acquires Precisely In $3.5 Billion Deal
  • Ansible Automation Story Gets Better on IBM i
  • Why Open Source Is Critical for Digital Transformation
  • mrc Refreshes IBM i Low-Code Dev Tool
  • Unit Testing Automation Hits Shift Left Instead of Ctrl-Alt-Delete Cash
  • Four Hundred Monitor, March 3
  • IBM i PTF Guide, Volume 23, Number 9

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

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.