IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > JDBC > Troubleshooting and support

Solutions to common problems

Some of the problems you might encounter while running IBM WebSphere Adapter for JDBC with your database are described along with solutions and workaround. These problems and solutions are in addition to those documented as technotes on the software support Web site.

For a complete list of technotes about IBM WebSphere Adapters, see http://www.ibm.com/support/search.wss?tc=SSMKUK&rs=695&rank=8&dc=DB520+D800+D900+DA900+DA800+DB560&dtm.


Invalid date format exception during inbound when the date field is set as a primary key


Problem

During inbound processing, if the PrimaryKey is of the Date type, and in the discovered objects if the 'DateFormat' application-specific information is set to a value different than the database default value, the processing fails, and the 'Invalid date format' or 'ParseException' exception is generated.


Solution

Ensure that the 'DateFormat' application-specific information format matches the value entered in the 'Object_Key' field of the WBIA_JDBC_EventStore table.

For more information see, WebSphere Adapter for JDBC Version 6.1 Receives unparseable date exception and Date and time format for object_key column in event table of WebSphere Adapter for JDBC.


Cannot add object to selection


Problem

At design time, IBM WebSphere Adapter for JDBC enterprise service discovery process fails when the adapter tries to import a stored procedure from Sybase using the jConnect driver.

The problem does not occur when using the jTDS 1.2.2 driver.

The following message is generated: Cannot add object to selection: com.sybase.jdbc2.jdbc.SybSQLException: The "CREATE TABLE" is not allowed within a multi-statement transaction in 'tempdb' database.


Cause

The Auto commit property Set Auto Commit on database connection was not selected during enterprise service discovery process and the transaction mode setting for the stored procedure in the Sybase database was set to the default value of "unchained mode". The default "unchained mode" requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.


Solution

Evaluate the stored procedure definition to determine if you can change it to appropriately process transactions. If you cannot change the stored procedure definition, you can select Set Auto Commit on database connection from the Specify the Discovery Properties window of the external service wizard and rerun the discovery process.

By selecting Set Auto Commit on database connection you can automatically override the default processing associated with the "unchained mode" configuration. For detailed information about how transaction modes work for the Sybase database, see the Sybase database documentation.

If you select Set Auto Commit on database connection from the Specify the Discovery Properties window of the external service wizard, you also need to select Set Auto Commit on database connection on the last screen of the external service wizard. The value for Set Auto Commit on database connection on the last screen applies to the managed connection factory property used by the adapter at run time to create an outbound connection instance with the database.


CLOB data type of 4K or larger cannot be inserted into Oracle 9i or 10g databases


Problem

The following exception is generated when inserting CLOB (character large object) values of 4K and larger into Oracle 9i or 10g databases:


Cause

You are using an older version of the driver that does not correctly support CLOBs larger than 4K.


Solution

Use the Oracle thin driver from Oracle 10.1.0.2 or a later release.


Some generated business objects have no attributes for Oracle database objects


Problem

Some business objects that are generated from an Oracle database object do not have attributes for the table columns.


Cause

Under certain conditions, the Oracle JDBC driver does not return the column information for a database object. The following bugs are currently filed with Oracle for these issues:

Also, column information is not returned if a private synonym that references an object in another schema is used.


Solution

For tables that have a synonym, generate the business object using the synonym for the table.

For synonyms of a procedure, generate the business object using the original procedure that the synonym is based on.

For private synonyms that reference an object in another schema, either use the original table or create a synonym in the current schema.


Use IBM WebSphere Adapter for JDBC to connect to IBM DB2 for z/OS with a JDBC (Type 2 or Type 4) universal driver


Problem and Cause

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


Solution

To connect to DB2 for z/OS using the adapter, ensure that the following connection requirements are met:

There might be issues with importing metadata for stored procedures using the wizard. To use stored procedures and import metadata from DB2 using the adapter, DB2 needs to be re-configured as described in the following steps. Follow these steps in addition to the preceding steps:


Use XA transactions for outbound support with a remote DB2 database


Use XA Transactions with the IBM WebSphere Adapter for JDBC using the Universal Driver

The following versions of software and configuration properties are required to use XA transactions with the adapter and the Universal driver to connect to a remote DB2 database:


Use XA transactions for Outbound support with a remote DB2 database

Here is the configuration requirements for XA support for IBM WebSphere Adapter for JDBC with a remote DB2 database.


Use XA Transactions on a remote DB2 database


Adding a remote DB2 database

To add the database commands to create an alias in the local database, you can use Command line or DB2 configuration assistant.

  1. Command line

    The following are the commands to make a local instance of the remote database:

    1. Db2 catalog tcpip node <nodename> remote server <hostname/ipaddress> server <servicename>
    2. Db2 catalog database <databasename> as <databasename> atnode <nodename>
    3. Db2 connect to <databasename> user <username> using <password>
  2. DB2 configuration assistant

    1. Run the db2admin ( DB2_InstallPath\SQLLIB\BIN) command on the DB2 server.
    2. Open the DB2 Configuration Assistant.

    3. Go to View > Advanced View.
    4. Add the remote system

      1. Select the Systems tab.

      2. From the menu, select 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 selected must not already exist in the node directory or the admin node directory.

      6. Select the operating system and click OK.
    5. Add an instance node

      1. Select the Instance Nodes tab.

      2. From the menu, select 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 selected 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.
    6. Add a database

      1. Select the Databases tab.

      2. From the menu, select Selected > Add Database.

      3. In the Instance node field, select 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.

        This alias name value should be entered in the XADatabaseName property for the adapter.

        The following are the properties set in the EMD to connect to the remote database for the JDBC adapter outbound at node level :

        1. DataBase Vendor : DB2
        2. XA Datasource name :com.ibm.db2.jcc.DB2XADataSource.
        3. XA Database name : database_name.
Test the database connection

  1. Select the Databases tab.

  2. Choose the database added in the task: Adding a database.

  3. From the menu, select 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.


A closer look at the transaction (XID) column in the event table

If the adapter is configured for assured once delivery, use the status column with the XID column to determine whether the event has been processed:


Handling unexpected results from a query SQL statement

If you receive unexpected results from a query, turn the tracing on and look at the query SQL in the log. Turning on tracing is especially helpful when you are in the test client to see if you forgot to unset all the unnecessary attributes. It is also practical to turn on tracing to determine if you filled in your input business object correctly.


Configure SQL Server 2000 to support XA transaction

To configure SQL SERVER 2000 to support XA transaction:

  1. Copy sqljdbc.dll from Microsoft SQL Server 2000 Driver for JDBC\SQLServer JTA\ to the path sqlserver_install_directory\MSSQL\Binn.
  2. Open the SQL Query Analyzer and run Microsoft SQL Server 2000 Driver for JDBC\SQLServer JTA\instjdbc.sql.


IBM WebSphere Adapter for JDBC fails to connect to the SQL Server 2000 using SQL Server 2000 JDBC driver


Cause

This is a limitation with SQL Server 2000 JDBC driver.


Solution

You can use one of the following approaches to solve this problem:


Multiple IBM WebSphere Adapter for JDBC export components in the same SCA module

When multiple adapter export components exist in the same SCA module, these exports do not pick up and operate on the same event record from same event table.

