Helpful DQL

Helpful DQL:

1. Creating a user
create “dm_user” object
set client_capability=2,
set default_folder=’’,
set home_docbase=’’,
set user_address=’[e-mail]’,
set user_os_domain=’’,
set user_name=’’,
set user_os_name=’’,
set user_privileges=0;

2. DQL to execute stored procedure (works for all supported DBMSes)
execute exec_sql with query=’execute my_sp_test ”123”,”prasad” ‘;

3. Running DQL in batch mode
a. Create a dql file
b. Run the following from command prompt
$idql docbasename -Uusername -Ppassword -Rtest.dql
Example command
C:\Documentum\product\5.3\bin>idql32 docbasename-Uusername -Ppassword -RC:
\test.dql
test.dql would contain something like this

go
go

4. DQL to Get all Files under a Particular Cabinet
select r_object_id, object_name from dm_document(all) where folder(’/Cabinet name’, descend);
The above DQL gives all versions. To get only current versions
select * from dm_document where folder (’/Cabinet name’, descend)

5. DQL to get total number of documents and folders under a cabinet
SELECT count(*) as cnt, ‘Docs’ as category FROM dm_document(all)
WHERE FOLDER (’/Cabinet Name’,DESCEND)
UNION
SELECT count(*) as cnt, ‘Folders’ as category FROM dm_folder
WHERE FOLDER (’/Cabinet Name’,DESCEND)

6. DQL to find whether a document is a part of virtual document
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 = ‘’))

7. Repeating attributes
Repeated attribute queries are always a fun. I am going to present here more repeated attribute queries. But for starters, recognize the importance of ANY keyword
select r_folder_path from dm_folder where object_name=’myFolder’ and any r_folder_path =’/Cabinet Name/test’;

8. DQL to find object type of a document
select r_object_type from dm_document where object_name=’ObjectName’;

9. DQL for index
Following query creates index
EXECUTE make_index WITH type_name=’dmi_workitem’,attribute=’r_workflow_id’
Using the query below, one can find out whether index has been succesfully created or alternatively whether the index exists or not

Select r_object_id,index_type,attribute,attr_count,data_space from dmi_index where index_type in (select r_object_id from dm_type where name=’dmi_workitem’);

10. DQL to see sessions
execute show_sessions

11. Enable FTDQL
SELECT r_object_id, score, text, object_name, r_object_type, r_lock_owner, owner_name, r_link_cnt, r_is_virtual_doc, r_content_size, a_content_type, i_is_reference, r_assembled_from_id, has_frzn_assembly,
a_compound_architecture, i_is_replica, r_policy_id,acl_name, r_creation_date, r_modify_date, subject
FROM custom_document WHERE (custom_attr1 = ’search1′ AND (custom_attr2 = ’search2′ AND
)) AND (a_is_hidden = FALSE) ENABLE (FTDQL)

12. DQL to get current date, time
select DATE(now) as systime from dm_server_config;

13. DQL to list all available templates in Webpublisher
select * from my_document where folder(’/WebPublisher Configuration/Content Templates/myTemplates’,descend) and any r_version_label =’Approved’;

14. DQL to list objects having duplicate names
SELECT object_name, count(*) FROM dm_document
GROUP BY object_name
HAVING count(*) > 1
ORDER BY object_name

15. Clear INBOX
delete dmi_queue_item objects where delete_flag=0

16. DQL to retrieve all required attributes of a particular type
SELECT attr_name FROM dmi_dd_attr_info WHERE type_name=’dm_document’ AND is_required <> 0

17. DQL to list workflow attachments
select r_component_id, r_component_name from dmi_wf_attachment where r_workflow_id = ‘’

18. If your statistics are not up to date, database may choose a very inefficient execution plan. Be sure toupdate statistics often. It is recommended that you use the dm_UpdateStatistics job as it will calculateextended statistics on particular tables and columns which provide additional performance benefits. If the DBA uses their own scripts to calculate the statistics, then these enhancements will not be available.

