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 WebSphere Application Server version, edition, and FixPak.

 

Notes about various databases

  • When using local DB2 databases for data access by session clients on AIX V4.3.3 or later versions, in some cases one 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.

  • 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 WAS administration:

  • Enabling JDBC 2.0 with DB2 on Windows NT systems

    To enable JDBC 2.0 use on Windows NT systems:

    • For DB2 V7.2

      1. Stop the DB2 JDBC Applet Server service.

      2. Run the following batch file: 

        SQLLIB\java12\usejdbc2.bat

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

    • For DB2 V8.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 V7.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 V8.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 V7.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 V8.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 V7.2

      • To determine if you are using JDBC 2.0, one 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 V8.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:

    1. 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).

    2. 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:

    1. Stop all DB2 services.

    2. Stop the IBM WAS administrative service.

    3. Stop any other processes that use the db2java.zip file.

    4. Make sure that you already enabled JDBC 2.0.

    5. Start the DB2 services.

    6. 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

    7. 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

    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, one 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, one 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:

    1. 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

    2. Stop the Sybase Adaptive Server database and start it again.

    3. 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


Recreating database tables from the exported table data definition language

 

See Also


Data sources

 

See Also


Data access : Resources for learning