WebSphere Adapter for JDBC

Solutions to some common problems

Some of the problems you may encounter running the Adapter for JDBC with your database are provided, along with solutions and workarounds. These problems and solutions are also documented as technotes on the software support Web site.

Attribute not created in business object for Oracle column of NCHAR type

Problem

When a business object is generated from an Oracle database object, an attribute is not created in the business object for columns of type NCHAR.

Cause

The JDBC driver returns the type of the column as Other, which is not supported by enterprise service discovery.

Solution

Use the Business Object Designer tool to manually add attributes for supported types to the business object.

Class loader violation occurs when starting JDBC enterprise service discovery

Problem

It is not possible to use JDBC enterprise service discovery after using a connection to the database in the Data perspective. At the end of the second panel of the enterprise service discovery wizard, the following exception is generated: com.ibm.adapter.framework.api.ImportException
Reason: class loading constraint violated (class:
oracle/jdbc/driver/OracleConnection
method: getWrapper()Loracle/jdbc/OracleConnection;) at pc:0

The error occurs in both of the following situations:
  • When you establish a connection to the database through the enterprise service discovery, an error occurs when you attempt to connect to the database from the Data perspective.
  • When you establish a connection to the database through the Data Perspective, the error occurs when you attempt to connect to database through enterprise service discovery.

Cause

The error occurs because the Data Perspective and enterprise service discovery use their own class loaders. Once the DLL, which is the native library used by the JDBC driver, is loaded in the Data Perspective, it cannot be loaded again in enterprise service discovery. JVMs have an inherent restriction that only allows one class loader to load native libraries at any given time. So if class loader A loads DLL B, then no other class loader can load DLL B until class loader A is released and garbage is collected. Because you cannot really control garbage collection, it usually means that if you want to load DLL B with another class loader, you need to restart the JVM. This limitation is a known one and is documented for WebSphere® Application Server.

Solution

The only solution is to restart WebSphere® Integration Developer when this error occurs.

Closed connection error occurs when using XA with Oracle 10g

Problem

When the Adapter for JDBC is used to perform an XA transaction using Oracle 10g, the adapter generates a closed connection exception: javax.resource.ResourceException: Closed Connection.

Cause

This is a known issue with the Oracle 10g database driver. The following bug has been filed with Oracle for this issue: 3488761 Connection closed error from OracleConnection.getConnection() - 10G drivers.

Solution

The bug has been fixed in the Oracle 10g Release 2 driver. As a workaround, you can use the Oracle 9i JDBC thin drivers to connect to the database for XA transactions.

Error occurs while starting a transaction on Oracle

Problem

When the Adapter for JDBC is used to perform an XA transaction using Oracle database, the following error is generated: WTRN0078E: An attempt by the transaction manager to call start on a transactional resource has resulted in an error. The error code was XAER_RMERR.

Cause

For the Oracle database server to support XA transactions, some commands need to be run.

Solution

Two scripts that are included in the Oracle directory should be run. This activity will likely need to be performed by your Oracle database administrator, because you must be logged into Oracle as SYSOPER or SYSDBA in order to have the necessary permissions for these scripts to work. The scripts are:
<ORACLE_HOME>javavm\install  
file: initxa.sql  
file: initjvm.sql  

The initxa.sql script configures the database for XA. Once it runs successfully, your database is configured for XA. The script might run successfully the first time you try. Unfortunately, it probably will not run successfully because some of the database's memory spaces are too small.

To fix this, run the initjvm.sql script. It will probably fail too, but in doing so, it will indicate which parameters need to be adjusted. The parameters are stored in this file:
<ORACLE_HOME>\database  
file: init<DATABASE_SID>.ora
The following table shows two parameters that typically need to be increased. Your particular database configuration might require adjustment of different parameters.
Table 1.
Parameter Name Minimum Value
java_pool_size 12000000
shared_pool_size 24000000

Using the adapter to connect to DB2 on IBM® z/OS with a JDBC (Type 2 or Type 4) universal driver

