DQL

Documentum Query Language

Limit DQL results

The number of results from a DQL query can be limited by using the RETURN_TOP keyword. Since multiple rows can be returned for one object, the result limit does not always limit the results to a number of objects, but to a number of rows. The database does not know about 'Objects', therefore the result limit of returned objects is implemented in the content server. The database can only limit the result of rows. The way result limitation is executed is controlled by the 'return_results_row_based' parameter in the server.ini file : - return_results_row_based = False -> Returns Objects. - return_results_row_based = True -> Returns Rows. Default = True (Rows)

DQL Hint : enable(RETURN_TOP N)
Example : SELECT * FROM dm_document enable(RETURN_TOP 100)

General workflow information

SELECT task_name, task_state, actual_start_date, dequeued_date FROM  
dmi_queue_item WHERE router_id = ‘workflowId’​
 
 

List of attachments for a specific workflow

SELECT r_component_id, r_component_name FROM dmi_wf_attachment WHERE  
r_workflow_id = '< workflow ID >'
 
 

Number of versions for a specific object

Object versions are related to each other by the i_chronicle_id attribute. The DQL counts the number of objects with the same i_chronicle_id. Any r_object_id of a document in the version tree can be used

SELECT count(r_object_id) FROM dm_sysobject (ALL) WHERE i_chronicle_id IN  
(SELECT i_chronicle_id FROM dm_sysobject WHERE r_object_id='< object id >'
 
 

List of failed authentication attempts (audit trail)

​SELECT * FROM dm_audittrail WHERE event_name='dm_logon_failure'​
 
 

Physical location of a file stored in the docbase

Files stored in Documentum are written to the content storage of the content server. To find the physical location of those objects on the disc, use the getpath command

DQL : Execute get_file_url FOR < object id >
API : getpath,c,< object id >
 
 

Workflow Reporting indices

These DQLs will create indices on important columns to speed up workflow reporting

EXECUTE make_index WITH type_name='dmi_queue_item', attribute='stamp'
go
 
EXECUTE make_index WITH type_name='dm_workflow', attribute='process_id'
go
 
 

Creation of relations between roles, groups and users

DQL-script for the creation relations between roles, groups and users. An excel-sheet is available for the automated generation of these scripts. This sheet also contains (*) creation of users with a personal cabinet, (*) groups and roles, (*) creation of acl's (a code snippet already exists) 

ALTER GROUP group_name ADD  
group_name_1,group_name_2,role_name_1,role_name_2,user_name_1,user_name_2;
go
 
 

Creation of users with a personal cabinet

DQL-script for the creation of users with a personal cabinet. An excel-sheet is available for the automated generation of these scripts. This sheet also contains (*) creation of roles / grous, (*) relations between groups, roles and users (= where composer fails!), (*) creation of acl's (a code snippet already exists)

CREATE dm_user OBJECT SET user_name='di_appl_beheerder_1', SET  
user_login_name='di_appl_beheerder_1', SET  
user_address='gert.stalmans@hp.com', SET user_privileges=16, SET  
client_capability=8, SET defa
 
 

creation of roles / groups

DQL-script for the creation of roles (drop the update-statements if you just want to create groups). An excel-sheet is available for the automated generation of these scripts. This sheet also contains (*) creation of users with a personal cabinet, (*) relations between groups, roles and users (= where composer fails!), (*) creation of acl's (a code snippet already exists)

CREATE GROUP di_administrators;
go
UPDATE dm_group OBJECT SET group_class = 'role' WHERE group_name =  
'di_administrators';
go
 
 
Syndicate content