19.Use the script “dctm_indexes_by_table.sql” to generate a list of all indexes on Documentum tables, ordered by table name. (Available from http://developer.documentum.com.)
Use the script “dctm_indexes_by_index.sql” to generate a list of all indexes on Documentum tables,
ordered by index name. (Available from http://developer.documentum.com.)


20.It is strongly recommended that all indexes on Documentum base tables be created from within Documentum and not at the database level.
There are two reasons for this:
The internal conversion process from DQL to SQL will check for the presence of a dmi_index object for repeating valued attributes and will generate different SQL according to what it finds.
If indexes are created directly through SQL*Plus, then Documentum will not know anything about them and will assume it is unindexed. This may result in a less efficient SQL query.
Secondly, if the indexes are created from within Documentum, and they are inadvertently dropped, the dm_DBWarning job will automatically recreate them at next execution.

The syntax for creating new indexes is as follows:
In DQL:
EXECUTE make_index WITH type_name=object_type,
attribute=attribute_name{,attribute=attribute_name,…)
or
Using APIs:
dmAPIGet(”apply,session,NULL,MAKE_INDEX,TYPE_NAME,S,object_type,ATTRIBUTE,S
,attribute_name(,ATTRIBUTE,S,attribute_name,…}
These indexes will be created in the tablespace identified by the index_store server.ini parameter.
Be sure to create the index on the appropriate type table.
For example, although ‘keywords’ is an attribute of the dm_document type, it is actually inherited from ‘keywords’ from the dm_sysobject type.
Drop the Index if Necessary
If there is no performance improvement, OR the optimizer is not using the new index, drop it using:
EXECUTE drop_index [[FOR] dmi_index_id] [WITH name = index_name]
or
dmAPIGet(”apply,session,dmi_index_id,DROP_INDEX [,NAME,S,index_name]“)

21. Using the iapi utility and the trace API you can generate log information that contains the SQL resulting from a DQL query.
This is useful when it is a DQL query that is performing poorly and you wish to test using a nonsuperuser account. Non-query type APIs are not traced.
Here is an example of tracing a simple DQL query.
trace,c,10,,DM_QUERY.
More on TKPROF here
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm


22. DQL Hints
SELECT object_name FROM dm_document ENABLE (RETURN_TOP 10)

For DB2, performance can be improved using
SELECT object_name FROM dm_document ENABLE (RETURN_TOP 10, OPTIMIZE_TOP 10)

The FETCH_ALL_RESULTS N hint fetches all the results from the database
immediately and closes the cursor. The hint does not affect the execution plan,
but may free up database resources more quickly.
To fetch all the results, set N to 0.
On SQL Server, it is recommended that you use SQL_DEF_RESULT_SETS
instead of the FETCH_ALL_RESULTS hint. SQL_DEF_RESULTS_SETS
provides the same benefits and is the recommended way to access SQL Server
databases.
Passthrough hints are hints that are passed to the RDBMS server. They are not
handled by Content Server.
To include a passthrough hint, you must identify the database for which the
hint is valid. To identify the target database, keywords precede the hints. The

valid keywords are: ORACLE, SQL_SERVER, SYBASE, and DB2. For

example, the following statement includes passthrough hints for SQL Server:

SELECT “r_object_id” FROM “dm_document”

WHERE “object_name” =’test’

ENABLE SQL_SERVER(’ROBUST PLAN’,'FAST 4’,’ROBUST PLAN’)

For portability, you can include passthrough hints for multiple databases in

one statement. The entire list of hints must be enclosed in parentheses. The

syntax is:
(database_hint_list {,database_hint_list})
where database_hint_list is:
db_keyword(’hint’{,’hint})
db_keyword is one of the valid keywords identifying a database. hint is any hint
valid on the specified database.

For example:
SELECT object_name FROM dm_document doc dm_user u
WHERE doc.r_creator_name = u.user_name ENABLE
(ORACLE(’RULE’,’PARALLEL’), SYBASE(’AT ISOLATION READ
UNCOMMITTED’),SQL_SERVER(’LOOP JOIN’,’FAST 1’)
Use FETCH_ALL_RESULTS if you want to reduce the resources used by the
database server by quickly closing cursors. On SQL Server, try
FETCH_ALL_RESULTS if using SQL_DEF_RESULT_SETS did not improve
query performance.

23. If the table is registered, check with the owner of the registered table, or a Documentum superuser, about giving you access to the table.

You will need at least a BROWSE permit on the registered table object in order to access it. If the table has not yet been registered, check with the table’s owner about registering it. Note that you must have READ access to the dm_registered object for this table in order to access it in any way (SELECT, INSERT, UPDATE, DELETE).

Note that if you specified the special dm_dbo document base owner, the first parameter will hold the actual name of the document base owner.

24. DQL to list docbrokers
execute list_targets

25. DQL to list worrkflow information
select task_name, task_state, actual_start_date, dequeued_date from dmi_queue_item where router_id = ‘workflowId’


26. Query to find active workflows, supervisors of the 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))


26. Though this is not DQL, I thought of mentioning here as it most widely used DFC operation

sysObject.queue (”dm_autorender_win31″, _
“rendition”, _
0, _
False, _
dueDate, _
“rendition_req_ps_pdf”)


27. To find ACLs related to dm_sysobject:

select r_object_id as obj_id, object_name from
dm_sysobject (all)
where acl_name =” and
acl_domain = ”
To find ACLs as the default ACL of a user:

select user_name from dm_user where acl_name=”

To find ACLs associated with a type:

select r_object_id, r_type_name from dmi_type_info where acl_domain=” and acl_name=”

When these queries do not return any related objects and you still cannot delete the ACL, use tracing:

In the Message Tester or IAPI, execute the command: apply,c,NULL,SQL_TRACE,LEVEL,I,1

Then, try to delete the ACL that is causing problem.

Turn off tracing by executing:

apply,c,NULL,SQL_TRACE,LEVEL,I,0

Examine the session log for the trace output, located in $DOCUMENTUM/dba/log//

Excerpt of the session log:

[DM_ACL_E_DESTROY_IN_USE]error: “Failed to destroy the ACL ‘Documentum Users’ in domain ‘dbabep’ because it is in use.”

[DM_SESSION_I_SESSION_QUIT]info: “Session 01000dcb80010ccf quit.”

Run the following query from Oracle SQL:

select r_object_id from dm_sysobject_s where acl_domain =’dbabep’ and acl_name =’Documentum Users’ union select r_object_id from dm_user_s where acl_domain = ‘dbabep’ and acl_name =’Documentum Users’ union select r_object_id from dmi_type_info_s where acl_domain =’dbabep’ and acl_name =’Documentum Users’

It will return the r_object_id value = 09000dcb800362c4.

Run the following query from IDQL:
select object_name
from dm_sysobject
where r_object_id=’09000dcb800362c4′
If the above query returned nothing, then this object cannot be accessed by any Documentum WorkSpace method, only via SQL in the underlying Oracle database. The following entries must be deleted at the RDBMS level: dm_sysobject_s and dm_sysobject_r tables; there are no entries in the dmi_object table


28. List of object types and corresponding identifiers. Helpful when reading the code.
00 dmi_audittrail_attrs
03 dm_type
05 dmr_containment
06 dmr_content
08 dm_application
08 dm_job
08 dm_procedure
08 dm_query
08 dm_script
08 dm_smart_list
09 dm_document
0b dm_folder
0c dm_cabinet
0d dm_assembly
10 dm_method
11 dm_user
12 dm_group
19 dm_registered
1f dmi_index
26 dmi_registry
27 dm_format
28 dm_filestore
28 dm_store
2c dm_distributedstore
2e dmi_type_info
2f dm_dump_record
30 dmi_dump_object_record
31 dm_load_record
32 dmi_load_object_record
37 dm_relation
3a dm_location
3b dm_fulltext_index
3c dm_docbase_config
3d dm_server_config
40 dm_blobstore
41 dm_note
45 dm_acl
46 dm_policy
49 dmi_package
4a dmi_workitem
4c dm_activity
4d dm_workflow
53 dm_literal_expr
5e dm_federation
5f dm_audittrail_acl
5f dm_audittrail_group
5f dm_audittrail
66 dm_alias_set
6a dmi_dd_attr_info
0b dm_taxonomy
0b dm_xml_application
6b dm_display_config
20 dmi_sequence

29. Job scheduler Query
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

29. DCTM 5.3SP1 dmclean has a new argument -clean_aborted_wf. If specified, the method removes all aborted workflows in the repository. You can set this using DA

30. To deal with apostrophes in DQL, escape with apsotrophe. For example to find all users with name like O’Hare, use following DQL
select group_name from dm_group where any users_names like ‘% O”Hare%’

31. In order to view completed workflows, you need to enable workflow auditing. Workflow Manager has an option to turn auditing ON within a workflow template to view completed workflows.

From Workflow Manager:
1) Uninstall template
2) File > Template Properties
3) Under Template Audit Trail Setting, select option ‘Always On’ - Audit trail data for each instance will be available and saved at workflow completions.

