IBM DB2 for iSeries

Introduction

Oracle Data Integrator (ODI) seamlessly integrates data in IBM DB2 for iSeries. Oracle Data Integrator features are designed to work best with IBM DB2 for iSeries, including reverse-engineering, changed data capture, data integrity check, and mappings.

Concepts

The IBM DB2 for iSeries concepts map the Oracle Data Integrator concepts as follows: An IBM DB2 for iSeries server corresponds to a data server in Oracle Data Integrator. Within this server, a collection or schema maps to an Oracle Data Integrator physical schema. A set of related objects within one schema corresponds to a data model, and each table, view or synonym will appear as an ODI datastore, with its attributes, columns and constraints.

Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to IBM DB2 for iSeries.

Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 16-1 for handling IBM DB2 for iSeries data. In addition to these specific IBM DB2 for iSeries Knowledge Modules, it is also possible to use the generic SQL KMs with IBM DB2 for iSeries. See Generic SQL for more information.

Table 16-1 DB2 for iSeries KMs

Knowledge Module	Description
IKM DB2 400 Incremental Update	Integrates data in an IBM DB2 for iSeries target table in incremental update mode.
IKM DB2 400 Slowly Changing Dimension	Integrates data in an IBM DB2 for iSeries target table used as a Type II Slowly Changing Dimension in your Data Warehouse.
JKM DB2 400 Consistent	Creates the journalizing infrastructure for consistent journalizing on IBM DB2 for iSeries tables using triggers.
JKM DB2 400 Simple	Creates the journalizing infrastructure for simple journalizing on IBM DB2 for iSeries tables using triggers.
RKM DB2 400	Retrieves metadata for IBM DB2 for iSeries: physical files, tables, views, foreign keys, unique keys.

Installation and Configuration

Make sure you have read the information in this section before you start working with the IBM DB2 for iSeries technology:

System Requirements and Certifications

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html.

Technology Specific Requirements

Some of the Knowledge Modules for IBM DB2 for iSeries use specific features of this database. The following restrictions apply when using these Knowledge Modules.

See the IBM DB2 for iSeries documentation for additional information on these topics.

Using CDC with Journals

This section describes the requirements that must be met before using the Journal-based Change Data Capture with IBM DB2 for iSeries:

This journalizing method requires that a specific program is installed and runs on the iSeries system. See Setting up Changed Data Capture for more information.

To know more about the supported data types, refer to IBM DB2 for iSeries documentation.

Setting up the Topology

Setting up the Topology consists of:

Creating a DB2/400 Data Server

An IBM DB2/400 data server corresponds to an iSeries server connected with a specific user account. This user will have access to several databases in this server, corresponding to the physical schemas in Oracle Data Integrator created under the data server.

Creation of the Data Server

Create a data server for the IBM DB2/400 technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator. This section details only the fields required or specific for defining an IBM DB2/400 data server:

In the Definition tab:
- Name: Name of the data server that will appear in Oracle Data Integrator
- Host (Data Server): Name or IP address of the host
- User/Password: DB2 user with its password
In the JDBC tab:
- JDBC Driver: weblogic.jdbc.db2.DB2Driver
- JDBC URL: jdbc:as400://<host>[;libraries=<library>][;<property>=<value>...]
  
  The URL parameters are:
  - <host>: server network name or IP address
  - <library>: default library or collection to access
  - <property>=<value>: connection properties. Refer to the driver's documentation for a list of available properties.

Creating a DB2/400 Physical Schema

Create an IBM DB2/400 physical schema using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator.

The work schema and data schema in this physical schema correspond each to a schema (collection or library). The work schema should point to a temporary schema and the data schema should point to the schema hosting the data to integrate.

Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema in Administering Oracle Data Integrator and associate it in a given context.

Setting Up an Integration Project

Setting up a project using the IBM DB2 for iSeries database follows the standard procedure. See Creating an Integration Project of Developing Integration Projects with Oracle Data Integrator.

