Tuesday, September 12, 2023

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 * 
05            ('LIMA',CURRENT_TIMESTAMP),
07            ('ALPHA',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:

---------  --------------------------
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:

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:

---------  --------------------------

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 ;

11             FROM FINAL TABLE
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-'

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.


  1. Hi Simon,
    I found this too, a while back when looking for a way to record the data impacted by an update.
    z/DB2 has that already.
    Maybe it will be added in a future release of i/DB2?

  2. This is wonderful, Simon. Thanks for the detailed use explanation.

  3. maybe useful when you have a bunch of autogenerated values (auto timestamps, identity) ... you can insert the record in one shot and fetch the record with the correct autogen fields right away...

  4. Hi Simon, Your comment about doing RPG Code in FREE is great unless you are supporting a ton of legacy code and you do not have time to convert it.

    1. I also support an ERP with lots of legacy code.

      If it is RPG3 I convert it using this method described here, and then make my changes.

      If it is RPG4 I still insert free format code into the existing source code.

      If it is a new program then we should all be writing it in totally free format.

  5. I love this feature!!


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.