Rebuilding the KeyCrossReferenceTwoWay table
Article ID: KB0078908
Updated On:
| Products | Versions |
|---|---|
| TIBCO Scribe Insight | 7.9.2 |
Description
There are times when the TIBCO Scribe® Insight Key Cross Reference table(s) become out of sync resulting in improper lookups or duplicate key errors when trying to update the table. In these cases it may be necessary to rebuild the table.
This example uses the GP to CRM Customer Accounts for building the table.
Prior to running in production, this process should be tested in a development/test environment.
1. Stop all integrations processes in the TIBCO Scribe® Insight Console.
2. Backup the ScribeInternal Database.
3. In the ScribeInternal database, delete the entries that match the Label from the DTS KeyCrossReferenceTwoWay settings that need to be rebuilt:
Delete FROM [SCRIBEINTERNAL].[SCRIBE].[KEYCROSSREFERENCETWOWAY] Where ObjectLabel = 'ERP2CRM-Customer'
4. Create a DTS that uses a simple update step to rebuild the KeyCrossReferenceTwoWay. The easiest thing may be to make a copy of an existing DTS that has Key Cross Reference settings already configured and modify as necessary.
The Source of the DTS may need to be modified to select all records from a particular entity such as the GP Customer table. In this case the source is SQL and you would typically you would read all existing records.
5. Verify the DTS KeyCrossReferenceTwoWay settings are correct. The settings will need to be modified if the original source was based on another connection type (e.g. XML)
6. Construct a DTS target that does a simple update.
a. The Update step creates an entry in the Key Cross Reference table.
b. The Seek step should go to the next row if a match is not found in the target.
c. Specify the appropriate Lookup links for the Seek & Update Steps.
** Note: Key Cross Reference will not get updated using an Update/Insert step – must use separate Insert & Update steps.
d. Create a target variable that holds the value of the field the update will be performed on.
The idea is you simply update the field with its current value.
7. Run the DTS and verify the KeyCrossReferenceTwoWay has been rebuilt.
This example uses the GP to CRM Customer Accounts for building the table.
Prior to running in production, this process should be tested in a development/test environment.
1. Stop all integrations processes in the TIBCO Scribe® Insight Console.
2. Backup the ScribeInternal Database.
3. In the ScribeInternal database, delete the entries that match the Label from the DTS KeyCrossReferenceTwoWay settings that need to be rebuilt:
Delete FROM [SCRIBEINTERNAL].[SCRIBE].[KEYCROSSREFERENCETWOWAY] Where ObjectLabel = 'ERP2CRM-Customer'
4. Create a DTS that uses a simple update step to rebuild the KeyCrossReferenceTwoWay. The easiest thing may be to make a copy of an existing DTS that has Key Cross Reference settings already configured and modify as necessary.
The Source of the DTS may need to be modified to select all records from a particular entity such as the GP Customer table. In this case the source is SQL and you would typically you would read all existing records.
5. Verify the DTS KeyCrossReferenceTwoWay settings are correct. The settings will need to be modified if the original source was based on another connection type (e.g. XML)
6. Construct a DTS target that does a simple update.
a. The Update step creates an entry in the Key Cross Reference table.
b. The Seek step should go to the next row if a match is not found in the target.
c. Specify the appropriate Lookup links for the Seek & Update Steps.
** Note: Key Cross Reference will not get updated using an Update/Insert step – must use separate Insert & Update steps.
d. Create a target variable that holds the value of the field the update will be performed on.
The idea is you simply update the field with its current value.
7. Run the DTS and verify the KeyCrossReferenceTwoWay has been rebuilt.
Issue/Introduction
This article provides a step-by-step example of rebuilding the KeyCrossReferenceTwoWay table for one object label.
Feedback
Yes
No
Powered by
