The Key Cross Reference feature maintains a mapping between records in two systems when the key for a record in one system is different from the key for the corresponding record in the other system.

 

Cross Reference Keys Example
 

For an example of how the cross reference feature works, let's say that you have two applications, App A, and App B. These applications have the following customer ID fields:

• App A — A system-assigned unique integer, such as 1234
• App B — A user-assigned alphanumeric code, such as ACME0001

To integrate customer data from App A into App B, you need to know the relationship between these two IDs, (that is, 1234 = ACME0001). The Cross Reference feature facilitates the process of configuring and maintaining the cross reference of the IDs.

KeyCrossReferenceTwoWay Table
 

Duplicate key errors in the KeyCrossReferenceTwoWay table are caused because a primary key or unique constraint has been violated. Usually this means the data is out of sync due to a mismatch between the App_A_Key and the App_B_Key fields, for example, when the data in each field belongs to a different record.

The KeyCrossReferenceTwoWay process can be seen in the TIBCO Scribe® Insight trace file and works as follows:

1. Process attempts to Insert in KeyCrossReferenceTwoWay table.
2. If a duplicate key error is encountered, it uses the ObjectLabel and A Key as a lookup, and updates the B Key value.
3. If a B Key duplicate error is encountered, this is an error in the KeyCrossReferenceTwoWay table. This may be caused by mismatched records in the DTS that update the Cross Reference.

File PK And Unique Indexes In The ScribeInternal DB

Based on the PK and Unique Indexes the following fields must be unique:

• ObjectLabel and App_A_Key
• ObjectLabel and App_B_Key

Example Of Duplicate Key Issue
 

TIBCO Scribe® Insight attempts to perform an Insert into the KeyCrossReferenceTwoWay table. If that fails, it looks up the record using the ObjectLabel and APP_B_Key fields, and performs an update.  Since an Insert always occurs it can be normal to see errors in the trace, but you shouldn’t see any errors from running the DTS. Below are examples of a successful Insert and an unsuccessful Insert/Update.
 

Successful Cross Reference Insert Example

 
Below is the KeyCrossReferenceTwoWay Table Configuration.
  The image below illustrates the KeyCrossReferenceTwoWay entries created from the initial run for all records.
 
If you were to review the trace for a successful subsequent run for one product, you would see the following entries:
 

KeyCrossReferenceTwoWay Insert

Update Since The Insert Failed

LOOKUP LINK VALUES: [APP_A_KEY] = N'25', [OBJECTLABEL] = N'Product XRef'

DATA LINK VALUES: APP_B_KEY = N'Northwind Traders Almonds', APP_A_NAME = N'Northwind Traders Almonds', APP_B_NAME = N'4121'

DoUpdate affected 1 record(s)

• The actual SQL for the update is not shown in the Scribe Trace.txt file.
• The Where clause is constructed using the fields specified in the Lookup Links

The update process is similar to this:

UPDATE [SCRIBEINTERNAL].[SCRIBE].[KEYCROSSREFERENCETWOWAY] SET [APP_B_KEY]='Northwind Traders Almonds',[APP_A_NAME]='Northwind Traders Almonds',[APP_B_NAME]=4121

Where [APP_A_KEY]='25' And [OBJECTLABEL] = 'ProductXref'

Troubleshooting

If there is a problem with cross referencing it is often because the A or B keys get mixed up, or related to the wrong record. This could be caused by a DBLookup or XrefLookup that returns the wrong value and is used in the DTS.

Assume the wrong product name was retrieved from a DBLookup that would normally go into the App_B_Key for the record above, such as App_A_Key 25. In this case the lookup returns Northwind Traders BBQ Sauce. The Key Cross Reference process attempts to insert using the ObjectLabel = ProductXref and the App_a_Key of 25. This fails as it usually does since the Primary Key already exists.

The next step is to do an update Where [APP_A_KEY]='25' And [OBJECTLABEL] = 'ProductXref' and that succeeds. However, the App_B_Key is overwritten replacing Northwind Traders Almonds with Northwind Traders BBQ Sauce. Now the table is out of sync. When TIBCO Scribe® Insight attempts to generate the record for the Northwind Traders BBQ Sauce product, both the Insert and the Update fail because of the unique constraint on App_B_Key. This constraint is that a unique or only one App_B_Key can exist for an ObjectLabel. You see an error from the Workbench similar to the error shown in the Cross Reference Insert/Update Failure Example. 

The key to trouble shooting is to examine the Scribe trace file and compare data between the Cross Reference table and the applications you are cross referencing. In the trace file, determine what values were used in the failed Cross Reference Insert/Update operation. In the Cross Reference table locate the actual values stored in the table. Compare those values to the values in the applications you are cross referencing and make sure they match.  Reviewing these files helps you verify whether or not the App_A_Key/Name and App_B_Key/Name combinations are correct.
 

Cross Reference Insert/Update Failure Example

 

For additional information, see the following in the TIBCO Scribe® Insight Help: About Cross Reference Keys, XREFLOOKUP Functions, Defining Cross Reference Keys

Duplicate key errors in the KeyCrossReferenceTwoWay table are caused because a primary key or unique constraint has been violated. Usually this means the data is out of sync due to a mismatch between the App_A_Key and the App_B_Key fields.