If you define multiple exports to access the same event record, potential problems like database deadlock error might occur. You have to configure different EventTypeFilter (Event types to process) condition on each export for multiple adapter export components picking up records from the same event table, so that the risk of one event record being picked up by several exports is eliminated.


Cannot create business object correctly for stored procedures with different specific names in iSeries DB2


Cause

For DB2 iSeries JDBC driver Toolbox for Java™ & JTOpen, the connection property source "metadata source" is used to specify how to retrieve DatabaseMetaData from the database. When it is set to "0", the metadata is retrieved through the Retrieve Object Information (ROI) data flow. When it is set to "1", the metadata is retrieved by calling system stored procedures. For version 6.1 and earlier, by default, the metadata source is not set to 1. The database metadata is retrieved through the Retrieve Object Information (ROI) data flow. This causes the problem.

The default for the DB2 iSeries version 7.1 is to retrieve database metadata by calling stored procedures.

Here is the detail information about the database metadata in the jtopen_6_4_source\com\ibm\as400\access\doc-files\JDBCProperties.html from the JTOpen download Web site : http://sourceforge.net/projects/jt400/files/JTOpen-full/6.4/


Solution

Add the metadata source=1 to the connection URL and ensure that there is no space after ";" when connecting to DB2 iSeries version 6.1 and earlier.

For example, jdbc:as400://wsbcas12.rtp.raleigh.ibm.com;metadata source=1


Changing the schema without regenerating the artifacts

If only the schema is changed and the table name is not changed, edit the *.export file for inbound or *.import file for outbound, and *.xsd files and change the original schema to the new schema as described below:

To make changes to the import or export file:

  1. In the Java™ perspective, open the *.import or *.export file in the text editor.

    For example, JDBCInboundInterface.export.

    Following is the code snippet for JDBCInboundInterface.export: 

    <connection type="com.ibm.j2ca.jdbc.inbound.JDBCActivationSpecWithXid" 
listenerType="com.ibm.j2ca.base.ExtendedInboundListener" 
selectorType=
"com.ibm.j2ca.extension.emd.runtime.StructuredDataFunctionSelector">
      <properties>
        <databaseURL>jdbc:oracle:thin:@localhost:1521:ORA92
</databaseURL>
        <databaseVendor>ORACLE</databaseVendor>
        <jdbcDriverClass>oracle.jdbc.driver.OracleDriver
</jdbcDriverClass>
        <password>jcajdbc</password>
        <returnDummyBOForSP>false</returnDummyBOForSP>
        <userName>jcajdbc</userName>      
</properties>
    </connection>
  2. Change the existing user name and password to the user name and password used by new schema.

To make changes to the *.xsd files:

  1. Open the *.xsd file in the text editor.

    For example, JcajdbcCustomer.xsd

    Following is the code snippet for JcajdbcCustomer.xsd: 

    <jdbcasi:TableName>JCAJDBC.CUSTOMER</jdbcasi:TableName>
<jdbcasi:Operation>
<jdbcasi:Name>Retrieve</jdbcasi:Name>
<jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>BeforeRetrieveSP
</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>JCAJDBC.fn_beforeRetrievesSP
</jdbcasi:StoredProcedureName>
<jdbcasi:ReturnValue>RS</jdbcasi:ReturnValue>
<jdbcasi:Parameters>
<jdbcasi:Type>IP</jdbcasi:Type>
<jdbcasi:PropertyName>pkey</jdbcasi:PropertyName>
</jdbcasi:Parameters>
</jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>AfterRetrieveSP
</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>JCAJDBC.sp_afterRetrieveSP
</jdbcasi:StoredProcedureName>
<jdbcasi:Parameters>
<jdbcasi:Type>IP</jdbcasi:Type>
<jdbcasi:PropertyName>fname</jdbcasi:PropertyName>
</jdbcasi:Parameters>
<jdbcasi:Parameters>
<jdbcasi:Type>OP</jdbcasi:Type>
<jdbcasi:PropertyName>RS</jdbcasi:PropertyName>
</jdbcasi:Parameters>
</jdbcasi:StoredProcedures>
</jdbcasi:Operation>
</jdbcasi:JDBCBusinessObjectTypeMetadata>
  2. Update the schema name prefix for table and procedure names (if you defined in BO) with the new schema name. In the example, change JCAJDBC.CUSTOMER to <NewSchema>.CUSTOMER, change JCAJDBC.fn_beforeRetrievesSP to <NewSchema>.fn_beforeRetrievesSP, change JCAJDBC.sp_afterRetrieveSP to <NewSchema>.sp_afterRetrieveSP.


Result set does not contain any values when validating the stored procedure


Problem

When the adapter is configured to invoke a stored procedure on MS SQL Server 2000, the wizard does not return any values from the result set after selecting the "validate the syntax..." check box.


Solution

Either set AutoCommit ='true' or upgrade your driver to SQL Server version 2005.


Stored procedure name BIDI support for MS SQL Server


Problem

For MS SQL server, if the stored procedure name is in a language written from right-to-left, the business object configuration window for the stored procedure is not displayed. The log file displays the exception "java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value '??' to data type int". If the EMD wizard is completed ignoring this exception, the corresponding stored procedure business object is not generated.


Solution

The stored procedure name must be in a language written from left-to-right. Change the language in which the stored procedure name is represented.


Schema name BIDI support for Oracle database


Problem

For Oracle database, if the schema name is in a language written from right-to-left, the stored procedure validation fails. The log file displays the exception "java.sql.SQLException: ORA-06550: line xx, column xx: PLS-00302: component 'xxx' must be declared". If the EMD wizard is completed ignoring this exception, the corresponding stored procedure business object is generated. However, when the corresponding outbound operation is processed an exception is generated.


Solution

The schema name must be in a language written from left-to-right. Change the language in which the schema name is represented.


IBM WebSphere Adapter for JDBC fails when a table name or column name contains SQL keywords or other special characters


Problem

When the table or column name contains special characters like space, single quotation mark, or SQL reserved keywords, the adapter fails.


Cause

There are table names with special characters, for example, in Oracle server, "TABLE", "TABLE 2", "table", "table 2", "'TABLE'", "'TABLE 2'", or there are columns in a table with special characters, for example, "DESC", "DESC 2", "desc", "desc 2", "'COLUMN'", "'COLUMN 2'".


Solution

The terms "TABLE" and "DESC" are reserved SQL keywords. As these table names or column names are not handled correctly during the processing of the SQL statement, you have to edit the TableName or ColumnName application-specific information and enclose table names or columns with double quotation marks.

Here is an example in which the table name and column name contain a SQL keyword and a special character (space): 

<jdbcasi:TableName>YUANJS.table 2</jdbcasi:TableName>
<jdbcasi:ColumnName>DESC</jdbcasi:ColumnName>

Edit the ASI to include the table name and column name with double quotation marks. 

<jdbcasi:TableName>"YUANJS.table 2"</jdbcasi:TableName>
<jdbcasi:ColumnName>"DESC"</jdbcasi:ColumnName>


IBM WebSphere Adapter for JDBC and MySQL database support

