DB2Connection.BeginChain Method

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

Remarks

If you need to execute a large batch of insert, update, and delete statements, you can improve application performance by chaining your SQL statements, thereby reducing the number of network flows to the database server.

Before calling the BeginChain method in order to activate chaining, the DB2Connection object must be open. After you call the BeginChain method, the insert, update, and delete statements that you execute against the DB2Connection will be queued on the client until the EndChain method is called. After you call the EndChain method, these statements will be sent to the database server and executed.

Whether chaining is active or inactive, you can execute insert, update, and delete statements using the DB2CommandClass.ExecuteNonQuery method. But when chaining is active, all calls to DB2Command.ExecuteNonQuery will return -1. Since chained statements are all executed together after the EndChain method is called, the number of rows affected for a particular statement is unknown. Chained statements can be executed from either multiple DB2Command objects or a single DB2Command object. All DB2Command objects that are used for executing a series of chained statements must be created from the same DB2Connection 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. There are some data servers that do not support chaining. If a server does not support chaining, the IBM Data Server Provider for .NET will ignore 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 if chaining 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 have been queued, the IBM Data Server Provider for .NET will 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.

[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();