Table of Contents

Search

  1. Preface
  2. Introduction
  3. Servers
  4. Console Client
  5. Search Clients
  6. Table Loader
  7. Update Synchronizer
  8. Globalization
  9. Siebel Connector
  10. Web Services
  11. ASM Workbench
  12. Cluster Merge Rules
  13. Forced Link and Unlink
  14. System Backup and Restore
  15. Batch Utilities

Synchronization Level

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

0 COMMENTS

We’d like to hear from you!