Documentum fundamental helpful DQL queries

Helpful DQL Queries

DQL to get the user that deleted a document

select * from dm_user where r_object_id= (select user_id from dm_audittrail where event_name='dm_destroy' and audited_obj_id='ID OF DELETED OBJECT')

DQL for path and number of files in a Folders

select count(*) as doc_cnt, f.r_folder_path from dm_document d, dm_folder f where d.i_folder_id = f.r_object_id and folder('/System', descend) and f.r_folder_path is not nullstring group by f.r_folder_path

UNION

select 0 as doc_cnt, r_folder_path from dm_folder where folder('/System', descend) and r_object_id in (select r_object_id from dm_folder f where r_object_id NOT IN (select distinct i_folder_id from dm_sysobject where any i_folder_id = f.r_object_id)) and r_folder_path is not nullstring group by r_folder_path enable(row_based)

DQL to get all empty (sub)folders in a cabinet
select * from dm_folder where r_link_cnt=0 and folder('/Temp',descend)

DQL to get list of ALL documents and their folder path
select distinct d.r_object_id,d.object_name,f.r_folder_path from dm_document d, dm_folder f where any d.i_folder_id=f.r_object_id and r_folder_path is not nullstring enable(ROW_BASED)

DQL to display the supertypes hierarchy brach of the specified type
select r_supertype from dmi_type_info where r_type_id = (select r_object_id from dm_type where name='my_type')

DQL to get number of modified documents for each month
select datetostring(r_modify_date,'mm/yyyy'),count(*)from dm_document [WHERE condition] group by datetostring(r_modify_date,'mm/yyyy')

DQL to execute an SQL query
execute exec_sql with query = 'create or replace my_view (cod) as (select some_id from my_table)'

DQL to get the object type of a document
select r_object_type from dm_document where r_object_id='092e6adc800001f0'

DQL to get the number of sysobjects for each object type
select count(*),r_object_type from dm_sysobject group by r_object_type

DQL to create a DB index on a type attribute
EXECUTE make_index WITH type_name='dmi_workitem',attribute='r_workflow_id'

DQL to see Documentum sessions on current Content Server
execute show_sessions

DQL to get ids of documents deleted in a time interval (dm_audittrail)
select * from dm_audittrail where event_name='dm_destroy' where time_stamp > date('date before') and time_stamp < date('date after')

DQL To find All Forms in Draft State (dm_xfm_form)
select object_name,  definition_state from dm_xfm_form WHERE definition_state = 0 AND object_name like '<Name>%'

DQL To find All Forms in Installed State (dm_xfm_form)
select object_name,  definition_state from dm_xfm_form WHERE definition_state = 2 AND object_name like '<Name>%'

DQL To find All Forms NOT in Installed State (dm_xfm_form)
select object_name,  definition_state from dm_xfm_form WHERE definition_state != 2 AND object_name like '<Name>%'

DQL to retrieve Uninstalled documentum process flows (dm_process)
select object_name, r_definition_state  from dm_process where object_name like '<Name>%' and r_definition_state = 1

DQL to retrieve installed documentum process flows (dm_process)
select object_name, r_definition_state  from dm_process where object_name like '<Name>%' and r_definition_state = 2

DQL to retrieve NOT installed documentum process flows (dm_process)
select object_name, r_definition_state  from dm_process where object_name like '<Name>%' and r_definition_state != 2

DQL Non-Working Days from taskspace Calendar (dmc_calendar_event)
select r_object_id, object_name, subject, start_date, end_date from dmc_calendar_event where any i_folder_id in (select r_object_id from dmc_calendar where object_name like '<Your Calendar Name>') AND object_name = 'Non-Working Day'