IBM BPM, V8.0.1, All platforms > Administer applications and processes in the runtime environment > Manage relationships > Querying relationship data

Example: Querying relationship data using database views

This example uses SQL scripts with a DB2 Universal Database™ to query an identity relationship with three sets of data from three enterprise applications: Clarify, SAP, and Siebel.

The data is correlated using the IBM BPM relationship service. Each application contains similar customer information, with an identity relationship to correlate the information between each application.

The following three tables show the data as it is stored within each database:

Clarify customer
Given Name Last Name Home Phone ID
Jessica Reed 111 111 11111 clarify_1
Tara McLean 333 333 33333 clarify_2

SAP customer
Given Name Last Name Home Phone ID
Jessica Reed 111 111 11111 sap_10
Tara McLean 333 333 33333 sap_8

Siebel customer
Full Name Home Phone ID
Jessica Reed 111 111 11111 siebel_6
Tara McLean 333 333 33333 siebel_8

The customer business object definition names and elements (created in Integration Designer for each database) are shown in the following table:

Business object definitions for customer on each database
ClarifyCustomer SapCustomer SiebelCustomer
Element Type Element Type Element Type
givenName string firstName string fullName string
lastName string lastName string    
homePhone string homePhone string homePhone string
clarifyId string sapId string siebelId string

An identity relationship is defined to correlate the customer information between each database. This relationship, called ID in this example, uses the business object elements clarifyId, sapId, and siebelId. These elements are used because they contain the ID data for each database, and that data is unique for each customer. The following table describes the roles used to correlate different databases in the relationship to a common ID used by IBM BPM:

ID relationship definition
Relationship name Role name Business object name Key
ID GenCustomer GenCustomer genId
ClarifyCustomer ClarifyCustomer clarifyId
SapCustomer SapCustomer sapId
SiebelCustomer SiebelCustomer siebelId

The full relationship name is http://CustomerModule/ID. The full role names are

You can correlate the data within the business objects contained in all three databases by using the defined relationship. The customer ID data from each database is correlated with the customer data from the other databases by sharing instance IDs.

For example, Tara McLean is identified by clarify_3 ID in Clarify, sap_8 in SAP, and siebel_8 in Siebel. A unique ID is generated by the IBM BPM relationship service.

You can use the views to browse the relationship table content.

You can define multiple relationship instances by using the views created in the Common database. The mapping of the view name (using the naming convention as previously described) to its corresponding relationship role is captured in the RELN_VIEW_META_T table in the Common database. The following table shows an example of the view names for the ClarifyCustomer, SapCustomer, and SiebelCustomer roles:

RELN_VIEW_META_T table
VIEW_NAME RELATIONSHIP_NAME ROLE_NAME
V_ID_CLARIFYCUSTOMER_098 http://CustomerModule/ID http://CustomerModule/ClarifyCustomer
V_ID_SAPCUSTOMER_515 http://CustomerModule/ID http://CustomerModule/SapCustomer
V_ID_SIEBELCUSTOMER_411 http://CustomerModule/ID http://CustomerModule/SiebelCustomer
V_USASTATE_ABBREVIATION_DE8 http://CustomerModule/USASTATE http://CustomerModule/Abbreviation
V_USASTATE_CODE_B32 http://CustomerModule/USASTATE http://CustomerModule/Code
V_USASTATE_NAME_933 http://CustomerModule/USASTATE http://CustomerModule/FullName
The view column definition as described in table 1 will have a ROLE_ATTRIBUTE_COLUMN with the following properties:

View column definition
Column Name Data Type Value Description
KEY_ATTRIBUTE_NAME depends on the key attribute type Not null This is where the role instance data is stored. For identity relationships, the column is named by the name of the key attribute.

For example, SAPCUSTOMER_SAPID will use sapid as the key attribute name and sapcustomer as the business object name. One column is defined for each key attribute. For static relationships, the column is named DATA

The following table shows the show the views in the Common database for the ID relationships.

View column definition
Clarify role view SAP role view Siebel role view
INSTANCEID INSTANCEID INSTANCEID
CLARIFYCUSTOMER_CLARIFYID SAPCUSTOMER_SAPID SIEBELCUSTOMER_SIEBELID
STATUS STATUS STATUS
LOGICAL_STATE LOGICAL_STATE LOGICAL_STATE
LOGICAL_STATE_TIMESTAMP LOGICAL_STATE_TIMESTAMP LOGICAL_STATE_TIMESTAMP
CREATE_TIMESTAMP CREATE_TIMESTAMP CREATE_TIMESTAMP
UPDATE_TIMESTAMP UPDATE_TIMESTAMP UPDATE_TIMESTAMP
ROLEID ROLEID ROLEID

All of the column names in the views match, except the key attribute column names.

You must first know the name of the role runtime table view before you can run SQL against the view to manipulate role instance data. The following SQL script shows an example using DB2 Universal Database. The example assumes that all the data from each database has been copied to the relationship database. You can copy the data using the SELECT INTO SQL statement: 

//Create a table to store ID values from all three applications for each customer, 
//and associate a unique instance ID with each customer. Use this table as a base 
//source table to populate relationship tables.
CREATE TABLE joint_t (instanceid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, clarify_id VARCHAR(10) NOT NULL, sap_id VARCHAR(10) NOT NULL, siebel_id VARCHAR(10) NOT NULL)

//Compare the name and home phone number across the three application tables. 
//If a match is found, insert that person's ID value from each application table 
//into the joint_t table. Associate the three ID values to a unique ID; this 
//ID will be used later as the relationship instance ID. 
INSERT INTO joint_t (clarify_id,sap_id,siebel_id)
SELECT A.ID, B.ID, C.ID
FROM clarifycustomer A,sapcustomer B, siebelcustomer C
WHERE A.homephone=B.homephone AND
B.homephone=C.homephone, AND
B.givenname=C.firstname AND
B.lastname=C.lastname AND 
A.fullname=C.firstname CONCAT ' ' CONCAT C.lastname

//Create a sequence for each application; this sequence will be 
//used later as a role ID in each role table.
CREATE SEQUENCE clarify_roleid MINVALUE 1 ORDER CACHE 100
CREATE SEQUENCE sap_roleid MINVALUE 1 ORDER CACHE 100
CREATE SEQUENCE siebel_roleid MINVALUE 1 ORDER CACHE 100

//Populate the role instance table for the CLARIFY role.
INSERT INTO V_ID_CLARIFYCUSTOMER_098 (instanceid, roleid,   clarifycustomer_clarifyid, status, logical_state, logical_state_timestamp,   create_timestamp, update_timestamp)
FROM joint_t

//Populate the role instance table for the SAP role.
INSERT INTO V_ID_SAPCUSTOMER_515 (instanceid, roleid, sapcustomer_sapid,   status, logical_state, logical_state_timestamp, create_timestamp,   update_timestamp)
SELECT instanceid NEXTVAL FOR sap_roleid, sap_id, 0, 0, current   timestamp, current timestamp, current timestamp
FROM joint_t

//Populate the role instance table for the SIEBEL role.
INSERT INTO V_ID_SIEBELCUSTOMER_AFC (instanceid, roleid, siebelcustomer_siebelid,   status, logical_state, logical_state_timestamp, create_timestamp, update_timestamp)
SELECT instanceid, NEXTVAL FOR siebel_roleid, sap_id, 0, 0, current timestamp,   current timestamp, current timestamp
FROM joint_t
The joint_t table is created to temporarily store key values. You can delete the table when you are finished to save resources, if necessary. Alternatively, you can create a view table or a temporary table.

Querying relationship data