Before V6R1, INSERT and SELECT were two separate operations in DB2 for i5/OS. To retrieve or query inserted column values, you could use a subsequent SELECT to query the target of an INSERT. As mentioned in "Leap Ahead with DB2 for V6R1" (March 2008, article ID 21166 at SystemiNetwork.com), this method can be a big challenge with identity columns generated by DB2 because it requires executing an additional SQL statement. The INSERT statement is executed to add the new row. Then, a SELECT statement with the Identity_Val_Local function retrieves the generated value. This two-statement solution is also problematic when multiple rows are inserted, because the Identity_Val_Local function returns only the generated key value for the last row inserted.

The SELECT from INSERT function introduced in V6R1 provides a simpler solution by combining INSERT and SELECT into a single SQL statement. As row values are inserted, the database manager places a temporary copy of all inserted row values into an intermediate result table (IRT). This IRT is managed internally and is transparent to the application or user requesting SELECT from INSERT. After all row values have been inserted, the SELECT statement queries row data in the IRT.

Benefits of this new function include

1. The result set contains generated column values for inserted rows.
2. For tables shared among multiple clients, the result set contains only rows inserted by the client issuing the SELECT from INSERT statement, and none that may have been inserted concurrently by other clients.
3. Performance may improve. For example, in a distributed environment, request flow occurs only between the client and the relational database server.

New SQL Syntax for SELECT from INSERT

With this new function, you can now specify an INSERT statement in the FROM clause. Previously, the FROM clause table reference could be a single table, a nested table expression, a table function, or a joined table. Now you use a new data-change-table-reference option to specify a SELECT from INSERT statement. Figure 1 illustrates the data-change-table-reference syntax. Either FROM FINAL TABLE or FROM NEW TABLE precedes the INSERT statement, which is enclosed in parentheses. An optional correlation-clause can follow INSERT. See the DB2 for i5/OS SQL reference manual (publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/db2/rbafzintro.htm&tocNode=int_213851 - Click Here) for further information. A basic statement would look like this:

SELECT column1
  FROM FINAL TABLE
  (INSERT INTO t1 VALUES(1,'John Smith') )

The difference between FINAL TABLE and NEW TABLE is subtle. Both make temporary copies of inserted row data available to the outer SELECT. However, FINAL TABLE ensures that no changes are made to inserted rows until after the entire statement (SELECT) completes. In contrast, with NEW TABLE, an after trigger could run and change one or more inserted rows such that new row data could differ from the temporary copy in the IRT.

The SELECT from INSERT syntax includes the option to use an ORDER BY INPUT SEQUENCE clause, which returns the data in the order it was inserted into the table or view.

When an INSERT is specified in a FROM clause, there is an optional INCLUDE column clause. This INCLUDE clause can define one or more new columns in the IRT, adding to columns implicitly defined by the INSERT target table or view. The SELECT statement can then reference these new columns, along with columns of the INSERT target.

The following examples illustrate the new syntax of SELECT from INSERT in DB2 for i5/OS.

Example 1. Given the definition of the table ORDERS in Figure 2, you first insert customer numbers into the ORDERS table using the VALUES clause form of INSERT. Then, let the database manager assign a value for the ordernum column of the table. In Figure 2, the assigned value is generated, but it could also be a default value, a special register value, the result of an expression, or a value provided by a trigger. Wrap the INSERT statement with a SELECT statement so assigned order number values are returned to the invoking application. You can ensure that the order numbers are returned in the same row order in which the data was specified in the VALUES clause, by stipulating INPUT SEQUENCE in the ORDER BY clause, as Figure 3 shows.

Example 2. Given the ORDERS table in Figure 2, specify that the order numbers be returned in the reverse row order in which the data was placed in the VALUES clause for the inserted rows. You can use an INCLUDE clause to create a new column and reference this new column in the ORDER BY clause to order the rows of the result, as Figure 4 shows.

Example 3. Assuming the same ORDERS table as in Figure 2 and a second table named QUEUE (Figure 5), insert data into the ORDERS table from the QUEUE table by using a subquery (instead of the VALUES clause). In the subquery of the INSERT statement, select

1. customer numbers (custno), to be inserted into ORDERS
2. number in queue (num), used to order the rows of the result of the outer SELECT

As in Example 2, use the INCLUDE clause to include the inc column in the result set, and specify inc again in the ORDER BY clause to order the rows of the result (Figure 6).

Example 4. As part of your application reengineering, you reduce an existing table in size by replacing a three-column key with an identity column. Figure 7 displays the existing table definition, and Figure 8 shows the new table definition.

You need to insert the data into the new table and insert the original three-column key value to return the generated identity value (new key) so that the relationship to the old key can be maintained (Figure 9).

Note that the order of the fields in the INCLUDE list — keypt1, keypt2, and keypt3 — must match the order in the subselect from table_old.

How to Use SELECT from INSERT on the System i

The following scenarios describe a few ways to use SELECT from INSERT support in DB2 for i5/OS, and some of the processes involved.

Retrieve a generated column value for a single inserted row.

Preconditions:

