Selecting data I just inserted into a Table

This piece of SQL was brought to my attention by a friend who works with Db2 for z/OS (mainframe). He said that at times he uses what I am going to explain to validate that the data he inserted into Table has the values he expected.

He refers to it as "Select final table", which will be the name I am going to use for it. I did find this referred to in the IBM Documentation web site, there is a link to this page at the bottom of this post. The Db2 for z/OS version has more functionality than one for Db2 for i. This has me puzzled, why they are not the same?

With Db2 for i "Select final table" can only be used with a SQL Insert subselect. It returns the rows that were inserted into the table.

The Table, TESTTABLE, I will be using in these examples has two columns:

1. MY_COLUMN:  Defined as a variable length character of 20 long
2. MY_TIMESTAMP:  Timestamp column

In this first SQL statement I am doing a mass insert of five rows into the Table:

01 SELECT * 02 FROM FINAL TABLE 03 (INSERT INTO TESTTABLE 04 VALUES('MIKE',CURRENT_TIMESTAMP), 05 ('LIMA',CURRENT_TIMESTAMP), 06 ('SIERRA',CURRENT_TIMESTAMP), 07 ('ALPHA',CURRENT_TIMESTAMP), 08 ('FOXTROT',CURRENT_TIMESTAMP))

Line 1: Select all of the columns from the Table.

Line 2: FINAL TABLE displays the data from the inserted columns from the Table they were inserted into.

Lines 3 – 8: The Insert statement contains within parentheses, or brackets, depending upon which form of English you are familiar with.

When executed the displayed results are:

MY_COLUMN MY_TIMESTAMP --------- -------------------------- MIKE 2023-09-10 09:08:06.755379 LIMA 2023-09-10 09:08:06.755379 SIERRA 2023-09-10 09:08:06.755379 ALPHA 2023-09-10 09:08:06.755379 FOXTROT 2023-09-10 09:08:06.755379

What if I only want to insert values into some of the columns of the Table. Providing the Insert statement is valid it can be used:

01 SELECT MY_COLUMN,MY_TIMESTAMP 02 FROM NEW TABLE 03 (INSERT INTO TESTTABLE (MY_COLUMN) 04 VALUES('CHARLIE'))

Line 1: I want to display both of the Table's columns in the results.

Line 2: Here I have used NEW TABLE, rather than FINAL TABLE. I can use either as they have the same functionality.

Lines 3 and 4: I am only inserting a value into the MY_COLUMN column.

The result is:

MY_COLUMN MY_TIMESTAMP --------- -------------------------- CHARLIE <NULL>

As I did not give a value for the MY_TIMESTAMP column it contains its default, null.

I only see limited usefulness with interactive SQL. I do see it as potentially useful in a RPG program:

01 **free 02 dcl-s TestColumn varchar(20) ; 03 dcl-s TestTimestamp timestamp ; 04 dcl-ds Data qualified ; 05 My_Column varchar(20) ; 06 My_Timestamp timestamp ; 07 end-ds ; 08 TestColumn = 'WHISKY' ; 09 TestTimestamp = %timestamp ; 10 exec sql SELECT MY_COLUMN,MY_TIMESTAMP INTO :Data 11 FROM FINAL TABLE 12 (INSERT INTO TESTTABLE 13 VALUES(:TestColumn, :TestTimestamp)) ;

Line 1: If you are not using totally free RPG you need to start doing so before you get left behind.

Lines 2 and 3: Definition of a couple of variables I will be using.

Lines 4 – 7: Defining a data structure that contains subfields for the two columns in the Table I will be inserting into.

Line 8: Being part-Scot I know it is spelled "Whisky", not "Whiskey"!

Line 9: Update the variable with the current timestamp.

In the real world there could be a whole lot of other code before I get to insert into the Table. I want to be able to check that the values in the variables I am using contain the data I expected.

Lines 10 – 13: This is the "Select final table" statement.

Line 10: I take the values from the Tables MY_COLUMN and MY_TIMESTAMP columns and move them into the data structure I defined on lines 4 – 7.

Line 11: I am using FINAL TABLE in this statement.

Lines 12 and 13: I am inserting into the table the values in the TestColumn and TestTimestamp variables.

If I place a debug breakpoint after line 13 I can check what I inserted into the Table:

> EVAL data DATA.MY_COLUMN = 'WHISKY ' DATA.MY_TIMESTAMP = '2023-09-10-10.33.17.663199'

In this example there were the values I wanted to insert.

 

You can learn more about the "Select final table" SQL statement from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.