Regurgitating Data With DB2 For i Data Change Table References

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