TIBCO Scribe® Insight includes a set of special-purpose functions that work
with the Insight Cross Reference
feature. These functions perform a lookup on a table or object when
you are trying to find a match. The Cross Reference feature, in conjunction
with the XREFLOOKUP functions, allows you to determine if data already
exists in a target when you are inserting data from the source. If the
data exists in the target, you need a value to uniquely identify the target
record in case you want to update it. In most cases, you will use the
XREFLOOKUP functions in a formula on a lookup link or in a formula in
a pre-operation step flow formula.
Classes of XREFLOOKUP Functions
The XREFLOOKUP functions can be categorized into three main types:
Use
with Any Connection — The XREFLOOKUP
function is similar to DBLOOKUP, but adds the feature of returning an
error if the lookup finds more than one matching row.
Use
with the KEYCROSSREFERENCE Table — XREFLOOKUPINTERNAL
also returns an error if the lookup returns more than one row and is designed
to work specifically with the KEYCROSSREFERENCE table in the Scribe Internal
database.
Use
with the KEYCROSSREFERENCETWOWAY Table — The following functions
are designed to work with the KEYCROSSREFERENCETWOWAY table in the Scribe Internal database:
When using message
queues as the source, multiple Insight processes can access the cross
reference record at the same time. By using the right function, you can
lock the cross reference record during the lookup to prevent multiple
Insight processes from looking up a key at the same time and avoid the
possibility of duplicate data in the target.
XREFLOOKUP Function Example
In the following example, you have the task of integrating customer
data between APP A and APP B. In this example, APP A is the source, APP
B is a target, and you need to integrate customer master data.
For this application:
You are using the cross reference
keys feature to maintain primary ID cross references between APP A and
APP B.
You are using the KEYCROSSREFERENCETWOWAY
table in the SCRIBEINTERNAL database to store the IDs and using “CUSTOMER”
for the label in the cross reference table.
Your DTS has a pre-operation
step flow formula that takes the primary ID from APP A, looks it up
in the KEYCROSSREFERENCETWOWAY table using the following formula:
Where Step 3 is an Update step.
Using this example:
If no match is found, an Insert
step occurs.
If a match is found,the field
is updated.
Your DTS file has a lookup link
that maps the primary ID field from the source (S1) to the primary ID
field for your target (CustomerID). The lookup link has a formula
that uses an XREFLOOKUP locking function:
XREFLOOKUPLOCK_BKEY(“CUSTOMER”, S1).
What you want to happen:
When you run the DTS file, the XREFLOOKUPLOCK_BKEY
function in the formula will be used as part of the lookup link.
If a matching ID is found in the cross reference table:
You know the record already exists
in the target.
At that point, you can decide whether
the DTS file will update the target record, or skip inserting the record
in the target.
If you update the target record,
retrieve the primary ID of the target record to update from the cross
reference table.
If a matching ID is not found in the cross reference table:
You know the record does not exist
in the target so you want to insert it.
The cross reference feature stores
the primary ID from the source record and the primary ID from the target
record that results from the insert.
You can use this pair of primary
IDs for lookups the next time you need to integrate data from that type
of record.
Note: If you are
not using message queuing, XREFLOOKUPNOLOCK_AKEY and XREFLOOKUPNOLOCK_BKEY
may run faster.