WebSphere Enterprise Service Bus, Version 6.2.0 Operating Systems: AIX, HP-UX, i5/OS, Linux, Solaris, Windows


Querying relationship data using database views

You can use views in a database to query relationship data without using the relationship manager.

You can use your database views to directly query relationship data stored on the database. When you create a new relationship database table, a corresponding SQL view is automatically created. These views are essentially encapsulations of the relationship data stored in database tables. You can use these views to populate, query relationship data, or both by: In either case, you can use the SQL views in the same manner as you would for tables. You can use this technique as an alternative method to the Relationship Manager application to directly populate large sets of application-specific data by using SQL statements into your relationship database(s). You can also use this technique to import data from a flat-text file into a database table
Relationship database SQL views are created based on data contained in tables located elsewhere in the data source. The view will exist even when the database table itself is empty. Each view is has its own unique name which follows this convention: "V_"+relationship_display_name+"_"role_display_name+"_"+uuid (notice that the variables are concatenated using an underscore character "_"). Both display names are limited to 20 alphanumeric characters, while the uuid is a number generated from the combination of both display names. Consequently, each view name should be unique within a data source. An example of this naming convention can be shown if we use these variables: The resulting view name would be "V_SAMPLECUSTID_MYCUSTOMER_80C". For a given relationship, you should have two corresponding views containing the same relationship display name but different role display names and uuids.
Note: For Oracle databases, the naming convention differs in this regard: only the first ten characters of the relationship_display_name and role_display_name are used.

Each view will contain the columns (including the associated properties of type, value, and nullable) listed in the following table:

Table 1. Relationship database view columns
Name Data type Value Nullable?
INSTANCEID Integer The ID number used to correlate instance data between different applications. No
ROLE_ATTRIBUTE_COLUMNS
  • Dynamic relationship - defined in business object
  • Static relationship - DATA
  • Dynamic relationship - defined in business object
  • Static relationship - Varchar
The column name and type depends on the role definition. Column names are based on the key attribute names, while. column types are database data types that is mapped based on key attribute type defined in role definition. No
STATUS Integer 0-4
  • 0 – created
  • 1 – updated
  • 2 – deleted
  • 3 – activated
  • 4 – deactivated
Note: When populating instances through views, ensure that the value for this column is 0.
Yes
LOGICAL_STATE Integer
  • 0 = activated
  • 1 = deactivated

Ensure that you set the proper value when you populate the database with data.

No
LOGICAL_STATE_TIMESTAMP Timestamp Date and time when the logical state column data was last updated. Yes
CREATE_TIMESTAMP Timestamp Date and time when the role instance was created. Yes
UPDATE_TIMESTAMP Timestamp Date and time when the role instance was last updated. Yes
ROLEID Integer ID number used to identify a role instance No

Example

This example presented here is an identity relationship that includes three sets of data from three enterprise applications:
  • Clarify
  • SAP
  • Siebel
The data is correlated using the WebSphere® ESB 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 2. Clarify customer
Given Name Last Name Home Phone ID
Jessica Reed 111 111 11111 clarify_1
Tara McLean 333 333 33333 clarify_2
Table 3. SAP customer
First Name Last Name Home Phone ID
Jessica Reed 111 111 11111 sap_10
Tara McLean 333 333 33333 sap_8
Table 4. 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 WebSphere Integration Developer for each database) are shown in the following table:

Table 5. 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 WebSphere ESB:
Table 6. 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 WebSphere ESB relationship service.
Note: You cannot manipulate relationship instance tables using the views with the Derby database. You can, however, 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 7. 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 8. 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 9. 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.

concept Concept topic

Terms of use | Feedback


Timestamp icon Last updated: 21 June 2010


http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r2mx/topic//com.ibm.websphere.wesb620.doc/doc/cadm_relationship_database.html
Copyright IBM Corporation 2005, 2010. All Rights Reserved.
This information center is powered by Eclipse technology (http://www.eclipse.org).