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

Querying relationship data

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

Use 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.

Use 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.

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:

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

Manage relationships