Modifying Data with Embedded SQL

Previously, I discussed the capability to update and delete single rows based on cursor positioning. You can also embed pure set-level processing UPDATE , DELETE , and INSERT SQL statements into a host language program.

Simply code the appropriate SQL statement, and delimit it with EXEC SQL and END-EXEC . The statement can contain host variables . When issued in the program, the statement is processed as though it were issued interactively. Consider the following example:

 EXEC SQL     UPDATE EMP         SET SALARY = SALARY * 1.05         WHERE EMPNO = :EMPNO END-EXEC. EXEC SQL     DELETE FROM PROJACT     WHERE ACENDATE < CURRENT DATE END-EXEC. EXEC SQL     INSERT INTO DEPT        (DEPTNO,         DEPTNAME,         MGRNO,         ADMRDEPT)     VALUES        (:DEPTNO,         :DEPTNAME,         :MGRNO,         :ADMRDEPT) END-EXEC. 

These three SQL statements are examples of coding embedded data modification statements ( UPDATE , DELETE , and INSERT ) using host variables.

Embedded Modification SQL Guidelines

The following guidelines should be followed to ensure that optimal SQL data modification techniques are being deployed in your DB2 applications.

Favor Cursor-Controlled UPDATE and DELETE

Favor UPDATE and DELETE with a cursor specifying the FOR UPDATE OF clause over individual UPDATE and DELETE statements that use the set-level processing capabilities of SQL.

Set-level processing is preferable, however, when an OPEN , a FETCH , and a CLOSE are performed for each UPDATE or DELETE . Sometimes, performing these three actions cannot be avoided (for example, when applying transactions from a sequential input file).

Use FOR UPDATE OF to Ensure Data Integrity

If a program is coded to SELECT or FETCH a row and then, based on the row's contents, issue an UPDATE or DELETE , use a cursor with FOR UPDATE OF to ensure data integrity. The FOR UPDATE OF clause causes a lock to be taken on the data page when it is fetched , ensuring that no other process can modify the data before your program processes it. If the program simply SELECT s or FETCH s without the FOR UPDATE OF specification and then issues an SQL statement to modify the data, another process can modify the data in between, thereby invalidating your program's modification, overwriting your program's modification, or both.

CAUTION

When programming pseudo-conversational CICS transactions, FOR UPDATE OF is not sufficient to ensure integrity. A save and compare must be done prior to any update activity.

Specify a Primary Key in the WHERE Clause of UPDATE and DELETE Statements

Never issue independent, embedded, non-cursor controlled UPDATE and DELETE statements without specifying a primary key value or unique index column values in the WHERE clause unless you want to affect multiple rows. Without the unique WHERE clause specification, you might be unable to determine whether you have specified the correct row for modification. In addition, you could mistakenly update or delete multiple rows.

Of course, if your desired intent is to delete multiple rows, by all means, issue the embedded, non-cursor controlled UPDATE and DELETE statement. Just be sure to test the statement thoroughly to ensure that the results you desire are actually achieved.

Use Set-at-a-Time INSERT s

When you need to issue INSERT statements in your program, try to use the set-level processing capabilities. Using the set-level processing of INSERT is usually possible only when rows are being inserted into one table based on a SELECT from another table. For example

 INSERT INTO user.EMP_SMITH    SELECT *    FROM DSN8810.EMP    WHERE LASTNAME = 'SMITH'; 

This SQL statement causes every row in the EMP table, for employees whose last name is "Smith", to be inserted into the EMP_SMITH table.

Consider Multi-Row INSERT s Using Host Variable Arrays

As discussed previously in this chapter, as of DB2 V8 it is possible to insert multiple rows with a single INSERT using host-variable arrays. Consider using this approach when your application program logic requires many rows to be inserted during a single program invocation.

Use LOAD Rather Than Multiple INSERT s

Favor the LOAD utility over an application program performing many insertions in a table. If the inserts are not dependent on coding constraints, format the input records to be loaded and use the LOAD utility. If the inserts are dependent on application code, consider writing an application program that writes a flat file that can subsequently be loaded using the LOAD utility. In general, LOAD outperforms a program issuing INSERT s.

One reason LOAD outperforms INSERT s is logging overhead. You can LOAD data without logging by specifying LOG NO , but INSERT s are always logged. By removing the overhead of logging, LOAD performance improves significantly. Of course, you will need to take an image copy backup of the data after the LOAD to preserve recoverability.