32. In a workflow, a performer can enter comments while he/she is performing the task. And those comments are carried to the next performer in this workflow. When using WebPublisher workflow report to review the workflow instances, and in workflow history, you are only able to get the truncated comments back from WP’s interface. Especially for those already aborted workflow instances, there is no way that you can get the complete comments back from WP’s GUI. In some cases, those comments are very important to our customers and they need to find a way to get those comments back.

Here is the steps to get those comments back:

==

1) Identify the workflow from the dm_workflow table, get the r_object_id of the workflow:
select r_object_id, object_name from dm_workflow where object_name = ‘your work flow name’

2) Identify the notes that are carried by this workflow:
select r_note_id from dmi_package where r_workflow_id = ‘ the object id of the workflow’

3) Get the content id of each of those note ids returned:
select r_object_id from dmr_content where any parent_id = ‘the note id’

4) Go to DA, Administration->Job Management->Administration, use the “GET_PATH” method to find out the path of the files which stores the comments.

33. Query to get all documents expired in previous 1 month
SELECT s.r_object_id, s.object_name, DATETOSTRING(”r.a_expiration_date”,’mm/dd/yyyy’) as creation_date
FROM dm_sysobject_s s, dm_sysobject_r r
WHERE s.r_object_id = r.r_object_id
AND s.r_object_type = ‘dm_document’
AND DATEDIFF(month,”r.a_expiration_date”,DATE(NOW)) >= 0
AND DATEDIFF(month,”r.a_expiration_date”,DATE(NOW)) <= 1
AND r.r_version_label = ‘Expired’
ORDER BY 3

