Table options - 7.3

Talend Change Data Capture Reference Guide
Version
7.3
Language
English
Product
Talend Change Data Capture
Module
Talend Change Data Capture
Content
Data Governance > Third-party systems > Database components (Integration) > Change Data Capture
Data Quality and Preparation > Third-party systems > Database components (Integration) > Change Data Capture
Design and Development > Third-party systems > Database components (Integration) > Change Data Capture
Last publication date
2023-11-09

These options specify the default values for the tables belonging to this model.

AS/400 IBMi source type Other source types
 

Distribution of all members (AS/400 IBMi source only)

This specifies whether the replication concerns all the members or the first member by default. 

You can override this rule for each file individually.

If the distribution concerns all the members, by default they are replicated and their names matched (same name on the source and the target), unless a member name (or *FIRST) is explicitly specified as the target member. 

Otherwise, by default, only the first member is replicated to the first member (*FIRST) in the target.

 

Access Mode

This parameter defines the access mode used on the target machine for the I/Os (inputs/outputs).

Access mode Target type Meaning
Auto  All The target program chooses the most appropriate mode.

This option is useful when you have different types of target databases, to avoid changing this parameter too frequently.

In the case of an AS/400 IBMi target, the target program checks the target table for the presence or absence of keys. If there is no key, the RRN mode is used.

This option can also manage the cases not handled by the By SQL option with an AS/400 IBMi target (Unicode management, for example).
By Record number AS/400 IBMi The access mode is RRN.
Warning: Only use this option for identical replication, as it has many limitations, such as a complicated repair procedure.
By Key AS/400 IBMi The access mode is the key.
By SQL AS/400 IBMi or NT A SQL query is used.

This is the default option as it manages most cases (see Auto option for the exceptions).
Backup AS/400 IBMi or NT Use this option only with an AS/400 IBMi-type source.

Replication is performed in accordance with the record cartography (gaps) in the target file.

In all cases, the target program's choice is specified for each file by a message in the Talend Change Data Capture log.

If you have many tables, you can define this choice yourself to avoid having multiple messages.

 

Orphan management (JOURNAL model)

This specifies whether orphan management is enabled (Yes) or not (No).

The orphan principle goes hand in hand with selection.

If, due to selection criteria, a journal sequence is not selected, and this sequence already exists in the target, the sequence on the target is an orphan that must be deleted.

Use case:

Suppose that, for an Articles table, you want to select only articles with a positive stock. 

If a stock becomes negative during an update, the update is not sent.

The target would have a record of the article with the former stock. This is an orphan.

Automatic orphan management consists of automatically deleting orphans. The update that causes an orphan to appear is a delete operation.

 

Continue on error

This specifies whether or not the replication must continue for the other tables if an error occurs on a table.

There are two options:

  • Yes

    If an error is found in a table, the replication of the table stops at the point of the error, whereas the replication of the other tables continues. If all the tables contain errors, the replication stops.

  • No

    As soon as an error is found in a table, the replication is stopped.

The only way to counter an error in a table is to correct it or make a manual recovery on the next sequence (see Recovery in Distributions and Recovery in Distributed tables).

 

Repair

The repair mode specifies how the Talend Change Data Capture engine must react to certain errors.

There are four options:

Access mode Description

No

If an attempt is made to add a record/row that is already present, or to modify or delete a record/row that is missing, the table is stopped with errors. 

Yes

During an add operation, if the record already exists, it is updated.

During an update operation, if the record/row does not exist, it is added.

During a delete operation, if the record/row does not exist, no action is taken.

Each time a repair operation is performed, the repair counter is incremented (see Supervision).

Skip

If an attempt is made to add a record/row that already exists, or to modify or delete a record/row that is missing, the journal sequence is skipped.

This mode is ideal for updating external key data in tables, with the SQL access mode.

Suppose that you have two tables (File1 and File2) on the source side, and that File1 contains a Code whose description is in File2, and that the Code and the Description are both present in the Target File.

Now suppose that when you modify the description in File2, you want all the descriptions for this code in the Target File to be modified.

However, if the code, and therefore the description, is not used in the target file, you do not want an error to be triggered. In this case, in the repair options for File2, you must specify skip. 

Yes with trace

This is the same process as with the Yes option, with a script in the trace file on the target of the actions and the messages returned by the database.
Note: It is preferable not to define skip as the default value in the repair parameters for tables.

