This topic applies only to the IBM Business Automation Workflow Advanced configuration.

Querying relationship data

If you want to query relationship data, you can use the relationship manager or views in a database.

Using the relationship manager to query relationship data

The relationship manager supports the following options for querying the instance data associated with a relationship:
All
Get a list of all instances in the relationship. You can select to display all activated, all inactivated, or all activated and inactivated relationship instance data.
By ID
Get relationship instances in the range of the starting and ending instance identifiers. If you leave one field blank, the query returns only the single instance. The query returns all of the roles for the instances it finds.
By property
Get relationship instances by specific property values.
By role
Get relationship instances based on a role name, key attribute value, date range during which the role was created or modified, or specific property value.
The query returns a result set displayed in table format, with each row representing one relationship instance.

For more information on querying relationship data with the relationship manager, see the relationship manager online help.

Using database views to query relationship data

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 using 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:

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 are 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