34. DQL to find all the folders in a Cabinet, where the folders are contentless
SELECT f1.object_name, f1.r_object_id, f1.r_folder_path
FROM dm_folder f1
WHERE FOLDER(’/Cabinetname’,descend)
AND NOT EXISTS (SELECT f2.object_name FROM dm_sysobject f2 WHERE ANY f2.i_folder_id = f1.r_object_id)
ORDER BY object_name

35. DQL for finding all checked out documents in a docbase
select * from dm_document where (r_lock_owner is not nullstring or r_lock_owner <> ” or r_lock_owner <> ‘ ‘)

36.DQL to list the users who has access to particular folder path
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

37. Query to find out what user signed off on what document
SELECT “audited_obj_id” FROM “dm_audittrail” WHERE
“event_name” = ‘dm_signoff’ AND
“user_name” = ‘tom’ AND
substr (”audited_obj_id”, 1, 2) = ‘09′AND
“time_stamp” >= DATE(’01/01/1998′, ‘dd/mm/yy’) AND
“time_stamp” <= DATE(TODAY)

38. Is there any way or method by which we can can get to know about the period , whenever a metadata(attribute) is added to object or any of its sub object types.

You would need to register the save event to be audited on your object type. Whenever a property is changed it will have a save event to write it to the content server.

39. listing all contents that are in WIP state only. If a content is also in Staging or Active or Expired state, it will not show up in the result:
DQL> 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’) )

39. Query to get all expired documents in previous month

SELECT s.r_object_id, s.object_name, DATETOSTRING(”r.a_expiration_date”,’mm/dd/yyyy’) as creation_date FROM dm_sysobject_s s, dm_sysobject_r r WHERE s.r_object_id = r.r_object_id AND s.r_object_type = ‘dm_document’ AND DATEDIFF(month,”r.a_expiration_date”,DATE(NOW)) >= 0
AND DATEDIFF(month,”r.a_expiration_date”,DATE(NOW)) <= 1 AND r.r_version_label = ‘Expired’
ORDER BY 3

40. Query to find the file system path location of a document
select doc.r_object_id, doc.object_name, MFILE_URL(”,-1,”) as mypath,doc.i_folder_id from dm_document doc where

 

＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

 

1. List attributes of a Custom Type

SELECT distinct attr_name, type_name FROM dmi_dd_attr_info WHERE attr_name not in (SELECT attr_name FROM dmi_dd_attr_info WHERE type_name=’dm_document’) and type_name = ‘yourcustom_type’

 

2. Select form templates

select * from dm_xfm_form

select r_object_id, object_name,template_type,a_content_type from dm_xfm_form where object_name like ‘H%’

select object_name from dm_xfm_instance where r_object_id in(select r_object_id from dm_xfm_instance_s where is_default=’1′)

 

3. Select taskspace application names

select * from dmc_taskspace_app

 

4. Select workqueues

select wq_name from dmc_workqueue where wq_name like ‘hr%’

5. To delete all versions of a dm_process

