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.name, 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

UNREGISTER TABLE testable

· 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