Application Design and Tuning for Performance › Avoiding Redundant Statements › Multi-Row Operations › MERGE Statement

MERGE Statement

Applications often interface with other applications. In these situations, an application may receive a large quantity of data that applies to multiple rows of a table. Typically, the application performs a blind update. That is, the application attempts to update the rows of data in the table, and if any update fails because a row was not found, the application inserts the data. In other situations, the application may read all of the existing data, compare the data to the new incoming data, and then programmatically insert or update the table with the new data.

DB2 9 supports this type of processing using the MERGE statement. The MERGE statement updates a target (table, view, or the underlying tables of a full select) using the specified input data. Rows in the target that match the input data are updated as specified, and rows that do not exist in the target are inserted. MERGE can use a table or an array of variables as input.

Because the MERGE operates against multiple rows, you can code it as ATOMIC or NOT ATOMIC. The NOT ATOMIC option allows rows that have been successfully updated or inserted to remain if others have failed. Use the GET DIAGNOSTICS statement with NOT ATOMIC to determine the updates or inserts that failed. As with the multi-row INSERT operation, limit the use of GET DIAGNOSTICS due to the high CPU overhead.