Skip to main content

Setting advanced connection properties

In the Advanced tab, you can set advanced connection properties such as overriding CCSID to Character Set mapping and setting internal Replicate parameters

Using the R4I UDTF for Change Capture

By default, changes captured from IBM DB2 for iSeries are filtered on Replicate 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 Replicate, significantly improving CDC performance.

To set up change capture using the R4I UDTF:

  1. Download the R4I UDTF from the Download Site and install it on IBM DB2 for iSeries.
  2. Select the Enable UDTF capturing option.
  3. If you changed the default CDC reader UDTF name during the R4I installation, specify the new name in the CDC reader UDTF name field.
  4. Optionally, change the default Check for changes every interval (5 seconds).

Overriding CCSID to character set mapping

In some cases, character data in source tables may be encoded in a different CCSID than what is declared in the source database table definition. For example, a specific table or column definition might indicate that its uses CCSID 500 (EBCDIC International) whereas in fact, it uses CCSID 1148 (ENCDIC International with EURO). In this case, you can tell Replicate that the source definition CCSID 500 should be treated as CCSID 1148 (specifically, the character set named IBM-1148).

Note that when the source table definition specifies CCISD 65535 (meaning character set is unknown), you must specify what character set should be assumed when reading data from that table or column.

Note:

If there is a conflict between the character set mapping for a specific column and the character set mapping defined in the endpoint settings, the column-level character set mapping takes precedence.

For more information on overriding character set mapping at column level, see Using the Transform tab.

To do this:

  1. In the Override CCSID to Character Set Mapping section, click the New button.

    A row is added to the table.

  2. Enter the CCSID in the CCSID column and the code page in the Character set column.

    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.

  3. Repeat to map additional CCSID values.

Converting to a custom code page

Perform the following procedure if your source endpoint tables are defined with an incorrect CCSID and the correct definition is actually in a UCM file.

  1. Create a mapping data file with the file extension .ucm.

    Note:

    Note  If you edit an existing UCM file, you must also change the values of the <code_set_name> and <icu:alias> properties. If the file does not contain an <icu:alias> property, then you only need to change the value of the <code_set_name> property.

  2. Create a CNV file for the UCM file by running the following command:

    <product_dir>\bin\makeconv.exe -v <file_name>.ucm

    Example:  

    "c:\Program Files\Attunity\Replicate\bin\makeconv.exe" -v 1047_EX.ucm

    This will create a CNV file with the same name as the UCM file (e.g. 1047_EX.cnv).

  3. Copy the file to the following location:

    <product_dir>\bin\icudt58l

  4. Add a new character set mapping as follows:
    1. In CCSID column, enter the original source CCSID number (e.g. 1047)
    2. In the Character set column, enter the name of the CNV file without the extension (e.g. 1047_EX).
  5. Restart the Qlik Replicate UI Server service.

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:

  • Do not add RNN column to target tables
  • Add RRN column to target tables without a primary key or unique index
  • Add RRN column to all target tables

To support DELETE operations in auto-commit mode for tables journaled with *AFTER images, do the following:

  1. Enable the Add RRN column to all target tables option.
  2. 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.

Note:

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

Replicating System Names

The IBM DB2 for iSeries source endpoint replicates 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 replication based on system names.

To do this, select the Use table and schema system names check box.

Skipping Journal Validation

From IBM DB2 for iSeries 7.3, Replicate automatically validates the specified journal. This involves checking that the journal exists and that it contains the tables selected for replication. When numerous tables are selected for replication, 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.

To do this, select the Skip journal validation check box.

Internal parameters

Internal parameters are parameters that are not exposed in the UI. You should only use them if instructed by Qlik Support.

To add internal Qlik Replicate parameters:

  1. Click the Internal Parameters link.

    The Internal Parameters dialog box opens.

  2. In the edit box, type the name of the parameter you need to add and then click it.
  3. The parameter is added to the table below the search box with its default value.
  4. Change the default value as required.
  5. To reset the parameter value to its default, click the "Restore default value" icon at the end of the row.

Settings summary

You can view a summary of your settings by clicking the Setting Summary link. This is useful if you need to send a summary of your settings to Qlik Support.