| | Count= 133 | | | Count= 133 |
|
 Category : ACL (10)
|
| | Count= 10 | | | Count= 10 |
| | | To retrieve all the ACLs which have groups | SELECT object_name
FROM dm_acl
WHERE r_accessor_name IN (SELECT DISTINCT group_name FROM dm_group)
ENABLE (ROW_BASED)
| ACL |
| | | | SELECT
ds.object_name, ds.acl_name,
dr.r_accessor_permit, df.r_folder_path
FROM
dm_acl da, dm_acl_r dr, dm_sysobject ds, dm_folder_r df
WHERE
(dr.r_accessor_name IN (SELECT DISTINCT group_name FROM dm_group WHERE ANY i_all_users_names= user)
or dr.r_accessor_name = user)
and da.object_name = ds.acl_name
and da.r_object_id = dr.r_object_id
and ds.r_object_id IN (SELECT r_object_id FROM dm_sysobject WHERE folder('/Cabinetname/Foldername',DESCEND))
and any ds.i_folder_id = df.r_object_id
and df.r_folder_path IS NOT NULLSTRING
| ACL |
| | | | select object_name from dm_sysobject where acl_name = '<acl name>' and acl_domain = '<acl domain>'
| ACL |
| | | Replace ACL NAME with the required ACL | SELECT r_object_id, users_names , group_name FROM dm_group WHERE (group_name IN (SELECT r_accessor_name FROM dm_ACL WHERE object_name = '<ACL NAME>')) ORDER BY group_name;
| ACL |
| | | This query is useful to find the inconsistencies of docbase related to ACLs. Documentum uses this query internally while running the consistency checker job | select acr.r_object_id, acr.r_accessor_name
from dm_acl_r acr
where acr.r_accessor_name is not NULL
and acr.r_accessor_name <> ' '
and acr.r_accessor_name not like '/%%' ESCAPE '/'
and not exists (select usr.user_name
from dm_user_s usr
where usr.user_name = acr.r_accessor_name)
and acr.r_accessor_name !='dm_owner'
and acr.r_accessor_name !='dm_group'
and acr.r_accessor_name !='dm_world' | ACL |
| | | This query is useful for finding inconsistencies in the docbase related to ACLs | select acs.r_object_id
from dm_acl_s acs
where not exists (select acr.r_object_id
from dm_acl_r acr
where acr.r_object_id = acs.r_object_id) | ACL |
| | | |
select sys.r_object_id, sys.acl_domain
from dm_sysobject_s sys
where sys.i_is_deleted = False
and not exists (select usr.user_name
from dm_user_s usr where usr.user_name = sys.acl_domain)
and sys.acl_domain != 'dm_world'
and sys.acl_domain != 'dm_group'
and sys.acl_domain != 'dm_owner'
| ACL |
| | | To bulk update objects with relevant ACLs in a given folder | update dm_sysobject object
set acl_name = ‘myACL’ set acl_domain =
‘myDomain’ where folder (’/testCabinet/testFolder’,DESCEND) | ACL |
| | | | SELECT i_all_users_names FROM dm_group
WHERE group_name IN (SELECT
r_accessor_name FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM
dm_folder
WHERE ANY r_folder_path = ‘/folderpath’))
ORDER BY
i_all_users_names
| ACL |
| | | | select object_name from dm_acl where acl_class = 1
| ACL |
| Category : Audit (5)
|
| | Count= 5 | | | Count= 5 |
| | | To find out the authentication failed events from audit trail | select * from dm_audittrail where event_name='dm_logon_failure' | Audit |
| | | To find out the events registered in audit trail | select distinct event,r_object_id,registered_id from dmi_registry
| Audit |
| | | To find out the no. of times an object is accessed | select count(distinct user_name) from dm_audittrail where
event_name='dm_fetch' and audited_obj_id ='0bxxxxxxxxxxxxxx'
| Audit |
| | | | execute make_index with type_name=’dm_audittrail’, attribute=’i_is_archived’
| Audit |
| | | User must have purge audit privileges to execute this DQL | EXECUTE purge_audit WITH delete_mode='PREDICATE',
dql_predicate=
'dm_audittrail where user_name like ''<user name>''' | Audit |
| Category : Custom Object Type (7)
|
| | Count= 7 | | | Count= 7 |
| | | To get the non-inherited attributes of a custom type, we need to query
dmi_dd_attr_info table which consists of all attributes information for all
object types. We need to exclude the attributes of the parent object type of
custom object type
| SELECT attr_name FROM dmi_dd_attr_info WHERE type_name = 'my_custom_type'
AND attr_name NOT IN (SELECT attr_name FROM dmi_dd_attr_info WHERE type_name =
'dm_document') | Custom Object Type |
| | | To find out repeating and single attributes for a given object type | 1. SELECT attr_name, attr_repeating
FROM dm_type
WHERE
name = 'YOUR_TYPE_NAME'
2. SELECT r.attr_name, r.attr_repeating
FROM dmi_dd_attr_info d,
dm_type_s s, dm_type_r r
WHERE d.type_name = s.name
AND
d.attr_name = r.attr_name
AND s.r_object_id = r.r_object_id
AND
s.name = 'your type name'
ORDER BY r.attr_name | Custom Object Type |
| | | Finding all subtypes under given object type | 1. select r_type_name from dmi_type_info where ANY r_supertype IN
('$TYPE')
2. select name from dm_type where super_name = '<type name>'
| Custom Object Type |
| | | | select r_object_id, attr_name, default_value from dmi_dd_attr_info where type_name = '<type_name>' and attr_name = '<attr_name>' and business_policy_id = '0000000000000000' and nls_key = ''
| Custom Object Type |
| | | | select distinct attr_name, label_text from dmi_dd_attr_info where
attr_name in
(select attr_name from dm_type_s a, dm_type_r b
where a.r_object_id = b.r_object_id
and a.name ='<type_name>' and (i_position*-1)-1 >= start_pos)
and type_name='<type_name>' | Custom Object Type |
| | | | ALTER TYPE "custom_type" MODIFY ("doc_language" (SET default='English')) | Custom Object Type |
| | | | select attr_name, type_name from dmi_dd_attr_info where is_searchable = 1
order by type_name
| Custom Object Type |
| Category : DAB (1)
|
| | Count= 1 | | | Count= 1 |
| | | | select application_object_id from dm_application where object_name =
'<docapp name>'
| DAB |
| Category : Data Dictionary (2)
|
| | Count= 2 | | | Count= 2 |
| | | | select valid_values from dm_value_list where r_object_id = (select default_id from dm_cond_id_expr where r_object_id in (select cond_value_assist from dmi_dd_attr_info where type_name='<objtype> and attr_name='<attributename>'))
| Data Dictionary |
| | | | select query_attribute, query_string from dm_value_query where r_object_id = (select default_id from dm_cond_id_expr where r_object_id in
(select cond_value_assist from dmi_dd_attr_info where type_name='<objtype>' and attr_name='<attribute>')) | Data Dictionary |
| Category : Folders (10)
|
| | Count= 10 | | | Count= 10 |
| | | It works only for folders. | EXECUTE fix_link_cnt for '<object_id>' | Folders |
| | | | select distinct s.object_name, fr.r_folder_path from dm_sysobject (all)
s,dm_sysobject_r sr,dm_folder_r fr where sr.i_position = -1 and
sr.r_object_id = s.r_object_id and fr.r_object_id = sr.i_folder_id and
fr.i_position = -1 and fr.r_folder_path like '/xyz/%'
order by fr.r_folder_path,s.object_name | Folders |
| | | The query will list out all the empty folders provided cabinet ids given in
the 'IN' clause.
| SELECT f1.r_object_id as objectid, f1.object_name, f1.r_object_id,
f1.r_folder_path
FROM dm_folder f1
WHERE ANY i_ancestor_id IN
('cab1id','cab2id','cab3id'...)
AND NOT EXISTS (SELECT f2.object_name FROM
dm_sysobject(all) f2 WHERE ANY f2.i_folder_id = f1.r_object_id)
AND ANY
f1.r_folder_path IS NOT NULLSTRING
ORDER BY 1,2 | Folders |
| | | | SELECT object_name from dm_folder where r_object_id IN
(select
i_ancestor_id from dm_folder where r_object_id = 'objectid of the lowest
folder') | Folders |
| | | | SELECT object_name FROM dm_folder WHERE r_object_id IN
(SELECT
i_folder_id FROM dm_sysobject WHERE FOLDER('/Cabinetname/Foldername,DESCEND) AND
r_object_type = 'dm_folder') | Folders |
| | | | select r_object_id,object_name from dm_folder where object_name like '%
'
| Folders |
| | | | SELECT fr.r_folder_path, count(*) AS NumberOf_dmDocuments
FROM
dm_sysobject s, dm_sysobject_r sr, dm_folder_r fr, dm_document dm
WHERE
s.i_is_deleted = 0
AND sr.i_position = -1
AND fr.i_position = -1
AND
sr.r_object_id = s.r_object_id
AND fr.r_object_id = sr.i_folder_id
AND
dm.r_object_id = s.r_object_id
AND FOLDER('/CabinetA', descend)
GROUP BY
fr.r_folder_path
Try this for all versions.
SELECT fr.r_folder_path,
count(*) AS NumberOf_dmDocuments
FROM dm_sysobject(ALL) s, dm_sysobject_r
sr, dm_folder_r fr, dm_document(ALL) dm
WHERE s.i_is_deleted = 0
AND
sr.i_position = -1
AND fr.i_position = -1
AND sr.r_object_id =
s.r_object_id
AND fr.r_object_id = sr.i_folder_id
AND dm.r_object_id =
s.r_object_id
AND FOLDER('/CabinetA', descend)
GROUP BY
fr.r_folder_path | Folders |
| | | | select u.user_name,c.object_name,fld.object_name
from dm_cabinet
c,dm_user u,dm_folder fld where c.r_folder_path = u.default_folder and
u.r_is_group=False and u.user_state=0 and fld.i_ancestor_id=c.r_object_id order
by u.user_name enable (row_based)
| Folders |
| | | | select sum (r_full_content_size/1024.0) as totalKB from dm_sysobject where
folder ('/My Cabinet', DESCEND)
| Folders |
| | | | select folder_security from dm_docbase_config
| Folders |
| Category : Full Text Indexing (6)
|
| | Count= 6 | | | Count= 6 |
| | | | EXECUTE mark_for_retry WITH NAME = 'index_name' | Full Text Indexing |
| | | | select task_state, count(*) as cnt_of_queue_item
from dmi_queue_item where name = 'dm_fulltext_index_user'
group by name, task_state;
| Full Text Indexing |
| | | | select registered_id, event from dmi_registry where user_name =
'dm_fulltext_index_user' | Full Text Indexing |
| | | |
SELECT "r_update_count" FROM "dm_fulltext_index"
WHERE
"r_object_id"=’fulltext_index_ojb_id’
SELECT "r_object_id","object_name",
"owner_name","a_content_type" FROM
"dm_sysobject" (ALL)
WHERE "r_object_id" IN
(SELECT "parent_id" FROM
dmr_content a, dmr_content_r b
WHERE a.r_object_id=b.r_object_id AND
b_fulltext_index=’fulltext_
index_obj_id’ AND
b_update_count=’update_count_from_step_1’)
| Full Text Indexing |
| | | | select r_object_id, name, sent_by, task_state, item_name from
dmi_queue_item q, dm_sysobject s where name = 'dm_fulltext_index_user' and
task_state = 'warning' and q.item_name = s.object_name and s.a_full_text =
true;
| Full Text Indexing |
| | | | select object_name, force_inactive, index_name, r_is_active from
dm_ftindex_agent_config order by 1
| Full Text Indexing |
| Category : General (22)
|
| | Count= 22 | | | Count= 22 |
| | | To find out the location of all objects under particular object type | SELECT DISTINCT s.object_name, fr.r_folder_path
FROM dm_sysobject (all)
s, dm_sysobject_r sr, dm_folder_r fr
WHERE sr.i_position = -1
AND
sr.r_object_id = s.r_object_id
AND fr.r_object_id = sr.i_folder_id
AND
fr.i_position = -1
AND s.r_object_type = 'your type'
ORDER BY
fr.r_folder_path, s.object_name | General |
| | | | | General |
| | | Retrieves server information and heap size information | | General |
| | | | EXECUTE get_path FOR '<object_id>' | General |
| | | This query is for Oracle. We can run this query in SQLPlus | select index_name, table_name, column_name from user_ind_columns where table_name like 'DM_SYSOBJECT_%';
| General |
| | | This query will retrieve current time on content server machine | select DATE(NOW) as systime from dm_server_config
| General |
| | | | select a.object_name, b.content_size/1024 as "TotalSize_KB" from dm_sysobject a, dmr_content b
where
any b.parent_id = a.r_object_id
and FOLDER('/xyz',descend)
| General |
| | | | SELECT docs.r_object_id
, docs.object_name
, fol.r_folder_path
FROM dm_sysobject_s docs
, dm_sysobject_r docr
, dm_folder_r fol
WHERE docs.r_object_id=docr.r_object_id
AND docs.i_cabinet_id='<cabinet id>'
AND fol.r_object_id=docr.i_folder_id
AND fol.r_folder_path <> ' ' | General |
| | | | select sum(content_size) from dmr_content where storage_id in (select r_object_id from dm_store where name = ‘file store name’)
| General |
| | | | select file_system_path from dm_location where object_name = 'log'
| General |
| | | | update dm_docbase_config_s set i_crypto_key = ''
update dm_docbase_config_s set i_ticket_crypto_key = ''
select r_object_id from dmi_vstamp_s where i_application = 'dm_docbase_config_crypto_key_init'
delete from dmi_object_type where r_object_id = '<result from above query>'
delete from dmi_vstamp_s where r_object_id = '<result from above query>'
select r_object_id from dmi_vstamp_s where i_application = 'dm_docbase_config_ticket_crypto_key_init'
delete from dmi_object_type where r_object_id = '<result from above query>'
delete from dmi_vstamp_s where r_object_id = '<result from above query>'
| General |
| | | | select r_docbase_id from dm_docbase_config | General |
| | | To get all the orphaned content objects | select r_object_id, storage_id from dmr_content_s where parent_count = 0 | General |
| | | | EXECUTE make_index WITH
type_name=object_type,
attribute=attribute_name{,attribute=attribute_name,…)
| General |
| | | | EXECUTE drop_index [[FOR] dmi_index_id] [WITH name = index_name] | General |
| | | | SELECT sum(r_full_content_size) as Totalsize FROM dm_document
WHERE
r_creation_date >= Date('11/20/2006','MM/DD/YYYY')
AND r_creation_date
<= Date('11/28/2006','MM/DD/YYYY')
| General |
| | | | select default_acl from dm_server_config
| General |
| | | | select concurrent_sessions from dm_server_config
| General |
| | | | select t.super_name, t.name, ti.default_storage from dm_type t,
dmi_type_info ti where t.info = ti.r_object_id order by t.super_name
| General |
| | | | select title, r_federation_name, r_docbase_id, security_mode,
folder_security, auth_protocol, r_dbms_name, index_store, mac_access_protocol
from dm_docbase_config
| General |
| | | | `select object_name, r_server_version, default_acl, r_host_name,
r_install_owner, r_install_domain, operator_name, agent_launcher,
checkpoint_interval, compound_integrity, turbo_backing_store,
rend_backing_store, web_server_loc, web_server_port, rightsite_image,
secure_connect_mode, r_trusted_mode from dm_server_config
| General |
| | | | select servlet_name, base_uri, supported_protocol from dm_jms_config | General |
| Category : Jobs (3)
|
| | Count= 3 | | | Count= 3 |
| | | | SELECT ALL r_object_id, a_next_invocation
FROM dm_job
WHERE
(
(run_now = 1)
OR ( (is_inactive = 0)
AND
( ( a_next_invocation <= DATE(’now’)
AND
a_next_invocation IS NOT NULLDATE )
OR (
a_next_continuation <= DATE(’now’)
AND
a_next_continuation IS NOT NULLDATE )
)
AND (
(expiration_date > DATE(’now’))
OR (expiration_date IS
NULLDATE))
AND ( (max_iterations = 0)
OR (a_iterations
< max_iterations) )
)
)
AND
(i_is_reference = 0 OR i_is_reference is NULL)
AND (i_is_replica = 0 OR
i_is_replica is NULL)
ORDER BY a_next_invocation, r_object_id | Jobs |
| | | | Update dm_job object set is_inactive = FALSE where object_name = 'JOB
NAME'
| Jobs |
| | | | select * from dm_job_request
| Jobs |
| Category : Lifecycle (1)
|
| | Count= 1 | | | Count= 1 |
| | | | select object_name from dm_policy where r_definition_state = 2
| Lifecycle |
| Category : Log (1)
|
| | Count= 1 | | | Count= 1 |
| | | | select r_object_id, object_name, title, subject, method_name,
pass_standard_arguments, start_date, expiration_date, max_iterations,
run_interval, run_mode, is_inactive, target_server, method_trace_level,
a_continuation_interval, a_current_status, a_iterations, a_last_invocation,
a_next_invocation, method_arguments from dm_job order by title,
object_name
| Log |
| Category : Objects (18)
|
| | Count= 18 | | | Count= 18 |
| | | | select object_name from dm_document where r_lock_owner = '<user
name>'
| Objects |
| | | | select object_name from dm_document group by object_name having
count(object_name)>=2
| Objects |
| | | Query to get all objects which have non-existent ACLs
|
select sys.r_object_id, sys.acl_name
from dm_sysobject_s sys
where sys.i_is_deleted = False
and not exists (select acs.object_name
from dm_acl_s acs where acs.object_name = sys.acl_name
and acs.owner_name = sys.acl_domain) | Objects |
| | | | select sys.r_object_id, sys.i_contents_id
from dm_sysobject_s sys where sys.i_is_deleted = False
and sys.i_contents_id != '0000000000000000'
and not exists (select con.r_object_id
from dmr_content_s con
where con.r_object_id = sys.i_contents_id) | Objects |
| | | | select sys.r_object_id, sys.i_contents_id
from dm_sysobject_s sys where sys.i_is_deleted = False
and sys.i_contents_id != '0000000000000000'
and not exists (select con.r_object_id
from dmr_content_s con
where con.r_object_id = sys.i_contents_id)
| Objects |
| | | |
select sys.r_object_id, sys.i_cabinet_id
from dm_sysobject_s sys
where sys.i_is_deleted = False
and not exists (select cab.r_object_id
from dm_cabinet_s cab
where cab.r_object_id = sys.i_cabinet_id)
| Objects |
| | | | select sys.r_object_id, sys.i_chronicle_id
from dm_sysobject_s sys
where sys.i_chronicle_id <> '0000000000000000'
and not exists (select *
from dm_sysobject_s sys
where sys.r_object_id = sys.i_chronicle_id)
| Objects |
| | | This query returns the count for the user who logged in. | select FOR READ count(r_object_id) from dm_document
| Objects |
| | | | select doc.r_object_id,doc.object_name,fld.r_folder_path
from
dm_document doc, dm_folder fld
where doc.i_folder_id = fld.r_object_id
and
fld.r_folder_path is not nullstring
group by
doc.r_object_id,doc.object_name,fld.r_folder_path
having
count(doc.i_folder_id) = 1
ENABLE (ROW_BASED)
| Objects |
| | | | select d.object_name, fr.r_folder_path,d.r_lock_owner
from dm_document
d,dm_sysobject_r sr,dm_folder_r fr
where d.i_is_deleted = 0 and
sr.i_folder_id = fr.r_object_id and d.r_object_id = sr.r_object_id and
sr.i_position = -1 and fr.i_position = -1 and folder (’/myCABINET’,descend) and
r_lock_owner <> ”
| Objects |
| | | | EXECUTE get_file_url FOR 09xxxxxxxxxxxxxx WITH format='<format
name>'
| Objects |
| | | | update custom_type (ALL) objects move to '/<cabinet>/<folder>'
where object_name = 'document name' and folder('/<original folder>')
| Objects |
| | | | select * from dm_document (ALL) where r_object_id in
(select parent_id from dmr_content ct,dm_filestore fs
where fs.name='filestore_01' and
fs.r_object_id=ct.storage_id)
enable (row_based)
| Objects |
| | | |
select r_object_id, parent_id, max(full_content_size) from
dmr_content
where any parent_id in (select r_object_id from dm_document(all)
where folder('/CabinetName/YourFolder', descend)) group by r_object_id,
parent_id, full_content_size
order by full_content_size desc
enable(return_top 5);
=======================================
=======================================
select r_object_id,object_name,r_content_size
from dm_document where
Folder('/Templates',DESCEND)
order by r_content_size desc
enable
(return_top 5)
| Objects |
| | | | select sys.r_object_id, sys.owner_name
from dm_sysobject_s sys
where sys.i_is_deleted = False
and not exists (select usr.user_name
from dm_user_s usr
where usr.user_name = sys.owner_name)
| Objects |
| | | | select s.name as storename, sum(c.content_size * 1.00) as sum_content,
avg(c.content_size * 1.00) as avg_content, max(c.content_size) as max_content
from dmr_content c, dm_store s where c.storage_id = s.r_object_id and
s.store_type in (1,2,3,5) And ANY parent_id IS NOT NULL group by s.name order by
name
| Objects |
| | | | select full_format,sum(content_size * 1.00) as total, avg(content_size *
1.00) as avarage, max(content_size) as maximum from dmr_content group by
full_format order by full_format
| Objects |
| | | | select full_format ,sum(content_size * 1.00) as total, avg(content_size *
1.00) as avarage, max(content_size) as maximum from dmr_content where rendition
!= 0 group by full_format order by full_format
| Objects |
| Category : Relations (2)
|
| | Count= 2 | | | Count= 2 |
| | | |
create dm_relation object set parent_id = '09xxxxxxxxxxxxxx',
set child_id = '09xxxxxxxxxxxxxx',
set relation_name = '<user defined relation name>',
set description = '<description of dependency>'
| Relations |
| | | |
create dm_relation_type object set child_parent_label = '<some
text>',
set parent_child_label = '<some text>',
set description = '<some text>'
set parent_type = 'dm_document'
set child_type = 'dm_document'
set direction_kind = 0
set integrity_kind = 0
set relation_name = '<Name of relation>',
set security_type = 'SYSTEM'
| Relations |
| Category : Renditions (4)
|
| | Count= 4 | | | Count= 4 |
| | | | select * from dm_document where exists(select * from dmr_content where any parent_id=dm_document.r_object_id and full_format='pdf')
| Renditions |
| | | | SELECT object_name,r_version_label,a_content_type,owner_name,
r_creation_date,r_modifier,r_modify_date, r_object_id
FROM dm_document
WHERE r_object_id not in
(select parent_id from dmr_content where rendition <> 0) | Renditions |
| | | | SELECT * FROM dmr_content WHERE (full_format = 'pdf') AND (rendition >
0) AND (ANY parent_id IN (SELECT r_object_id FROM dm_document WHERE object_name
like 'xxx%'));
| Renditions |
| | | we'll get one row per rendition, and attribute has following values,
0, for original content
1, for a rendition generated by the server
2,
for a rendition generated by the client
3, meaning keep the rendition when
the content
with which it is associated is updated or removed from
the
document or repository
|
select r_object_id,rendition,full_format from dmr_content where any
parent_id =<your_doc_id>'
| Renditions |
| Category : Users&Groups (17)
|
| | Count= 17 | | | Count= 17 |
| | | | EXECUTE roles_for_user
WITH user_name=dmadmin, domain="my_app" | Users&Groups |
| | | | select count(*) from dm_group where group_name = ‘<GROUP_NAME>’ and any i_all_users_names = ‘<USER_NAME>’
| Users&Groups |
| | | | select a.user_name, b.object_name, b.r_creation_date, from dm_user a, dm_folder b
where a.default_folder = b.r_folder_path
and a.r_is_group = 0
enable(ROW_BASED) | Users&Groups |
| | | | select a.r_object_id, a.user_name, b.group_name
from dm_user a, dm_group b
where b.users_names = a.user_name
and a.user_state <> 0 enable(row_based) | Users&Groups |
| | | | select i_all_users_names from dm_group
where group_name = 'group
name'
| Users&Groups |
| | | | select * from dm_user where
datediff(day,last_login_utc_time,date(today))>360
| Users&Groups |
| | | |
execute list_sessions;
execute show_sessions;
| Users&Groups |
| | | | update dm_user objects
set client_capability = 2
where
client_capability = 1 and user_privileges != 16
| Users&Groups |
| | | | select user_name from dm_user where user_state = 1
| Users&Groups |
| | | | select group_name from dm_group where group_class = 'role'
| Users&Groups |
| | | | select
r_object_id,group_name,count(users_names),count(groups_names)
from
dm_group
group by r_object_id,group_name
having count(users_names) >= 0
and count(groups_names) >= 0 enable (row_based)
| Users&Groups |
| | | | select last_login_utc_time from dm_user where user_name='THE USER YOU ARE
LOOKINGFOR'
| Users&Groups |
| | | |
select a.user_name, a.user_group_name
from dm_user_s a
where a.user_group_name is not NULL
and a.user_group_name <> ' '
and not exists
(select b.group_name
from dm_group_s b
where b.group_name = a.user_group_name)
| Users&Groups |
| | | | select a.r_object_id, a.groups_names
from dm_group_r a
where
a.groups_names is not NULL
and a.groups_names <> ' '
and not exists
(select b.group_name
from dm_group_s b
where b.group_name = a.groups_names)
| Users&Groups |
| | | Query to check for disconnected groups. i.e., To find the groups in which
any of the groups above/below the current in hierarchy tree are missing
| select a1.group_name, a2.i_supergroups_names
from dm_group_s a1,dm_group_r a2
where a1.r_object_id = a2.r_object_id
and a2.i_supergroups_names is not NULL
and a2.i_supergroups_names <> ' '
and a1.group_name <> a2.i_supergroups_names
and a2.i_supergroups_names not in
(select b2.i_supergroups_names
from dm_group_r b1, dm_group_r b2
where b1.groups_names = a1.group_name
and b1.r_object_id = b2.r_object_id)
For disconnected Sub Groups
select a2.r_object_id, a2.groups_names
from dm_group_s a1, dm_group_r a2
where a1.r_object_id = a2.r_object_id
and a2.groups_names is not NULL
and a2.groups_names <> ' '
and not exists
(select b1.group_name
from dm_group_s b1, dm_group_r b2
where b1.r_object_id = b2.r_object_id
and b1.group_name = a2.groups_names
and b2.i_supergroups_names = a1.group_name) | Users&Groups |
| | | | select gs.group_name
from dm_group_s gs, dm_group_r gr
where gs.r_object_id = gr.r_object_id
and gs.group_name = gr.groups_names
| Users&Groups |
| | | |
create “dm_user” object set client_capability=2, set
default_folder=’<foldername>’, set
home_docbase=’<docbasename>’, set user_address=’a@abc.com’, set
user_os_domain=’<domain>’, set user_name=’<username>’, set
user_os_name=’<username>’, set user_privileges=0;
| Users&Groups |
| Category : Versions (3)
|
| | Count= 3 | | | Count= 3 |
| | | | select i_chronicle_id from dm_document where r_object_id = '<OBJECT_ID>' | Versions |
| | | | select count(r_object_id) from dm_document (ALL) where
i_chronicle_id in
(select i_chronicle_id from dm_document where r_object_id =
'<your_id>') | Versions |
| | | | DELETE dm_document(all)
WHERE FOLDER('/Cabinetname/foldername',descend)
AND r_immutable_flag = 1
| Versions |
| Category : Virtual Documents (5)
|
| | Count= 5 | | | Count= 5 |
| | | | SELECT object_name, r_object_id as objectid, PARENT, DEPTH FROM
dm_sysobject
IN DOCUMENT ID('<obj id>') DESCEND USING ASSEMBLIES
OR
SELECT object_name, r_object_id as objectid, PARENT, DEPTH FROM
dm_sysobject
IN DOCUMENT ID('<obj id>') DESCEND | Virtual Documents |
| | | | select object_name from dm_document where r_is_virtual_doc = 1
| Virtual Documents |
| | | | select r_object_id from dm_sysobject in document id ('<object id>')
descend with any r_version_label = 'CURRENT'
| Virtual Documents |
| | | | SELECT object_name,r_object_id FROM dm_sysobject
WHERE r_object_id
IN
(SELECT parent_id FROM dmr_containment
WHERE component_id =
(SELECT i_chronicle_id FROM dm_sysobject WHERE r_object_id =
‘<child-object-id>’)) | Virtual Documents |
| | | | select r_object_id from dm_sysobject in document id('<object
id>')
| Virtual Documents |
| Category : Web publisher (2)
|
| | Count= 2 | | | Count= 2 |
| | | | SELECT object_name FROM dm_document
WHERE folder('/WebPublisher
Configuration/Content Templates',descend) | Web publisher |
| | | | SELECT object_name, r_version_label FROM dm_sysobject where any
r_version_label in (’WIP’) and r_object_id not in (select r_object_id from
dm_sysobject where any r_version_label in (’Staging’, ‘Approved’, ‘Expired’)
)
| Web publisher |
| Category : Workflows (12)
|
| | Count= 12 | | | Count= 12 |
| | | | select * from dmi_queue_item where name='<YOURUSERNAME>' | Workflows |
| | | | delete dmi_queue_item objects where delete_flag=0
| Workflows |
| | | | select r_component_id, r_component_name from dmi_wf_attachment where
r_workflow_id = ‘<workflow ID>’
| Workflows |
| | | | select task_name, task_state, actual_start_date, dequeued_date from
dmi_queue_item where router_id = ‘workflowId’
| Workflows |
| | | | 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))
| Workflows |
| | | | select r_act_def_id from dm_process where r_object_id = <>
| Workflows |
| | | | select r_object_id from dmi_workitem where r_workflow_id = <> and
r_act_seqno = <>
| Workflows |
| | | | select r_object_id from dmi_workitem where r_runtime_state in (3, 4, 5, 6)
and r_auto_method_id <> '0000000000000000'
| Workflows |
| | | | select actual_start_date, dequeued_date from dmi_queue_item
| Workflows |
| | | We can also get the information using DQL | EXECUTE get_inbox [WITH name='user_name'][,category=value]
[,batch=value]{,order_by='attr_name [asc|desc]'}] | Workflows |
| | | | select * from dm_document where r_object_id in
(select r_component_id from dmi_package where r_workflow_id = '<workflow_id>') | Workflows |
| | | | EXECUTE get_inbox WITH name='{user}';
| Workflows |
| Category : xCP (2)
|
| | Count= 2 | | | Count= 2 |
| | | | select r_object_id, object_name as adaptor, title as description, param_value as dql
from dmc_xfm_adaptor_config where impl_class like '%DataSourceAdaptorService' | xCP |
| | | BAM's Host and Port Information | select host_name,host_port from bam_connect_props. | xCP |