dql> select r_object_id, r_object_id, object_name, r_version_label from dm_process (all) where object_name = ‘<the process name>’

API>uninstall,c,4bde75d1800118d4
API>destroy,c,4bde75d1800118d4

 

6.  Create a user

create dm_user object
set user_name = ‘employee_rv1′
set user_login_name = ‘employee_rv1′
set user_address = ‘[e-mail]’
set user_source = ‘inline password’
set user_password = ‘demo’
set default_folder = ‘/Temp’
set user_privileges = 0
set client_capability = 2

 

7. Create a Role

Create dm_group OBJECT
set group_name = ‘hr_employee_role’
set group_class = ‘role’
set group_address=’[e-mail]’

8. Add User to a Role
Alter group ‘hr_employee_role’ add ‘manager_rv1′,’employee_rv1′

 

9. Find all roles assigned to a user

select * from dm_group where any i_all_users_names=’<user name>’ and group_class=’role’

10. Find all users assigned a specific role

select i_all_users_names from dm_group where group_class=’role’ and group_name = ‘hr_employee_role’

 

11. Find the folder path of an object

SELECT s.object_name, fr.r_folder_path
FROM dm_sysobject s,dm_sysobject_r sr, dm_folder_r fr
WHERE s.i_is_deleted = 0 AND 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 FOLDER(‘/Cabinetname’, DESCEND)
ORDER BY fr.r_folder_path,s.object_name

 

12. Create a dm_folder OBJECT

Create dm_folder OBJECT set object_name = ‘AcceptableDocumentTypes’
link ‘/Picklists’

 

13. Create a Custom Type

CREATE TYPE “acceptabledoctype” (“doccontentType” string(32)) WITH SUPERTYPE “dm_document”

 

14. Alter a Custom Type

ALTER TYPE nhspatient MODIFY nhs_dob (SET format_pattern = NULL, SET format_pattern_tag = NULL, SET label_text = ‘Date Of Birth’) PUBLISH;

 

15. How to run scripts in idql

On command prompt> idql32 <repository name>-uAdministrator -pdemo -rc:\xx.txt

 

16. List all configured audit events
select r.registered_id, r.event,r.user_namefrom dmi_registry r
where r.is_audittrail =1

17. Across-the-board  audit configuration
select ‘ALL’,r.event,r.user_namefrom dmi_registry rwhere r.is_audittrail = 1
and r.registered_id = ’0000000000000000′

18. Audit configuration for types
select t.name,r.event,r.user_namefrom dmi_registry r, dm_type t
where r.is_audittrail = 1and t.r_object_id = r.registered_id

19. Audit configuration for Sysobjects
select t.object_name,r.event,r.user_namefrom dmi_registry r, dm_sysobject t
where r.is_audittrail = 1and t.r_object_id = r.registered_id

20. Audit configuration for users
select t.user_name,r.event,r.user_namefrom dmi_registry r, dm_user t
where r.is_audittrail = 1and t.r_object_id = r.registered_id

 

21. Cleanup

UPDATE dm_document OBJECTS
SET r_lock_owner =”
SET r_lock_machine =”,
SET r_lock_date = date(‘nulldate’)
WHERE CABINET (‘/Patient’, descend)
AND r_lock_owner IS NOT NULLSTRING;
go;
DELETE dm_document(ALL) OBJECTS WHERE FOLDER(‘/Patient’,DESCEND)
go;

 

22. Kill a Job
DQL>select r_object_id from dm_job where object_name = ‘YOUR_JOB_NAME’
API>unlock,c,JOB_OBJECT_ID
DQL>update dm_job object set is_inactive = 1 where r_object_id = ‘JOB_OBJECT_ID’

You can change it from the API.These are the steps to update the Next invocation :
Get the job id using DQL:select r_object_id , object_name from dm_job where object_name= ‘The_job_name’
Connect to Iapi
API>dump,c,r_object_id of your Job
API>fetch,c,r_object_id of your Job
API> set,c,l,a_next_invocation
SET> 11/25/2007 04:00:00
API>dump,c,l

 

23. Run a Job Manually

execute do_method WITH method = ‘dm_nightlyextract’,
arguments = ‘-docbase_name edmrep -user_name Administrator -password demo -domain dce2008′

24. delete dm_cabinet object

delete dm_cabinet object where object_name= ‘Patient’

25. drop type
drop type patient object_name= ‘Patient’

 

 

转自：http://vsrr2020.blogspot.com/2009/12/dql-queries-help.html