DQL to find queued items

selectg.r_object_id,g.object_name,q.date_sent,g.r_object_type,g.r_content_size,q.sent_by from gar_global g, dm_queue  q where q.name='dm_mediaserver' and g.r_object_id=q.item_id order by q.date_sent

================================================
API to dequeue items

dequeue,c,<stamp>

================================================

Status of a workflow can also be monitored using certain DQL statements.

The following are a few DQL queries that can be used for the monitoring purposes:

select  wf.object_name as workflow_name, wf.r_creator_name as creator, qi.task_name as task_name, qi.task_state as task_state, wi.r_performer_name as performer, wi.r_creation_date as creation_date, wi.r_due_date as due_date, wi.r_priority as priority, wi.r_sign_off_req as sign_off_req from dm_workflow wf, dmi_queue_item qi, dmi_workitem wi where qi.router_id = wi.r_workflow_id and wi.r_workflow_id=wf.r_object_id

This query will query the workflow and its activity with the status, performer, dates, priority, and sign off request.

workflow_name: is the name of the workflow that in currently active

creator: is the user who starts the workflow (usually the user that is provided to run the workflow job

task_name: current task/activity that the workflow is running

performer: user who is assigned to perform the task

task_state: status of the task. (dormant/acquire/finish)

creation_date: start date of the activity

due_date: exist if the activity has a timer required to be finished with in certain time

priority: priority of the activity

sign_off_req: is 1 if the activity is needed to be sign-off before finishing the task

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

select wf.object_name as workflow_name, pkgs.r_package_name as package_name, pkgr.r_component_chron_id as component_id, pkgr.r_component_name as component_name from dm_workflow wf, dmi_package_rp pkgr, dmi_package_sp pkgs where wf.r_object_id = pkgs.r_workflow_id and pkgr.r_object_id=pkgs.r_object_id

This query will query the package that is attached to the workflow and its attached objects.
 
workflow_name: is the name of the workflow that in currently active

package_name: package that is used within the workflow

component_id: an object id of an object that is in the package

component_name: an object name of an object that is in the package

=============================================

Count Objects by Month or Year 

select DATETOSTRING(r_creation_date, 'yy/mm') as period, count(*)
from dm_document group by DATETOSTRING(r_creation_date, 'yy/mm')

============================================

Get Value Assistance for an Attribute

select valid_values as type_value from dm_value_list where r_object_id in
(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='employee' and attr_name='first_name'))
order by valid_values

=======================================================

It is the query that the agent exec process uses to work out what jobs to run next.

SELECT ALL r_object_id, object_name, 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

============================================

 Index Queue Count

select count(*) from dmi_queue_item a, dm_sysobject (all) b where b.r_object_id = a.item_id and b.object_name = a.item_name and (a.task_state = 'acquired' or a.task_state is NULL or a.task_state = '') and a.name = 'dm_fulltext_index_user'

============================================

To add a format

create dm_format object set name='jpeg_ws', set description='Custom alticor Thumbnails', set mac_creator='????', set mac_type='JPEG', set dos_extension='jpg', set mime_type='image/jpeg', set default_storage='280175d680000114'

=============================================

To See active sessions

execute show_sessions

execute list_sessions

execute count_sessions

==========================

Display objects with Cabinet

select d.object_name as "Document", c.object_name as "Repository",f.object_name from dm_cabinet c,dm_folder f, dm_document d where f.i_cabinet_id=c.r_object_id and any d.i_folder_id=f.r_object_id

================================

To find file system path of an object’s content file

select file_system_path from dm_location where object_name in (select root from dm_filestore where name in(select a_storage_type from dm_document where r_object_id = ‘object_id’))

========================================

To find the default file store of a type

select name from dm_store where r_object_id in(select default_storage from dmi_type_info where r_type_id in(select r_object_id from dm_type where name = ‘my_document’))

========================================

To find users and  groups they belong to

select u.r_object_id,u.user_name,u.user_state,u.r_modify_date,g.group_name from dm_user u, dm_group g where u.user_name not like 'ldap%' and u.user_name not like 'dm_%' and any g.users_names = u.user_name and u.user_source='LDAP' order by u.user_name,g.group_name

============================================

DQL to get folder path


SELECT s.object_name, f.r_folder_path FROM dm_sysobject s, dm_folder f WHERE s.i_folder_id = f.r_object_id AND f.r_folder_path != ‘’ AND additional conditions ENABLE (ROW_BASED)


=======================================================