These properties vary according to the database vendor requirements for JDBC driver implementations. You must set the appropriate properties on every data source that you configure. These settings are for Microsoft® SQL Server data sources.
Compatibility or usage concern | Resolution or recommended action | |
---|---|---|
Requirements | Specifying your database version | You do not have to specify your version of Microsoft SQL Server. Specify the name of your database as usual. WebSphere Application Server detects the version and makes any necessary class attribute adjustments. |
Parenthesis requirement for locking hints | Microsoft SQL Server 2008
and 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 alternateServers property to include a combination of Microsoft SQL Server 2008, 2005 and 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:
|
JDBC provider | Microsoft SQL Server feature | Configuration consideration |
---|---|---|
Microsoft SQL Server JDBC Driver | SNAPSHOT isolation level | Set the isolation level constant
by invoking the setTransactionIsolation method with one of the following
attributes:
|
READ_COMMITTED_SNAPSHOT isolation level | This isolation level is an implementation of
the Read committed isolation level. The policy enforces
optimistic locking for read operations with Microsoft SQL Server 2008
and 2005.
|
|
Transact-SQL enhancements, including: new functions, additional data types, and the ability to create recursive queries | Do not use these features with the application server. | |
DataDirect ConnectJDBC type 4 driver for Microsoft SQL Server | SNAPSHOT isolation level | This isolation level implements
optimistic locking for transactions in which Microsoft SQL Server
2008 and 2005 serializes the data. Configure the ALLOW_SNAPSHOT_ISOLATION
setting on the database, and then set the isolation level in one of
two ways:
|
READ_COMMITTED_SNAPSHOT isolation level | This isolation level is an implementation of
the Read committed isolation level. The policy enforces
optimistic locking for read operations with Microsoft SQL Server 2008
and 2005.
|
|
Transact-SQL enhancements, including: new functions, additional data types, and the ability to create recursive queries | Do not use these features with the application server. |
For the Microsoft SQL Server JDBC drivers that support both database versions, perform the same steps and set the same class paths and properties that were previously required for Microsoft SQL Server 2000.
If you already have profiles defined when you install Version 6.1.0.15, or later, you cannot use the administrative console to select the Microsoft SQL Server JDBC driver as a database type for these profiles. However, if you want to use the Microsoft SQL Server JDBC driver for one or more of the already existing profiles, you can add the Microsoft SQL Server JDBC driver as a new user-defined database type, and then associate this user-defined database type with those profiles.
newfeatcom.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelperThe JDBC provider requires a valid authentication alias.
com.microsoft.sqlserver.jdbc.SQLServerXADataSource
com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelperThe JDBC provider requires a valid authentication alias.
DataDirect ConnectJDBC type 4 driver for Microsoft SQL Server is a Type 4 JDBC driver that provides JDBC access to the Microsoft SQL Server 2008, Microsoft SQL 2005, and Microsoft SQL Server 2000 databases. This provider is for use only with the Connect JDBC driver purchased from DataDirect Technologies.
com.ddtek.jdbcx.sqlserver.SQLServerDataSource
sqlserver.jar, base.jar and util.jar(The spy.jar file is optional. You 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.)
com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Requires a valid authentication alias.
DataDirect ConnectJDBC type 4 driver for Microsoft SQL Server (XA) is a Type 4 JDBC driver which provides XA-compliant JDBC access to the Microsoft SQL Server 2008, Microsoft SQL Server 2005, and Microsoft SQL Server 2000 databases. This provider is for use only with the Connect JDBC driver purchased from DataDirect Technologies.
com.ddtek.jdbcx.sqlserver.SQLServerDataSource.
sqlserver.jar, base.jar and util.jar.(The spy.jar file is optional. You 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.)
com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Requires a valid authentication alias.
WebSphere embedded ConnectJDBC driver for Microsoft SQL Server is a Type 4 JDBC driver that provides JDBC access to the Microsoft SQL Server 2008, Microsoft SQL Server 2005, and Microsoft SQL Server 2000 databases. This JDBC driver ships with WebSphere Application Server. Only use this provider with the Connect JDBC driver embedded in WebSphere; it cannot be used with a Connect JDBC driver purchased separately from DataDirect Technologies.
com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource.
sqlserver.jar base.jar and util.jar.(The spy.jar file is optional. You need this file to enable spy logging. The spy.jar file for the WebSphere embedded Connect JDBC driver ships with WebSphere Application Server. All the files are located in the WAS_HOME/lib/ directory.)
All of the JAR files in the previous list are shipped with WebSphere Application Server and are installed automatically with the product. They are also updated automatically when you apply WebSphere Application Server service packs.
com.ibm.websphere.rsadapter.WSConnectJDBCDataStoreHelper
Requires a valid authentication alias.
WebSphere embedded ConnectJDBC driver for Microsoft SQL Server (XA) is a Type 4 JDBC driver that supports two-phase commit transactions on connections with the Microsoft SQL Server 2008, Microsoft SQL Server 2005, and Microsoft SQL Server 2000 databases. This JDBC driver ships with WebSphere Application Server. Use this provider with the IBM WebSphere Connect JDBC driver embedded in WebSphere Application Server. Do not use it with the DataDirect Connect JDBC driver purchased separately from DataDirect Technologies.
com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource.
sqlserver.jar base.jar and util.jar.An additional file, the spy.jar file, is optional. You 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 WebSphere Application Server and are installed automatically with the product. They are also updated automatically when you apply WebSphere Application Server service packs.
com.ibm.websphere.rsadapter.WSConnectJDBCDataStoreHelper
Requires a valid authentication alias.
Patches to the IBM WebSphere Connect JDBC driver jar files are installed automatically when you apply WebSphere Application Server service packs. However, to update Microsoft SQL Server-side programs for this JDBC driver, you must go to the IBM FTP site for WebSphere Application Server embedded product updates.
An important server-side program is Stored Procedures for the Java Transaction API (JTA). Whether you need to run one or two phase transactions with the XA-enabled IBM WebSphere Connect JDBC driver, you must install Stored Procedures for JTA on all machines that run Microsoft SQL. The WebSphere Application Server installation disks contain a base level of Stored Procedures for JTA. Go to the FTP site for updates to this API.
This type 3 JDBC driver for Microsoft SQL Server is deprecated in WebSphere Application Server Version 6.0. Therefore it is no longer an available choice among provider types in the administrative console.
com.ddtek.jdbcx.sequelink.SequeLinkDataSource
sljc.jar and spy-sl.jar(The JDBC driver shipped with WebSphere Application Server 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. You need these files to enable spy logging.)
com.ibm.websphere.rsadapter.SequeLinkDataStoreHelper
Requires a valid authentication alias.
The DataDirect SequeLink type 3 JDBC driver requires installation of SequeLink Server on all machines running Microsoft SQL Server. See the readme.html file found in the DataDirect folder on the WebSphere Application Server CD for instructions on how to install SequeLink Server. (Install SequeLink Server from the WebSphere Application Server CD only if you 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 WebSphere Application Server 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 Microsoft SQL Server-side JDBC driver updates.
This type 4 JDBC driver for Microsoft SQL Server 2000 is deprecated in WebSphere Application Server Version 6.0. Therefore it is no longer an available choice among provider types in the administrative console.
For best results with JDBC access from the application server to Microsoft SQL Server, use only JDBC drivers that are not marked for deprecation. However, if you must continue using a deprecated driver for JDBC access to Microsoft SQL Server, you can configure it through the administrative console. Follow the steps listed in the topic on configuring a JDBC provider using the administrative 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 your user-defined JDBC provider type. Replace those defaults with the following settings that are specific to the Microsoft JDBC driver for Microsoft SQL Server 2000.
com.microsoft.jdbcx.sqlserver.SQLServerDataSource
mssqlserver.jar, msbase.jar and msutil.jar(The spy.jar file is optional. You need it to enable spy logging. However, Microsoft does not ship the spy.jar file. Contact Microsoft about this issue.)
com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Requires a valid authentication alias.