It is recommended to import the following knowledge modules into your project for getting started with IBM DB2 for iSeries:

IKM DB2 400 Slowly Changing Dimension
JKM DB2 400 Consistent
JKM DB2 400 Simple
RKM DB2 400
CKM SQL

Creating and Reverse-Engineering an IBM DB2/400 Model

This section contains the following topics:

Create an IBM DB2/400 Model

Create an IBM DB2/400 Model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator.

Reverse-engineer an IBM DB2/400 Model

IBM DB2 for iSeries supports both Standard reverse-engineering - which uses only the abilities of the JDBC driver - and Customized reverse-engineering, which uses a RKM to retrieve the metadata.

In most of the cases, consider using the standard JDBC reverse engineering for starting.

Consider switching to customized reverse-engineering for retrieving more metadata. IBM DB2 for iSeries customized reverse-engineering retrieves the physical files, database tables, database views, columns, foreign keys and primary and alternate keys.

Standard Reverse-Engineering

To perform a Standard Reverse-Engineering on IBM DB2 for iSeries use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.

Customized Reverse-Engineering

To perform a Customized Reverse-Engineering on IBM DB2 for iSeries with a RKM, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator. This section details only the fields specific to the IBM DB2/400 technology:

In the Reverse tab of the IBM DB2/400 Model, select the KM: RKM DB2 400.<project name>.

Setting up Changed Data Capture

Oracle Data Integrator handles Changed Data Capture on iSeries with two methods:

Trigger-based CDC on the journalized tables. This method is set up with the JKM DB2/400 Simple or JKM DB2/400 Consistent. This CDC is not different from the CDC on other systems. See Setting up Trigger-Based CDC for more information.
Log-based CDC by reading the native iSeries transaction journals.This method does not support Consistent Set CDC and requires a platform-specific configuration. See Setting up Trigger-Based CDC for more information.

Setting up Trigger-Based CDC

This method support Simple Journalizing and Consistent Set Journalizing. The IBM DB2 for iSeries JKMs use triggers to capture data changes on the source tables.

Oracle Data Integrator provides the Knowledge Modules listed in Table 16-2 for journalizing IBM DB2 for iSeries tables using triggers.

See Working with Changed Data Capture of Developing Integration Projects with Oracle Data Integrator for details on how to set up journalizing and how to use captured changes.

Table 16-2 IBM DB2 for iSeries Journalizing Knowledge Modules

KM	Notes
JKM DB2 400 Consistent	Creates the journalizing infrastructure for consistent journalizing on IBM DB2 for iSeries tables using triggers.
JKM DB2 400 Simple	Creates the journalizing infrastructure for simple journalizing on IBM DB2 for iSeries tables using triggers.

Setting up Data Quality

Oracle Data Integrator provides the generic CKM SQL for checking data integrity against constraints defined in DB2/400. See Flow Control and Static Control in Developing Integration Projects with Oracle Data Integrator for details.

See Generic SQL for more information.

Designing a Mapping

You can use IBM DB2 for iSeries as a source, staging area or a target of a mapping.

The KM choice for a mapping or a check determines the abilities and performance of this mapping or check. The recommendations in this section help in the selection of the KM for different situations concerning an IBM DB2 for iSeries data server.

Loading Data from and to IBM DB2 for iSeries

IBM DB2 for iSeries can be used as a source, target or staging area of a mapping. The LKM choice in the Mapping Flow tab to load data between IBM DB2 for iSeries and another type of data server is essential for the performance of a mapping.

Integrating Data in IBM DB2 for iSeries

Oracle Data Integrator provides Knowledge Modules that implement optimized data integration strategies for IBM DB2 for iSeries. These optimized IBM DB2 for iSeries KMs are listed in Table 16-3. I

In addition to these KMs, you can also use the Generic SQL KMs.

The IKM choice in the Mapping Flow tab determines the performances and possibilities for integrating.

Table 16-3 KMs for integrating data to IBM DB2 for iSeries

