The version of the JDBC driver used with SQL Server 2005 should be changed for best performance

Symptom

Platforms/Versions Affected:
Gentran Integration Suite, Release 4.2
Gentran Integration Suite, Release 4.3
Microsoft SQL Server 2005
Sun JVM

An issue has surfaced recently with the version of the JDBC Driver used with SQL Server 2005 (only) that is specified in the Install Guide for GIS 4.3 and 4.2.

The JDBC driver, when used with a Java Virtual Machine, will only cache a small number of characters sets, and the required character set (collation) for GIS (SQL_Latin1_General_CP850_Bin) is not one of them. This causes the driver to repeatedly call a method to see if the character set is supported, which can drastically slow down the returns of a query.

There is more detail at the following link in the Microsoft Knowledge Base:

http://support.microsoft.com/kb/92 5050

At this time, Sterling Commerce strongly recommends that all installs using the Sun JVM and SQL Server 2005 upgrade to the version of the JDBC Driver for SQL Server 2005 that is available at the link above (version 1.1.1501.200) . This includes all Builds of GIS 4.3 and 4.2.

Please note that Sterling Commerce does not at this time support the 1.2 version of the JDBC Driver for SQL Server 2005.

For GIS 4.3, the 1.1.1501.200 version of the JDBC Driver will now be the officially supported and documented version as of Build 4316. Any current or new installs should be using this driver, and Build 4316 will no longer warn the user of an unsupported JDBC driver version.

For GIS 4.2, the same 1.1.1501.200 version will also be the officially supported version, though the change to the documentation will happen at a future date. In addition, the GIS install will continue to report an unsupported JDBC Driver version until a change is made in an upcoming patch.

On a standard Windows install, the driver will be referenced in the file 'dynamicclasspath.cfg', located in the properties directory of the install. By searching in that file for 'sqljdbc.jar', the user should be able to locate all of the locations where the driver would need to be replaced. If the original location of the driver as specified during installation still exists, it will need to be replaced there as well as in the following two locations (assuming that the product is installed in the default location of 'C:\SterlingCommerce'):

C:\SterlingCommerce\SI\dbjar\jdbc\MSSQL - rename existing 'sqljdbc.jar' file and then copy the new verson into the directory.

C:\SterlingCommerce\SI\jdk\jre\lib\ext - remove the existing 'sqljdbc.jar' file and then copy the new version into the directory.

If assistance is needed in changing the driver, please contact Customer Support.

NOTE:
As noted above, please be aware that updating the JDBC driver version from 1.1.1501.101 to 1.1.1501.200 will result in GIS issuing a warning similar to the following during system start up, which can be disregarded:

************************************************************
WARNING: Unsupported Database Driver Version!
Please update your database drivers. Check the latest
release notes for details on supported database drivers.
************************************************************

Database vendor: MSSQL2005
Database version: 9.xx.xxxx
Driver Version: 1.1.1501.200

As of Build 4316 for GIS 4.3, this warning will no longer occur. See article NFX4796 for details.

An update to remove this warning will also be released in an upcoming patch for GIS 4.2.

Related:
HTG1326 - How to replace the JDBC driver jar (in an existing GIS installation)