Sample DQL

To get all Users in a group

select all users_names from dm_group where group_name='$group name$';

-------------------------------------------------------------------------------

To get r_object_id of document in a workflow

select r_component_id from dmi_package where r_workflow_id in(select r_object_id from dm_workflow where process_name ='$process template name$');

here

dm_process= belongs to your workflow template definition.

dm_workflow= runtime instance of your process

dmi_package= package added to the workflow

---------------------------------------------------------

To get replicated documents in a repository.

select r_object_id from dm_document where i_is_replica='1';

To get source document id for repliacated documents.

select a_special_app from dm_document where i_is_replica='1' and r_object_id='$document object id$';

--------------------------------------

To get all folder paths for a document

select all r_folder_path from dm_folder where r_object_id in(select i_folder_id from dm_document where

r_object_id='$document object id$');

--------------------------------------------------------

To halt all workflow instances for any process

update dm_workflow object set r_runtime_state=3 where process_id='4b001a6e800904e1' and r_runtime_state=1;

Index server info

select * from dm_ftengine_config;

select * from  dm_ftindex_agent_config;

--------------------------------------------------------

To get all registered audit events

select * from dmi_registry;

select * from dm_audittrail;

1. dmi_registry: Object type that keeps infomation about objects/object types and events for either

user that gets notification or event written to audit_trail (is_audittrail attribute)

2. dm_audittrail: Object type where audit trail is stored

3. dmi_queue_item: Object type where notifications are stored. Inbox is basically showing entries in dmi_queue_item.

--------------------------------------------------------

Get documents with pdf renditions

select * from dm_document where exists(select * from dmr_content where any parent_id=dm_document.r_object_id and full_format='pdf')​

--------------------------------------------------------

To find out who has modified the Content\ PDF Rendition for a document

Select page_modifier from dmr_content where any parent_id in(select r_object_id from dm_document where exists (select * from dmr_content where any parent_id=dm_document.r_object_id and full_format='pdf')​);

--------------------------------------------------------

To get all logged in users at particular point

select user_name, count(*) from dm_audittrail where time_stamp >= date('27/07/2013','dd/mm/yyyy') and event_name='dm_connect' group by user_name;

--------------------------------------------------------

To create method using DQL script.

create dm_method object set object_name='Method Name', set run_as_server=1, set use_method_server=1, set method_type='java/docbasic', set a_special_app='Workflow', set method_verb='java method classpath';

--------------------------------------------------------

To create job using DQL script.

create dm_job object set object_name='Job Name', set pass_standard_arguments=1, set method_name='Method Name', set run_interval=1 , set run_mode=6, set expiration_date=date('01/01/2023 00:00:00','MM/DD/YYYY hh:mm:ss');

To create user using DQL script.

create dm_user object set user_name = 'n4', set user_os_name ='n4', set user_address = 'n4@springer.com', set user_source ='inline password', set user_password ='abc123', set default_folder = '/n4';

--------------------------------------------------------

Add users to a group.

alter group group_test add 'n1','n2','n3';

--------------------------------------------------------

To check how many applications deployed as taskspace application.

SELECT r_object_id, object_name, title, a_application_type FROM dmc_taskspace_app where a_application_type = 'TaskSpace'

--------------------------------------------------------

To get all the alias sets defined in application.

select * from dm_alias_set;

To get document content size in bytes

Select r_content_size from dm_document where r_object_id=’’;

--------------------------------------------------------

To get index server associated with content server.

select * from dm_ftengine_config;

--------------------------------------------------------

To get list of events registered for fulltext index user.

select event from dmi_registry where user_name=’dm_fulltext_index_user’ 

--------------------------------------------------------

DQL Search query(FTDQL query syntax)

As our simple dql search is case sensitive, fulltext index search is case sensitive search against object_name, title and subject attributes of dm_sysobject and its subtypes.

The DQL search query searches for the exact work mentioned in search criteria and not a fragment of a word.

Also it removes special charecters with spaces e.g if you enter vk@gmail.com as a search criteria, it will replace it as “vk gmail com” so the string containing sequence of those 3 words will be returned as result.

select * FROM dm_sysobject SEARCH DOCUMENT CONTAINS '$my_parameter$';

SELECT * FROM dm_document SEARCH DOCUMENT CONTAINS '0' ENABLE (FT_DQL, FETCH_ALL_RESULTS 501, RETURN_TOP 100);

--------------------------------------------------------

To check fulltext index status

select sent_by, date_sent, item_name, content_type, task_state, message from dmi_queue_item where item_id = ” and name=’ dm_fulltext_index_user’;

--------------------------------------------------------

dmi_queue_item events:

with respect to indexing

with respect to workflows

--------------------------------------------------------

Documentum mail notification:

Check for attributes- if you are using windows env. smtp_server should be configured for this

SELECT object_name, smtp_server, mail_method FROM dm_server_config;

To get the method name getting used for mail notification:

select method_verb from dm_method where object_name='mail';

----------------------------------------------------------------

The Following DQL will retrieve the object_ids that correspond to the content objects that have been deleted

select r_object_id from dmr_content where any parent_id is null 

----------------------------------------------------------------------------------------------

DQL to see sessions

execute show_sessions;

Virtual Documents:

To find out virtual documents in repository.

Select * from dm_sysobject where r_is_virtual_doc=1;

 ------------------------------------------------

To find frozen/immutable objects in repository.

Select * from  dm_sysobject where r_immutable_flag=1;

 ---------------------------------------------------------

DQL to get current date and time

select DATE(now) as systime from dm_server_config;

Documents having duplicate names.

SELECT object_name, count(*) FROM dm_document GROUP BY object_name HAVING count(*) > 1 ORDER BY object_name;

DQL to get all attribute of a type

SELECT attr_name FROM dmi_dd_attr_info WHERE type_name='dm_document';

DQL to get all required attributes of a type.

SELECT attr_name FROM dmi_dd_attr_info WHERE type_name='abc_document' AND is_required <> 0;

To get count of types available/defined in repository.

SELECT count(*) from dmi_dd_type_info;

To get workflow package.

Select * from dmi_package where r_workflow_id=’’$workflow id$;

To get workflow attachments.

Select * from dmi_wf_attachment where r_workflow_id=’$workflow id$’;

Fetch top 10 results in a query.

SELECT object_name FROM dm_document ENABLE (RETURN_TOP 10)

To fetch list of docbrokers available

execute list_targets

To find active workflows and supervisors of a workflow.

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))

-----------------------------------

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 

------------------------------------

To get all checked out documents in repository

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

To get all paused workitems for a particular workflow.

select * from dmi_workitem where r_workflow_id in(select r_object_id from dm_workflow where process_id= (select r_object_id from dm_process where object_name='workflow name')) and r_runtime_state=5;

To insert new value for multivalued attribute:

UPDATE ajwf_workitem OBJECTS append r_version_label= 'test2' where r_object_id ='';

*******************************************************************