Data source minimum required settings for Microsoft SQL Server


 

+

Search Tips   |   Advanced Search

 

Overview

SQL Server 2005 and SQL Server 2000 drivers are roughly equivalent. As long as you use only those new features of SQL Server 2005 that have no impact on JDBC transactions, there is generally no risk of exceptions.

 

Database version

You do not have to specify the version of MS SQL Server. Specify the name of the database as usual. WAS detects the version and makes any necessary class attribute adjustments.

 

Parenthesis requirement for locking hints

MS SQL Server 2005 requires that you place parentheses around locking hints. For example:

select value from t1 (holdlock) where name = ?

 

New syntax for joining multiple locking hints

 

Use of the alternate servers custom data source property

Verify that all application component clients of the data source issue commands that are valid for both database versions before you configure the alternate servers property to include both MS SQL Server 2005 and Server 2000 machines.

Deprecated data types

Microsoft deprecated three data types for SQL Server 2005, which are shown in the following list along with each replacement data type:

 

Isolation level

Microsoft SQL Server JDBC Driver

 

DataDirect ConnectJDBC type 4 driver for MS SQL Server

 

Providers

Configure the following types of providers:

 

Microsoft SQL Server JDBC Driver

New feature: Fix Pack 6.1.0.15 adds support for the Microsoft SQL Server JDBC driver. The Microsoft SQL Server JDBC driver is only available as a selection option to profiles that are created in or updated to V6.1.0.15, or later.

If we already have profiles defined when you install Fix Pack 6.1.0.15, or later, we cannot use the admin console to select the SQL Server JDBC driver as a database type for these profiles. However, to use the SQL Server JDBC driver for one or more of the already existing profiles, we can add the SQL Server JDBC driver as a new user-defined database type, and then associate this user-defined database type with those profiles.

The SQL Server JDBC driver supports this data source:

com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
The JDBC provider requires the following JAR files:

The JDBC provider requires the following DataStoreHelper class:

com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper
The JDBC provider requires a valid authentication alias. The JDBC driver requires the following properties:

 

SQL Server JDBC Driver (XA)

New feature: V6.1.0.15 of Application Server supports the Microsoft SQL Server JDBC driver. The Microsoft SQL Server JDBC driver is only available to profiles that are created in or updated to V6.1.0.15 or later

This JDBC provider supports this data source:

com.microsoft.sqlserver.jdbc.SQLServerXADataSource
The JDBC provider requires the following JAR files:

The JDBC provider requires the following DataStoreHelper class:

com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper
The JDBC provider requires a valid authentication alias. The JDBC driver requires the following properties:

 

DataDirect ConnectJDBC type 4 driver for MS SQL Server

This JDBC provider supports this data source:

com.ddtek.jdbcx.sqlserver.SQLServerDataSource

Requires JDBC driver files:

sqlserver.jar base.jar util.jar

(The spy.jar file is optional. we need this file to enable spy logging. The spy.jar file is not in the same directory as the other three jar files. Instead, it is located in the ../spy/ directory.)

Requires DataStoreHelper class:

com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper

Requires a valid authentication alias. Requires properties:

 

DataDirect ConnectJDBC type 4 driver for MS SQL Server (XA)

This JDBC provider supports this data source:

com.ddtek.jdbcx.sqlserver.SQLServerDataSource

Requires JDBC driver files:

sqlserver.jar base.jar util.jar

(The spy.jar file is optional. we need this file to enable spy logging. The spy.jar file is not in the same directory as the other three jar files. Instead, it is located in the ../spy/ directory.)

Requires DataStoreHelper class:

com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper

Requires a valid authentication alias. Requires properties:

 

IBM WebSphere embedded ConnectJDBC driver for MS SQL Server

This JDBC provider supports this data source:

com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource

Requires JDBC driver files:

sqlserver.jar base.jar util.jar

(The spy.jar file is optional. we need this file to enable spy logging. The spy.jar file for the WebSphere embedded Connect JDBC driver ships with WAS. All the files are located in the WAS_HOME/lib/ directory.)

All of the JAR files in the previous list are shipped with WAS and are installed automatically with WAS ND. They are also updated automatically when you apply WAS service packs.

Requires DataStoreHelper class:

com.ibm.websphere.rsadapter.WSConnectJDBCDataStoreHelper

Requires a valid authentication alias. Requires properties:

 

IBM WebSphere embedded ConnectJDBC driver for MS SQL Server (XA)

The ConnectJDBC provider supports the following data source:

com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource

Requires JDBC driver files:

sqlserver.jar base.jar util.jar

An additional file, the spy.jar file, is optional. we need spy.jar for spy logging, which is a form of JDBC driver-level trace.

All of the JAR files in the previous list are shipped with WAS and are installed automatically with WAS ND. They are also updated automatically when you apply WAS service packs.