During metadata discovery, the JDBC Enterprise Metadata Discovery (EMD) first retrieves the schema information from the database-specific JDBC driver and then depending on schema selection, the information about other database entities such as tables, procedures, views, and synonyms are retrieved. However, in the case of MySQL, the JDBC driver does not return schema information. This is because MySQL JDBC driver implementation does not return the information about database schemas in versions before version 5.0.4. Hence, you cannot proceed further with the discovery and artifact generation.


Sample SQL script for generating tables and triggers on IBM i


Problem

The JDBC Java EEConnector architecture (JCA) resource adapter ships a sample script for DB2 called scripts_db2.sql. For UDB on IBM i, the SQL syntax in the DB2 sample script does not function as it is written.


Cause

UDB requires a fully qualified schema SQL in the form SCHEMA.TABLE. The current script does not contain qualified schema.


Solution

Modify scripts_db2.sql for use on IBM i to include the fully qualified schema name with the table and trigger names of the table and trigger scripts.

For example, following is a sample table creation script from scripts_db2.sql: 

CREATE TABLE customer 
		( 
			pkey VARCHAR(10) NOT NULL PRIMARY KEY, 
			fname VARCHAR(20), 
			lname VARCHAR(20), 
			ccode VARCHAR(10) 
);

For use on IBM i, modify the script and replace <schema_name> with the fully qualified schema name as shown in the following code: 

CREATE TABLE <schema_name>.customer 
( 
		pkey VARCHAR(10) NOT NULL PRIMARY KEY, 
		fname VARCHAR(20), 
		lname VARCHAR(20), 
		ccode VARCHAR(10) 
);


IBM WebSphere Adapter for JDBC locks rows in the table causing other applications to timeout


Problem

The adapter was included as part of a global transaction accessing shared data in database tables. This shared data in the database tables was also accessed by another application. As part of the global transaction, the adapter held the locks on the rows, causing the other applications to timeout when trying to access the same shared data.


Solution

Remove the adapter from the global transaction if you are accessing shared data. Instead, use some mechanism at the business level to ensure that the business data is consistent.

For example, use a variable to indicate if the operation on the shared data is successful or not and log the critical operation on this data.


Incorrect binary data is returned when querying DB2/AS400


Problem

When performing a query against DB2/AS400 using the adapter version 6.1, the binary fields are returned as EBCDIC characters on IBM i.

For example, the field returns 4040f9f9f1f0f0f0f1f7f6f1 instead of 9910001761. The field in the AS400 is defined as BINCHAR and tagged with CCSID 65535.


Cause

The field in the IBM i database is tagged with CCSID 65535. The toolbox JDBC driver recognizes this CCSID as a field that should not be translated.


Solution

Tag fields that you want to be translated with a valid CCSID. Alternately, you can set the "translate binary" connection property to "true". This instructs the JDBC driver to translate all fields, including those tagged with CCSID 65535. A quick way to do this is to add ";translate binary=true" to the end of the URL used, when connecting to the database.


Error when using stored procedure with return value


Problem

The adapter fails when processing stored procedures that have a return value. The error message generated would be something like: Procedure 'GetPolicyCount' expects parameter '@count', which was not supplied.


Cause

The adapter currently does not support processing stored procedures with return value. The following example highlights the lines in the XSD generated by the Enterprise Metadata Discovery which causes the error, due to lack of support in the adapter.

When using a stored procedure that returns a status value, in the XSD that is generated, we have the following references to the return value (given in bold) in the following example: 

<?xml version="1.0" encoding="UTF-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema" 
targetNamespace=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/pocusergetpolicycount" 
xmlns:pocusergetpolicycount=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/pocusergetpolicycount"
xmlns:jdbcasi="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<import namespace="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata" 
schemaLocation="JDBCASI.xsd"/>

<annotation>
<appinfo source="commonj.connector.asi">
<asi:annotationSet xmlns:asi="commonj.connector.asi" 
asiNSURI="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata"/>
</appinfo>
</annotation>
<complexType name="PocuserGetpolicycount">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:TableName>POCUser.GetPolicyCount</jdbcasi:TableName>
<jdbcasi:StatusColumnName>returnvalue</jdbcasi:StatusColumnName>
<jdbcasi:StatusValue></jdbcasi:StatusValue>
<jdbcasi:Operation>
<jdbcasi:Name>Retrieve</jdbcasi:Name>
<jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>RetrieveSP</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>GetPolicyCount</jdbcasi:StoredProcedureName>
<jdbcasi:Parameters>
<jdbcasi:Type>OP</jdbcasi:Type>
<jdbcasi:PropertyName>returnvalue</jdbcasi:PropertyName>
</jdbcasi:Parameters>
<jdbcasi:Parameters>
<jdbcasi:Type>IP</jdbcasi:Type>
<jdbcasi:PropertyName>ipolicynum</jdbcasi:PropertyName>
</jdbcasi:Parameters>
</jdbcasi:StoredProcedures>
</jdbcasi:Operation>
</jdbcasi:JDBCBusinessObjectTypeMetadata>
</appinfo>
</annotation>
<sequence minOccurs="1" maxOccurs="1">
<element name="returnvalue" type="int" minOccurs="1" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:ColumnName>@RETURN_VALUE</jdbcasi:ColumnName>
<jdbcasi:PrimaryKey>false</jdbcasi:PrimaryKey>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
<element name="ipolicynum" type="string" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:ColumnName>@iPolicyNum</jdbcasi:ColumnName>
<jdbcasi:PrimaryKey>false</jdbcasi:PrimaryKey>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
</sequence>
</complexType>
</schema>


Solution

Delete all references to the return value (the ones given in bold above) from the XSD file for the stored procedure execution to work without errors. The modified XSD in the example above would now look like the following: 

<?xml version="1.0" encoding="UTF-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema" 
targetNamespace="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/pocusergetpolicycount"
xmlns:pocusergetpolicycount=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/pocusergetpolicycount" 
xmlns:jdbcasi="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<import namespace="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata"
 schemaLocation="JDBCASI.xsd"/>

<annotation>
<appinfo source="commonj.connector.asi">
<asi:annotationSet xmlns:asi="commonj.connector.asi" 
asiNSURI="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata"/>
</appinfo>
</annotation>
<complexType name="PocuserGetpolicycount">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:TableName>POCUser.GetPolicyCount</jdbcasi:TableName>
<jdbcasi:StatusColumnName>returnvalue</jdbcasi:StatusColumnName>
<jdbcasi:StatusValue></jdbcasi:StatusValue>
<jdbcasi:Operation>
<jdbcasi:Name>Retrieve</jdbcasi:Name>
<jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>RetrieveSP</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>GetPolicyCount</jdbcasi:StoredProcedureName>
<jdbcasi:Parameters>
<jdbcasi:Type>IP</jdbcasi:Type>
<jdbcasi:PropertyName>ipolicynum</jdbcasi:PropertyName>
</jdbcasi:Parameters>
</jdbcasi:StoredProcedures>
</jdbcasi:Operation>
</jdbcasi:JDBCBusinessObjectTypeMetadata>
</appinfo>
</annotation>
<sequence minOccurs="1" maxOccurs="1">
<element name="ipolicynum" type="string" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:ColumnName>@iPolicyNum</jdbcasi:ColumnName>
<jdbcasi:PrimaryKey>false</jdbcasi:PrimaryKey>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
</sequence>
</complexType>
</schema>


Class loader violation occurs when starting the external service wizard