• Target table of INSERT exists and includes a column with generated values.
• INSERT requester is authorized to add rows to the target table.

Flow:

1. An application program is called containing an SQL SELECT INTO statement, with an INSERT statement in the FROM clause. The INSERT uses the VALUES clause to insert a single new row into a table (Figure 10).
2. The application program executes the SELECT INTO statement, passing an input column value (custnum host variable) to the database manager.
3. DB2 processes the INSERT request (wrapped by the SELECT INTO) and inserts a new row into the ORDERS table. This includes generating a value for the ordernum column.
4. DB2 processes the SELECT INTO request and returns the generated ordernum column value to the application program.

Retrieve an extra column associated with rows inserted using a subquery.

Preconditions:

• Two tables exist: the target of the INSERT and the source of queried data to be inserted.
• INSERT requester is authorized to add rows to the target table.
• INSERT requester is authorized to read data from the queried table.

Flow:

1. Requester establishes an SQL connection to the DB2 for i5/OS database.
2. Requester runs an SQL script containing a SELECT statement with an INSERT statement in the FROM clause. The INSERT uses a query to determine the rows to be inserted and specifies an INCLUDE column to be added to the result set (not inserted into the target table), as Figure 11 shows.
3. DB2 processes the INSERT request (wrapped by the SELECT). Rows from the query of the sales_summary table are inserted into the bonus_candidates table. Those same rows are also inserted into a temporary result table, along with ytd_sales column values from the sales_summary table.
4. The script processor fetches rows from the temporary result table (result set for the outer SELECT). Each row returned includes all column values inserted into the bonus_candidates table, as well as corresponding ydt_sales column values.

Retrieve a single value, resulting from a variable number of rows inserted into a table using a query.

Preconditions:

• Two tables exist: the target of the INSERT and the source of queried data to be inserted.
• INSERT requester is authorized to add rows to the target table.
• INSERT requester is authorized to read data from the queried table.

Flow:

1. Requester calls an application written to the CLI SQL interface and passes a single parameter value that specifies the rows to be inserted into the table.
2. The CLI program establishes an SQL connection to the DB2 for i5/OS database.
3. The CLI program prepares an SQL statement to both insert a varying number of rows into a table and return a single value from the result set of inserted rows (Figure 12).
4. The CLI program binds program variables for both the input parameter marker (WHERE clause) and the output parameter marker of the previously prepared statement, for example, using the SQLSetParam() API. The program variable for the input parameter marker is set to the input parameter value passed by the CLI program caller.
5. The CLI program executes the prepared statement.
6. DB2 processes the INSERT request (wrapped by the SELECT). Rows from the query of the sales_summary table are inserted into the bonus_candidates table. Those same rows are also inserted into a temporary result table. Rows from the temporary result table are used as input to the AVG() function in the outer query, and the result of that AVG() is returned in the program variable for the output parameter marker.

Using JDBC, insert a row into a table and retrieve all generated column values for the inserted row.

Preconditions:

• Target table of INSERT exists with at least one column containing generated values.
• INSERT requester is authorized to add rows to the target table.

Flow:

1. Requester establishes an SQL connection to the i5/OS database.
2. Requester runs a JAVA program that uses the RETURN_GENERATED_KEYS option of the executeUpdate() method, and the getGeneratedKeys() statement method, as Figure 13 shows.
3. The Java program runs the executeUpdate() method, causing the JDBC driver to wrap the INSERT statement with a SELECT statement.
4. DB2 processes the INSERT request (wrapped by the SELECT). A new row is inserted into the authors table, and values are generated for any columns, such as an IDENTITY column or columns with default values for which an explicit column value was not supplied with the INSERT. The same row data (with generated column values) is inserted into a temporary result table associated with the cursor for the SELECT supplied by the JDBC driver.
5. The Java program runs the getGeneratedKeys()method, which opens the cursor for the SELECT and provides access to the cursor's result set (named rs). Other result set methods run to retrieve the generated column values.

Note: You can use an alternative syntax of theexecuteUpdate method, where the name of the generated column is specified instead of the Statement.RETURN_GENERATED_KEYS option.

The last two figures are SELECT from INSERT examples using embedded SQL in programs. Figure 14 is a C program with a VALUES INTO statement. Figure 15 is an RPG program doing blocked insert from data contained in a host structure.

Improving SQL Performance

V6R1's new SELECT from INSERT function combines two operations into one SQL statement. This enhancement lets users retrieve or query information for either a single or multiple row INSERT operation. Using INSERT in the FROM clause of a SELECT, users can retrieve or query the inserted row data. This ability is especially useful for retrieving generated column information such as ROWID, identity column, sequence, dynamic values, or generated expressions during an insert. It can also improve SQL application performance because what used to require two separate functions (and the overhead of those two separate functions) can now be done with one.

Jinmei Shen is a software developer, and she has been working IBM DB2 for i around 10 years in IBM Rochester, MN.

Karl Hanson is a software developer in the IBM Rochester, MN development lab. He has worked on operating systems for System/38 and the AS/400 product line, including System i. For the last ten years he has been a part of the IBM DB2 for i development team.