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.

Example: Suppose you create a lookup relationship called StatAdtp for the AddressType values. In Figure 122, each box represents a participant in the StatAdtp lookup relationship. Notice that each participant in this relationship is of type Data.

Figure 122. 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 122) 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 123 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 123. 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 91 shows the column names in a lookup table.

Table 91. 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 Server Express 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.

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

Table 92. 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 92 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 Server Express 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.

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 customizing lookup relationships in Activity Editor, see Example 3: Using Static Lookup for conversion.

Table 93 shows the Mapping API methods needed to implement a lookup relationship. This table also lists in which map the API call is needed.

Table 93. Mapping API methods for lookup relationships
Step in lookup relationship Map Mapping API method
Obtain relationship instance ID for the participant data from the source business object. The instance ID is saved in the generic business object. Inbound map retrieveInstances()
Obtain participant instances for the relationship instance ID from the generic business object. The participant data is saved in the application-specific business object. Outbound map retrieveParticipants()

Tip:
The retrieveInstances() and retrieveParticipants() methods do not populate the lookup tables. They assume that the participant data already exists in the tables. Make sure you populate lookup tables before you run a map that contains a lookup relationship. For more information, see Populating lookup tables with data.

Coding the inbound map

You call the retrieveInstances() method in the inbound map to retrieve relationship instance IDs for the participant data in the source business object. The following piece of code performs a lookup in the inbound map:

String addrtype = ObjSrcObj.getString("SrcAttr");
int[] generic_ids = Relationship.retrieveInstances(
   "RelationshipDefName", "ParticipantDefName", dataFromSrcObj);
if (generic_ids != null && generic_ids.length > 0)
   {
   ObjDestObj.setWithCreate("DestAttr", generic_ids[0]);
   }

else
   {
   throw new MapFailureException(
   logError("No generic instance ID for lookup found");
      "No generic instance ID for lookup found");
   }

Tips: Keep the following tips in mind when coding the retrieveInstances() method:

Coding the outbound map

You call the retrieveParticipants() method in the outbound map to retrieve relationship instance IDs for the participant data in the source business object. The following piece of code performs a lookup in the outbound map:

int addrtype = ObjSrcObj.getInt("SrcAttr");

if (addrtype != null)
   {
   Participant[] psft_part = Relationship.retrieveParticipants(
      "RelationshipDefName", "ParticipantDefName", addrtype);
   if (psft_part != null && psft_part.length > 0)
      ObjDestObj.setWithCreate("DestAttr", psft_part[0].getString());
   }

Tips: Keep the following tips in mind when coding the retrieveParticipants() method:

Copyright IBM Corp. 2004, 2005