• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Regurgitating Data With DB2 For i Data Change Table References

    January 20, 2015 Hey,

    Four Hundred Guru:

    I’ve been reading your SQL articles in hopes of finding a solution to a problem. I need to insert the same data into two tables at the same time in detail form to one table and in summary form to the other. Is such a thing possible?

    I have an incoming transaction table that I want to insert into another table. The incoming transaction table has multiple columns and some of those columns are quantities (integer) fields or amount (with a decimal) fields. I want SQL to run an INSERT INTO… SELECT FROM type statement to insert the incoming transactions into the transaction master. When the INSERT is completed, I want to be able to get a total of some of the amount and quantity fields from the rows that were just inserted. But I do not want to read the incoming data again just to get the totals. I need these sums to update fields in a batch control file for the transactions in the batch.

    Do you know of a way to have SQL keep running totals on the insert so when the insert is completed I will have the totals of the columns inserted without having to read the incoming data a second time just to get the totals/sums?

    I was thinking of using the GET DIAGNOSTICS statement, but cannot see where I can tell SQL to keep track of the grand total(s) of specific columns. I can see where I can get the total number of rows written, but that is all I see.

    –Doug

    Well, Doug. Sorta kinda. You are correct. GET DIAGNOSTICS won’t do what you want, as its only purpose is to return diagnostic information about the most recently executed SQL statement. Performing aggregate operations such as a SUM can only be done with a SELECT, VALUES or SET statement.

    You can’t get away from SQL reading data twice (that I know of), but if you’re on V6R1 or later you can leverage a “data change table reference” to minimize the impact.

    For simplicity, say this table stores your transaction master data:

    DECLARE GLOBAL TEMPORARY TABLE TRANSDATA
    (ID INT NOT NULL,
     EXTPRICE DEC(7,2) NOT NULL,
     QTY INT NOT NULL,
     ITEM VARCHAR(15) NOT NULL,
     TRANDATE DATE NOT NULL);
    

    And this is the INSERT/SELECT that populates the transaction master table:

    INSERT INTO SESSION.TRANSDATA
    SELECT *
      FROM (VALUES
    (1001,100.00,1,'PART1','2014-03-02'),
    (1002,300.00,2,'PART2','2014-03-02'),
    (1003,200.25,1,'PART3','2014-03-03'),
    (1004,100.00,1,'PART1','2014-03-03')
            ) TRANS(ID,EXTPRICE,QTY,ITEM,TRANDATE)
    ;
    

    By using the “data change table reference” DB2 lets the inserted data make an encore appearance so that it can be operated on again!

    SELECT SUM(QTY),SUM(EXTPRICE),MAX(TRANDATE)
      FROM FINAL TABLE (
     INSERT INTO SESSION.TRANSDATA
     SELECT *
       FROM (VALUES
     (1001,100.00,1,'PART1','2014-03-02'),
     (1002,300.00,2,'PART2','2014-03-02'),
     (1003,200.25,1,'PART3','2014-03-03'),
     (1004,100.00,1,'PART1','2014-03-03')
             ) TRANS(ID,EXTPRICE,QTY,ITEM,TRANDATE)
     ) INSERTED_DATA;
    

    As you can see, the special FINAL TABLE reference allows DB2 to access the data just inserted into the table to perform the requested aggregates laid out in the SELECT. Note that the column names referenced in FINAL TABLE originate from the table in the INSERT. You cannot reference a column name from the SELECT unless you use a special INCLUDE feature of the INSERT statement. Because DB2 is handling this operation under the covers I imagine it is more efficient than dumping the inserted data to a temp table and reading it again.

    Unfortunately DB2 will not let you do a secondary INSERT to dump the aggregated results to the transaction summary table. However, since you’re only getting one row we can take advantage of this situation to use a SELECT INTO to slam the aggregates into variables that you can then dump into the summary table with an INSERT/VALUES statement:

    DECLARE GLOBAL TEMPORARY TABLE TRANSSUMMARY
    (BATCH_ID INT AS IDENTITY NOT NULL,
     TOTPRICE DEC(9,2) NOT NULL,
     TOTQTY INT NOT NULL,
     LASTDATE DATE NOT NULL);
    
    BEGIN
        DECLARE @TOTQTY INT NOT NULL DEFAULT 0;
        DECLARE @TOTPRICE DEC(9,2) NOT NULL DEFAULT 0;
        DECLARE @LASTDATE DATE NOT NULL DEFAULT '1900-01-01';
    
        SELECT SUM(QTY),SUM(EXTPRICE),MAX(TRANDATE)
          INTO @TOTQTY, @TOTPRICE, @LASTDATE
          FROM FINAL TABLE (
        INSERT INTO SESSION.TRANSDATA
        SELECT *
          FROM (VALUES
        (1001,100.00,1,'PART1','2014-03-02'),
        (1002,300.00,2,'PART2','2014-03-02'),
        (1003,200.25,1,'PART3','2014-03-03'),
        (1004,100.00,1,'PART1','2014-03-03')
                ) TRANS(ID2,EXTPRICE2,QTY2,ITEM2,TRANDATE2)
        ) INSERTED_DATA;
    
        INSERT INTO SESSION.TRANSSUMMARY(TOTPRICE,TOTQTY,LASTDATE)
        VALUES(@TOTPRICE,@TOTQTY,@LASTDATE);
    END
    

    This code sample uses a dynamic compound statement, which requires 7.1 group PTF Level 26. If you’re not at 7.1 you can accomplish the same thing in RPG, C, or COBOL using embedded SQL.

    As long as we’re on the topic, I might as well call attention to the fact that there are two options for how to handle a data change table reference (using the FINAL keyword as shown above or using the NEW keyword). The differences are as follows (from the SQL Reference):

    FINAL TABLE: Specifies that the rows of the intermediate result table represent the set of rows that are inserted by the SQL data change statement as they appear at the completion of the data change statement. If there are AFTER INSERT triggers or referential constraints that result in further changes to the inserted rows of the table that is the target of the data change statement, an error is returned.

    NEW TABLE: Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement prior to the application of referential constraints and AFTER triggers. Data in the target table at the completion of the statement might not match the data in the intermediate result table because of additional processing for referential constraints and AFTER triggers.

    To recap, a data change table reference in DB2 for i allows you to capture the results of an INSERT statement for further processing. In other versions of DB2, data change table references can also be used with UPDATE and DELETE statements; an exciting prospect if these features ever make it into DB2 for i.

    –Mike

    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.

    RELATED STORIES

    Dynamic Compound Statements In DB2 For i

    Make Your DB2 For i Apps Environment Aware

    Retrieve The Call Stack In DB2 For i

    Treasury of new DB2 6.1 Features, Part 2: Grouping Sets and Super Groups

    Treasury Of New DB2 6.1 Features, Part 1: Query Enhancements

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    LaserVault

    Integrate Virtual Tape to Automate Your Backups And Strengthen Your Ability To Recover From Cyber Attacks And Disasters

    With most IT departments stretched thin, finding something that can quickly free up IT time is definitely a bonus. That’s why it’s important to stop and take a look at integrating virtual tape into your backup and recovery. Virtual tape is one of those technologies where once you have it, you’ll wonder why you didn’t do it sooner. See a demo and get a $50 gift card.

    But what is it about using virtual tape that makes it so worthwhile? Why is it that so many IBM i shops are already using or considering using virtual tape for all or part of their backup and recovery systems?

    Virtual tape and virtual tape libraries offer a way to both simplify and strengthen backup and recovery operations. By incorporating virtual tape technology, automation of backups becomes possible resulting in hundreds of hours saved annually for IT departments and personnel.

    “We needed to find a replacement that would lower the maintenance cost and reduce complexity of our backup and recovery functions without a major disruption to our operations.” David Fray, Director of Enterprise Systems, ABC Financial

    LaserVault ViTL is a virtual tape and tape library solution developed specifically for use with IBM Power Systems (from AS/400 to iSeries to Power 9s). With ViTL you can:

    • Replace physical tape and tape libraries and eliminate associated delays
    • Automate backup operations, including the ability to purge or archive backups
    • Remotely manage your backups – no need to be onsite with your server
    • Save backups to a dedupe appliance and the cloud
    • Recover your data at lightspeed greatly improving your ability to recover from cyberattacks
    • And so much more

    Sign-up now to see a ViTL online demo and get a $50 Amazon e-gift card when the demo is complete as our way of saying thanks for your time. Plus when you sign-up you’ll receive a free facts comparison sheet on using virtual tape vs tape so you can compare the functionality for yourself.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  Reach Your Modernization Goals. Register for the February 25 Webinar now!
    New Generation Software:  Ask us about Query, Reporting, and Analytics. Order a FREE Trial of NGS-IQ.
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Dallas, March 17-19

    Strengthening Dollar Curtails Global IT Spending Growth IBM i Predictions For 2015 From Around The Community

    2 thoughts on “Regurgitating Data With DB2 For i Data Change Table References”

    • seth.overbury@claytonhomes.com says:
      April 16, 2019 at 10:03 am

      This is very useful…especially for off-platform access. Is it possible to use select from final table under transaction control?

      Reply
    • Mike S says:
      January 17, 2020 at 11:21 pm

      Hi Seth – yes you can use final table under transaction control. The only thing is, if the final table result set is consumed and then the transaction is rolled back, the code that consumed the result set should know how to handle the fact that the results were invalidated.

      Reply

    Leave a Reply Cancel reply

Volume 15, Number 01 -- January 20, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL Software
WorksRight Software

Table of Contents

  • Faking Create Or Replace Table
  • Everybody Likes Shortcuts! Part 1, Navigation
  • Regurgitating Data With DB2 For i Data Change Table References

Content archive

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

Recent Posts

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

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 © 2022 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.