DB2 10.5 for Linux, UNIX, and Windows

DB2Connection.BeginChain method

Marks the beginning of a chain of insert, update, and delete statements to be sent to the database server.

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

Syntax

[Visual Basic]
Public Sub BeginChain()
[C#]
public void BeginChain();
[C++]
public: void BeginChain();
[JScript]
public function BeginChain();

Exceptions

Exception type	Condition
InvalidOperationException	The connection is not open.

Remarks

You can improve the performance of large batch insert, update, and delete statements by chaining SQL statements. Chaining SQL statements can improve application performance by reducing the number of network flows to the database server.

You must open the DB2Connection object before you call the BeginChain method to activate the chaining of SQL statements. After you call the BeginChain method, the insert, update, and delete statements that you run under the DB2Connection object are queued on the client until the EndChain method is called. After you call the EndChain method, these statements are sent to the database server.

Whether chaining is active or inactive, you can call the DB2CommandClass.ExecuteNonQuery method to run insert, update, and delete statements. But when chaining is active, all calls to DB2Command.ExecuteNonQuery returns -1. Since chained statements are all run together after the EndChain method is called, the number of rows that are affected for a particular statement is unknown. Chained statements can be run from either multiple DB2Command objects or a single DB2Command object. All DB2Command objects that are used for running a series of chained statements must be created from the same DB2Connection object where chaining was activated.

For connections to database servers that support chaining, the Chaining property is set to true when the BeginChain method is called, and is reset back to false when the EndChain method is called. If a server does not support chaining, the IBM® Data Server Provider for .NET ignores the BeginChain and EndChain requests, leave the DB2Connection.Chaining property in a false state, and submit all statements to the database server individually. You can determine whether chaining of SQL statements is active by checking the Chaining property.

There is no limit to the number of statements that can be chained for a single DB2Connection object. However, after 2,147,483,646 statements are queued, the IBM Data Server Provider for .NET internally close the chain, submit the statements, and restart the chain. Additionally, once the communications buffer between the application and the database server (usually 32KB) is filled, the buffer's contents are sent to the server and saved there until EndChain is called. You can adjust the size of this communications buffer with the rqrioblk configuration parameter.

For optimal performance, use parameter markers in your insert, update, and delete statements.

You cannot chain SQL statements in a connection to DB2® for z/OS® server that is enlisted in an XA transaction.

Example

[Visual Basic, C#] The following example uses chaining to insert 10000 rows into the STAFF table.

[Visual Basic]
Dim con As DB2Connection = new DB2Connection("DATABASE=sample;")
Dim cmd As DB2Command = con.CreateCommand()
con.Open()

' Initialize an insert statement using parameter markers
cmd.CommandText = "INSERT INTO STAFF(ID) VALUES( ? )"

' Add a parameter
Dim p1 As DB2Parameter = cmd.Parameters.Add("@ID", DB2Type.Integer )

' Start the chain
con.BeginChain()

Try
   ' Loop to add 10000 rows
   Dim I As Int32
   For I = 1 To 10000
      ' Set the parameter value
      p1.Value = I

      ' Execute the command. 
      ' Since chaining is active, this statement is now added
      '   to the chain
      cmd.ExecuteNonQuery()
   Next I

   ' Execute the chain
   con.EndChain()
Catch db2Ex As DB2Exception
   Dim db2Error As DB2Error
   
   ' Loop through all the errors
   For Each db2Error in db2Ex.Errors
      Console.WriteLine("SQLSTATE =" & db2Error.SQLState )
      Console.WriteLine("NativeErr=" & db2Error.NativeError )
      Console.WriteLine("RowNumber=" & db2Error.RowNumber )
      Console.WriteLine( db2Error.Message )
   Next DB2Error
Finally
   ' Explicitly turn chaining off in case it is still on
   If (con.Chaining) Then
      con.EndChain()
   End If 
End Try

con.Close()[C#]
DB2Connection con = new DB2Connection("DATABASE=sample;");
DB2Command cmd = con.CreateCommand();
con.Open();      

// Initialize an insert statement using parameter markers
cmd.CommandText = "INSERT INTO STAFF(ID) VALUES( ? )";

// Add a parameter
DB2Parameter p1 = cmd.Parameters.Add("@ID", DB2Type.Integer );

// Start the chain
con.BeginChain();

try
{
   // Loop to add 10000 rows
   for( Int32 i = 1; i <= 10000; i++ )
   {
      // Set the parameter value
      p1.Value = i;

      // Execute the command. 
      // Since chaining is active, this statement is now added
      //   to the chain
      cmd.ExecuteNonQuery();
   }

   // Execute the chain
   con.EndChain();
}
catch( DB2Exception db2Ex )
{
   // Loop through all the errors
   foreach( DB2Error db2Error in db2Ex.Errors )
   {            
      Console.WriteLine("SQLSTATE =" + db2Error.SQLState );
      Console.WriteLine("NativeErr=" + db2Error.NativeError );
      Console.WriteLine("RowNumber=" + db2Error.RowNumber );
      Console.WriteLine( db2Error.Message );
   }                  
}
finally
{
   // Explicitly turn chaining off in case it is still on
   if( con.Chaining )
   {
      con.EndChain();
   }
}

con.Close();