Relationship of assembly and administrative console data access settings: WAS v5.1

Relationship of assembly and administrative console data access settings

This article provides miscellaneous tips for using supported databases. See also the related links.
Always consult the product documentation for a list of the database brands and versions that are supported by your particular appserver version, edition, and FixPak.

Notes about various databases

When using local DB2 databases for data access by session clients on AIX Version 4.3.3 or later versions, in some cases you cannot establish multiple connections for session clients. This is because AIX , by default, does not permit 32-bit applications to attach to more than 11 shared memory segments per process. Of these 11 shared segments, a maximum of 10 can be used for local DB2 connections. To use EXTSHM with DB2 and avoid stale connections when there are large numbers of session clients, do the following:

In DB2 client environment (that is the WAS run time environment in this case)
export EXTSHM=ON

In DB2 UDB Server environment
export EXTSHM=ON db2set DB2ENVLIST=EXTSHM

When using Sybase 11.x, you might encounter the following error when HttpSession persistence is enabled:
DBPortability W Could not create database table: "sessions" com.sybase.jdbc2.jdbc.SybSQLException: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'database_name' database
where database_name is the name of the database for holding sessions.
If you encounter the error, issue the following commands at the Sybase command line
use database_name go sp_dboption db,"ddl in tran ",true go

Sybase 12.0 does not support local transaction modes with a JTA enabled data source. To use a connection from a JTA enabled data source in a local transaction, install Sybase patch EBF9422.

Additional administrative tasks for specific databases

For your convenience, this article provides instructions for enabling some popular database drivers, and performing other administrative tasks often required to provide data access to applications running on WebSphere Application Server. These tasks are performed outside of the WAS administrative tools, often using the database product tools. Always refer to the documentation accompanying your database driver as the authoritative and complete source of driver information.
See the Supported hardware, software, and APIs for the latest information about supported databases, drivers, and operating systems.

Enable JDBC 2.0

Ensure that your operating system environment is set up to support JDBC 2.0. This action is required to use data sources created through WebSphere Application Server.
The following steps make it possible to find the appropriate JDBC 2.0 driver for use with WAS administration...
Enabling JDBC 2.0 with DB2 on Windows NT systems

To enable JDBC 2.0 use on Windows NT systems...

For DB2 Version 7.2

Stop the DB2 JDBC Applet Server service.
Run the following batch file:
SQLLIB\java12\usejdbc2.bat

Stop WAS (if it is running) and start it again.

For DB2 Version 8.1

JDBC 2.0 is supported by default, there are no additional steps for you to perform.

Perform the steps once for each system.
Determining the level of the JDBC API in use for DB2 on Windows NT systems

To determine the JDBC level in use on your system...

For DB2 Version 7.2

If JDBC 2.0 is in use, this file exists:
SQLLIB\java12\inuse

If JDBC 1.0 is in use, this file exists:
SQLLIB\java11\inuse

...or no java11 directory exists.

For DB2 Version 8.1

Go to directory SQLLIB\samples\java, compile and run the class db2JDBCVersion.java.

Enabling JDBC 2.0 with DB2 on UNIX systems

For DB2 Version 7.2

Before starting WAS, call $INSTHOME/sqllib/java12/usejdbc2 to use JDBC 2.0. For convenience, you might want to put this in your root user's startup script. For example, on AIX, add the following to the root user's .profile
if [ -f /usr/lpp/db2_07_01/java12/usejdbc2 ] ; then . /usr/lpp/db2_07_01/java12/usejdbc2 fi

For DB2 Version 8.1

JDBC 2.0 is supported by default, there are no additional steps for you to perform.

Determining the level of the JDBC API in use for DB2 on UNIX systems

For DB2 Version 7.2

To determine if you are using JDBC 2.0, you can echo $CLASSPATH. If it contains
$INSTHOME/sqllib/java12/db2java.zip

then JDBC 2.0 is in use.
If it contains
$INSTHOME/sqllib/java/db2java.zip

then JDBC 1.0 is in use.

For DB2 Version 8.1

Go to directory sqllib/samples/java, compile and run the class db2JDBCVersion.java.

Sourcing the db2profile script on UNIX systems

Before starting WAS to host applications requiring data access, source the db2profile...
. ~db2inst1/sqllib/db2profile

where db2inst1 is the user created during DB2 installation.

Use Java Transaction API drivers

Instructions are available for using Java Transaction API (JTA) drivers on particular operating systems. See your operating system documentation for more information.
The goal of this section is to provide information about the steps that make DB2 work well with applications utilizing XA classes -- that is, those whose dataSourceClasses implement javax.sql.XADataSource.
Using Java Transaction API drivers for DB2 on Windows NT systems