Problem

It is not possible to use the external service wizard after using a connection to the database in the Data perspective. At the end of the second panel of the 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 the situations described below:


Cause

The error occurs because the Data perspective and the wizard 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 the wizard. 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 typically 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 IBM WebSphere Application Server.


Solution

The only solution is to restart IBM Integration Designer when this error occurs.


Closed connection error occurs when using XA with Oracle 10g


Problem

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


Cause

This is a known issue with the Oracle 10g database driver. This 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 while starting a transaction on Oracle


Problem

When the adapter 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.


Solution

Run the scripts initxa.sql and initjvm.sql that are included in the Oracle directory. 

<ORACLE_HOME>javavm\install
file: initxa.sql
file: initjvm.sql
This activity is likely to be performed by your Oracle database administrator, because you must be logged on to Oracle as SYSOPER or SYSDBA in order to have the necessary permissions for these scripts to work.

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. It might not run successfully if the database memory space is too small.

To fix this, run the initjvm.sql script. This indicates which parameters need to be adjusted. The parameters are stored in the following file: 

<ORACLE_HOME>\database
file: init<DATABASE_SID>.ora

Table 1.shows two parameters that typically need to be increased. Your particular database configuration might require adjustment of different parameters.

Typical parameter sizes
Parameter Name Minimum Value
java_pool_size 12000000
shared_pool_size 24000000


ResourceException during outbound processing

If you get a ResourceException, examine the root cause field to determine the cause. Common problems have the following root causes:


ResourceException during inbound processing

This exception indicates that there is a repeated problem connecting to the database. To polls for events, the adapter must connect to the database. If the connection fails, the adapter waits for the configured time before trying to connect to the database again. The adapter tries to connect the configured number of times before it stops polling. When the adapter stops polling, it returns the ResourceException.


RecordNotFoundException is generated when processing a RetrieveAll or Retrieve operation in test client


Problem

When processing a RetrieveAll or Retrieve operation in IBM Integration Designer test client, if the attributes that are not needed in the WHERE condition (for the SELECT statement) are left blank, the RetrieveAll or Retrieve operation fails, and the following exception may be generated: RecordNotFoundException: Record not found in EIS.


Solution

In the test client, set the values of the attributes that are required to <unset>. Process the RetrieveAll operation. If the exception is generated again, it is likely that no matching records exist in the database table.


IBM WebSphere Adapter for JDBC throws RecordNotFoundException


Problem

When the adapter tries to retrieve data from the database and there is no entry for the requested keys, the adapter throws the following exception instead of returning an empty or null object: 5/13/09 12:28:29:332 GST com.ibm.j2ca.base.exceptions.RecordNotFoundException.


Cause

When processing the RetrieveAll operation, the adapter generated the RecordNotFoundException when no records are returned from the database. The adapter does not handle the empty entry correctly.


Problem

Contact IBM support to retrieve the interim fix V6.0.2.3IF10 of the adapter. With this fix, a new property called errorOnEmptyResultset has been added on the Managed Connection Factory. The default value for this property is "true" and if no rows are returned for the RetrieveAll operation, a RecordNotFoundException is thrown.

To override the behavior of RetrieveAll operation, you can change the value for the errorOnEmptyResultset property through the process administrative console and set it to False after deploying the application. The adapters returns an empty container when no records are found after processing the RetrieveAll operation. This property is not configurable through the EMD in this fix, hence it has to be changed in the process administrative console.

You can also add the "errorOnEmptyResultset" property to *.import file as shown below: 

<connection type="com.ibm.j2ca.jdbc.JDBCManagedConnectionFactory" 
interactionType="com.ibm.j2ca.jdbc.JDBCInteractionSpec">
<properties>
<databaseURL>
jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=adapter
</databaseURL>
<jdbcDriverClass>
com.microsoft.jdbc.sqlserver.SQLServerDriver</jdbcDriverClass>
<password>adrienne</password>
<userName>sa</userName>
<errorOnEmptyResultset>false</errorOnEmptyResultset>
</properties>


JDBC JCA adapter 6.x throws exception when no records are found while retrieving data

See IBM WebSphere Adapter for JDBC throws RecordNotFoundException.


ResultSet ASI for stored procedures requires manual intervention for returning a result set


Problem

IBM WebSphere Adapter for JDBC enterprise metadata discovery process does not set the ResultSet ASI for stored procedures that return a result set and are attached to a business object as a verb ASI.


Cause

Only the Oracle database returns a result set as an output parameter. For all other databases, there is no way to determine if the stored procedure returns a result set.


Solution

For all stored procedures that return a result set and are attached to a business object as a verb ASI, manually set the ResultSet ASI to true to indicate that the stored procedure returns a result set.

The following example shows how the ASI must be set in the business object. 

<jdbcasi:Operation>
<jdbcasi:Name>RetrieveAll</jdbcasi:Name>
<jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>RetrieveAllSP</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>SCOTT.GETCUSTS1</jdbcasi:StoredProcedureName>
<jdbcasi:ResultSet>true</jdbcasi:ResultSet>
<jdbcasi:Parameters>


Enterprise service discovery cannot save the values of MaxNumOfRetRS and ResultSet ASI in the generated business object


Solution

In the wizard, if you first set the value for the The maximum number of ResultsSets returned from the stored procedure field and validate the stored procedure, the value in the The maximum number of ResultsSets returned from the stored procedure field is automatically reset to 0 ignoring the value you had earlier. For the values to be saved correctly in the generated business object, first validate the stored procedure successfully and then specify a value in the The maximum number of ResultsSets returned from the stored procedure field.

See also, Selecting and configuring stored procedures and stored functions.


IBM WebSphere Adapter for JDBC does not support multiple stored procedures with the same name in one schema

See Selecting and configuring stored procedures and stored functions.


Date type ASI for business object attributes refers to columns of type Date and Time


Problem

The JDBC Enterprise Metadata Discovery maps all Time, TimeStamp, and Date database columns to attributes of Service Data Objects type Date in the business object. The SDO date types are handled in such a way that the Date type attributes are displayed in GMT instead of the actual time stored in the database. The time portion is lost when values are set on an SDO or on a database.


Solution

For those columns whose SQL type is DATE, TIME, and TIMESTAMP; JDBC Enterprise Metadata Discovery sets the corresponding attribute type to string, and an attribute application-specific information named DataType is added. Its value is set to either Date, Time, or TimeStamp. To generate the business object XSDs with the proper attribute type and application-specific information, run the JDBC Enterprise Metadata Discovery again.

When setting the date and time values on the business object during outbound, use the following formats:

Similarly, for inbound, if the key value is of the date or time type for the object_key column in the event table, the values have to be entered in the format as described above.


XML data types and XQueries are not directly supported by J2CA JDBC adapter

See How does J2CA Adapter work with XML data type in DB2


BPEL cannot catch specific business fault

See BPEL cannot catch specific Business Fault


Outbound fails due to an exception in global transaction


Problem

Outbound fails due to an exception in global transaction and the transaction cannot not be rolled back successfully.


Cause

