- SELECT from INSERT, UPDATE whenever possible. This feature is great for getting the values associated with identity, sequence, and any other database column keys while doing an INSERT or UPDATE SQL operation.
This capability provides for the capturing of generated key values needed to be returned to the application during an UPDATE or INSERT statement. Instead of executing two SQL statements a task can be handled with a one SQL call and have all the application data to continue processing.
- Use MERGE to reference the database and make sure the data is there and updated. Avoid complex program logic to perform the data checking and then update.The DB2 SQL MERGE statement improves application performance by automatically checking to see if the data entry exists. If the data exists, DB2 performs an update; if not, DB2 inserts the data. This MERGE SQL statement allows DB2 to put the data into a table in a single SQL statement instead of first doing a SQL SELECT and then the appropriate INSERT or UPDATE SQL statement.
The MERGE SQL statement operation can also work with multiple row operations using an input array. When multiple input rows are used, the phrase NOT ATOMIC CONTINUE ON SQL EXCEPTION can be specified. This phrase allows DB2 to process each row independently. If an error occurs when merging a row, only the error row is backed out and processing continues. DB2 will take each of the multiple input rows, then determine the correct insert or update operation to integrate it into the table. When there are triggers over the table, each successfully merged row fires the trigger and the appropriate actions are taken. Error rows encountered during the MERGE process don’t fire triggers.
Experienced DB2 Consulting and Training