Operating Systems: i5/OS
             Personalize the table of contents and search results

 

Data source minimum required settings for MS SQL Server

 

These properties vary according to the database vendor requirements for JDBC driver implementations. Set the appropriate properties on every data source that you configure. These settings are for MS SQL Server data sources. MS SQL Server 2005: WebSphere Application Server V6.10 supports JDBC transactions with the new Microsoft SQL Server 2005, as well as Microsoft SQL Server 2000. For access to either version of the database, use V3.5, Service pack 2, of either 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 the new version. WebSphere Application Server does, however, support two new options for setting isolation level in MS SQL Server 2005: SNAPSHOT and READ_COMMITTED_SNAPSHOT. The following chart describes these isolation levels as well as the few requirements and concerns for using MS SQL Server 2005 with Application Server v6.1.

Compatibility or New Usage concern Resolution or Recommended action
New 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)

 
New features: SNAPSHOT isolation level This new isolation level implements optimistic locking for transactions in which MS SQL Server 2005 serializes the data.You must 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 method setTransactionIsolation 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 custom data source property:

    • Set the new data source custom property snapshotSerializable to true, and

    • Invoke the method setTransactionIsolation with the attribute: 

      conn.setTransactionIsolation
(java.sql.Connection.TRANSACTION_SERIALIZABLE)

READ_COMMITTED_SNAPSHOT isolation level This isolation level is a new implementation of Read committed. The policy enforces optimistic locking for read operations with MS SQL Server 2005.You must configure the isolation level on the database. Then invoke the method setTransactionIsolation with the attribute: 

conn.setTransactionIsolation
(java.sql.Connection.TRANSACTION_READ_COMMITTED)
Transact-SQL enhancements, including: new functions, additional data types, and the ability to create recursive queries WebSphere Application Server does not currently support these features; do not use them with WebSphere Application Server Version 6.10.

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 information


Backward Compatibility for MS SQL Server components
FTP site for MS SQL Server-side JDBC driver updates

 

Reference topic