KM	Notes
IKM DB2 400 Incremental Update	Integrates data in an IBM DB2 for iSeries target table in incremental update mode.
IKM DB2 400 Slowly Changing Dimension	Integrates data in an IBM DB2 for iSeries target table used as a Type II Slowly Changing Dimension in your Data Warehouse.

Using Slowly Changing Dimensions

For using slowly changing dimensions, make sure to set the Slowly Changing Dimension value for each attributes of the target datastore. This value is used by the IKM DB2 400 Slowly Changing Dimension to identify the Surrogate Key, Natural Key, Overwrite or Insert Column, Current Record Flag and Start/End Timestamps columns.

Specific Considerations with DB2 for iSeries

This section provides specific considerations when using Oracle Data Integrator in an iSeries environment.

Alternative Connectivity Methods for iSeries

It is preferable to use the built-in IBM DB2 Datadirect driver in most cases. This driver directly use the TCP/IP network layer and require no other components installed on the client machine. Other methods exist to connect DB2 on iSeries.

Using Client Access

It is also possible to connect through ODBC with the IBM Client Access component installed on the machine. This method does not have very good performance and does not support the reverse engineering and some other features. It is therefore not recommended.

Using the IBM JT/400 and Native Drivers

This driver appears as a jt400.zip file you must copy into your Oracle Data Integrator installation drivers directory.

To connect DB2 for iSeries with a Java application installed on the iSeries machine, IBM recommends that you use the JT/400 Native driver (jt400native.jar) instead of the JT/400 driver (jt400.jar). The Native driver provides optimized access to the DB2 system, but works only from the iSeries machine.

To support seamlessly both drivers with one connection, Oracle Data Integrator has a built-in Driver Wrapper for AS/400. This wrapper connects through the Native driver if possible, otherwise it uses the JT/400 driver.

To configure a data server with the driver wrapper, change the driver and URL to your AS/400 server with the following information:

Driver: com.sunopsis.jdbc.driver.wrapper.SnpsDriverWrapper
URL: jdbc:snps400:<machine_name>[;param1=value1[;param2=value2...]]

Troubleshooting

This section provides information on how to troubleshoot problems that you might encounter when using Oracle Knowledge Modules. It contains the following topics:

Troubleshooting Error messages

Errors in Oracle Data Integrator appear often in the following way:

java.sql.SQLException: The application server rejected the connection.(Signon was canceled.)
at ...
at ...
...

the java.sql.SQLExceptioncode simply indicates that a query was made to the database through the JDBC driver, 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 DB2 or iSeries documentation. If its contains sometimes an error code specific to your system, with which the error can be immediately identified.

If such an error is identified in the execution log, it is necessary to analyze the SQL code send to the database to find the source of the error. The code is displayed in the description tab of the erroneous task.

Common Problems and Solutions

This section describes common problems and solutions.

Connection Errors

UnknownDriverException

The JDBC driver is incorrect. Check the name of the driver.
The application requester cannot establish the connection.(<name or IP address>) Cannot open a socket on host: <name or IP address>, port: 8471 (Exception: java.net.UnknownHostException:<name or IP address>)

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.
Datasource not found or driver name not specified

The ODBC Datasource specified in the JDBC URL is incorrect.
The application server rejected the connection.(Signon was canceled.) Database login failed, please verify userid and password. Communication Link Failure. Comm RC=8001 - CWBSY0001 - ...

The user profile used is not valid. This error occurs when typing an invalid user name or an incorrect password.
Communication Link Failure

An error occurred with the ODBC connectivity. Refer to the Client Access documentation for more information.
SQL5001 - Column qualifier or table &2 undefined. SQL5016 - Object name &1 not valid for naming convention

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://192.0.2.1;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.
SQL0204 &1 in &2 type *&3 not found

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 mapping).
Hexadecimal characters appear in the target tables. Accentuated characters are incorrectly transferred.

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.
SQL0901 SQL system error

This error is an internal error of the DB2/400 system.
SQL0206 Column &1 not in specified tables

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.