From version 6.2 onwards, IBM WebSphere Adapter for JDBC supports business objects for exceptions including IntegrityConstraintViolationException, MatchesExceededLimitException, MissingDataException, MultipleMatchingRecordsException, ObjectNotFoundException, RecordNotFoundException, and UniqueConstraintViolatedException. When any of these exceptions are thrown, the EIS binding encapsulates the exceptions as corresponding fault business objects like IntegrityConstraintFault, MatchesExceededLimitFault, MissingDataFault, MultipleMatchingRecordsFault, ObjectNotFoundFault, RecordNotFoundFault, and UniqueConstraintFault.

As soon as the EIS binding receives the fault business object, it throws a ServiceBusinessException instead of a ServiceRuntimeException. In global transactions, if a component throws a ServiceRuntimeException, the transaction manager will roll back the global transaction. However, if a component throws a ServiceBusinessException, the transaction manager commits global transaction.


Solution

  1. Delete all fault business object binding in the .import file.

  2. Define fault handlers to deal with business faults as shown in the following code. 
    DataObject bo = null;
		try {
			bo = (DataObject) locateService_JDBCOutboundInterfacePartner().invoke
("createDb2adminAddressBG",createDb2adminAddressBGInput);
		} catch (ServiceBusinessException e) {
			// TODO Auto-generated catch block
			throw new ServiceRuntimeException(e);
		}

    Whenever the adapter outbound has any exception, a ServiceRuntimeException is generated and the global transaction is rolled back. Otherwise, the global transaction is committed.


Sharing a JDBC adapter with one or more modules in an EAR file

To share a JDBC adapter with one or modules within an EAR file by embedded deployment:

  1. In the Business Integration perspective of IBM Integration Designer, select File > New > External Service.

  2. Select IBM WebSphere Adapter for JDBC and create an Outbound or Inbound service. Ensure that you add the JDBC drivers to the adapter connector project in order to connect to various databases. If you want the adapter to connect to different databases, you can add all the JDBC drivers to the adapter connector project at once.

  3. Select the exiting connector project and create another Outbound or Inbound service. Use the same module created earlier for the artifacts.

  4. After you create the Inbound or Outbound services, check whether there is only JDBC adapter connector project listed in the Java EE area of Dependencies page for the selected module.

See Sharing a JDBC adapter with one or more modules in an ear file.


Setting the object_key column value in the event table for a composite key


Problem

In the event table which IBM WebSphere Adapter for JDBC polls, there are several columns set as the composite primary key. How do you set the object_key column in the event table for the adapter to poll the event?


Solution

Following is an example to explain the scenario. Here is an Employee table in the database: 

CREATE TABLE ADMIN.EMPLOYEE ( EMPLOYEEID VARCHAR (10) NOT NULL , 
JOBCODE VARCHAR (10) NOT NULL , EMPLOYEENAME VARCHAR (10) NOT NULL , 
AGE VARCHAR (10) NOT NULL , CONSTRAINT CC1182727951922 PRIMARY KEY ( 
EMPLOYEEID, JOBCODE) ) ;

Both EmployeeID and JobCode are composite keys in the Employee table.

Use the following SQL statement to insert data into the Employee table: 

INSERT INTO employee (employeeid,jobcode,employeename,age) VALUES 
('1','8','Mike','30');

Both 1 (EmployeeID ) and 8 (JobCode ) have a record for the employee "Mike" in the "Employee" table.

When using a trigger to insert a record into the event table, the SQL statement must be as shown below: 

INSERT INTO wbia_jdbc_eventstore (object_key, object_name, 
object_function, event_priority, event_status) 
VALUES ('1;8', 'AdminEmployeeBG', 'Create', 1, 0);

After running the ESD to generate the business object for the "Employee" table , both the generated key attributes from the business object have the same order as the table definition. The correct order must be "EmployeeID" first and the "JobCode" next.

Thus, when inserting the value of "object_key" into the event table, you have to follow the same order as the business object definition and the table definition.

For example, if the "EmployeeID" column has the value "1" and "JobCode" column has the value "8" , then the correct value in "object_key" must be "1;8" and not "8;1".

In addition, you should use the semicolon character (;) as the delimiter to separate each composite key value in the "object_key" column.


Use IBM WebSphere Adapter for JDBC with WebSphere Business Integration Adapter for JDBC for user-defined event query


Problem

When using the XWORLDS_EVENTS event table from WebSphere Business Integration Adapter for JDBC for the user-defined event query instead of the WBIA_JDBC_EventStore event table from IBM WebSphere Adapter for JDBC, an error with the message "field object_function not there" or "Invalid column name" is generated.


Cause

The field "object_function" does not exist in WebSphere Business Integration Adapters event table XWORLDS_EVENTS, and the field "connector_ID" does not exist in the event table WBIA_JDBC_EVENTSTORE .


Solution

Here are the migration steps.

  1. Create table "WBIA_JDBC_EVENTSTORE" with the connector_ID. 
    CREATE TABLE WBIA_JDBC_EventStore
(
event_id INTEGER NOT NULL PRIMARY KEY, xid VARCHAR(200), object_key VARCHAR(80) NOT NULL, object_name VARCHAR(40) NOT NULL, object_function VARCHAR(40) NOT NULL, event_priority INTEGER NOT NULL, event_time TIMESTAMP default CURRENT TIMESTAMP NOT NULL, event_status INTEGER NOT NULL, connector_ID VARCHAR(40), event_comment VARCHAR(100)
);

    The "connector_ID" field will be used to filter some events from JDBC adapter instance. The "connector_ID" enables seamless migration for WebSphere Business Integration Adapters customers to JCA where customers are currently taking advantage of the connectorID filtering. This feature will allow the customers to balance load when they have large number of events of the same type.

  2. Create and run the following SQL statements.

    user-defined query: 

    select event_id, object_key, object_name, object_verb as object_function , 
connector_id from xworlds_events where event_status = 0

    user-defined update: 

     update xworlds_events set event_status= 1 where event_id = ?

    user-defined delete: 

    delete from xworlds_events where event_id= ?


Implementing archive events function for inbound service of IBM WebSphere Adapter for JDBC

See Implementing archive events function for inbound service of WebSphere Adapter for JDBC.


IBM WebSphere Adapter for JDBC adapter cannot find method for native method during inbound process


Problem

The adapter cannot find method for native method during inbound process.


Cause

The adapter uses native method internally to map each supported operation. The relationship between inbound operation with native method is defined in method bindings of export component properties.

The adapter constructs native method for the Create operation by using the prefix 'emitCreateAfterImage' and the object name, which is retrieved from the adapter event table.

If the defined native method in the adapter Export component is emitCreateAfterImageAdminCustomerBG, and the inserted object name into the adapter event table is AdminCUSTOMERBG, then the error is generated because the names of the two native methods do not match each other.


Solution

You can use one of the following ways to fix this issue:


Null point exception is generated by the driver when retrieving contents from a result set containing a BLOB column


Problem

Null point exception is generated by the driver when the adapter tries to retrieve contents from a result set containing a BLOB column returned by a DB2 stored procedure.


Cause

DB2 JCC driver version is 4.7.85.


Solution

Use DB2 JCC driver 3.50.152.


Oracle Date type mapped to dateTime instead of date


Problem

In Oracle, the Date data type is mapped to dateTime instead of date by default for some fields.


Cause

