The data import process may take a long time to be completed when you use the Microsoft OLE DB Provider for DB2 1.0 that is included with the SQL Server 2005 Feature Pack to perform bulk imports of IBM DB2 data

When you use the Microsoft OLE DB Provider for DB2 1.0 that is included with the Microsoft SQL Server 2005 Feature Pack to perform bulk imports of IBM DB2 data, the data import process may take a long time to be completed. This delay is most likely to occur when you use SQL Server Integration Services (SSIS) or SQL Server Analysis Services (SSAS) to import IBM DB2 tables that contain millions of rows of data.

The bulk import data rate will vary depending on several factors. These factors include the following:

• The hardware platform of the SQL Server 2005 system
• The hardware platform of the IBM DB2 system
• The network type, such as 100 Mb Ethernet or Gigabit Ethernet
• The network topology
• The IBM DB2 data types that are used in the DB2 tables that you import

In one specific environment, the bulk import data rate was approximately 1 GB per hour. In this case, the IBM DB2 database system and the SQL Server 2005 system were connected on the same Gigabit Ethernet network segment. The SQL Server 2005 system was running on a dual processor x64 server that was running 64-bit versions of SQL Server 2005 and Microsoft Windows Server 2003. When an IBM DB2 table that consisted of more than 8 million rows of data was imported, the process took approximately 70 minutes to be completed. For comparison purposes, the same IBM DB2 table was transferred to the SQL Server 2005 system in approximately 5 minutes by using FTP.

Note Because each environment is different, we cannot provide specific throughput rates when doing bulk imports of IBM DB2 data into SQL Server 2005.

The OLE DB Provider for DB2 1.0 has been updated to improve the overall performance of the OLE DB Provider for DB2 when you perform bulk imports of IBM DB2 data. The software update implements optimizations that include the following changes.

New Rowset Cache Size property added

The Rowset Cache Size property is an optional OLE DB data source initialization property. This property instructs the OLE DB Provider for DB2 to prefetch rows of data from a IBM DB2 database. At the same time, the OLE DB Provider for DB2 is instructed to process and return rows to the data consumer when the IRowset::GetNextRows method is called. For example, the data consumer may be SSIS or SSAS. This feature may improve performance in bulk read-only operations on multiprocessor computers, based on SELECT or CALL statements that return single result sets. For example, a bulk read-only operation may involve using SSIS to perform a data flow task when the OLE DB source is IBM DB2.

Note You should only use Rowset Cache Size property on multiprocessor systems. You should not use this new property when you use SQL Server queries that return small amounts of IBM DB2 data. In those scenarios, performance may be adversely affected.

The data value for this property is a VT_I4 variant type. By default,. the data value for this property is 0. A setting of 0 indicates that this optional prefetch feature is disabled. When the feature is disabled, the OLE DB Provider for DB2 will not prefetch rows.

A recommended data value for this property is between 2 and 30. We recommend an initial value of 30. When you set this property value, you instruct the OLE DB Provider for DB2 to prefetch up to the specified number of row batches. These row batches are stored in the rowset cache of the provider. The size of the row batches is automatically determined based on the value for the cRows parameter on the OLE DB IRowset::GetNextRows interface that the data consumer specifies.

When you use SSIS, you can set the optional OLE DB data source initialization property to a starting value of 30. This value instructs the provider to prefetch up to 30 row batches.

You can set this property in the following locations:

• The Advanced Options page of the Data Source Wizard
• The All tab of the Data Links dialog box
• In an OLE DB initialization string (connection string) by using the "Rowset Cache Size=Value" argument. The following sample connection string implements the Rowset Cache Size property:

  ; Everything after this line is an OLE DB initialization string.
  Provider=DB2OLEDB;Password=<Password>;Persist Security Info=True;User ID=<UserName>;Initial Catalog=S105FRDM;Defer Prepare=False;Derive Parameters=False;Rowset Cache Size=<Value>;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=<IPAddress>;Network Port=446;Package Collection=DB2USER;Default Schema=DB2USER;Default Qualifier=DB2USER;DBMS Platform=DB2/AS400;Process Binary as Character=False;Connection Pooling=True;Units of Work=RUW
  

SSIS uses the DefaultBufferMaxRows parameter to control the number of rows that it requests in each IRowset::GetNextRows method call. By default, the value of the DefaultBufferMaxRows parameter is 10000. If the Rowset Cache Size property is set to 30, the OLE DB Provider will try to prefetch up to 300,000 rows of data. This number of rows is the number of blocks of rows (30) multiplied by the number of rows that are specified in the IRowset::GetNextRows method call (10000). For more information about the DefaultBufferMaxRows parameter, see SQL Server 2005 Books Online.

Considerations

You should only implement the Rowset Cache Size property when the following conditions are true:

• SQL Server 2005 and the OLE DB Provider for DB2 are installed on a multiprocessor system.
• You are using SSIS or SSAS to import IBM DB2 data.
• You are using SSIS or SSAS to import large amounts of IBM DB2 data. Typically, this data includes millions of rows of data.

You should not use the Rowset Cache Size property in the following scenarios:

• SQL Server Replication
• SQL Server Reporting Services (SSRS)
• Custom or complex OLE DB consumers that open multiple OLE DB rowsets at the same time. The Distributed Query Processor (DQP) in SQL Server is an example of an OLE DB consumer that can open multiple OLE DB rowsets at the same time.

DB2 Decimal data conversion algorithm rewritten

The DB2 Decimal to OLE DB Decimal data conversion algorithm was rewritten to make conversions of DB2 Decimal data types more efficient.

Software update information

A supported feature that modifies the product's default behavior is now available from Microsoft, but it is only intended to modify the behavior that this article describes. Apply it only to systems that specifically require it. This feature may receive additional testing. Therefore, if the system is not severely affected by the lack of this feature, we recommend that you wait for the next OLE DB Provider for DB2 1.0 release that contains this feature.

To obtain this feature immediately, contact Microsoft Product Support Services. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

File information

The English version of this software update has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

32-bit (x86) version

64-bit (x64) version

64-bit (IA-64) version

Note Because of file dependencies, the most recent fix that contains these files may also contain additional files.

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.