Basic DQL and API

Some basic DQL Queries

Folder:

To get all documents under a folder

select * from dm_document where FOLDER ('/Temp')

To get all documents under a folder and its sub folders

select * from dm_document where FOLDER ('/Temp',DESCEND)

To get folder path of a document

select r_folder_path from dm_folder where r_object_id in (select i_folder_id from dm_document where r_object_id='<r_object_id of document>')

     

Workflow:

To get the instance of a particular workflow:

select * from dm_workflow where process_id in (select r_object_id from dm_process where object_name='testworkflow')

To get the queue items for a workflow:

select * from dmi_queue_item where router_id='<r_object_id of workflow>'

To get the documents attached to a workflow

select distinct r_component_id from dmi_package where r_workflow_id='<r_object_id of workflow>'

Data Dictionary:

To get data dictionary information of attributes in an object type (for eg : dm_document)

select attr_name,label_text,type_name from dmi_dd_attr_info where type_name = 'dm_document'

To get data dictionary information of an object type:

select * from dmi_dd_type_info where type_name = 'dm_document'

Virtual Document:

To check whether a document is virtual document

select count(*) from dmr_containment where parent_id = <r_object_id>.

               If it returns a value > 0, it is a virtual document.

To select all direct and indirect components of virtual document

SELECT r_object_id, PARENT, DEPTH FROM dm_document IN DOCUMENT ID ('<r_object_id of virtual document>') DESCEND

To get the list of parent virtual documents to which our object is linked to:

select object_name,r_object_id from dm_sysobject

where r_object_id in (select parent_id from dmr_containment

where component_id = (select i_chronicle_id from dm_sysobject where r_object_id = '<child-object-id>')) 

Others:

To get current logged in user:

select user_name from dm_user where user_name = USER

Some basic API command

To Get All Attributes of an object

dump,c,<r_object_id>

To get all current logged in users

get,c,sessionconfig,r_user_name

To publish data dictionary

publish_dd,c

reinit,c

Basics of Documentum Workflow

            Series of activities/tasks are called as Workflow.  Mostly these activities are performed on documents.  These activities can be performed by a user or by a group of users or by any system. Most common example for a workflow is ’Leave Application’. Following is the flow for basic leave application.

In above example we have series of activities like

1.      User submits leave application form. 

2.      Approver approves / rejects the application

3.      Updating the database

4.      Mail is sent to the user.

The activities can either be manual or automatic.  Manual activity is performed by User (Performers). Automatic activity is done by the system (i.e. not by user).

The document involved in this workflow is ‘Leave application form’.

How to design a Workflow:

            In documentum 6.x, the workflows are designed using the tool called “Documentum Process Builder”.  In process builder we have following Templates.

-         Manual Activity Templates, where we can select the performer.

-         Auto-Activity Template, where we can write our own code to be executed.

-         Pre defined Activity Templates

 A set of activity templates, each performing different functions are provided in process builder. Following are some of the pre-defined activity templates

·        DQL Read/Write

·        Link to Folder

·        Create ACL

·        Process Data Mapping

·        Create Folder

·        Invoke Process

With use of these templates we need to design our Workflow as shown below.

Approver is the manual activity and the performer is selected in activity template. Whenever the workflow is initiated, this Manual task goes and sits in the Approver’s Inbox.  Once the approver selects or rejects the application, the flow is decided accordingly.

One more important point is we need to configure which type of document should flow through this workflow. We call them as package. We can have one or more packages for a workflow.

Object types involved in Workflow:

Following are the important document types and attributes involved in workflow

Type	Explanation
dm_process	The workflow template we create using Process Builder is saved as dm_process object. We can call the process as blue-print of the workflow.
dm_activity	The Activity Template details are stored under this type.
dm_workflow	Created during runtime. Whenever we start a workflow, dm_workflow object is created. We can tell dm_workflow is the runtime instance of dm_process.
dm_workitem	Created during runtime. dm_workitem is runtime instance of dm_activity.
dmi_package	Created during runtime. It contains details about the documents attached to the workflow.

How these objects are related

           

Basics of BAM & PRS

What is BAM database?

Business Activity Monitor (BAM) is used to monitor processes . Process data are captured and stored in BAM database. These data are used for reporting purpose. The following data can be captured in BAM database.

-          Process details (workflow details)

-          Activity details

-          Package Object Types

-          Process variables

-          SDT

Package object type and SDT are represented as business data.

What is PRS?

 ‘Process Reporting Services (PRS)’ is a tool to create reports from the data stored in BAM database.

What is BAM Dashboard?

Dashboard is the display environment for the reports. Dashboards are created in Taskspace to display the reports to users.

How to enable process & packages for monitoring?

- Process

If we enable a process for monitoring, the activity details and Process variables are also captured along with the process.

To monitor a process, ‘Audit trail’ has to be enabled for that process.  For enabling audit trial,

-          Open process builder.

-          Open the process which has to be monitored.

-          Go to Tools-> Process Properties from the menu

-          In ‘General’ tab, Select ‘On’ under Audit Trial Settings.

- Package object type

-          In ‘Data’ tab of process properties, open the package which has to be captured.

-          Select the checkbox ‘ This package can be used to generate reports’

- Structured Data Types

-          Open the SDT and select the checkbox ‘Update BAM database tables based on this SDT definitions’ as shown below

How to update BAM data definitions?

            If our data definitions are not yet reflected in BAM database, you can update the BAM data definitions as follows

It can also be done from Taskspace ‘Administration’ tab.

Note:

Let us consider a process ‘Example_workflow’ has two activities ‘Activity -1’ and ‘Activity -2’. The process has package of type ‘custom_type’.

