Using lookup relationships

A lookup relationship associates data that is equivalent across business objects but may be represented in different ways. The following sections describe the steps for using lookup relationships:

Note:
For background information, see "Lookup relationships"..

Creating lookup relationship definitions

Lookup relationship definitions differ from identity relationship definitions in that the participant types are not business objects but of the type Data (the first selection in the participant types list). For more information on how to create a relationship definition for a lookup relationship, see "Defining lookup relationships"..

For example, suppose you create a lookup relationship called StatAdtp for the AddressType values. In Figure 100, each box represents a participant in the StatAdtp lookup relationship. Notice that each participant in this relationship is of type Data.

Figure 100. The StatAdtp lookup relationship definition

Because a lookup relationship does not indicate which attributes are being related, you can use one lookup relationship definition for transforming several attributes. In fact, you can use one lookup relationship definition for every attribute that requires a lookup, regardless of the business object being transformed. However, because only one set of tables is created for each relationship definition, using one relationship definition for all lookup relationships would make the tables large and hard to maintain.

A better strategy might be to create one lookup relationship definition per common unit of data, such as country code or status. This way, each set of relationship tables contains information related by meaning. Relationships defined this way are also more modular because you can add new participants, as you support new collaborations or applications, and reuse the same relationship definition. For instance, suppose you create a lookup relationship definition for country code to transform Clarify_Site business objects to SAP_Customer. Later on, if you add new collaborations or a new application, you can reuse the same relationship definition for every transformation involving a country code.

Populating lookup tables with data

When you deploy the lookup relationship definition with the option Create Schema enabled, Interchange Server Express generates a relationship table (also called a lookup table) for each participant. Each lookup table has a name of the form:

RelationshipDefName_ParticipantDefName
 

When you deploy the StatAdtp relationship definition (see Figure 100) with the option Create Schema enabled, Interchange Server Express generates the following two lookup tables:

A lookup table contains a column for the relationship instance ID (INSTANCEID) and its associated participant instance data (data). Figure 101 shows the lookup tables for the PsftAdtp and SAPAdtp participants in the StatAdtp lookup relationship. These two lookup tables use the relationship instance ID to correlate the participants. For example, the instance ID of 116 correlates the PsftAdtp value of Fired and the SAPAdtp value of 04.

Figure 101. Relationship tables for the CustLkUp lookup relationship

Unlike relationship tables that hold data for identity relationships, lookup tables do not get populated automatically. You must populate these tables by inserting data into their columns. You can populate a lookup table in either of the following ways:

Inserting participant instances with SQL

You can insert participant data into a lookup table with the SQL statement INSERT. This method is useful when you need to add many rows of data to the lookup table. You can create the syntax for one INSERT statement and then use the editor to copy and paste this line as many times as you have rows to insert. In each line, you only have to edit the data to be inserted (usually in a VALUES clause of the INSERT statement).

To use the INSERT statement, you must know the name of the lookup relationship table and its columns. Table 71 shows the column names in a lookup table.

Table 71. Columns of a lookup table

Column in lookup table Description
INSTANCEID The relationship instance ID.
data The participant data
STATUS Set to zero (0) when the participant is active
LOGICAL_STATE Indicates whether the participant instance has been logically deleted (zero indicates "no")
TSTAMP Date of last modification for the participant instance.
Attention:
When you use SQL statements to insert participant data into a lookup table, make sure you provide a value for the STATUS, LOGICAL_STATE, and TSTAMP columns. All values are required for IBM WebSphere business integration tools to function correctly. In particular, omission of the TSTAMP value causes Relationship Manager to be unable to retrieve the participant data; if no timestamp value exists, Relationship Manager raises an exception.

Suppose you want to add the participant data in to the relationship table that contains information for address type, shown in Table 72.

Table 72. Sample values for address type for PsftAdtp participant

INSTANCEID STATUS LOGICAL_STATE TSTAMP data
1 0 0 current date Home
2 0 0 current date Mailing

The following INSERT statements create the Table 72 participant data in the PstfAdtp lookup table:

INSERT INTO StatAdtp_PsftAdtp_T 
  
    (INSTANCEID, STATUS, LOGICAL_STATE, TSTAMP, data)
    VALUES (1, 0, 0, getDate(), 'Home')
  
 INSERT INTO StatAdtp_PsftAdtp_T 
    (INSTANCEID, STATUS, LOGICAL_STATE, TSTAMP, data)
    VALUES (2, 0, 0, getDate(), 'Mailing')
 
Note:
The preceding INSERT syntax is compatible with the MicroSoft SQL Server 7.0. If you are using another database server for your relationship table, make sure you use INSERT syntax compatible with that server.

Inserting participant instances with Relationship Manager

Relationship Manager is an IBM WebSphere business integration tool that graphically displays run-time data in a relationship table. Relationship Manager is useful when you only need to add a few rows to the lookup table. For more information on Relationship Manager, see the User Guide for WebSphere Business Integration Express and Express Plus for Item Synchronization.

Customizing map transformations for a lookup relationship

Once you have created the relationship definition and participant definitions for the lookup relationship, you can customize the map transformation rule for performing the lookups. For information on using lookup relationships, see "Example 3 of using the Activity Editor".

Copyright IBM Corp. 2003