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 MS 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:
- Microsoft SQL Server JDBC Driver
- Version 3.5, Service pack 2 of the DataDirect ConnectJDBC Provider, type
4 driver, for MS SQL Server -- including the XA implementation
- Version 3.5, Service pack 2 of the IBM WebSphere embedded ConnectJDBC
Provider for MS SQL Server -- including the XA implementation
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 |
|
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.
- Configure the isolation level on the database
- 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.
- Configure the isolation level on the database
- 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.
- Microsoft SQL Server JDBC Driver
New feature: Version 6.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 Version 6.1.0.15 or later.
newfeat
This
JDBC provider supports this data source:
com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
The
JDBC provider requires the following Java archive (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:
- serverName - specifies the name of the server in
which Microsoft SQL Server resides. Example: myserver.mydomain.com
- portNumber - specifies the TCP/IP port that Microsoft
SQL Server uses for communication. Port 1433 is the default.
- databaseName - specifies the name of the database
from which the data source obtains connections. Example: Sample.
- Microsoft SQL Server JDBC Driver
(XA)
New feature: Version 6.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 Version
6.1.0.15 or later.
newfeat
This JDBC provider supports this data source:
com.microsoft.sqlserver.jdbc.SQLServerXADataSource
The JDBC provider requires the following Java archive (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:
- serverName - specifies the name of the server in
which Microsoft SQL Server resides. Example: myserver.mydomain.com
- portNumber - specifies the TCP/IP port that Microsoft
SQL Server uses for communication. Port 1433 is the default.
- databaseName - specifies the name of the database
from which the data source obtains connections. Example: Sample.
- DataDirect ConnectJDBC type 4 driver for MS SQL Server
DataDirect ConnectJDBC type 4 driver for MS SQL Server is
a Type 4 JDBC driver that provides JDBC access to the MS SQL Server 2005 and
MS SQL Server 2000 databases. This provider is for use only with the Connect
JDBC driver purchased from DataDirect Technologies.
This
JDBC provider supports this data source:
com.ddtek.jdbcx.sqlserver.SQLServerDataSource
Requires
JDBC driver files:
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.)
Requires
DataStoreHelper class:
com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Requires
a valid authentication alias.
Requires properties:
- serverName The name of the server in which MS SQL Server resides.
Example: myserver.mydomain.com
- portNumber The TCP/IP port that MS SQL Server uses for communication.
Port 1433 is the default.
- databaseName The name of the database from which the data source
obtains connections. Example: Sample.
- DataDirect ConnectJDBC type 4 driver for MS SQL Server
(XA)
DataDirect ConnectJDBC type 4 driver for MS SQL
Server (XA) is a Type 4 JDBC driver which provides XA-compliant JDBC access
to the MS SQL Server 2005 and MS SQL Server 2000 databases. This provider
is for use only with the Connect JDBC driver purchased from DataDirect Technologies.
This JDBC provider
supports this data source:
com.ddtek.jdbcx.sqlserver.SQLServerDataSource.
Requires JDBC driver files:
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.)
Requires
DataStoreHelper class:
com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Requires a valid authentication alias.
Requires properties:
- serverName The name of the server in which MS SQL Server resides.
Example: myserver.mydomain.com
- portNumber The TCP/IP port that MS SQL Server uses for communication.
Port 1433 is the default.
- databaseName The name of the database from which the data source
obtains connections. Example: Sample.
- IBM WebSphere embedded ConnectJDBC driver for MS SQL
Server
WebSphere embedded ConnectJDBC driver for MS
SQL Server is a Type 4 JDBC driver that provides JDBC access to the MS SQL
Server 2005 and MS 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.
This JDBC provider
supports this data source:
com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource.
Requires JDBC driver files:
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.
Requires
DataStoreHelper class:
com.ibm.websphere.rsadapter.WSConnectJDBCDataStoreHelper
Requires a valid authentication alias.
Requires properties:
- serverName The name of the server in which MS SQL Server resides.
Example: myserver.mydomain.com
- portNumber The TCP/IP port that MS SQL Server uses for communication.
Port 1433 is the default.
- databaseName The name of the database from which the data source
obtains connections. Example: Sample.
- IBM WebSphere embedded ConnectJDBC driver for MS SQL
Server (XA)
WebSphere embedded ConnectJDBC driver
for MS SQL Server (XA) is a Type 4 JDBC driver that supports two-phase commit
transactions on connections with the MS SQL Server 2005 and MS 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.
The ConnectJDBC
provider supports the following data source:
com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource.
Requires JDBC driver files:
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.
Requires
DataStoreHelper class:
com.ibm.websphere.rsadapter.WSConnectJDBCDataStoreHelper
Requires a valid authentication alias.
Requires properties:
- serverName The name of the server in which MS SQL Server resides.
Example: myserver.mydomain.com
- portNumber The TCP/IP port that MS SQL Server uses for communication.
Port 1433 is the default.
- databaseName The name of the database from which the data source
obtains connections. Example: Sample.
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.
Install
Stored Procedures for JTA by performing the following steps:
- Determine whether you are running the 32-bit or 64-bit MS SQL Server and
select the appropriate sqljbc.dll and instjdbc.sql files.
- Stop your MS SQL Server service.
- Copy the sqljdbc.dll file into your %SQL_SERVER_INSTALL%\Binn\ directory.
- Restart the MS SQL Server service.
- 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 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 WebSphere Application Server JDBC access to MS 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 MS SQL Server,
you can configure it through the WebSphere Application Server administrative
console. Follow the steps listed in
Configuring a JDBC provider using the administrative console.
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 your user-defined JDBC provider type. Replace those defaults
with the following settings that are specific to the DataDirect SequeLink
type 3 JDBC driver.
Incompatible with MS SQL Server 2005: Use this JDBC driver for access to MS SQL Server 2000 only.
DataDirect
SequeLink type 3 JDBC driver supports the following data source:
com.ddtek.jdbcx.sequelink.SequeLinkDataSource
Requires JDBC driver files:
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.)
Requires
DataStoreHelper class:
com.ibm.websphere.rsadapter.SequeLinkDataStoreHelper
Requires a valid authentication alias.
Requires properties:
- serverName The name of the server in which SequeLink Server resides.
Example: myserver.mydomain.com
- portNumber The TCP/IP port that SequeLink Server uses for communication.
By default, SequeLink Server uses port 19996.
- databaseName The name of the database from which the data source
obtains connections. Example: Sample.
- enable2phase This property is necessary only if your application
must participate in two-phase transactions. By default, Application Server
sets a user-defined implementation type to a connection pool data source.
The connection pool data source supports only one-phase transactions. Configure
two-phase transaction support by setting the enable2Phase custom property
on each data source that you create with your user-defined JDBC provider.
Follow these steps:
- Go to the Custom properties administrative console page by clicking Resources > JDBC
Providers > your_JDBC_provider > Data sources > your_data_source > Custom
properties.
- Click New.
- Input enable2Phase as the property name and assign it
the value of true. For Version 4 data sources, use a property
that works in the opposite manner: Input disable2Phase and
assign it the value of false.
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 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 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 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 MS 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 MS 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 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 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.)
Requires
DataStoreHelper class:
com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Requires a valid authentication alias.
Requires properties:
- serverName The name of the server in which MS SQL Server resides.
Example: myserver.mydomain.com
- portNumber The TCP/IP port that MS SQL Server uses for communication.
Port 1433 is the default.
- databaseName The name of the database from which the data source
obtains connections. Example: Sample.
- enable2phase This property is necessary only if your application
must participate in two-phase transactions. By default, Application Server
sets a user-defined implementation type to a connection pool data source.
The connection pool data source supports only one-phase transactions. Configure
two-phase transaction support by setting the enable2Phase custom property
on each data source that you create with your user-defined JDBC provider.
Follow these steps:
- Go to the Custom properties administrative console page by clicking Resources > JDBC
Providers > your_JDBC_provider > Data sources > your_data_source > Custom
properties.
- Click New.
- Input enable2Phase as the property name and assign it
the value of true. For Version 4 data sources, use a property
that works in the opposite manner: Input disable2Phase and
assign it the value of false.