Useful DQL Queries

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

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'

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

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

DQL To 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';

DQL to register Table
register table dbo.my_app_holiday (dummy string(1));

DQL to 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';

DQL to create an object
Create dm_sysobject object 
set object_name = 'Test Value'

DQL 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

DQL to ger 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

DQL to check # 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');

DQL To 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;

DQL To 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'

DQL 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);

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

DQL to get Next Value from Sequencer in SQL Server database

execute EXEC_SELECT_SQL with QUERY='SELECT NEXT VALUE FOR NameOfTheSeq as nextidseqval'

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'

Also I am adding some useful URL's here for some additional nice and helpful DQL queries