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 the Replicate Server 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 Replicate Server, significantly improving CDC performance.
To set up change capture using the R4I UDTF:
- Download the R4I UDTF from the Qlik Download Site and install it on IBM DB2 for iSeries.
- 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).
Checking for changes
Check for changes every (sec): How often to check the source database for changes.
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.
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:
In the Override CCSID to Character Set Mapping section, click the New button.
A row is added to the table.
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.
- 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.
Create a mapping data file with the file extension .ucm.Information 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.
Create a CNV file for the UCM file by running the following command:
<product_dir>\bin\makeconv.exe -v <file_name>.ucm
"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).
Copy the file to the following location:
When using the Replicate File Channel, the file should be copied to the same location on both Replicate servers.
- Add a new character set mapping as follows:
- In CCSID column, enter the original source CCSID number (e.g. 1047)
- In the Character set column, enter the name of the CNV file without the extension (e.g. 1047_EX).
- Restart the Qlik Replicate UI Server service.
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:
- 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).
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.
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 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:
Click the Internal Parameters link.
The Internal Parameters dialog box opens.
- In the edit box, type the name of the parameter you need to add and then click it.
- The parameter is added to the table below the search box with its default value.
- Change the default value as required.
- To reset the parameter value to its default, click the "Restore default value" icon at the end of the row.
These options are not exposed in the UI as they are only relevant to specific versions or environments. Consequently, do not set these options unless explicitly instructed to do so by Qlik Support or product documentation.
To set an option, simply copy the option into the Add feature name field and click Add. Then set the value or enable the option according to the instructions you received.
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.