This is an issue with the Oracle JDBC driver. In Oracle database, the Date type is similar to the Timestamp type defined in JDBC specification. The Date type also contains the time information. The adapter depends on the data type returned from the driver to map the JDBC type to the SDO type. If the driver returns the Date JDBC type, the adapter maps it to the date SDO type. If the driver returns the Timestamp JDBC type, then adapter map it to the dateTime SDO type.


Solution

You can manually map it to the required type.


Support for XML data type in Oracle


Problem1

When xmlparserv2.jar is added to the application library, an exception is generated in the process administrative console when you are trying to deploy the application.


Cause

When xmlparserv2.jar is added to the application library, it registers as an implementation of XML processing interfaces, DocumentBuilderFactory, SAXParserFactory and TransformerFactory. When xmlparserv2.jar is registered as the TransformerFactory implementation, due to compatibility issue the following exception is generated in the application deployment phase: 

java.lang.IllegalArgumentException
	at oracle.xml.jaxp.JXTransformer.setOutputProperty(JXTransformer.java:793)
	at org.eclipse.xsd.util.DefaultJAXPConfiguration.createTransformer
  (DefaultJAXPConfiguration.java:63)
	at org.eclipse.xsd.util.XSDResourceImpl.doSerialize(XSDResourceImpl.java:153)
	at org.eclipse.xsd.util.XSDResourceImpl.serialize(XSDResourceImpl.java:136)
	at org.eclipse.xsd.ecore.XSDEcoreBuilder.setAnnotations(XSDEcoreBuilder.java:3087)
	at com.ibm.ws.bo.BOModelBuilder.access$201(BOModelBuilder.java:103)
	at com.ibm.ws.bo.BOModelBuilder$1.run(BOModelBuilder.java:1778)
	at java.security.AccessController.doPrivileged(AccessController.java:202)
	at com.ibm.ws.bo.BOModelBuilder.setAnnotations(BOModelBuilder.java:1774)
	at org.eclipse.xsd.ecore.XSDEcoreBuilder.getEPackage(XSDEcoreBuilder.java:161)
	at com.ibm.ws.bo.BOModelBuilder.getEStructuralFeature(BOModelBuilder.java:983)
	at org.eclipse.xsd.ecore.XSDEcoreBuilder.generate(XSDEcoreBuilder.java:2709)
	at com.ibm.ws.bo.BOModelBuilder.generate(BOModelBuilder.java:340)
	at com.ibm.ws.bo.BOModelBuilder.generate(BOModelBuilder.java:650)
	at com.ibm.ws.bo.BOModelBuilder.build(BOModelBuilder.java:309)
	at com.ibm.ws.bo.BOModelHolder.loadModels(BOModelHolder.java:591)
	at com.ibm.ws.bo.BOModelHolder.loadAllModels(BOModelHolder.java:626)
	at com.ibm.ws.bo.BOModelHolder.loadNamespace(BOModelHolder.java:545)
	at com.ibm.ws.bo.BOEPackageRegistry.loadEPackage(BOEPackageRegistry.java:218)
	at com.ibm.ws.bo.BOEPackageRegistry.getEPackage(BOEPackageRegistry.java:295)


Solution

Force the classloader to load the compatible implementation by renaming jaxp.properties.sample to jaxp.properties under <WPS_HOME>/java/jre/lib/. In addition, in the jaxp.properties file, uncomment the properties javax.xml.transform.TransformerFactory, javax.xml.parsers.SAXParserFactory, and javax.xml.parsers.DocumentBuilderFactory. Restart the server.

For Oracle, if you are using a JDNI datasource to connect to the database, ensure that you add xdb.jar and xmlparserv2.jar to the class path when you are create the datasource.


Problem2

In Oracle (driver version 11.1.0.7.0), for a table with xml data type, the Retrieve and RetrieveAll operations returns incorrect result.


Cause

In Oracle driver version 11.1.0.7.0, the content of the XML type attribute in a complex type is not read correctly. The driver always returns null.


Solution

Use driver version 11.2.0.1.0.


Encoding issue when handling Oracle NCHAR or NVARCHAR type


Problem

IBM WebSphere Adapter for JDBC has an encoding issue when handling Oracle NCHAR or NVARCHAR type. The Retrieve and RetrieveAll operation returns unreadable code for a column with the NCHAR or NVARCHAR type.


Solution 1

If you use the Connection Properties (URL) as the connection type, add a connection property defaultNChar:true, as shown in the figure. The Retrieve and RetrieveAll operations list the correct values for the NCHAR or NVARCHAR column.


Solution 2

If you do not use Connection Properties (URL) as the connection type, add a Java Virtual Machine property oracle.jdbc.defaultNChar=true, as shown in the figure and restart the server. The Retrieve and RetrieveAll operations list the correct values for the NCHAR or NVARCHAR column.


IBM WebSphere Adapter for JDBC does not display the list of user-defined functions during external service discovery


Problem

During external service discovery, the adapter does not display the list of user-defined functions.


Solution

This feature is not supported due to limitation with the DB2 JDBC driver.


Adapter returns version conflict exception message


Problem

When install multiple adapters with different versions of CWYBS_AdapterFoundation.jar, and if a lower version of the CWYBS_AdapterFoundation.jar is loaded during run time, the adapter returns the ResourceAdapterInternalException error message, due to a version conflict.

For example, when install Oracle E-Business Suite adapter version 7.0.0.3 and WebSphere Adapter for JDBC version 7.5.0.3, the following error message is displayed "The version of CWYBS_AdapterFoundation.jar is not compatible with IBM WebSphere Adapter for JDBC" as IBM WebSphere Adapter for JDBC loads file:/C:/IBM/WebSphere/ProcServer7/profiles/ProcSrv01/installedConnectors/CWYOE_OracleEBS.rar/CWYBS_AdapterFoundation.jar with version 7.0.0.3. However, the base level of this jar required is version 7.5.0.3. To overcome this conflict, you must migrate all adapters to the same version level.


Solution

Migrate all adapters to the same version level.

For further assistance, visit http://www.ibm.com/support/docview.wss?uid=swg27006249.


IBM WebSphere Adapter for JDBC does not support Oracle system complex type in table BO, if the schema is different for table and type


Problem

For Oracle tables, if the column type is system type schema, the schema cannot be retrieved by the Oracle JDBC driver.


Cause

This is caused by the Oracle JDBC driver limitation.


Solution

Ensure that the table schema and the type schema are the same.


Exceptions thrown when handling user-defined types in operations


Problem 1

The database throws the following exception if the ARRAY type or the STRUCT type contains the ARRAY or LOB attributes that was used in the 'where' clause of SQL statement in the RetrieveAll, UpdateAll, DeleteAll, or Exists operations:

ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type.


Problem 2

The adapter generates the RecordNotFoundException error if there are no records found when you use the STRUCT data type with the attribute value set to null.


Cause

This is caused by the Oracle database limitation.


Solution 1

Unset the corresponding user-defined type (STRUCT, ARRAY) attributes, if they were used to construct the where clause of SQL statement.


Solution 2

Unset the STRUCT type to avoid the RecordNotFoundException error.


Exception thrown when an earlier version of IBM WebSphere Adapter for JDBC is migrated to a newer version


Problem


Cause

The data types of the business object attributes with the Primary Key-Foreign Key relationship in hierarchy business objects are different.

