13 IBM DB2 for iSeries

13.3.1.1 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 the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining an IBM DB2/400 data server:

1. In the Definition tab:

   • Name: Name of the data server that will appear in Oracle Data Integrator

   • Server: Physical name of the data server

   • User/Password: DB2 user with its password

2. In the JDBC tab:

   • JDBC Driver: weblogic.jdbc.db2.DB2Driver

   • JDBC URL: jdbc:weblogic:db2://hostname:port[;property=value[;...]]

13.6.2.1 How does it work?

A iSeries transaction journal contains the entire history of the data changes for a given period. It is handled by the iSeries system for tables that are journaled. A journaled table is either a table from a collection, or a table for which a journal receiver and a journal have been created and journaling started.

Reading the transaction journal is performed by the a journal retriever CDCRTVJRN RPG program provided with Oracle Data Integrator. This program loads on demand the tables of the Oracle Data Integrator CDC infrastructure (J$ tables) with the contents from the transaction journal.

This program can be either scheduled on the iSeries system or called by the KMs through a stored procedure also called CDCRTVJRN. This stored procedure is automatically created by the JKM DB2/400 Journal Simple and invoked by the LKM DB2/400 Journal to SQL when data extraction is needed.

13.6.2.2 CDCRTVJRN Program Details

This program connects to the native iSeries journal for a given table, and captures changed data information into the Oracle Data Integrator Journal (J$).

The program works as follows:

1. Journalized table attributes retrieval:

   1. Table attributes retrieval: PK columns, J$ table name, last journal reading date.

   2. Attributes enrichment (short names, record size, etc.) using the QSYS.QADBXREF system table.

   3. Location of the iSeries journal using the QADBRTVFD() API.

2. PK columns information retrieval:

   1. PK columns attributes (short name, data types etc.) using the QSYS.QADBIFLD system table.

   2. Attributes enrichment (real physical length) using the QUSLFLD() API.

   3. Data preprocessing (RPG to SQL datatype conversion) for the primary key columns.

3. Extraction the native journal information into the J$ table:

   1. Native journal reading using the QJoRetrieveJournalEntries() API.

   2. Conversion of the raw data to native SQL data and capture into the J$ table.

   3. Update of the changes count.

This program accepts the parameters listed in Table 13-3.

13.6.2.3 Installing the CDC Components on iSeries

There are two major components installed on the iSeries system to enable native journal reading:

• The CDCRTVJRN Program. This program is provided in an archive that should installed in the iSeries system. The installation process is described below.

• The CDC Infrastructure. It includes the standard CDC objects (J$ tables, views, ...) and the CDCRTVJRN Stored Procedure created by the JKM and used by the LKM to read journals. This stored procedure executes the CDCRTVJRN program.

Installing the CDCRTVJRN Program

To install the CDCRTVJRN program:

1. Identify the location the program SAVF file. It is located in the ODI_HOME/setup/manual/cdc-iseries directory, and is also available on the Oracle Data Integrator Companion CD.

2. Connect to the iSeries system.

3. Create the default work library if it does not exist yet. You can use, for example, the following command to create an ODILIB library:

   CRTLIB LIB(ODILIB)
   

4. Create in this library an empty save file that has the same name as the SAVF file (mandatory). For example:

   CRTSAVF FILE(ODILIB/SAVPGM0110)
   

5. Upload the local SAVF file on the iSeries system in the library and on top of the file you have just created. Make sure that the upload process is performed in binary mode.

   An FTP command sequence performing the upload is given below as an example.

   FTP 192.168.0.13 LCD /oracle/odi/setup/manual/cdc-iseries/
   BI
   CD ODILIB
   PUT SAVPGM0110 
   BYE
   

• Restore the objects from the save file, using the RSTOBJ command. For example:

  RSTOBJ OBJ(*ALL) SAVLIB(CDCODIRELE) DEV(*SAVF) OBJTYPE(*ALL) SAVF(ODILIB/SAVPGM0110) RSTLIB(ODILIB)
  

