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

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.