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 (918362)
The information in this article applies to:
- Microsoft OLE DB Provider for DB2 1.0
SUMMARYWhen 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. MORE INFORMATIONThe 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 addedThe 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: 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. ConsiderationsYou 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 rewrittenThe DB2 Decimal to OLE DB Decimal data conversion algorithm was rewritten to make conversions of DB2 Decimal data types more efficient.
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.
| Modification Type: | Minor | Last Reviewed: | 7/26/2006 |
|---|
| Keywords: | kbQFE kbinfo kbhotfixserver kbpubtypekc KB918362 kbAudDeveloper |
|---|
|