• Check that the objects are correctly restored. The target library should contain a program object called CDCRTVJRN.

  Use the following command below to view it:

  WRKOBJ OBJ(ODILIB/CDCRTVJRN)
  

The CDCRTVJRN Stored Procedure

This procedure is used to call the CDCRTVJRN program. It is automatically created by the JKM DB2/400 Journal Simple KM when journalizing is started. Journalizing startup is described in the Change Data Capture topic.

The syntax for the stored procedure is provided below for reference:

create procedure ODILIB.CDCRTVJRN(
   SbsTName char(138), /* Qualified Subscriber Table Name */
   JrnTName char(138), /* Qualified Table Name */
   Subscriber char(50) , /* Subscriber Name */
   LogMessages char(1) /* Create a Log (Y - Yes, N - No) */
)
language rpgle
external name 'ODILIB/CDCRTVJRN'

13.6.2.4 Using the CDC with the Native Journals

Once the program is installed and the CDC is setup, using the native journals consists in using the LKM DB2/400 Journal to SQL to extract journalized data from the iSeries system. The retrieval process is triggered if the RETRIEVE_JOURNAL_ENTRIES option is set to true for the LKM.

13.6.2.5 Problems While Reading Journals

This section list the possibly issues when using this changed data capture method.

CDCRTVJRN Program Limits

The following limits exist for the CDCRTVJRN program:

• The source table should be journaled and the iSeries journal should be readable by the user specified in the iSeries data server.

• The source table should have one PK defined in Oracle Data Integrator.

• The PK declared in Oracle Data Integrator should be in the 4096 first octets of the physical record of the data file.

• The number of columns in the PK should not exceed 16.

• The total number of characters of the PK column names added to the number of columns of the PK should not exceed 255.

• Large object datatypes are not supported in the PK. Only the following SQL types are supported in the PK: SMALLINT, INTEGER, BIGINT, DECIMAL (Packed), NUMERIC (Zoned), FLOAT, REAL, DOUBLE, CHAR, VARCHAR, CHAR VARYING, DATE, TIME, TIMESTAMP and ROWID.

• Several instances of CDCRTVJRN should not be started simultaneously on the same system.

• Reinitializing the sequence number in the iSeries journal may have a critical impact on the program (program hangs) if the journal entries consumption date (SNP_SUBSCRIBERS.JRN_CURFROMDATE) is before the sequence initialization date. To work around this problem, you should manually set a later date in SNP_SUBSCRIBERS.JRN_CURFROMDATE.

Troubleshooting the CDCRTVJRN Program

The journal reading process can be put in trace mode:

• either by calling from your query tool the CDCRTVJRN stored procedure with the LogMsg parameter set to Y,

• or by forcing the CREATE_SPOOL_FILE LKM option to 1 then restarting the interface.

The reading process logs are stored in a spool file which can be reviewed using the WRKSPLF command.

You can also review the raw contents of the iSeries journal using the DSPJRN command.

13.8.1.1 Loading Data from IBM DB2 for iSeries

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from IBM DB2 for iSeries to a target or staging area database. These optimized IBM DB2 for iSeries KMs are listed in Table 13-4.

In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved to load data from IBM DB2 for iSeries to a target or staging area database.

13.8.1.2 Loading Data to IBM DB2 for iSeries

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from a source or staging area into an IBM DB2 for iSeries database. These optimized IBM DB2 for iSeries KMs are listed in Table 13-5.

In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.

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

13.9.2.2 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. It is recommended that you use this wrapper if running agents installed on AS/400 systems.

To configure a data server with the driver wrapper:

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

2. Set the following java properties for the java machine the run-time agent deployed on iSeries:

   • HOST_NAME: comma separated list of host names identifying the current machine.

   • HOST_IP: IP Address of the current machine.

   The value allow the wrapper to identify whether this data server is accessed on the iSeries machine or from a remote machine.

13.10.2.1 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://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.

• 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 interface).

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