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® Business Process Manager 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:
Table 1. Clarify customer| Given Name |
Last Name |
Home Phone |
ID |
|---|
| Jessica |
Reed |
111 111 11111 |
clarify_1 |
| Tara |
McLean |
333 333 33333 |
clarify_2 |
Table 2. SAP customer| Given Name |
Last Name |
Home Phone |
ID |
|---|
| Jessica |
Reed |
111 111 11111 |
sap_10 |
| Tara |
McLean |
333 333 33333 |
sap_8 |
Table 3. 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:
Table 4. 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 that are used
to correlate different databases in the relationship to a common ID
used by
IBM Business Process Manager:
Table 5. 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
- http://CustomerModule/ClarifyCustomer
- http://CustomerModule/SapCustomer
- http://CustomerModule/SiebelCustomer
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 Business Process Manager relationship
service.
Tip: 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:
Table 6. 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:
Table 7. 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.
Table 8. 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 |
Note: 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.
