An Introduction to the Data-change-table-reference Clause in DB2 UDB V8.1.4

by Roman B. Melnyk and Paul C. Zikopoulos

This article outlines result set retrieval from SQL data-change operations support in the IBM DB2 Universal Database (DB2 UDB) products as of Version 8.1.4. Result set retrieval from data-change operations (INSERT, UPDATE, and DELETE) is supported in DB2 UDB V8.1.4 with the new data-change-table-reference clause, which has been added to the FROM clause of the SQL subselect. The data-change-table-reference clause allows you to:

• Retrieve result sets from other SQL operations.
  
• Determine the contents of columns that are updated with default values or generated values, or columns whose values are altered by BEFORE triggers. It is especially important to be able to readily retrieve automatically generated values when they are used as primary key values.

The primary benefit of this feature is the performance advantage that can be realized when both data-change operations and the retrieval of intermediate result sets can be accomplished in a single unit of work (UOW).

The data-change-table-reference clause in DB2 UDB Version 8.1.4

Figure 1, which shows a partial syntax diagram for the SQL subselect, includes only those syntax fragments that form a path to the data-change-table-reference clause.

Figure 1. A partial syntax diagram for the SQL subselect, showing only those syntax fragments that form a path to the data-change-table-reference clause. The highlighted clause in each fragment points to the next fragment in the diagram.

To illustrate how this feature works, let’s try a simple example using the SAMPLE database that comes with DB2 UDB. You can try this yourself; we’re showing the code as it would look inside a DB2 command script.

First, we’ll clone the EMPLOYEE table and call the new table EMPTEMP:

connect to sample;

create table emptemp like employee;

export to emptemp.ixf of ixf messages export.msg

  select * from employee;

load from emptemp.ixf of ixf messages load.msg

  insert into emptemp;

The following queries show that there are 32 rows in the EMPTEMP table, and that the current salary for employee ‘000010’ is $52,750.00:

select count(*) as before_count from emptemp;

select salary from emptemp where empno = ‘000010’;

Now we’re ready to try a query with an embedded data-change table reference. Suppose we wanted to give employee ‘000010’ a 5% raise and retrieve her old salary, both in the same UOW. Because the columns in the target of the data-change operation (UPDATE) become the columns of the intermediate result table, they can be referenced by name (in this case, SALARY) in the select list of the query:

select salary from old table

  (update emptemp set salary = salary * 1.05

   where empno = ‘000010’);

By specifying the keywords OLD TABLE, we have requested that the intermediate result table of the data-change-table-reference contain row values from before the data-change operation has completed. In this case, the returned salary value is again $52,750.00.

If we now decide to delete the employee record for employee ‘000010’, but want to retrieve the updated salary value ($55,387.50) in the same UOW, we can issue the following statement:

select salary from old table

  (delete from emptemp

   where empno = ‘000010’);

The following query shows that there are now 31 rows in the EMPTEMP table:

select count(*) as after_count from emptemp;

Dropping the table and the database connection returns the SAMPLE database to initial conditions:

drop table emptemp;

connect reset;

As we’ve already mentioned, the keywords OLD TABLE specify that the intermediate result table of the data-change-table-reference is to contain row values from before the data-change operation has completed. You have two other options. NEW TABLE specifies that the intermediate result table is to contain row values from just after the data-change operation, but before referential integrity evaluation and the firing of defined AFTER triggers. And FINAL TABLE specifies that the intermediate result table is to contain row values from after the data-change operation, referential integrity evaluation, and the firing of defined AFTER triggers.

Not all intermediate result table types can be specified with all data-change operations. Table 1 summarizes which combinations are valid.

Table 1. Allowable intermediate result table types for each of the supported data-change operations. For example, OLD TABLE is the only type that can be specified when the data-change operation is DELETE.