For example, in the parent business object, called Customer, an attribute of the parent business object (Customer) acts as the foreign key, and in the child business object, called Payment, a corresponding attribute of the child business object (Payment) acts as the primary key. The data type of the foreign key in the parent business object is STRING and the data type of the primary key in the child business object is INT. Due to the inconsistent data types in the Primary Key-Foreign Key relationship, the IntegrityConstraintViolationException message is displayed when running on a new adapter version.


Solution

Ensure that the data types of the attributes with the Primary Key-Foreign Key relationship are consistent.


Exception thrown when IBM WebSphere Adapter for JDBC is running with WebSphere Application Server DataSource


Problem

IBM WebSphere Adapter for JDBC throws the following StaleConnectionException when running with WebSphere Application Server DataSource: 

Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: Io exception: Software caused connection abort: socket write errorDSRA0010E: SQL State = 08006, Error Code = 17,002
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance
(NativeConstructorAccessorImpl.java:44)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance
(DelegatingConstructorAccessorImpl.java:39)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:516)
	at com.ibm.websphere.rsadapter.GenericDataStoreHelper.
mapExceptionHelper(GenericDataStoreHelper.java:605)
	at com.ibm.websphere.rsadapter.GenericDataStoreHelper.
mapException(GenericDataStoreHelper.java:667)
	at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2111)
	... 75 more
---- Begin backtrace for Nested Throwables
java.sql.SQLRecoverableException: Io exception: Software caused connection abort: socket write errorDSRA0010E: SQL State = 08006, Error Code = 17,002
	at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
	at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:521)
	at oracle.jdbc.driver.T4CConnection.doRollback(T4CConnection.java:634)
	at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:3470)
	at oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:135)
	at com.ibm.ws.rsadapter.spi.WSRdbSpiLocalTransactionImpl.
rollback(WSRdbSpiLocalTransactionImpl.java:604)


Cause

This is caused by the following reasons:


Solution

Configure a preTestSQLString to test every connection obtained from the free pool. This resolves the problem, but it can affect the performance depending on the amount of connections obtained. See http://publib.boulder.ibm.com/infocenter/ws51help/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html


Endpoint application of the passive adapter instance listens to the events when enableHASupport is set to True


Problem

In the active-passive configuration mode of the adapters, the endpoint application of the passive adapter instance also listens to the events or messages even if the enableHASupport property is set to True.


Cause

By default, in WebSphere Application Server, the alwaysactivateAllMDBs property in the JMS activation specification is set to True. This enables the endpoint application of all the adapter (active or passive) instances to listen to the events.


Solution

To stop the endpoint application of the passive adapter instance from listening to the events, you must set the alwaysactivateAllMDBs property value to False. The JMS activation specification is associated with one or more MDBs and provides the necessary configuration to receive events. If the alwaysActivateAllMDBs property is set to False, then the endpoint application of only the active adapter instance receives the events.

Perform the following procedure, to set the alwaysActivateAllMDBs property to False.

  1. Log on to the Process Administrative Console.

  2. Go to Resources> JMS > Activation specifications.

  3. Click the activation specification corresponding to the application from the list.

  4. Click Custom properties under Additional properties.

  5. Click alwaysActivateAllMDBs.
  6. Change the value to False.

  7. Click Apply and OK.


Result

The endpoint application of only the active adapter instance listens to the events.


Create a single or multiple cardinality relationship between a parent business object and a child business object


Problem

Creating a single or multiple cardinality relationship between a parent business object and a child business object, when using IBM WebSphere Adapter for JDBC.


Solution

The database has three tables as displayed in the following figure: The CUSTOMER table contains multiple records corresponding to the ADDRESS table, and the CUSTINFO table contains multiple records corresponding to the CUSTOMER table.

Figure 1. Database Tables

To create these tables, using the SQL script, see the following procedure: 

--===================
--0. clean up
--===================
DROP TABLE address;
DROP TABLE customer;
DROP TABLE custinfo;

--===================
--1.create "custinfo"
--===================
CREATE TABLE custinfo 
(
  ccode    VARCHAR(10) NOT NULL  PRIMARY KEY,   cdata    VARCHAR(10)
);

--===================
--2.create "customer"
--===================
CREATE TABLE customer 
(
  pkey    VARCHAR(10) NOT NULL  PRIMARY KEY,   fname    VARCHAR(20),   lname    VARCHAR(20),   ccode    VARCHAR(10),   FOREIGN KEY(ccode) REFERENCES custinfo(ccode)
);

--===================
--3.create "address"
--===================
CREATE TABLE address 
(
  addrid    VARCHAR(10) NOT NULL  PRIMARY KEY,   custid    VARCHAR(10),   city      VARCHAR(20),   zipcode  VARCHAR(10),   FOREIGN KEY(custid) REFERENCES customer(pkey)
);

--===================
--4. prepare for data --===================
--1. custinfo
insert into custinfo (CCODE, CDATA)
values ('1', '1');

insert into custinfo (CCODE, CDATA)
values ('2', '2');

insert into custinfo (CCODE, CDATA)
values ('3', '3');

insert into custinfo (CCODE, CDATA)
values ('4', '4');

insert into custinfo (CCODE, CDATA)
values ('5', '5');

insert into custinfo (CCODE, CDATA)
values ('6', '6');

insert into custinfo (CCODE, CDATA)
values ('7', '7');

insert into custinfo (CCODE, CDATA)
values ('8', '8');

insert into custinfo (CCODE, CDATA)
values ('9', '9');

insert into custinfo (CCODE, CDATA)
values ('10', '10');

--2. customer insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('1', 'fname', 'lname', '1');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('2', 'fname', 'lname', '2');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('3', 'fname', 'lname', '3');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('4', 'fname', 'lname', '4');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('5', 'fname', 'lname', '5');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('6', 'fname', 'lname', '6');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('7', 'fname', 'lname', '7');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('8', 'fname', 'lname', '8');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('9', 'fname', 'lname', '9');

insert into customer (PKEY, FNAME, LNAME, CCODE)
values ('10', 'fname', 'lname', '10');

--3. address insert into address (ADDRID, CUSTID, CITY, ZIPCODE)
values ('1', '1', 'BeiJing', '100000');

insert into address (ADDRID, CUSTID, CITY, ZIPCODE)
values ('2', '1', 'ShangHai', '200000');

insert into address (ADDRID, CUSTID, CITY, ZIPCODE)
values ('3', '2', 'NanJin', '300000');

insert into address (ADDRID, CUSTID, CITY, ZIPCODE)
values ('4', '2', 'TianJin', '400000');

insert into address (ADDRID, CUSTID, CITY, ZIPCODE)
values ('5', '2', 'ChongQing', '500000');

insert into address (ADDRID, CUSTID, CITY, ZIPCODE)
values ('6', '3', 'ChangSha', '410000');

Commit;

