Download Precompiler User`s Manual - ALTIBASE Customer Support

10.2 Using Host Array Variables in Embedded SQL Statements
EXEC SQL FOR :cnt DELETE FROM EMPLOYEES
WHERE DNO = :a_dno;
10.2.6 ATOMIC FOR Clause
When the ATOMIC FOR Clause is used with an array-type input host variable in an embedded SQL
statement, multiple iterations of the statement corresponding to each of the array elements are
combined and processed all at the same time using so-called “Atomic Array Insert”.
Therefore, when using ATOMIC FOR, if the execution of even one of the iterations fails, then the execution of the entire statement fails. The individual resultant values are never written to disk or
shared with other processes. That is, either none of the values are inserted, or all of them are
inserted.
Figure 10-1 Values Resulting from Array Insert and Atomic Array Insert Operations
Array Insert
Atomic Array Insert
Insert SUCCESS
Insert SUCCESS
Insert
FAIL
Insert SUCCESS
Insert
FAIL
Insert
FAIL
Rollback
While “Non-Atomic Array Insert” (i.e. the use of an array input host variable with an INSERT embedded SQL statement) already offers the advantage of reduced communication costs, Atomic Array
Insert increases performance even further, because it reduces the number of statements that must
be executed.
Table 10-1 Difference between Array Insert and Atomic Array Insert
Array Insert
Atomic Array Insert
Number of Statement Executions
Number of Array Elements
One
Number of Resultant Values
Number of Array Elements
One
Speed
Fast
Faster
10.2.6.1 Syntax
EXEC SQL ATOMIC FOR <:host_var | constant> {INSERT … }
10.2.6.2 Arguments
<:host_var>: This is used to set the number of array elements to be processed. This variable doesn't
have to be declared in the host variable declaration section.
<constant>: This is used to set a fixed number of array elements to be processed.
141
Using Arrays in Embedded SQL Statements