To enable JTA drivers for DB2 on Windows NT systems, follow these steps...

Bind the necessary packages to the database. From the DB2 Command Line Processor window, issue the following commands:
db2=> connect to mydb2jta db2=> bind db2home\bnd\@db2cli.lst db2=> bind db2home\bnd\@db2ubind.lst db2=> disconnect mydb2jta

where mydb2jta is the name of the database to enable for the JTA, and db2home is the DB2 root installation directory path (for example, D:\ProgramFiles\SQLLIB\bnd\@db2cli.lst).
Specify the following settings when you use an IBM WebSphere Application Server administrative client (such as the WebSphere Administrative Console) to configure a JDBC driver:

Server class path = %DB2_ROOT%/Sqllib/java/db2java.zip
Implementation class name = COM.ibm.db2.jdbc.DB2XADataSource

Using Java Transaction API drivers for DB2 on UNIX systems

To enable JTA drivers on UNIX systems, follow these steps...

Stop all DB2 services.
Stop the IBM WAS administrative service.
Stop any other processes that use the db2java.zip file.
Verify that you already enabled JDBC 2.0.
Start the DB2 services.
Bind the necessary packages to the database. From the DB2 command-line process or window, issue the following commands:
db2=> connect to mydb2jta db2=> bind db2home\bnd\@db2cli.lst db2=> bind db2home\bnd\@db2ubind.lst db2=> disconnect mydb2jta

Specify the following settings when you use an IBM WebSphere Application Server administrative client (such as the WebSphere Administrative Console) to configure a JDBC driver:

Server class path = $INSTHOME/sqllib/java12/db2java.zip
For example, if $INSTHOME is /home/test, the path will be /home/test/sqllib/java12/db2java.zip
Implementation class name = COM.ibm.db2.jdbc.DB2XADataSource

For Oracle 8.1.7 two phase commit support

Use the Oracle 8.1.7 thin driver for JTA two-phase support with the following restrictions...

The thin driver that comes shipped with 8.1.7 might or might not work. Future patches from Oracle might work as well, but are not tested. The driver that was available from the Oracle Technology Network Web site as of February 20, 2001 does work and is the recommended driver. Later versions on this Web site are expected to work, but are not tested.
To obtain the driver from the Oracle support Web site, visit:
http://technet.oracle.com/

You need to be a registered user for the Oracle Technology Network to get the driver from this site. Contact Oracle for access. After you have access download the 8.1.7 driver for the platforms you use and follow the instructions for installing the new driver.
You must use the 8.1.7 driver with 8.1.7 databases, 8.1.6 databases do not support the recover() and forget() methods and other problems are encountered running with 8.1.6. Oracle does not support JTA with 8.1.6.
For Oracle, you can only use JTA with container-managed persistence (CMP) beans.
For the bean to create the table, start the bean with the JTA set to false. After the bean creates the table, you can set the JTA back to true.
Configure an entity bean that accesses Oracle with JTA set to true as follows:

Click...
deployment descriptor properties | Transactions | Remote tab

Set the Transaction Attribute to TX_REQUIRED.
Click...
Isolation | Remote tab
Set the Isolation Level to TRANSACTION_READ_COMMITTED.

Configure a session bean that is used with an entity bean that accesses Oracle with JTA set to true as follows...

Go to...
deployment descriptor properties | Transactions | Remote tab

Set the Transaction Attribute to TX_BEAN_MANAGED.
Click...
Isolation | Remote tab

Set the Isolation Level to TRANSACTION_READ_COMMITTED.

Using Java Transaction API drivers for Sybase products on AIX systems

To enable Java Transaction API (JTA) drivers for use with Sybase products on the AIX operating system, follow these steps...

Enable the Data Transaction Manager (DTM) by issuing these commands (one per line) at a command prompt:
isql -Usa -Ppassword -Sservername sp_configure "enable DTM", 1 go

Stop the Sybase Adaptive Server database and start it again.
Grant the appropriate role authorization to the enterprise bean user at a command prompt:
isql -Usa -Ppassword -Sservername grant role dtm_tm_role to EJB go

Notes about Sybase Java Transaction API drivers

Do not use a Sybase Java Transaction API (JTA) connection in an enterprise bean method with an unspecified transaction context. A Sybase JTA connection does not support the local transaction mode. The implication is that use the Sybase JTA connection in a global transaction context.

See Also
Data sources
Recreating database tables from the exported table data definition language
Data access : Resources for learning