IBM DB2 for iSeries
This section explains how to set up an IBM DB2 for iSeries source in a data task. Before you start the data task, make sure that you have fulfilled the Prerequisites, set up the Required permissions, and familiarized yourself with the Limitations and considerations.
Setting connection properties
This section describes the available connection properties. All properties are required unless otherwise indicated.
To open the connector, do the following:
-
In Connections, click Create connection.
-
Select the IBM DB2 for iSeries source connector and then provide the following settings:
Data source
-
Data gateway
-
Server: The IP address or host name of the IBM DB2 for iSeries server.
Select a Data Movement gateway to use for moving data.
Account properties
-
User name: The user name required to access the IBM DB2 for iSeries server.
-
Password: The password required to access the IBM DB2 for iSeries server.
Database properties
- Use table and schema system names: The IBM DB2 for iSeries source connector lands tables based on their SQL names (unlimited length). If your IBM DB2 for iSeries database does not use SQL names, it's likely that you'll want to keep the system names.
- Relative Record Number (RRN): See Adding the RRN Column to Target Tables below.
CDC properties
-
Journal name: The name of the journal containing the source tables.
See also: CDC prerequisites
-
Journal library: The name of the library where the journal is located.
See also: CDC prerequisites
- Skip journal validation when a task starts: From IBM DB2 for iSeries 7.3, Qlik Talend Data Integration automatically validates the specified journal. This involves checking that the journal exists and that it contains the tables selected for landing. When numerous tables are selected for landing, this process may take some time. In such cases, if you are sure that the specified journal exists and that it contains the correct tables, you may want to skip the validation phase.
- For information about the following options, see Using the R4I UDTF for Change Capture below:
- Enable UDTF capturing
- CDC reader UDTF name
- UDTF result set size (MB)
- Enable UDTF capturing
- Check for changes every (sec): How often to check the source database for changes.
Override CCSID mapping to code page
In the CCSID to character set mapping field enter the CCSID(s) and code page(s) in the following format (separated by commas with no spaces):
CCSID1,codepage1,CCSID2,codepage2,...
The Character set (code page) must be specified in the proper format (e.g. ibm-1148_P100-1997). For a list of valid formats, see the ICU Converter Explorer.
Internal properties
Internal properties are for special use cases and are therefore not exposed in the dialog. You should only use them if instructed by Qlik Support.
Use the 
and 
buttons to the right of the fields to add or remove properties as needed.
Name
The display name for the source connection.
Adding the RRN Column to Target Tables
Source tables that do not have a primary key, a unique index, or a combination of columns that can be used as a unique index, must be registered using the relative record numbers (RRN).
Select one the following options:
- Add RRN column to target tables without a primary key or unique index
-
Add RRN column to all target tables
- Do not add RNN column to target tables
To support DELETE operations in auto-commit mode for tables journaled with *AFTER images, do the following:
- Enable the Add RRN column to all target tables option.
-
Mark the new RRN column on the target as the table's sole Primary Key (using a transformation).
Note that adding an RRN column without marking it as a Primary Key will result in the table being suspended if a DELETE operation is performed.
When you select one of the "Add RRN columns" options, both the Change Tables and the target tables will have an extra column, ATTREP_RRN of type INTEGER, which contains a unique value for each row. This column contains the RRN that corresponds to each source table row.
The IBM DB2 for iSeries RRN values represent the exact position of the row in the file structure that holds the table data. When a table is reorganized, the table is rebuilt resulting in new RRNs being allocated to each row. Consequently, reorganization of tables where the RRN is being used as a target key should be avoided whenever possible. If such reorganization is unavoidable, you should immediately reload the reorganized table to prevent unpredictable behavior when changes are applied to the target (e.g. failed DELETEs, duplicate INSERTs, and so on).
Using the R4I UDTF for Change Capture
By default, changes captured from IBM DB2 for iSeries are filtered on the Data Movement gateway machine using the standard display_journal function. When there is a high volume of changes, this may impact Change Processing performance.
Installing the R4I UDTF on IBM DB2 for iSeries enables captured changes to be filtered on IBM DB2 for iSeries instead of on the Data Movement gateway machine, significantly improving CDC performance.
To set up change capture using the R4I UDTF:
-
Go to Product Downloads.
-
Select Qlik Data Integration.
-
Scroll down the Product list and select Replicate.
-
In the Download Link column, click the QlikReplicate__R4I.zip link to start your download.
-
Install the R4I UDTF on the IBM DB2 for iSeries server.
- Select the Enable UDTF capturing option.
- If you changed the default CDC reader UDTF name during the R4I installation, specify the new name in the CDC reader UDTF name field.
- In the UDTF result set size (MB) field, you can set the maximum size of the result-set buffer returned by the R4I UDTF. A larger result set will usually improve performance, but will also impact DB2 memory consumption. Therefore, If you want to increase the result-set size, best practice is to run the task in a test environment first as insufficient memory on DB2 might result in data inconsistency on the target (due to partial records being captured).