Problem and Cause

DB2® on z/OS® supports querying stored procedure metadata by using positional index by default and not using column name, which the Adapter for JDBC uses. The solution provides the steps for using the WebSphere Adapter for JDBC with DB2 on the z/OS platform.

Solution

To connect to DB2® on z/OS® using the Adapter for JDBC or using DB2 Connect™ with JDBC API, ensure that the following connection requirements are met:

  • The physical representation of the universal JDBC driver is the db2jcc.jar file. Ensure that the path to this file is set in the class path.
  • Database URL: To determine whether you are using the Type 2 or Type 4 driver, review the form of the connection:

    Type 2: jdbc:db2:database
    (For example: jdbc:db2:MyDB, where MyDB is the database name)

    Type 4: jdbc:db2://server:port/database
    (For example: jdbc:db2://9.182.15.129:50000/MyDB, , where MyDB is the database name)

  • Driver class: com.ibm.db2.jcc.DB2Driver.

    The driver class for both Type 2 and Type 4 drivers is the same.

  • Set the path of the db2jcc_license_cisuz.jar file in the class path.

    The license JAR file is the same for both Type 2 and Type 4 drivers. Access to DB2 for z/OS and DB2 for i5/OS® servers requires a valid DB2 Connect™ license. DB2 clients do not provide direct connectivity to zSeries® and iSeries™ servers without a DB2 Connect license.

    For more information on DB2 Connect licensing and usage, refer to the following pages:
    http://www-128.ibm.com/developerworks/db2/library/techarticle/0303zikopoulos1/0303zikopoulos1.html
    http://www-128.ibm.com/developerworks/db2/library/techarticle/0301zikopoulos/0301zikopoulos.html

There might be issues with importing metadata for stored procedures using enterprise service discovery. To use stored procedures and import metadata from DB2 using the Adapter for JDBC, DB2 needs to be reconfigured as described in the following steps. Follow these steps in addition to the steps provided above:

  • Apply the following APARs on DB2: PQ62695, PQ55393, PQ56616, PQ54605, PQ46183, and PQ62139.
  • If you want to use stored procedures with the adapter, follow the steps below, which are part of the fix for PQ62695. This fix introduces stored procedures that provide the ability to generate a result set that corresponds to the Schema Metadata APIs documented in the JDBC and ODBC specification.
    These procedures will be used by the JDBC and ODBC drivers provided in the DB2 Universal Driver. Follow these steps to enable support for stored procedures:
    1. Apply the APAR.
    2. Check the value of the DESCSTAT variable in the ZPARM assembly job DSNTIJUZ. If the value of the DESCSTAT variable is NO, change it to YES.
      Note: The default for DESCSTAT is NO on V7 but was changed to YES on V8.
    3. Reassemble and re-initialize the ZPARM module.
    4. Run the JCL job named DSNTIJMS. You can find this member in the db2prefix.SDSNSAMP data set.
    5. Restart DB2.

Using transactions in lieu of wrapper business objects

This applies only to the English language version.

Problem

The WebSphere Business Integration Adapter for JDBC did not expose support for transactions to the integration broker (WebSphere InterChange Server). This created a problem when users wanted to ensure that two unrelated objects, for example, a customer and an order, were created in their enterprise information system (EIS) at the same time.

To do this, two separate requests had to be sent, and if the adapter went down between requests, only one of the objects was created. Because of limitations of the WebSphere InterChange Server adapter interface, the solution was to allow users to send multiple requests in a single batch to the adapter through a wrapper object. This wrapper object was a dummy container to hold 1..n child business objects, and a commit to the backend database was issued only after each of the child business objects was processed successfully.

Solution

The new WebSphere Adapter for JDBC (JCA) supports local and global transactions. Now, when you want to send a request that simultaneously creates a customer and an order, for example, you can start a transaction from an adapter client (for example, a mediation or business process) and send the requests as you see fit. Once you have sent everything you want to send, you commit the transaction. There is no need for a wrapper business object.

Using XA transactions for outbound support with a remote DB2 database

This provides the steps, database versions, and configuration requirements for XA transaction support using the Adapter for JDBC with a remote DB2 database.

Using XA Transactions on a remote DB2 database

Adding a remote DB2 database

  1. Run the db2admin (<DB2_Installpath>\SQLLIB\BIN) command on the DB2 server machine.
  2. Open the DB2 Configuration Assistant.
  3. Go to View > Advanced View.

Perform the following four tasks–adding the remote system, adding an instance node, adding a database, and testing the database connection–in the order in which they are described.

Adding the remote system

  1. Select Systems tab.
  2. From the menu, choose Selected > Add System .
  3. In the System name field, specify the physical machine, server system, or workstation where the target database is located. The system name on the server system is defined by the DB2SYSTEM DAS configuration parameter. This is the value that you should use.
  4. In the Host name field, type the host name or Internet Protocol (IP) address where the target database resides.
  5. In the Node name field, specify a local nickname for the remote node where the database is located. The node name you choose must not already exist in the node directory or the admin node directory.
  6. Select the operating system and click OK.

Adding an instance node

  1. Select the Instance Nodes tab.
  2. From the menu, choose Selected > Add Instance Node .
  3. In the System name field, specify the physical machine, server system, or workstation where the target database is located. Select the system added in the Adding a remote system task.
  4. In the Instance name field, type the name of the instance (DB2, and so on) where the target database is located.
  5. In the Instance node name field, specify a unique nickname for the cataloged system (node) where the database is located. The node name you choose must not already exist in the node directory or the admin node directory.
  6. Select the operating system and type the host name. Use the same host name as in step 4 of the task: Adding the remote system.
  7. Enter the port number on which the remote DB2 instance is running.
  8. Click OK.

Adding a database

  1. Select the Databases tab.
  2. From the menu, choose Selected > Add Database.
  3. In the Instance node field, choose the instance created in the task: Adding an instance node. Specify the name of the database that you are adding in the Database name field.
  4. In the Alias field, specify a local nickname that can be used by applications running on your workstation. If nothing is entered, the alias will be the same as the database name. The alias name should be unique.
    Note: This alias name value should be entered in the XADatabaseName property for the adapter.

Testing the database connection

  1. Select the Databases tab.
  2. Choose the database added in the task: Adding a database.
  3. From the menu, choose Selected > Test Connection.
  4. Select the CLI check box, type the User Id and Password and click Test Connection. This should return a successful connection.

Using XA Transactions with the Adapter for JDBC using Universal Driver

The following versions of software and configuration properties are needed to use XA Transactions with the Adapter for JDBC (JCA) and a remote DB2 database.
  • DB2 version: 8.2
  • JDBC Driver: UDB driver (db2jcc.jar) Type 4
  • XA datasource name: com.ibm.db2.jcc.DB2XADataSource
  • XA Database name: This is the remote database alias configured on the local DB2 client.
  • Database URL: jdbc:db2://hostname:port/databasename
  • JDBC driver class: com.ibm.db2.jcc.DB2Driver

WebSphere Adapters have no separate logs for inbound and outbound operations

Problem

If you create and bind an import and an export to the same adapter instance (with the same AdapterID property) and you name the logs for the two bindings differently, for example: "a.log" for the import and "b.log" for the export, you will find after deploying the project to WebSphere Process Server, that the resource adapter has only one log, the one named "b.log."

Cause

WebSphere® Adapters do not distinguish messaging from inbound and outbound operations, and therefore create only one adapter log file.

Solution

Configure the inbound and outbound operations with the same log name if Import and Export are binding to the same adapter instance. If the inbound and outbound belong to different adapters and each has a different AdapterID, this issue will not exist.


Terms of use |

Last updated: Tue 12 Dec 2006 03:32:39

(c) Copyright IBM Corporation 2005, 2006.
This information center is powered by Eclipse technology (http://www.eclipse.org)