Friday, January 4, 2008

Queue Item Maintenance

The recording of task events are tracked in tables, namely the dmi_queue_item table. These build up proportionally to the number of tasks executed. As time goes on and performance potentially slows down, these queues will need to be deleted.

Periodically delete old records from the dmi_queue_item table, but keep track of a document’s workflow history for a certain amount of time beyond the dmi_queue_item cleaning.

The first thing to determine is whether the out-of-box dm_QueueMgt job can do what’s required. This job runs a method that can delete queue items older than a cutoff date. This is useful, however, we want to keep the workflow history of a document. This is also useful because this table keeps a record of many other events in the repository which need to be deleted on a scheduled basis. The solution was to create a custom table which holds the requirement queue items to maintain the workflow history of documents, and to create a new job and method to populate it before as part of queue management.

Solution Details:
First, create a custom table using DQL (Note: this table has the same columns as the dmi_queue_item table):

EXECUTE exec_sql WITH query='DROP TABLE wf_history_s'
EXECUTE exec_sql WITH query='CREATE TABLE wf_history_s
r_object_id VARCHAR2(32),
object_type VARCHAR2(32),
id_1 VARCHAR2(32),
string_5 VARCHAR2(200),
string_4 VARCHAR2(200),
string_3 VARCHAR2(200),
string_2 VARCHAR2(200),
string_1 VARCHAR2(200),
workflow_id VARCHAR2(32),
policy_id VARCHAR2(32),
registry_id VARCHAR2(32),
audit_signature VARCHAR2(255),
audited_obj_vstamp INTEGER,
user_name VARCHAR2(32),
time_stamp_utc DATE,
audit_version INTEGER,
chronicle_id VARCHAR2(32),
controlling_app VARCHAR2(32),
object_name VARCHAR2(255),
audited_obj_id VARCHAR2(32),
version_label VARCHAR2(32),
acl_domain VARCHAR2(32),
attribute_list_id VARCHAR2(32),
host_name VARCHAR2(128),
user_id VARCHAR2(32),
i_audited_obj_class INTEGER,
event_source VARCHAR2(64),
event_name VARCHAR2(64),
r_gen_source INTEGER,
owner_name VARCHAR2(32),
time_stamp DATE,
event_description VARCHAR2(64),
session_id VARCHAR2(32),
current_state VARCHAR2(64),
application_code VARCHAR2(64),
acl_name VARCHAR2(32),
attribute_list VARCHAR2(2000),
i_is_archived VARCHAR2(32),
id_5 VARCHAR2(32),
id_4 VARCHAR2(32),
id_3 VARCHAR2(32),
id_2 VARCHAR2(32)

REGISTER TABLE dm_dbo.wf_history_s
r_object_id CHAR(32),
object_type CHAR(32),
id_1 CHAR(32),
string_5 CHAR(200),
string_4 CHAR(200),
string_3 CHAR(200),
string_2 CHAR(200),
string_1 CHAR(200),
workflow_id CHAR(32),
policy_id CHAR(32),
registry_id CHAR(32),
audit_signature CHAR(255),
audited_obj_vstamp INT,
user_name CHAR(32),
time_stamp_utc TIME,
audit_version INT,
chronicle_id CHAR(32),
controlling_app CHAR(32),
object_name CHAR(255),
audited_obj_id CHAR(32),
version_label CHAR(32),
acl_domain CHAR(32),
attribute_list_id CHAR(32),
host_name CHAR(128),
user_id CHAR(32),
i_audited_obj_class INT,
event_source CHAR(64),
event_name CHAR(64),
r_gen_source INT,
owner_name CHAR(32),
time_stamp TIME,
event_description CHAR(64),
session_id CHAR(32),
current_state CHAR(64),
application_code CHAR(64),
acl_name CHAR(32),
attribute_list CHAR(2000),
i_is_archived CHAR(32),
id_5 CHAR(32),
id_4 CHAR(32),
id_3 CHAR(32),
id_2 CHAR(32)

update dm_registered object
set owner_table_permit = 15,
set group_table_permit = 15,
set world_table_permit = 15
where table_name = 'wf_history_s'

Second, create a custom Documentum method to be executed by the custom queue management job. This class should have the following methods and logic:

a. Populate Workflow History Table according to criteria. Here’s an example dql:

"insert into dm_dbo.wf_history_s " +
"(r_object_id, event_name, time_stamp, user_name, audited_obj_id, string_4, workflow_id, string_3) " +
"SELECT '0000000000000000' as r_object_id, task_name as event_name, date_sent as time_stamp, sent_by as user_name, r_object_id as audited_obj_id, name as string_4 , router_id, task_state as string_3 " +
"FROM dmi_queue_item " +
"WHERE r_object_id not in (select audited_obj_id from dm_dbo.wf_history_s) " +
"AND router_id != '0000000000000000' " +
"AND date_sent < DATEADD(Day, -"+sCutOffDate+", date(today)) " +
"AND delete_flag = 1";

b. If the Workflow History Table gets populated successfully, delete the dmi_queue_item rows according to criteria. Here’s an example dql:

"DELETE dmi_queue_item objects " +
"WHERE router_id != '0000000000000000' " +
"AND date_sent < DATEADD(Day, -"+m_cutoff+", date(today)) " +
"AND delete_flag = 1";

c. Write the job report to the repository.

Third, create the custom queue management job.