Data source minimum required settings for Microsoft SQL Server

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.

Version 6.1 of the application server supports JDBC transactions with the Microsoft SQL Server 2008, Microsoft SQL Server 2005, as well as Microsoft SQL Server 2000. For access to either version of the database use one of the following JDBC providers:
[Fix Pack 15 or later] These JDBC drivers provide the same function for Microsoft SQL Server 2008 and Microsoft SQL Server 2005 as Microsoft SQL Server 2000. As long as you use only those new features of Microsoft SQL Server 2005 or 2008 that have no impact on JDBC transactions, you generally risk no exceptions by upgrading to a later version. The following table describes a few requirements and concerns for using Microsoft SQL Server 2008 or 2005 with version 6.1 of the application server.
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
  • Microsoft SQL Server 2008 and 2005 requires use of the keyword with to join multiple locking hints. For example:
    select value from t1 with (updlock rowlock) 
    where name = ?
  • For Microsoft SQL Server 2000, no keyword is required. For example:
    select value from t1 (updlock rowlock) 
    where name = ?
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:
  • text, replaced by varchar(max)
  • ntext, replaced by nvarchar(max)
  • image, replaced by varbinary(max)
The application server also supports two options for setting isolation level in Microsoft SQL Server 2008 and 2005: SNAPSHOT and READ_COMMITTED_SNAPSHOT. The following table describes these isolation levels and configuration considerations:
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:
  • conn.setTransactionIsolation ( com.microsoft.sqlserver.jdbc.SQLServerConnection.TRANSACTION_SNAPSHOT)
  • conn.setTransactionIsolation(value_of_constant)
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.
  1. Configure the isolation level on the database
  2. Invoke the setTransactionIsolation method with the conn.setTransactionIsolation (java.sql.Connection.TRANSACTION_READ_COMMITTED) attribute.
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:
  • By isolation level constant. Invoke the setTransactionIsolation method with one of three new attributes:
    • conn.setTransactionIsolation (com.ibm.websphere.jdbc.extensions. ExtConstants.TRANSACTION_SNAPSHOT)
    • conn.setTransactionIsolation (com.ddtek.jdbc.extensions. ExtConstants.TRANSACTION_SNAPSHOT)
    • conn.setTransactionIsolation(16)
  • By the custom data source property:
    • Set the data source custom property snapshotSerializable to true.
    • Invoke the setTransactionIsolation method with the conn.setTransactionIsolation (java.sql.Connection.TRANSACTION_SERIALIZABLE) attribute:
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.
  1. Configure the isolation level on the database
  2. Invoke the setTransactionIsolation method with the conn.setTransactionIsolation (java.sql.Connection.TRANSACTION_READ_COMMITTED) attribute.
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.
Consult the Backward Compatibility for Microsoft SQL Server components Web page for a complete list of deprecated items, as well as backward compatibility provisions, for Microsoft SQL Server 2008 or 2005.

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.

You can configure the following types of providers:



Related tasks
Configuring a JDBC provider and data source
Configuring a JDBC provider using the administrative console
Configuring a data source using the administrative console
Creating and configuring a JDBC provider and data source using the Java Management Extensions API
Related reference
FTP site for IBM WebSphere embedded ConnectJDBC updates
Reference topic Reference topic    

Terms and conditions for information centers | Feedback

Last updatedLast updated: Aug 30, 2013 4:53:43 PM CDT
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=pix&product=was-base-dist&topic=rdat_minreqmssql
File name: rdat_minreqmssql.html