In day to day life I think you will find below queries very helpful. Do not forget to like or post comment :-)
Execute D2 Core Method
– for folder and security recalculation
|
execute do_method with method='D2CoreMethod',
arguments='-docbase_name $DOCBASENAME
-user $DOCBASEUSERID
-dql_filter "select r_object_id from dm_sysobject(all) where
r_modify_date BETWEEN DATE(''10/31/2011 11:30:00'',''mm/dd/yyyy hh:mm:ss'')
AND DATE(NOW)" -naming false -autolink true -security true -apply_for_vd
false -create false -transaction false'
|
Run D2UpdateChildACLMethod
as per security config
|
EXECUTE do_method WITH method =
'D2UpdateChildACLMethod', arguments = '-user_name username -docbase_name docbasename
-acl_config_name doc_approved_acl¬doc_draft_acl
|
DQL to get workflow
history of document in D2 application (provide documents i_chronicle_id in
red color section)
|
select a.r_object_id,a.audited_obj_id,a.event_name
as event_name,a.object_name AS workflow_name ,doc.object_name AS
document_name,ra.child_label AS document_version,a.owner_name As
supervisor_name,w.tracker_state as task_state, w.start_date as
date_sent,a.user_name AS task_performer,a.time_stamp AS
task_completion_time,a.string_2 AS outcome,a.event_source AS event_source,
a.string_3 AS delegation_from,a.string_4 AS delegation_to from dm_audittrail
a ,d2c_workflow_tracker w , dm_relation ra, dm_sysobject doc where
a.audited_obj_id in( select w.r_object_id from d2c_workflow_tracker w where
r_object_id in (select distinct w.r_object_id from dm_relation
r,d2c_workflow_tracker w where r.relation_name = 'D2_WF_TRACKER_DOCUMENT' and
r.child_id = '$object$(i_chronicle_id)'
and r.parent_id = w.r_object_id)) and a.audited_obj_id=w.r_object_id and
ra.parent_id = w.r_object_id and a.audited_obj_id=ra.parent_id and
((a.event_name='d2_workflow_sent_task' and a.user_name not in ( select
user_name from dm_audittrail b where b.event_name in
('d2_workflow_rejected_task','d2_workflow_forwarded_task','d2_delegation_delegated_task','d2_workflow_delegated_task')
and b.audited_obj_id=a.audited_obj_id)) or (a.event_name in
('d2_workflow_rejected_task','d2_workflow_forwarded_task') and a.string_2 is
not nullstring) or (a.event_name in
('d2_delegation_delegated_task','d2_workflow_delegated_task'))) and
doc.i_chronicle_id=ra.child_id and ra.child_label NOT In ('CURRENT',' ') order by 1 desc
|
Full Text Index Queue Summary
|
select min(date_sent) as minimum from dmi_queue_item
where delete_flag = 0 and (task_state is NULL or task_state = ' ') and name
IN (select queue_user from dm_ftindex_agent_config)
|
select count(*) as number from dmi_queue_item where
delete_flag = 0 and task_state = 'warning' and name IN (select queue_user
from dm_ftindex_agent_config)
|
select count(*) as number from dmi_queue_item where
delete_flag = 0 and task_state in ('error','failed') and name IN (select
queue_user from dm_ftindex_agent_config)
|
select count(*) as number from dmi_queue_item where
delete_flag = 0 and (task_state is NULL or task_state = ' ') and name IN
(select queue_user from dm_ftindex_agent_config)
|
Halted Workflows
|
select pr.object_name
as process, wf.object_name as workflow, wf.supervisor_name, qi.name,
wi.r_creation_date from dm_process pr, dm_workflow wf, dmi_workitem wi,
dmi_queue_item qi where wf.r_runtime_state = 3 and pr.r_object_id =
wf.process_id and wi.r_workflow_id = wf.r_object_id and qi.r_object_id =
wi.r_queue_item_id and qi.delete_flag = 0 order by 1, 2
|
User Workflows
|
select pr.object_name as process, wf.object_name as
workflow, qi.name, wf.r_runtime_state, wi.r_creation_date from dm_process pr,
dm_workflow wf, dmi_workitem wi, dmi_queue_item qi where pr.r_object_id =
wf.process_id and wi.r_workflow_id = wf.r_object_id and qi.r_object_id =
wi.r_queue_item_id and qi.delete_flag = 0 AND wf.supervisor_name = USER order
by 1, 2
|
Job Details
|
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
|
Number of Workflows by
Supervisor
|
select wf.supervisor_name, count(*) as number from
dm_workflow wf where wf.r_runtime_state != 2 and wf.r_runtime_state != 4
group by supervisor_name order by supervisor_name
|
DQL – Check if index exists
or successfully created
|
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’)
|
DQL/API – Create Index
|
DQL: Sample 1:
EXECUTE make_index WITH type_name='dm_audittrail', attribute='chronicle_id',index_name='chronicle_id_index', use_id_col=true; Sample 2: EXECUTE make_index WITH type_name=’dm_sysobject’,attribute=’i_is_deleted’,index_name=’DM_SYSOBJECT_S_NI1?,index_space=’TEST_INDEX’,use_id_col=true,“UNIQUE”=true API: Sample 1: apply,c,NULL,MAKE_INDEX,NAME,S,dm_sysobject,ATTRIBTUE,S,i_is_deleted,INDEX_NAME,S,DM_SYSOBJECT_S_NI1,INDEX_SPACE,S,TEST_INDEX,USE_ID_COL,B,true Sample 2: apply,c,NULL,MAKE_INDEX,NAME,S,dm_sysobject,ATTRIBTUE,S,i_is_deleted,INDEX_NAME,S,DM_SYSOBJECT_S_NI1,INDEX_SPACE,S,TEST_INDEX,USE_ID_COL,B,true,“UNIQUE”,B,true |
DQL/API – Drop Index
|
DQL: Syntax:
EXECUTE drop_index [[FOR] dmi_index_id] [WITH name = index_name] Example: EXECUTE drop_index WITH name=’chronicle_id_index′ API: Syntax: apply,session,dmi_index_id,DROP_INDEX [,NAME,S,index_name] Example: apply,session,dmi_index_id,DROP_INDEX,NAME,S,chronicle_id_index |
Use of ESCAPE character
|
SELECT r_object_id FROM dm_document WHERE
object_name LIKE '%\_%' ESCAPE '\'
[Note:Your LIKE predicate would look like this:
LIKE ’\%\_\\’ ESCAPE ’\’ escape character can escape itself] |
Virtual Document / Assembly
objects / D2C Snapshots
|
# get the associated assembly id from the virtual
document root object
select distinct book_id from dm_assembly where parent_id='090eruhterh380586f30' ; # get all the component under assembly object/d2csnapshot object using assembly id select r_object_id,object_name,a_status,r_version_label,i_latest_flag from dm_document(all) where r_object_id in (select r_object_id from dm_sysobject(all) in assembly id('0800fdhdhd80586f34') descend); # get D2CSnapshot objects for an virtual document using i_chronicle_id select r_object_id,upper(object_name),object_name,r_object_type,a_content_type,r_is_virtual_doc,r_link_cnt,r_lock_owner,r_policy_id,r_assembled_from_id,r_immutable_flag,r_frozen_flag ,r_creation_date,title from d2c_snapshot where r_assembled_from_id in (select r_object_id from dm_sysobject(all) where i_chronicle_id = '0900gsgsts04fd9db') order by r_object_id; |
