Errors in Oracle Data Integrator appear often in the following way:
java.sql.SQLException:
[IBM][Client Access ODBC Driver][32
bits][DB2/400 SQL]Communication link failure. Comm RC=0xb
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
...
java.sql.SQLException: The
application server rejected the connection.(Signon was canceled.)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:336)
at com.ibm.as400.access.AS400JDBCConnection.setProperties(AS400JDBCConnection.java:1984)
...
java.sql.SQLException: Cannot
open a socket on host: ha, port: 8471 (Exception: java.net.UnknownHostException:
ha).
at hit.as400sql.d.<init>([DashoPro-V1.3-013000])
at hit.as400.As400Driver.newConnection([DashoPro-V1.3-013000])
...
the java.sql.SQLException code simply indicates that a query was made to the database through the JDBC driver (or JDBC/ODBC bridge), which has returned an error. This error is frequently a database or driver error, and must be interpreted in this direction.
Only the part of text in bold must first be taken in account. It must be searched in the driver or database documentation. If it contains an error code specific to DB2/400, the error can be immediately identified.
If such an error is identified in the execution log, it is necessary to analyze the SQL code sent to the database to find the source of the error. The code is displayed in the description tab of the erroneous task.
The most common errors with a DB2/400 server are detailed below along with their principal causes.
The JDBC driver is incorrect. Check the name of the driver.
Oracle Data Integrator cannot connect to the database. Either the machine
name or IP address is invalid, the DB2/400 Services are not started or
the TCP/IP interface on AS/400 is not started.
Try to ping the AS/400 machine using the same machine name or IP address,
and check with the system administrator that the appropriate services
are started.
The ODBC Datasource specified in the JDBC URL is incorrect.
The user profile used is not valid. This error occurs when typing an invalid user name or an incorrect password.
An error occurred with the ODBC connectivity. Refer to the Client Access documentation for more information.
The iSeries 400 system implements commitment control by journaling. Any application that takes advantage of commitment control will require that the files used be journaled. Most of the Knowledge modules make use of commitment control in the staging area, and to write in the target datastores. We strongly recommend you use collections as staging areas on AS/400 and have the target tables journaled. It is possible to remove the use of commitment control in the knowledge modules by modifying them.
Your JDBC connection or ODBC Datasource is configured to use the wrong naming convention. Use the ODBC Administrator to change your datasource to use the proper (*SQL or *SYS) naming convention, or use the appropriate option in the JDBC URL to force the naming conversion (for instance jdbc:as400://195.10.10.13;naming=system) . Note that if using the system naming convention in the Local Object Mask of the Physical Schema, you must enter %SCHEMA/%OBJECT instead of %SCHEMA.%OBJECT.
"*SQL" should always be used unless your application is specifically designed for *SYS. Oracle Data Integrator uses the *SQL naming convention by default.
The table you are trying to access does not exist. This may be linked to an error in the context choice, or in the sequence of operations (E.g. : The table is a temporary table which must be created by another interface).
The iSeries computer attaches a language identifier or CCSID to files, tables and even fields (columns). CCSID 65535 is a generic code that identifies a file or field as being language independent: i.e. hexadecimal data. By definition, no translation is performed by the drivers. If you do not wish to update the CCSID of the file, then translation can be forced, in the JDBC URL, thanks to the flags ccsid=<ccsid code> and convert _ccsid_65535=yes|no. See the driver's documentation for more information.
This error is an internal error of the DB2/400 system.
Keying error in a mapping/join/filter. A string which is not a column name is interpreted as a column name, or a column name is misspelled.
This error may also appear when accessing an error table associated to a datastore with a structure recently modified. It is necessary to impact in the error table the modification, or drop the error tables and let Oracle Data Integrator recreate it in the next execution.