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

Tuesday Apr 20th 2004 by DatabaseJournal.com Staff
Share:

This article outlines result set retrieval from SQL data-change operations supported in DB2 as of Version 8.1.4. Learn how to retrieve result sets from other SQL operations and determine the contents of columns that are updated with default values or generated values, or columns whose values are altered by BEFORE triggers.

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.

Data-change Operation

Intermediate Result Table Type

OLD TABLE

NEW TABLE

FINAL TABLE

INSERT

no

yes

yes

UPDATE

yes

yes

yes

DELETE

yes

no

no

To see what you can do when the data-change operation is INSERT, consider the following simple scenario involving a CUSTOMER table and an ORDERS table. Again, you can try these examples yourself; remember to connect to some database, such as the SAMPLE database.

First, create the CUSTOMER table and populate it with some data:

create table customers (
  customer_id integer generated always as identity (start with 1000),
  customer_name varchar(16),
  primary key (customer_id) );

insert into customers (customer_name) values ('Jones');
insert into customers (customer_name) values ('King');
insert into customers (customer_name) values ('Smith');

The primary key for this table is CUSTOMER_ID, which is an automatically generated identity column. To retrieve the generated identity column value that is being used as a customer number, use the data-change-table-reference clause instead:

select * from final table
  (insert into customers (customer_name) values ('Hoffmann'));

This statement returns:

CUSTOMER_ID CUSTOMER_NAME
----------- ----------------
       1003 Hoffmann

  1 record(s) selected.

Next, create the ORDERS table and populate it with some data:

create table orders (
  order_id integer generated always as identity (start with 10000),
  customer_id integer not null,
  part_name varchar(16) not null,
  foreign key (customer_id) references customers (customer_id) );

insert into orders (customer_id, part_name)
  values (1001, 'Widget');
insert into orders (customer_id, part_name)
  values (1003, 'Thingamabob');

CUSTOMER_ID is a foreign key in the ORDERS table, linking this table to the CUSTOMERS table. ORDER_ID is an automatically generated identity column in the ORDERS table. To retrieve the generated identity column value that is being used as an order number, use the data-change-table-reference clause instead:

select * from final table
  (insert into orders (customer_id, part_name)
   values (1002, 'Widget'), (1000, 'Thingamabob') )
  order by customer_id;

Note that we have specified an ordering column (CUSTOMER_ID) for the inserted rows; the query results are returned in customer number order. Another way to sort the results is to use the new INPUT SEQUENCE keywords in the ORDER BY clause. These keywords specify that rows are to be returned in the order in which they were inserted. For example:

select * from final table
  (insert into orders (customer_id, part_name)
   values (1002, 'Widget'), (1000, 'Thingamabob') )
  order by input sequence;

Yet another way to sort the results is to define an extra "include" column whose values are specified in the VALUES clause, and to use this column as an ordering column for the inserted rows:

select * from final table
  (insert into orders (customer_id, part_name)
   include (insertnum integer)
   values (1002, 'Widget', 2), (1000, 'Thingamabob', 1) )
  order by insertnum;

This statement returns:

ORDER_ID    CUSTOMER_ID PART_NAME        INSERTNUM
----------- ----------- ---------------- -----------
      10003        1000 Thingamabob                1
      10002        1002 Widget                     2

  2 record(s) selected.

You can use the SET clause to define INCLUDE column values within an UPDATE statement and return both the old and the new column values for a row. Returning to our EMPLOYEE table example, we can use the SET clause as follows:

select salary, oldsalary from final table
  (update employee include (oldsalary decimal(9,2))
   set oldsalary = salary,
   salary = salary * 1.05
  where empno = '000100');

This statement returns:

SALARY      OLDSALARY
----------- -----------
   27457.50    26150.00

  1 record(s) selected.

Here are a couple of important points to remember about the data-change-table-reference clause:

  • If any error occurs during the processing of an SQL data-change statement, the modified rows are rolled back. This includes errors raised by constraints processing and triggers.
  • If a user with SELECT privilege, but without the appropriate data-change (for example, INSERT) privilege on the target table, attempts a data-change table reference in a subselect, the missing privileges cause the data-change operation to fail. As a result, the entire SELECT statement will fail.

Conclusion

The new data-change-table-reference clause, which allows you to retrieve intermediate result sets from other SQL operations (data-change operations, such as INSERT, UPDATE, and DELETE), or to determine the contents of columns that are affected by default values, generated values, or BEFORE triggers, simplifies application development and helps to improve the performance of your application. The performance advantage comes from the fact that this feature enables a data-change operation and the retrieval of intermediate results within the same unit of work.

About the Authors

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved