• 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
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    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

  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20
  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19

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