Operations Guide

10.5
- 10.5 HotFix 2
- 10.5 HotFix 1
- 10.2 HotFix 1
- 10.2
- 10.1 HotFix 1
- 10.1
- 10.0 HotFix 1
- 10.0

Back Next

Synchronization Level

The Synchronizer operates most efficiently when the nominated primary keys (PKn notation) are guaranteed to be unique. It is advisable that the User Source Tables are defined with integrity constraints to ensure this fact. However, in cases where this is not possible, a Synchronization Level can be specified which allows the Synchronizer to tolerate, or even expect duplicates.

The following synchronization levels may be specified:

Synchronization Level	Check on Load	Check on Sync	On Sync Error. . .
REJECT_DUPLICATE_PK	Yes	Yes	Stop / Ignore
REPLACE_DUPLICATE_PK	Yes	Yes (NSA only)	Replace row
WARN_DUPLICATE_PK	No	Yes	Issue Warning
ALLOW_DUPLICATE_PK	No	No	N/A

The default level is

REJECT_DUPLICATE_PK

. This specifies that an IDT cannot be loaded when duplicates are present. Rows containing duplicate PKs will not be added to the IDT, but may exist on the UST (where the Synchronizer has no control over them). Customers requiring duplicate protection of their source tables should use database constraints to prevent creating duplicates. The synchronization process operates most efficiently in this mode.

If only a few duplicates are present and/or you do not want the Synchronizer to stop when a duplicate is detected, use

WARN_DUPLICATE_PK

. This setting will process transactions less efficiently than

REJECT_DUPLICATE_PK

but will produce correct results even when duplicates are present. In cases where a duplicate is detected it will issue a warning and continue.

If the PK is known to be non-unique specify

ALLOW_DUPLICATE_PK

. This informs the Synchronizer to use algorithms that produce correct results when duplicates are present. However, this mode is less efficient than

REJECT_DUPLICATE_PK

. This mode must be used when the PK may contain NULL values.

The synchronization level

REPLACE_DUPLICATE_PK

can only be used in conjunction with an NSA transaction source. An add transaction (type ’A’) containing a duplicate PK value will replace the existing IDT row with the new value from the NSA Transaction Table.

Reject_Duplicate_PK

Correct synchronization relies on the ability to uniquely identify User Source PKn Tables records. A User Source Table Definition nominates the source table column(s) that are used to create a unique primary key [with the (

PKn

) notation].

When loading the IDT, the host DBMS will check the PK columns for unique values. The MDM-RE Table Loader will fail with an appropriate error messages if the USTs contain any duplicates. Therefore it is advisable that User Source Tables are defined with constraints to avoid this potential problem.

Without constraints on the columns, it is possible for a user transaction to create a duplicate PK value via an insert or update to a UST. The Synchronizer will detect this situation when attempting to add the same record to the IDT. A new row with a duplicate PK will not be added to the IDT.

If the new "duplicate" row is not identical to an existing row (excluding PK values), the Update Synchronizer reports the situation with an error message. A sample error message is as follows:

constraint violated by insert/update to UST
IDT ’IDS_01_IDT01’
UST Key field(s): SSA09.TESTX01A.EMPNO
PK1 ’99’

Identical duplicate rows are not added to the IDT and not reported as duplicates, as there are some situations where a specific transaction order may produce rows that appear to be duplicates when in fact, they are not.

The example above tells us that a transaction was applied to an IDT called

IDS_01_IDT01

. The PK field for this IDT contains values extracted from a User Source Table column called

SSA09.TESTX01A.EMPNO

and we have attempted to add a duplicate value of

The Synchronizer will roll back the transaction(s) updated during the current duty cycle and terminate with the above message. Manual intervention is required to

repair the UST integrity problem (remove the duplicate), and

inform the Synchronizer to delete the problem transaction.

Repairing the UST

Removing Duplicates

This section is relevant to Synchronization Level

REJECT_DUPLICATE_PK

. The record containing the duplicate primary key must be removed. This is a user responsibility. Once again, this problem could have been prevented if the UST contained DBMS integrity constraints to enforce the uniqueness of the PK column(s).

After deleting the duplicate record, you must update the original (correct) record to force the Synchronizer to re-index the IDT using the values in the correct record. This is necessary because the deletion of the duplicate generates a trigger that will delete all IDT records with this key. The subsequent update will result in the correct record being added to the IDT.

When updating the correct record, be sure to update a column that is present in the IDT, that is a column from the UST that appears in the IDT’s

sourced_from

clause. This is required because update triggers are only fired when a

sourced_from

column is modified.

If you do not update the correct record after deleting the duplicate, the IDT will not be correctly synchronized with the UST.

Restarting the Synchronizer

This section is only relevant to Sync Level

REJECT_DUPLICATE_PK

It is not possible to simply restart the Synchronizer because the transaction which attempted to add a duplicate is still in the

IDS_UPD_SYNC_TXN

table and will be reprocessed. You must inform the Synchronizer to delete this transaction by using the

-z

switch.

The recommended procedure is to start the Synchronizer with the

-m1

parameter. This will commit updates after every successful transaction is processed and rollback/terminate when the "duplicate transaction" is reprocessed leaving it at the head of the transaction queue.

Then start the Synchronizer with the following parameters to delete the "duplicate transaction", commit it and shutdown the Synchronizer:

-zTxn -m1 -c1

where

Txn

is the transaction sequence number of the failed transaction. You can now restart the Synchronizer with its normal parameters.

Rename Saved Search

Table of Contents

Operations Guide

Operations Guide

Synchronization Level

Synchronization Level

Reject_Duplicate_PK

Repairing the UST

Removing Duplicates

Restarting the Synchronizer