Custom_type has an attribute   ‘attr1’. Consider that the value for ‘attr1’ in ‘Activity-1’ is ‘value1’ and when it comes to ‘Activity-2’ it changes to ‘Value-2’.

 In this case the details of business data (custom_type) are stored in the following pattern.

Activity-1 > Custom_type (attr1 = Value1)

Activity-2 > Custom_type (attr1 = Value2)

i.e. Custom_type business data table in BAM will have 2 rows, each corresponding to one activity.

Creating Custom Filter in PRS

This article explains how to create a custom filter for an existing filter entity in PRS (Process Reporting Services).  PRS is used to create reports from the data stored in BAM (Business Activity Monitor) database. This article explains the steps to create custom filter for the existing Filter Entity ‘Activity Execution’.

    Requirement:

            In this article we are creating a filter condition to retrieve the latest activity instance from the workflows.

          For example: Consider we have completed the activities ‘Activity-1’ and ‘Activity-2’ in a workflow.  BAM will have entries for both the activities. When we generate a report to display activity details for a particular process, result will contain details about both the activity instances of the particular process.  In this example we are creating a filter to return only ‘Activity-2’ as the result.

    Steps to create custom filter:

          In this example we are creating a filter for Filter Entity ‘Activity Execution’.  We need to create the following 4 objects using DQL

·         Create filter tab definition (bami_filter_definition)

·         Create filer token item (bami_flt_filter_token_items)

·         Create filter entity relationship (bami_flt_filter_entity)

·         Create filter tree item (bami_filter_tree_item)

Create Filter Tab Definition:

In our example we are creating a separate tab definition. It is not mandatory. We can use any existing tab definitions also.

The following is the query to create Filter Tab

create bami_filter_definition object

set object_name='Example Filter Tab',

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

link '/System/BAM/Custom Entities'

object_name : must be Unique in the BAM

All objects should have acl_name = ‘BAM Admin ACL’

all 4 objects must be linked to  '/System/BAM/Custom Entities'

Create filter token item:

create bami_flt_filter_token_items object

set object_name='1',

set filter_id=(select r_object_id from bami_filter_definition where

object_name='Example Filter Tab'),

set filter_item_expr_name='INCLUDE-EXAMPLE-FILTER',

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

link '/System/BAM/Custom Entities'

object_name               : unique identifier for this filter item

filter_id                      : r_object_id of the bami_flt_filter_definition object.

filter_item_expr_name   : This string is used as filter expression. Value should be in capital and without space

Create filter entity relationship :

create bami_flt_filter_entity object

set entity_type='Activity Execution',

set filter_id=(select r_object_id from bami_filter_definition where object_name='Example Filter Tab'),

set filter_item_id=(select fti.r_object_id from bami_flt_filter_token_items

fti, bami_filter_definition fd where fti.filter_id=fd.r_object_id and

fd.object_name='Example Filter Tab' and fti.object_name='1'),

set filter_entity_token_name='EXA',

set filter_entity_from_clause='EXEC_ACTIVITY_INSTANCE e',

append flt_where_clause_oracle='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

append flt_where_clause_mssql='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

append flt_where_clause_db2='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

append flt_where_clause_sybase='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

set flt_sql_column_name_oracle='''True''',

set flt_sql_column_name_mssql='''True''',

set flt_sql_column_name_db2='''True''',

set flt_sql_column_name_sybase='''True''',

set filentity_token_long_name='EXAMPLE-FILTER',

set filter_entity_order_by=20,

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

link '/System/BAM/Custom Entities'

entity_type                        : The filter entity name

filter_id                             : r_object_id of the bami_flt_filter_definition object.

Filter_item_id                     : r_object_id of the bami_flt_filter_token_items object.

filter_entity_token_name     : Token name used in filter expression.

filter_entity_from_clause     : tables names that are part of the query

flt_sql_column_name_oracle,

flt_sql_column_name_mssql, :  filter conditions defined by user

flt_sql_column_name_db2,

flt_sql_column_name_sybase

flt_where_clause_oracle,

flt_where_clause_mssql,        :  where clause of filter expression.

flt_where_clause_db2,

flt_where_clause_sybase

filentity_token_long_name      : This field is shown in the label of the filter tab within PRS.

Create filter tree item:

create bami_filter_tree_item object

set object_name='Include Example Filter',

set filter_id=(select r_object_id from bami_filter_definition where

object_name='Example Filter Tab'),

set tree_item_last_level=0,

set tree_item_type_select='N',

set data_type='Boolean',

set for_web=0,

set expression[0]=':FilterTokenName.INCLUDE-EXAMPLE-FILTER = ''True''',

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

set internal_code ='example_filter',

link '/System/BAM/Custom Entities'

object_name            : This object name is the label shown in filter tree

filter_id                   : r_object_id of the bami_flt_filter_definition object.

tree_item_last_level : index level of tree item

expression[0]           : expression displayed if this filter is selected in PRS

Refresh entities in BAM database:

We need to restart the BAM database or trigger refresh entity task to get our filer in PRS.

Refresh entity task can be triggered by running the following sql in BAM database

Update I_BAM_SERVER_CONFIG set BLOCK=0 WHERE SERVERNAME='RefreshEntities'

    Output :

The following is the query generated for the report:

select EntityTable0.INSTANCEID as "myid0", EntityTable0.ACTIVITY_ID as "myid1", EntityTable0.ID as "myid2", EntityTable0.NAME as "field1", EntityTable0.ACTIVITY_ID as "field2"   from  V_ACT_EXECUTION EntityTable0   where 1=1 and EntityTable0.INSTANCEID in 

( select e.INSTANCEID  from EXEC_ACTIVITY_INSTANCE e where EntityTable0.ACTIVITY_ID=e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID) and 'True' = 'True' )   

The above query generated returns only the last activity instances of workflows.

    How the query is formed :