Performance
This topic contains the following sections that will help you maximize performance when you are using the Microsoft OLE DB Provider for DB2 (Data Provider).
Configuring for Performance
Measuring Performance
Configuring for Performance
You can set the following configurable properties on the connection string to help improve performance.
Pool OLE DB resources to reduce connection startup time
OLE DB Resource Pooling and Provider Connection Pooling may increase performance by reducing connection startup time. Resource Pooling is enabled through OLE DB Service Components that are part of the Windows operating system. You can enable OLE DB Resource Pooling by setting OLE DB initialization properties and registry settings. For more information, see OLE DB Resource Pooling (https://go.microsoft.com/fwlink/?LinkID=180446).
Pool Provider resources to reduce connection startup time
Data Provider Connection Pooling is a provider-specific client-side connection pooling used to cache and re-use TCP/IP network connections to DB2 database servers. Connection Pooling should be set to True in the OLE DB Data Source properties group.
Optionally, you can set Max Pool Size to specify the maximum number of connections that can exist in the connection pool when connection pooling is enabled for the data source. The default is 100.
There is no upper limit for the Max Pool Size property. If you configure a value that is less than 0 for the Max Pool Size property, the default value of 100 is used. You can set pooling properties from the Advanced and All Properties pages in the Data Source Wizard, or the All tab of the Data Links dialog box. You can also specify them in an OLE DB initialization string (connection string), by setting Connection Pooling = True and Max Pool Size = 100.
Optimize the OLE DB rowset cache when fetching data
The RowSetCacheSize instructs the Data Provider to pre-fetch rows from DB2 while concurrently processing and returning rows to the data consumer on calls to IRowset::GetNextRows. This feature may improve performance in bulk read-only operations on multi-processor or multi-core computers. The default value for this property is 0, which indicates that the optional pre-fetch feature is "off". We recommend setting a value between 50 and 200, with an initial recommended value of 100. This instructs the Data Provider to pre-fetch up to the specified number of row batches, which are stored in the Data Provider's rowset cache. The size of the row batches is automatically determined based on the value for cRows on the OLE DB IRowset::GetNextRows interface specified by the consumer. You can set this property from the All Properties page of the Data Source Wizard, or from the All tab of the Data Links dialog box. You can also specify this property in an OLE DB initialization string (connection string) by setting "Rowset Cache Size=100".
Deferring preparing of commands with parameters until execution
Defer Prepare instructs the Data Provider to optimize the processing of parameterized INSERT, UPDATE, DELETE, and SELECT commands. For the INSERT, UPDATE, and DELETE commands, the Data Provider combines prepare, execute, and commit commands into one network flow to the remote database. For the SELECT command, the Data Provider combines prepare and execute commands into one network flow. This minimizes network traffic and frequently improves overall performance. The default value is false. You can set this property in the initialization string using Defer Prepare=True, the Data Links All tab, or the Data Source Wizard Advanced dialog box.
Sending multiple rows in a single unit of work
The Data Provider supports the OLE DB IRowsetFastLoad interface to enable consumers, such as Integration Services, to execute multiple INSERT statements in a single unit of work. This better uses TCP/IP network packets and increases overall performance. Developers can select RowsetFastLoad when configuring Integration Services packages in the Business Intelligence Developer Studio package designer. The IRowsetFastLoad interface is supported when inserting rows into DB2 for i5/OS V5R4 and V6R1. For more information, see Access Mode for Integration Services OLE DB Destination Custom Properties (https://go.microsoft.com/fwlink/?LinkID=180768).
Command time-out to terminate long-running queries
The Data Provider offers an OLE DB Rowset DBPROP_COMMANDTIMEOUT property, to let developers automatically terminate long-running queries that may adversely affect performance. Integration Services and Analysis Services expose this property through the Data Source Query Timeout option in the Business Intelligence Development Studio. Reporting Services exposes this property through the Dataset Properties Timeout option in the Business Intelligence Development Studio. Replication and Query Processor expose this property through the sp_serveroption, @optname=query time-out.
Measuring Performance
To measure performance, the Data Provider offers performance counters. By default performance counters are turned off. They can be turned on by changing value of the following registry key to 1:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Host Integration Server\Data Integration\UpdateCounters = 1
The Data Provider performance counters capture information about open connections, open statements, packets and bytes sent/received, average host (DB2 server) processing time, command executions, data fetches, and transaction commits/rollbacks. For more information, see Performance Counters (https://go.microsoft.com/fwlink/?LinkID=119211).