DDL - Structure change

Structural changes on the source table include the following transactions: update, insert, delete.

By default, the Yes without Alter option is selected. When the structure of a source table changes, it is detected by Talend Change Data Capture source engine. Parameters are modified, but not the target table.

You can change this behavior according to the following options.

Parameter Value

No

 The structural changes are not taken into account.

The replication is stopped and an error message is displayed.

If you want to replicate changes on the model and the target table, a manual action is needed.

Ignore

The structural changes are ignored.

There is no replication stop.

Warning: This action may produce data inconsistency, especially in case a column was added on the source database.

Yes without Alter

This is the default option.

The structural changes are taken into account in the parameters only.

The target table is not modified.

Use this option if you want to keep data up-to-date into Talend Change Data Capture with the objective of sending these changes to the target table in the future.

Alter without Drop

Updated and inserted columns are taken into account on the target table.

Column deletion (also called "drop") and reduction are not taken into account.

Alter with Drop

All structural changes are taken into account on the target table.

The new data replaces the previous one.

This option is also used while checking the structure change on the tables of the model.

To check and integrate a new structure, go to the properties window for each table and click Control the structure changes, then check the changes in the Fields tab.

You can also select one or various tables and right-click to display an advanced menu. Then click Control the structure changes. If you are satisfied with the changes observed, click Force the change of structure.

Note: To apply the Change Column and Drop Column actions to a AS/400 IBMi target, when using a source different from AS/400 IBMi, the access mode must be of SQL type. There must also be an automatic I (Ignore) response to the CPA32B2 message (WRKRPYLE command) as well as the *SYSRPYL value to the INQMSGRPY setting of the DD_USRDATA/DDPARAMD jobd.
Note: For a AS/400 IBMi source, the engine needs access to a Windows machine with a target engine (without needing a TGT01 license) and the Manager. You will also need to add the DDL setting in the DD_USRDATA/DD_CFG data area, followed by the IP address of the Windows machine. For example, DDL="192.168.0.1".

TRUNCATE / CLRPFM (AS/400 IBMi source only)

This specifies how Talend Change Data Capture must react if it encounters a TRUNCATE TABLE or a CLRPFM (Clear Physical File Member) operation in the journal.

There are three options:

  • Yes   

    The CLRPFM operation is performed on the target.

  • No

    An error is triggered and recorded for the file.

  • Skip

    The operation is just ignored.

On a non-AS/400 IBMi target, the engine performs a DELETE FROM table, by default.

Note: With a Snowflake target table, you need to select Specific Clear for Truncate/CLRPFM and Clear before extract and enter truncate table %S in the field.

 

RGZPFM (AS/400 IBMi source only)

This specifies how Talend Change Data Capture must react if it encounters a RGZPFM operation in the journal.

There are three options:

  • Yes    The RGZPFM operation is performed on the target. 

  • No An error is triggered and recorded for the file. 

  • Skip The operation is ignored. 

RGZPFM on a non-AS/400 IBMi target does not result in any action.

 

ADDPFM (AS/400 IBMi source only)

This specifies how Talend Change Data Capture must react if it encounters an ADDPFM (Add Physical File Member) operation in the journal.

There are three options:

  • Yes   

    The ADDPFM operation is performed on the target. 

  • No

    An error is triggered and recorded for the file. 

  • Skip

    The operation is ignored. 

ADDPFM on a non-AS/400 IBMi target does not result in any action.

 

RMVM/RNM (AS/400 IBMi source only)

This specifies how Talend Change Data Capture must react if it encounters a RMVM (Remove Member) or RNM (Rename Member) operation in the journal.

There are three options:

  • Yes   

    The RMVM/RNM operation is performed on the target. 

  • No

    An error is triggered and recorded for the file. 

  • Skip

    The operation is just ignored. 

On a non-AS/400 IBMI target, RMVM/RNM does not result in any action.

On an AS/400 IBMi target, the RNM operation restarts (in a normal working state) the engine.

 

DLTF (AS/400 IBMi source only)

This specifies how Talend Change Data Capture must react if it encounters an RMVM (Remove Member) operation in the journal, and after following through, there are no more members in the target.

There are two options:

  • Yes   

    The target file is deleted. 

  • No

    The target file is kept. 

DLTF on a non-AS/400 IBMi target does not result in any action.