Very useful DQL

DQL is Document Query Language for Documentum, which is a content management system used to create, manage, deliver, and archive all types of content. Some useful DQLs are :

· Query to list objects having duplicate names

select object_name, count(*) FROM dm_document group by object_name having count(*) > 1 order by object_name

· Query to find checked out documents

select * from dm_document where (r_lock_owner is not nullstring or r_lock_owner <> ‘ ‘)

· Query to find documents without pdf rendition

select * from dm_document doc where not exists(select * from dmr_content where any parent_id=doc.r_object_id and full_format=’pdf’)

· Query to find documents having file size more than 10 MB

select * from dm_document where r_full_content_size > 10000000;

· Query to fetch specific number of documents

select * from dm_document enable (RETURN_TOP 50)

· Query to fetch document owner name and number of document owns

select owner_name,count(*) from dm_document group by owner_name

· Query to find all documents under a cabinet

select r_object_id, object_name from dm_document(all) where folder(’/[cabinet name]’, descend)

· Query to find all documents which are modified from a particular date

select * from dm_document where r_modify_date > DATE(‘06/05/2013 00:00:00’,’MM/DD/YYYY hh:mm:ss’) and r_modify_date < DATE(‘TODAY)

· Query to find documents which are in workflows (active)

select r_object_id, object_name, title, owner_name,r_object_type, r_creation_date, r_modify_date, a_content_type from dm_document where r_object_id in(select r_component_id from dmi_package where r_workflow_id in (select r_object_id from dm_workflow where r_runtime_state = 1))

· Query to find running workflow list

select distinct wi.r_object_id, wf.r_object_id as workflow_id, wf.object_name as workflow_name, wf.supervisor_name as supervisor_name,wi.r_performer_name as performer_name, wf.r_start_date as start_date from dm_workflow wf, dmi_package pkg, dmi_workitem wi where wf.r_runtime_state <2 and wf.r_object_id=pkg.r_workflow_id and wf.r_object_id=wi.r_workflow_id and pkg.r_workflow_id=wi.r_workflow_id and wi.r_runtime_state < 2 and any pkg.r_component_id in (select r_object_id from dm_document(all) where i_latest_flag=TRUE)

· Query to find workflow id of a particular document

select r_workflow_id from dmi_package where any r_component_id =’[r_object_id]’

· Query to find inbox details of an user

EXECUTE get_inbox WITH name=’[user_name]’

· Query to check registered types and the full-text user name

select distinct, t.r_object_id, i.user_name from dm_type t,dmi_registry i where t.r_object_id = i.registered_id and i.user_name like ‘%fulltext%’

· Query to verify that Content Server has loaded the correct full-text plugin

select r_object_id,object_name from dm_ftengine_config

· Query to get all the users created from yesterday

Select user_name,user_os_name,user_address,user_group_name,r_modify_date from dm_user where r_modify_date >DATE(‘YESTERDAY’)

· Query to create user

create “dm_user” object

set client_capability=2,set default_folder=’’,

set home_docbase=’’,set user_address=’[email protected]’,

set user_os_domain=’’,set user_name=’’,

set user_os_name=’’,set user_privileges=0,set user_source=’’

· Query to retrieve group to which user has direct membership

Select group_name from dm_group where any users_names =’[user_name]’

· Query to retrieve all groups to which user has membership

Select group_name from dm_group where any i_all_users_names =’[user_name]’

· Query to get list of users and groups they directly belong to

select distinct ur.user_name,ur.user_os_name, gr.group_name FROM dm_user ur, dm_group gr where any gr.users_names = ur.user_name and r_is_group = 0 order by ur.user_name

· Query to add user in group

alter group [group_name] add ‘user_name’

If wants to add more than one user then

alter group [group_name] add (select user_name from dm_user where user_name in (‘A’,’B’,’C’))

· Query to drop user from a group

alter group [group_name] drop ‘user_name’

· Query to get all the subgroups of a group

Select groups_names from dm_group where group_name=’[Group Name]’

· Query to view details of active jobs

select r_object_id, object_name, method_name,start_date, expiration_date, run_interval, run_mode, a_last_invocation, a_last_completion, a_last_return_code as error_txt, a_next_invocation, a_iterations, a_current_status from dm_job where is_inactive=FALSE order by 2

· Query to determine which jobs are currently running

select object_name, r_object_id from dm_job where a_special_app =’agentexec’

· Query to get details virtual documents

select * from dm_document(all) where r_link_cnt>0

· Query to get directly contained components of a virtual document

select r_object_id,r_object_type from dm_document(deleted) IN DOCUMENT ID (‘[RootId]’)

· Query to get all contained components of a virtual document tree

select r_object_id,r_object_type from dm_document(deleted) IN DOCUMENT ID (‘[RootId]’) DESCEND

· Query to execute SQL query

EXECUTE exec_sql WITH query=’sql_query’

· Query to register a table from the underlying RDBMS with the Docbase

register table dm_dbo.testtable(id String(10),name String(35),address String(40))

· Query to unregister a table from the underlying RDBMS with the Docbase


· Query to get information of DAR installed in the Docbase

select * from dmc_dar

· Query to get information of docbroker

execute list_targets

· Query to retrieve the SQL translation of the last DQL statement

EXECUTE get_last_sql

One thought on “Very useful DQL

  1. It’s clear that as we navigate the complexities of digital transformation, tools like Documentum will be crucial in managing the deluge of data and documents. The exploration of its potential to streamline workflows and ensure compliance across sectors adds a valuable perspective. Looking forward to more such enlightening pieces on similar technologies.

Leave a Reply

Your email address will not be published. Required fields are marked *