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

Multi-Row Insert

As with multi-row FETCH reading multiple rows per FETCH statement, a multi-row INSERT can insert multiple rows into a table in a single INSERT statement. The INSERT statement must contain the FOR n ROWS clause, and the host variables referenced in the VALUES clause must be host variable arrays. IBM states that multi-row INSERTs can result in as much as a 25 percent CPU savings over single row INSERTs. In addition, multi-row INSERTs can dramatically impact the performance of remote applications because the number of statements that are issued across a network can be reduced.

You can code the multi-row INSERT two ways:

ATOMIC

Specifies that if one INSERT fails, the entire statement fails

NOT ATOMIC

Specifies that any failure of any of the INSERTs impacts only that one INSERT of the set.

As with the multi-row FETCH, the GET DIAGNOSTICS statement is not initially necessary. We recommend that you limit its use due to the high CPU overhead. In the case of a failed NOT ATOMIC multi-row INSERT, you receive an SQLCODE of -253 if one or more of the INSERTs failed. Only then should you use GET DIAGNOSTICS to determine which one failed. If you receive an SQLCODE of zero, all INSERTs succeeded, and you do not need to perform further analysis.