DB2Command.AtomicArrayInput Property

Gets or sets boolean value to determine whether to execute multi-row operations in atomic or non-atomic scope.

Namespace:: IBM.Data.DB2
Assembly:: IBM.Data.DB2 (in IBM.Data.DB2.dll)

Syntax

[Visual Basic]
Public Property AtomicArrayInput As Boolean
[C#]
public bool AtomicArrayInput {get; set;}
[C++]
public: __property bool get_AtomicArrayInput();
public: __property void set_AtomicArrayInput(bool);
[JScript]
public function get ArrayBindCount() : bool;
public function set AtomicArrayInput(bool);

Property value

A boolean value that specifies atomic operation for the multi-row insert, update, or delete. Default value is true.

Remarks

When performing multi-row insert, update or delete operations, scope of given multi-row command can be performed as an atomic or non-atomic operation. If AtomicArrayInput property is set to true, and the server does not support an atomic operation, the execution of multi-row operation throws an InvalidOperation exception.

In non-atomic command execution, the result of the multi-row insert, update, or delete operation would depend on whether the command was executed inside a transaction. If error is encountered during multi-row insert, update, or delete command that takes place outside of a transaction, an appropriate exception would be thrown with already successfully inserted, updated, or deleted rows being committed. However, when an error is encountered inside of a transaction during the multi-row insert, update, or delete operation, the decision to commit or rollback is delegated to the application. The Table 1 summaries the various multi-row insert, update, or delete operation scenarios.

Table 1. Summary of various multi-row insert, update, or delete operation scenarios
Transaction	AtomicArrayInput setting	Error occurred	Behavior	Affected number of rows
Yes	True or False	No	Entire change is either committed or rolled back at the end of a transaction	Affected number of row is equal to the sum of all rows affected by the SQL execution
Yes or No	True	Yes	Entire operation is rolled back	Affected number of row is 0
Yes	False	Yes	Partial commit or complete rollback at the end of a transaction	Not available due to the thrown exception
No	True	No	Entire operation is treated as a single unit of work (UOW)	Affected number of row is equal to the sum of all rows affected by the SQL execution
No	False	No	Command is executed as multiple UOWs	Affected number of row is equal to the sum of all rows affected by the SQL execution
No	False	Yes	Changes are partially committed	Not available due to the thrown exception

Example

[C#] The following example perform non-atomic multi-row insert using the AtomicArrayInput property

[C#]
public static void MyArrayBindSample(DB2Command cmd)
{
    int[] myArrayC1 = new int[3] { 10, 20, 30 };
    string[] myArrayC2 = new string[3] { "abc", "test", "zyz" };

    cmd.CommandText = "INSERT INTO T1 ( C1, C2) values ( ?, ? )";

    cmd.ArrayBindCount = 3;
    cmd.AtomicArrayInput = false;

    DB2Parameter Parm1 =  new DB2Parameter();
    Parm1.DB2Type = DB2Type.Integer;
    Parm1.Direction = ParameterDirection.Input;
    Parm1.Value = myArrayC1;
    cmd.Parameters.Add(Parm1);

    DB2Parameter Parm2 = new DB2Parameter();
    Parm2.DB2Type = DB2Type.Char;
    Parm2.Direction = ParameterDirection.Input;
    Parm2.Value = myArrayC2;
    cmd.Parameters.Add(Parm2);

    cmd.ExecuteNonQuery();

    cmd.ArrayBindCount = DB2Command.ArrayBindCountOff;
    cmd.Parameters.Clear();
}