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=’demo@test.com’,
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
Please feel free to contact me or just leave a comment if you want me to add any more DQL statements :-) :-).
