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:
- using SQL statements with a DB client (for example, with the DB2® command center)
- using JDBC to run SQL statements with a Java™ program
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:
- relationship_display_name = SAMPLECUSTID
- role_display_name = MYCUSTOMER
- uuid = 80C (this number is generated automatically by the server)
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:
Example
This example presented here is an identity relationship that includes three sets of data from three enterprise applications:
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 customerGiven Name |
Last Name |
Home Phone |
ID |
Jessica |
Reed |
111 111 11111 |
clarify_1 |
Tara |
McLean |
333 333 33333 |
clarify_2 |
Table 3. SAP customerFirst Name |
Last Name |
Home Phone |
ID |
Jessica |
Reed |
111 111 11111 |
sap_10 |
Tara |
McLean |
333 333 33333 |
sap_8 |
Table 4. Siebel customerFull 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 databaseClarifyCustomer |
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 definitionRelationship 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 tableVIEW_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 definitionClarify 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.