Documentum DQL Master List

Find blank or empty folders in a cabiner
SELECT f1.object_name, f1.r_object_id, r_creation_date, f1.r_folder_path, r_modifier, r_creator_name
FROM dm_folder f1 WHERE FOLDER('/CabinetName',descend)AND NOT EXISTS (SELECT f2.object_name FROM dm_sysobject f2 WHERE ANY f2.i_folder_id = f1.r_object_id) ORDER BY r_creation_date

Using a custom date format in the where clause
select * from dm_document where r_creation_date > Date('07/01/2012','MM/DD/YYYY')

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

Retrieve all 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

Retrieve all 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

Find all forms in Draft state
select object_name,  definition_state from dm_xfm_form WHERE definition_state = 0 AND object_name like '<Name>%'

Find all forms in Installed State
select object_name,  definition_state from dm_xfm_form WHERE definition_state = 2 AND object_name like '<Name>%'

Find all forms NOT in Installed State
select object_name,  definition_state from dm_xfm_form WHERE definition_state != 2 AND object_name like '<Name>%'

Find Non-Working Days from taskspace Calendar through DQL
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'

Get the user name for for 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')

Get 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)
-- Toget all empty (sub)folders in a cabinet
select * from dm_folder where r_link_cnt=0 and folder('/Temp',descend)

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)

Get 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')

Find 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')

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

Get he object type of a document
select r_object_type from dm_document where r_object_id='092e6adc800001f0'

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

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

Get Documentum sessions on current Content Server
execute show_sessions

Get ids of documents deleted between certain 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')

Add repeating attributes values
update dm_document OBJECTS append keywords = 'abc', append keywords= 'xyz',append keywords = 'def' WHERE r_object_id = '09..........2'

Remove a repeating attributes values at particular index
update dm_document objects remove keywords[8] , remove authors[8]  where r_object_id = '09xxxxxxx'

Update a repeating attributes values at particular index
update dm_document OBJECT SET keywords[2] = '980' where r_object_id = '09.............2';

Truncate / delete/ remove all repeating attributes values
update dm_document objects truncate keywords where r_object_id = '09......2';

Get work flows that are attached to a particular document where I have r_object_id.
SELECT  *  FROM dm_workflow WHERE r_object_id IN(SELECT r_workflow_id FROM dmi_package WHERE ANY r_component_id =ID('09....'))

DQL Documentum DATETOSTRING DATE TO STRING (DATE_TO_STRING)
select DATETOSTRING(r_creation_date,'dd/mm/yy') from dm_document  where a_category = 'Default XML Application';

To Register a Table in docbase
register table dbo.my_app_holiday (dummy string(1));

Set permission on register table
update dm_registered objects
set owner_table_permit = 15,
set world_table_permit=15
where object_name='my_app_holiday';

Delete records from registered table
delete from dm_dbo.custom_processing where employee_id = '3274273';

To create an object
Create dm_sysobject object
set object_name = 'Test Value'

Delete custom type objects
delete dm_sysobject objects where object_name ='Test Value';

For Monitoring Workflows
select wi.r_creation_date, wi.r_object_id, object_name, wi.r_runtime_state   from dm_workflow w, dmi_workitem wi  where w.r_object_id=wi.r_workflow_id and wi.r_runtime_state IN ('0','1') and r_performer_name = 'USER_Performer' and  wi.r_creation_date < date('09/23/2014 09:05:00') order by wi.r_creation_date asc

Get ALL the Jars and version in Documentum
Select jarfile.r_object_id, jarfile.object_name, jarfile.jar_type,  jarfile.r_creation_date, jarfile.r_modify_date, jarfile.r_version_label, content.content_size from dmc_jar(all) jarfile, dmr_content content where jarfile.i_contents_id=content.r_object_id order by 7 desc

Get# of documents loaded between certain time
select MIN(r_creation_date), MAX(r_creation_date), count(*) from dm_documents where r_creation_date > date('01/15/2015 15:00:00') and r_creation_date < date('01/15/2015 16:00:00');

Check What activities the method server is picking up to be processed
select wf.r_object_id, wf.object_name as wfObjectName , p.object_name as process_name, a.object_name as activity_name from  dmi_workitem w, dm_server_config s, dm_workflow wf , dm_process p , dm_activity a where w.r_workflow_id = wf.r_object_id and s.r_object_id = w.a_wq_name and w.r_runtime_state != '2' and p.r_object_id = wf.process_id and w.r_act_def_id = a.r_object_id;

