Database-specific administrative tasks for JDBC applications<?Pub Caret>

Database-specific administrative tasks for JDBC applications

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 WebSphere Application Server 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 WebSphere Application Server 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 WebSphere Application Server 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.

Enabling 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 WebSphere Application Server 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
1. Stop the DB2 JDBC Applet Server service.
2. Run the following batch file:
```
SQLLIB\java12\usejdbc2.bat
```
3. Stop WebSphere Application Server (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 WebSphere Application Server, 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 WebSphere Application Server to host applications requiring data access, source the db2profile:

. ~db2inst1/sqllib/db2profile

where db2inst1 is the user created during DB2 installation.

Using 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 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 WebSphere Application Server administrative service.
Stop any other processes that use the db2java.zip file.
Make sure 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 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

You can 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:
- Click 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.

Related concepts
Data sources