Requires DataStoreHelper class:

com.ibm.websphere.rsadapter.WSConnectJDBCDataStoreHelper

Requires a valid authentication alias. Requires properties:

Patches to the IBM WebSphere Connect JDBC driver jar files are installed automatically when you apply WAS service packs. However, to update SQL Server-side programs for this JDBC driver, go to the IBM FTP site for WAS embedded product updates.

An important server-side program is Stored Procedures for the JTA. Whether we need to run one or two phase transactions with the XA-enabled IBM WebSphere Connect JDBC driver, install Stored Procedures for JTA on all machines that run Microsoft SQL. The WAS installation disks contain a base level of Stored Procedures for JTA. Go to the FTP site for updates to this API.

Install Stored Procedures for JTA by performing the following steps:

  1. Determine whether we are running the 32-bit or 64-bit MS SQL Server and select the appropriate sqljbc.dll and instjdbc.sql files.

  2. Stop the MS SQL Server service.

  3. Copy the sqljdbc.dll file into the %SQL_SERVER_INSTALL%\Binn\ directory.

  4. Restart the MS SQL Server service.

  5. Run the instjdbc.sql script. (The script can be run by the MS SQL Server Query Analyzer or the ISQL utility).

 

DataDirect SequeLink type 3 JDBC driver for MS SQL Server -- Deprecated

This type 3 JDBC driver for MS SQL Server is deprecated in WAS V6.0. Therefore it is no longer an available choice among provider types in the admin console.

For best results with WAS JDBC access to MS SQL Server, use only JDBC drivers that are not marked for deprecation. However, if continue using a deprecated driver for JDBC access to MS SQL Server, we can configure it through the WAS admin console. Follow the steps listed in Set a JDBC provider . Be sure to select User-defined for the database type. This selection triggers the console to display default class files, data source interfaces, and so on for the user-defined JDBC provider type. Replace those defaults with the following settings that are specific to the DataDirect SequeLink type 3 JDBC driver.

DataDirect SequeLink type 3 JDBC driver supports the following data source:

com.ddtek.jdbcx.sequelink.SequeLinkDataSource

Requires JDBC driver files:

sljc.jar, spy-sl.jar

(The JDBC driver shipped with WAS requires the sljc.jar and the spy-sl.jar files. The JDBC driver purchased from DataDirect requires the sljc.jar and the spy.jar files. The spy.jar and spy-sl.jar files are optional. we need these files to enable spy logging.)

Requires DataStoreHelper class:

com.ibm.websphere.rsadapter.SequeLinkDataStoreHelper

Requires a valid authentication alias. Requires properties:

The DataDirect SequeLink type 3 JDBC driver requires installation of SequeLink Server on all machines running MS SQL Server. See the readme.html file found in the DataDirect folder on the WAS CD for instructions on how to install SequeLink Server. (Install SequeLink Server from the WAS CD only if we are using the SequeLink JDBC driver embedded in WebSphere. Otherwise, install a copy of SequeLink Server purchased from DataDirect Technologies.)

Patches to the IBM WebSphere SequeLink JDBC driver jar files are installed automatically when applying WAS service packs. If updates are ever needed for the Microsoft SQL Server-side installables (SequeLink server) for the IBM WebSphere SequeLink JDBC driver, they will be made available from the FTP site for MS SQL Server-side JDBC driver updates.

 

Microsoft JDBC driver for MS SQL Server 2000 (deprecated)

This type 4 JDBC driver for MS SQL Server 2000 is deprecated in WAS V6.0. Therefore it is no longer an available choice among provider types in the admin console.

For best results with JDBC access from the appserver to MS SQL Server, use only JDBC drivers that are not marked for deprecation. However, if continue using a deprecated driver for JDBC access to MS SQL Server, we can configure it through the admin console. Follow the steps listed in the topic on configuring a JDBC provider using the admin console, and select User-defined for the database type.

This selection triggers the console to display default class files, data source interfaces, and so on for the user-defined JDBC provider type. Replace those defaults with the following settings that are specific to the Microsoft JDBC driver for MS SQL Server 2000. Microsoft JDBC driver for MS SQL Server 2000 supports the following data source:

com.microsoft.jdbcx.sqlserver.SQLServerDataSource

Requires JDBC driver files:

mssqlserver.jar msbase.jar msutil.jar
(The spy.jar file is optional. we need it to enable spy logging. However, Microsoft does not ship the spy.jar file. Contact Microsoft about this issue.)

Requires DataStoreHelper class:

com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper

Requires a valid authentication alias. Requires properties:




 

Related tasks

Set a JDBC provider and data source
Set a JDBC provider
Set a data source
Create and configuring a JDBC provider and data source using the Java Management Extensions API

 

Related


FTP site for IBM WebSphere embedded ConnectJDBC updates