Check the xCP End points and Parameters to validate if every thing is point right
select r_object_id ,config_name, config_type, property_name, property_value from dmc_xcp_app_config  where namespace = 'abc'

For Full Text search in documentum
SELECT r_object_id FROM dm_document SEARCH DOCUMENT CONTAINS 'UTAH' WHERE object_name LIKE '%DocConversion%' ENABLE(FTDQL);
or
SELECT r_object_id FROM dm_document SEARCH DOCUMENT CONTAINS 'New York City' ENABLE(FTDQL);

Updating dm_server_config settings
update dm_server_config objects set wf_sleep_interval=15, set wf_agent_worker_threads=15;

To get Cryptographic keys in dm_public_key_certificate
Select * from dm_public_key_certificate where key_type = 1 and FOLDER('/System/Cryptographic/Keys')

To get Next Value from Sequencer in SQL Server database
execute EXEC_SELECT_SQL with QUERY='SELECT NEXT VALUE FOR NameOfTheSeq as nextidseqval'

To get DFC Client rights
select r_object_id, object_name , principal_auth_priv, server_trust_priv from dm_client_rights where object_name like '%B-APPBPMCS%' and (principal_auth_priv = 0 or server_trust_priv = 0)

To query for getting the workflows with failed tasks given process id.
SELECT wf.r_object_id AS wf_r_object_id,wf.object_name AS wf_object_name,wf.supervisor_name AS wf_supervisor_name,wf.r_runtime_state AS wf_r_runtime_state,wf.r_start_date AS wf_r_start_date,wf.process_id AS wf_process_id,p.r_object_id AS p_r_object_id FROM dm_workflow wf,dm_process p WHERE (wf.process_id=p.r_object_id) AND (wf.r_runtime_state!=2 AND wf.r_runtime_state!=4 AND wf.process_id='4b01e24080076707' AND ANY wf.r_act_state=4 AND wf.process_id='4b01e24080076707') ORDER BY 1

To get halted activities for all workflows
SELECT wf.r_object_id AS wf_r_object_id,wf.object_name AS wf_object_name,wf.supervisor_name AS wf_supervisor_name,wf.r_runtime_state AS wf_r_runtime_state,wi.r_runtime_state as workItemRunTimeState, wf.r_start_date AS wf_r_start_date,wf.process_id AS wf_process_id,p.r_object_id AS p_r_object_id, wi.r_object_id as WorkItemID, wi.r_act_seqno FROM dm_workflow wf,dm_process p, dmi_workitem wi WHERE (wf.process_id=p.r_object_id) AND (wf.r_runtime_state!=2 AND wf.r_runtime_state!=4  AND ANY wf.r_act_state=4 ) and wi.r_workflow_id =wf.r_object_id
and wi.r_runtime_state != '2' ORDER BY 1;

Outer join
select o.transaction_id as OutPut_Transaction_Id, r.transaction_id as Result_Transaction_Id, r.as400_result, r.as400_result_message from parent_output o LEFT OUTER JOIN child_result r  ON  r.transaction_id=o.transaction_id  where  o.r_creation_date > Date('04/12/2015')

Query all versions of documents
select agency_number, r_creator_name, count(*) from custom_letters(all)  where r_creation_date > Date(yesterday) group by agency_number, r_creator_name

Update BPS Listener active instance
update dmc_bps_listener objects set preferred_instance = active_instance, set run_in_all_instances=1 where listener_act_id='4c020af584ea4ace'

ALL the Jars and version in Documentum 
Select jarfile.r_object_id, jarfile.object_name, jarfile.jar_type,  jarfile.r_creation_date, jarfile.r_modify_date, jarfile.r_version_label, content.content_size from dmc_jar(all) jarfile, dmr_content content where jarfile.i_contents_id=content.r_object_id order by 7 desc

Documentum DATETOSTRING DATE TO STRING (DATE_TO_STRING)
select DATETOSTRING(r_creation_date,'dd/mm/yy') from dm_document  where a_category = 'Default XML Application';

Monitoring Workflows
select wi.r_creation_date, wi.r_object_id, object_name, wi.r_runtime_state   from dm_workflow w, dmi_workitem wi  where w.r_object_id=wi.r_workflow_id and wi.r_runtime_state IN ('0','1') and r_performer_name = 'SAQA_DCTMiTrak' and  wi.r_creation_date < date('09/23/2014 09:05:00') order by wi.r_creation_date asc