WebSphere Application Server Network Deployment, Version 6.1
             Operating Systems: AIX, HP-UX, i5/OS, Linux, Solaris, Windows, z/OS

             Personalize the table of contents and search results

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 2005, as well as Microsoft SQL Server 2000. For access to either version of the database use one of the following JDBC providers:
These JDBC drivers provide the same function for MS SQL Server 2005 as MS SQL Server 2000. As long as you use only those new features of MS SQL Server 2005 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 MS SQL Server 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 MS 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 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
  • MS SQL Server 2005 requires use of the keyword with to join multiple locking hints. For example:
    select value from t1 with (updlock rowlock) 
    where name = ?
  • For MS 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 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:
  • 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 MS SQL Server 2005: SNAPSHOT and READ_COMMITTED_SNAPSHOT. The following table describes these isolation levels and configuration considerations:
JDBC provider MS 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 three attributes:
  • conn.setTransactionIsolation (com.ibm.websphere.jdbc.extensions. ExtConstants.TRANSACTION_SNAPSHOT)
  • conn.setTransactionIsolation (com.ddtek.jdbc.extensions. ExtConstants.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 MS SQL Server 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 MS SQL Server SNAPSHOT isolation level This isolation level implements optimistic locking for transactions in which MS SQL Server 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 MS SQL Server 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 MS SQL Server components Web page for a complete list of deprecated items, as well as backward compatibility provisions, for MS SQL Server 2005.

For the MS 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 MS 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    

Terms of Use | Feedback

Last updated: Feb 25, 2009 9:32:38 AM CST
http://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/index.jsp?topic=/com.ibm.websphere.nd.multiplatform.doc/info/ae/ae/rdat_minreqmssql.html