Use these three tables to build a single or multiple cardinality relationship between a parent business object and a child business object.

  1. Use the following process to create a multiple cardinality relationship between the parent business object (CUSTOMER) and the child business object (ADDRESS) :

    • Use the following process to run the external service discovery to generate the multiple cardinality relationship:

      • In the New External Service window, select the Prompt for additional configuration settings when adding business object check box.

        Figure 2. New External Service window

      • Add the CUSTOMER table to the Selected objects area, as the parent business object, as displayed in the following figure:

        Figure 3. Discovered and Selected objects

      • When you add an ADDRESS to the selected objects list, the configuration properties panel is displayed. From the configuration properties panel, select CUSTOMER as the parent table of ADDRESS, and select the PKEY column from the CUSTOMER table and the CUSTID column from the ADDRESS table. Clear the Single cardinality check box.

        Figure 4. Specifying the Configuration Properties for ADDRESS

    • Generated artifacts -

      For example, the following diagram illustrates the multiple cardinality relationship between the YuanjsCustomer and the YuanjsAddress business objects.

      Figure 5. Multiple Cardinality Relationship

      The ccode attribute of YuanjsCustomer business object defines the xsd as follows: 

      <jdbcasi:ColumnName>PKEY</jdbcasi:ColumnName>
<jdbcasi:PrimaryKey>true</jdbcasi:PrimaryKey>

      The ccode attribute of YuanjsAddress business object defines the xsd as follows: 

      <jdbcasi:ColumnName>CUSTID</jdbcasi:ColumnName>
<jdbcasi:PrimaryKey>false</jdbcasi:PrimaryKey>
<jdbcasi:ForeignKey>pkey</jdbcasi:ForeignKey>

      If it is a single cardinality relationship, the <jdbcasi:ForeignKey> annotation message will be stored in the parent business object.

  2. Use the following process to create a single cardinality relationship between the parent business object (CUSTOMER) and the child business object (CUSTINFO).

    • Use the following process to run the external service discovery to generate a single cardinality relationship:

      • In the New External Service window, select the Prompt for additional configuration settings when adding business object check box.

      • Add the CUSTOMER table to the Selected objects area, as the parent business object, as shown in the following figure.

        Figure 6. Adding Customer to Selected objects

      • When you add CUSTINFO to the selected objects list, the configuration properties panel is displayed. From the configuration properties panel, select CUSTOMER as the parent table of CUSTINFO and select the CCODE column from the CUSTOMER table, and the CCODE column of the CUSTINFO table. Then select the Single cardinality check box.

        Figure 7. Specifying the Configuration Properties for CUSTINFO

    • Generated artifacts -

      For example, the following diagram shows the single cardinality relationship between YuanjsCustomer and YuanjsCustinfo business objects.

      Figure 8. Single Cardinality Relationship

      The ccode attribute for the YuanjsCustomer business object defines the xsd as follows: 

      <jdbcasi:ColumnName>CCODE</jdbcasi:ColumnName>CCODE
<jdbcasi:PrimaryKey>false</jdbcasi:PrimaryKey>
<jdbcasi:ForeignKey>custinfoobj/ccode</jdbcasi:ForeignKey>
      The ccode attribute of YuanjsCustinfo business object defines the xsd as follows: 
       <jdbcasi:ColumnName>CCODE</jdbcasi:ColumnName>
<jdbcasi:PrimaryKey>true</jdbcasi:PrimaryKey>

      If it is a single cardinality relationship, the <jdbcasi:ForeignKey> annotation message will be stored in the parent business object.

      When building a single or multiple cardinality relationship between the parent business object and a child business object, first add all the parent tables to the Selected Objects list, and then configure the Primary Key and Foreign Key relationship of the parent and child table columns.

      If the cardinality relationship is "single", the foreign key annotation information is stored in the parent business object. If the cardinality relationship is "multiple", the foreign key annotation information is stored in the child business object.

      If the location of the foreign key annotation information is incorrect, then the relationship between the parent and child business objects will not be generated.


Performance of the Batch operations are reduced when the input is large


Problem

The performance of the Batch operations are reduced when the input is large.


Cause

Performance is reduced for database servers such as Oracle, wherein, a large number of errors might occur during Batch operations, when:


Error displayed when using external service wizard


Problem

Error is displayed when using the external service wizard.

  1. Add a JDBC driver file in the Locate the Required Files and Libraries window.

  2. Click Next and move to the Specify the Discovery Properties window.

  3. Click Back and go to the Locate the Required Files and Libraries. Then add another JDBC driver file.

  4. Click Next and move to the Specify the Discovery Properties window.

IBM WebSphere Adapter for JDBC returns the following error: 'Failure in connection to EIS. Failed to load the driver: com.ibm.db2.jcc.DB2Driver. Check that the JDBC driver classes are in the class path'.


Cause

This is an Eclipse limitation for class loading.


Solution

Close the connector project and reopen it to add the JDBC driver files again.


Batch operations driver issues


Problem 1

For Oracle Database - Even if the database operations are executed successfully, the driver always returns -2 for all the individual business objects. This implies that the execution of individual business objects was successful, but the number of rows affected for each business object is unknown. During the BatchUpdate and BatchDelete operations, when a corresponding record of an input business object has no matching existing records or multiple matching records in the database, the adapter does not generate the correct error message or exception.


Cause

This is a limitation of the Oracle database driver.


Problem 2

For MS SQL Server Database - If the operation with multiple input records fails within one batch size, the adapter might return the correct error message only for the first record that failed, and return unknown error messages for the other records that failed.


Cause

This is a limitation of the MS SQL Server database driver.


The Add and Remove buttons in the external service wizard do not function properly


Problem

When the external service wizard is run with the same JDBC RAR file consecutively, and the imported JDBC driver libraries are selected to be removed, all the libraries, even those not selected, are removed.

For example, the DB2 driver libraries consists of a driver JAR file and a license JAR file. During the second run, if you try to remove one of the JAR files, both the JAR files are removed.


Cause

This is caused because of an issue with IBM Integration Designer.


Unable to connect to the Informix Database


Problem

In the Specify the Service Generation and Deployment Properties page of the EMD wizard, if you select the Specify local database connection information option from the Database connection information field, and then specify the Informix database connection details in the Database system connection information field, the application generates the following exception after it is deployed and run:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10051][4.11.69] Invalid database URL syntax:

jdbc:informix-sqli://localhost:9090/sysmaster:INFORMIXSERVER=ol_ids_1150. ERRORCODE=-4461, SQLSTATE=42815.


Cause

Currently, the adapter connects to the Informix server only through data source.


Solution

Use data source connection for the Informix server instead of specifying the common local connection properties.

In the Specify the Service Generation and Deployment Properties page of the EMD wizard, select the Specify predefined connection pool Datasource option from the Database connection information field, and then specify the Informixdata source name that has already been configured.


Unable to invoke adapter through webservices


Problem

After configuring the adapter, you might note that:

  1. the webservices client based on the WSDL is not getting generated properly in IBM Integration Designer.
  2. you may not be able to invoke the WSDL using certain webservices client.


Solution

Perform the following steps to enable the adapter module to load the ASI file.

  1. Create a Library project.
  2. Change to Enterprise Explorer view in IBM Integration Designer.

  3. Find the adapterASI.xsd in connector project --> connectorModule.

  4. Copy the adapterASI.xsd and paste it to the Library project.

  5. Add Library project into Dependencies of the adapter module.
  6. Clean project.

Troubleshooting and support


Related concepts:

Outbound processing

Attribute application-specific information


Related tasks:

Setting deployment properties and generating the service

Selecting and configuring tables, views, and synonyms or nicknames for inbound processing

Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

Selecting and configuring query business objects

Adding external software dependencies