FIX: Performance enhancements to the Host Integration Server 2004 DB2 Providers

Hotfix Download Available
View and request hotfix downloads

View products that this article applies to.

SUMMARY

Based on customer feedback, the Microsoft Host Integration Server 2004 DB2 network client and data providers have been improved to enhance performance in complex customer scenarios. For example, these scenarios could include the following:

Microsoft SQL Server replication and distributed queries
Most cases of Client Cursor Engines (CCEs). With CCEs, applications that use data use prepared SQL statements and manual remote unit of work (RUW) commits.

Back to the top

RESOLUTION

A supported feature that modifies the default behavior of the product is available from Microsoft. However, this feature is intended to modify only the behavior that this article describes. Apply this feature only to systems that specifically require it. This feature might 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 software update that contains this feature.

If the feature is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the feature.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific feature. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support (http://support.microsoft.com/contactus/?ws=support)

Note The "Hotfix download available" form displays the languages for which the feature is available. If you do not see your language, it is because the feature is not available for that language. The English version of this hotfix 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 tool in Control Panel.

   Date         Time   Version     Size       File name
   ------------------------------------------------------------------------------------------
   09-Dec-2004  00:41  6.0.1918.0    486,912  Db2oledb.dll     
   09-Dec-2004  00:46                 24,516  Db2oledb.h
   09-Dec-2004  18:10  6.0.1918.0     86,016  Microsoft.hostintegration.dataaccesslibrary.dll  
   09-Dec-2004  18:10  6.0.1918.0  2,703,360  Microsoft.hostintegration.dataaccesstool.exe  
   09-Dec-2004  18:10  6.0.1918.0    114,688  Microsoft.hostintegration.msdb2client.dll  
   09-Dec-2004  00:41  6.0.1918.0     71,680  Mseidb2c.dll     
   09-Dec-2004  00:41  6.0.1918.0    435,200  Mseidb2d.dll     
   09-Dec-2004  00:41  6.0.1918.0    684,032  Mseidrda.dll

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

Back to the top

MORE INFORMATION

When this hotfix runs against DB2 for z/OS and DB2 UDB platforms, this hotfix combines the PREPARE, EXECUTE, and COMMIT wire level distributed relational database architecture (DRDA) flows into one flow for each parameterized INSERT, UPDATE, or DELETE statement that is issued by the application that is using the data. When this hotfix runs against all DB2 platforms, the hotfix combines the PREPARE and EXECUTE wire level DRDA flows into one flow for each parameterized SELECT statement. This change can reduce overall execution time when you are running many prepared SQL commands.

To use this fix, you must set an optional "connection parameter." This parameter could be a property, an attribute, or an option in the user interface. For more information, see the section for each data access architecture.

Back to the top

Microsoft OLE DB Provider for DB2

Property group: Initialization
Property set: DBPROPSET_DBINIT
Property ID: DBPROP_DB2OLEDB_DEFPREP
Property description: Defer Prepare
Default value: FALSE
Type: VT_BOOL
R/W: R/W
Default: FALSE
Required: No
Data Links dialog: All
Data Links control type: Drop-down list
Data Links control label: Defer Prepare
Explanation: Instructs the provider to optimize the processing of parameterized INSERT, UPDATE, DELETE, and SELECT commands. For the INSERT, UPDATE, and DELETE commands, the provider will combine the prepare, execute, and commit commands together into one network flow to the remote database. For the SELECT command, the provider will combine the prepare and execute commands into one network flow. This optimization will minimize network traffic and frequently improve overall performance.

Back to the top

Microsoft ODBC Driver for DB2

Connection attribute keyword: DFP
Registry DSN keyword: Defer Prepare
Type: Boolean
R/W: R/W
Default: FALSE
Supported values: TRUE, FALSE
Required: No
Driver Manager dialog: Target Database
Driver Manager control type: Check box
Driver Manager control label: Defer Prepare
Explanation: Instructs the provider to optimize the processing of parameterized INSERT, UPDATE, DELETE, and SELECT commands. For the INSERT, UPDATE, and DELETE commands, the provider will combine the prepare, execute, and commit commands together into one network flow to the remote database. For the SELECT command, the provider will combine the prepare and execute commands into one network flow. This optimization will minimize network traffic and frequently improve overall performance.

Back to the top

Managed Provider for DB2

Class: MsDb2Connection
Property: DeferPrepare
Type: Boolean
Default: FALSE
Explanation: Instructs the provider to optimize the processing of parameterized INSERT, UPDATE, DELETE, and SELECT commands. For the INSERT, UPDATE, and DELETE commands, the provider will combine the prepare, execute, and commit commands together into one network flow to the remote database. For the SELECT command, the provider will combine the prepare and execute commands into one network flow. This optimization will minimize network traffic and frequently improve overall performance.

Back to the top

Data Source Wizard

Dialog: Advanced Options
Control type: Check box
Control label: Defer Prepare
Default: FALSE
Supported Values: TRUE, FALSE
Required: No
Help text: Defer prepare reduces the number of network packets by delaying when prepare commands are sent to the database.

Back to the top

Data Access Library

Class: IConnectionString
Property: DeferPrepare
Type: Boolean
Default: FALSE
Explanation: Instructs the provider to optimize the processing of parameterized INSERT, UPDATE, DELETE, and SELECT commands. For the INSERT, UPDATE, and DELETE commands, the provider will combine the prepare, execute, and commit commands together into one network flow to the remote database. For the SELECT command, the provider will combine the prepare and execute commands into one network flow. This optimization will minimize network traffic and frequently improve overall performance.

Back to the top