Audit of Completed workflows

Once a WF is completed, Dctm removes its corresponding entries from dm_workflow, dmi_workitem and dmi_package.Only dmi_queue_item contains details for the workflow_id.On enabling audit on WF, you get only the corresponding workflow details from dm_audittrail.

Why no data showed when we choose completed workflows in workflow reporting?

When selecting "Completed Workflows" under Workflow, Reporting shows results under the tab but the results does not have any link to view the completed workflow.
Include "dm_all_workflow" event in the Audit Management, so the object id and rest of the details will be stored in the audittrial table after the workflow is completed.
From DA go to Audit Management node;
Click ok "Manage Auditing by events selected for all objects in the repository";
Add "dm_all_workflow" via the Add "Event Name";
(Note for creating/editing audittrial you need to have appropriate Extended Privilieges for Audit)

Select * from dmc_completed_workflow

Some audit related queries

select * from dm_audittrail where event_description='All Workflow Events'
select * from dm_audittrail where event_name='dm_addattachment'

select dmi_workitem.r_workflow_id, dmi_workitem.r_creation_date, dmi_workitem.r_performer_name, dm_workflow.object_name from dmi_workitem, dm_workflow where dmi_workitem.r_workflow_id = '4d00303980000501' order by r_creation_date

select * from dmi_package

select object_name,r_object_type from dm_sysobject(ALL) where any i_folder_id IN (select id_5 from dm_audittrail where event_source='workflow' and event_name= 'dm_addpackage' )

To see date wise workflow tasks of a user
select * from dmi_queue_item where router_id='4d00303980001100' order by date_sent desc

If you want the documents processed in the workflow(current/completed).
Presuming the audit trail is enabled for the workflow activities,try this query.

select object_name from dm_document( or your custom type) where r_object_id in
(
select distinct r_component_id
from dmi_package dp,dm_audittrail_sp da,dm_workflow dw
where
dp.r_workflow_id = da.workflow_id
and da.event_source = 'workflow'
and da.event_name = 'dm_startworkflow'
and dp.r_workflow_id = dw.r_object_id
)


What can I query on a workflow to find out what document is being routed in that ?

The r_object_id of the document in the workflow is the r_component_id attribute of the dmi_package object for that workflow. Below are some example queries.
select r_component_id from dmi_package where r_workflow_id = '4dxxxxxxxxxxxxxx'
This will return the r_object_id of the document that is being routed in the workflow.
select object_name from dm_document where r_object_id in (select r_component_id from dmi_package where r_workflow_id = '4dxxxxxxxxxxxxxx');
This will return the name of the document being routed in the currently running workflow.
select object_name from dm_document where r_object_id in (select r_component_id from dmi_package where r_workflow_id in (select router_id from dmi_queue_item where r_object_id = '1bxxxxxxxxxxxxxx'));
This will return the object_name of the document that is being referenced by a running workflow that has a dmi_queue_item associated with it. This is useful if you are trying to find the document name, but you only have information about the queue item in the Documentum Inbox.
The attributes of dmi_queue_item do not refer to the document directly (in this case the dmi_package) that is being routed, but refers to the associated dmi_workitem. You can find out the package information, but you will have to be creative in the query that you run (see above examples).

How do I troubleshoot workflows which contain automatic activity

To troubleshoot workflows which contain automatic activity that does not run follow the steps below:
1. Check if workflow agent is up and running.
Here is what you look in the docbase log file to see if workflow agent has started up fine.
Wed Mar 10 19:52:06 2004 122000 [DM_WORKFLOW_I_AGENT_START]info: "Workflow agent master (pid : 3512, session 010003e880000007) is started sucessfully."
Wed Mar 10 19:52:07 2004 122000 [DM_WORKFLOW_I_AGENT_START]info: "Workflow agent worker (pid : 2872, session 010003e88000000a) is started sucessfully."
Wed Mar 10 19:52:08 2004 122000 [DM_WORKFLOW_I_AGENT_START]info: "Workflow agent worker (pid : 2860, session 010003e88000000b) is started sucessfully."
Wed Mar 10 19:52:09 2004 122000 [DM_WORKFLOW_I_AGENT_START]info: "Workflow agent worker (pid : 2892, session 010003e88000000c) is started sucessfully."

When docbase starts up, it starts workflow agent master and worker threads and prints above message to server logfile
Workflow agent cannot be started manually. It starts up during docbase startup.

2. The default out of the box automatic workflow method that we ship with server is called dm_noop_auto_method.
When automatic activity does not run please create a simple workflow with first activity as dm_noop_auto_method and second activity as a manual task.
Please turn on trace launch for dm_noop_auto_method, and run the workflow.
Trace launch information in logged in the docbase log.

Set Trace Launch attribute to True on the custom method object and run the Workflow again. Any errors that are encountered with the method will be displayed in the Server log. Review the server log and the trace launch information, check the method verb. The method verb and trace launch attribute can be checked

3. Check for wrong

spellings and case sensitivity in the method verb.
Dump the dm_noop_auto_method and compare with one in-house.

4. Dump the method object, check the attributes, use_method_content, method verb. If the
use_method_content attribute is set to True, your method verb attribute should not include a ?-f? parameter, it?s not necessary.
Example : ./dmbasic eEntry_Point

5. If you have users that are not receiving tasks following an automatic activity, check the
workflow_disabled attribute on those specific users. Dump the user, using the Dump API.
Example: dump ,c,

6. Please dump the server config object
dump,c,serverconfig

Please look for following two parameters.
wf_agent_worker_threads : 3
wf_sleep_interval : 3

By default out of the box server comes with these two parameters set to 3.

How to check timeout?
You can check your method timeouts using the following query
select r_object_id,object_name,launch_async,timeout_min,timeout_default,timeout_max from dm_method
where the timeout values are min <= default <= max. These values are valid for synchronous methods.


Why do I recieve an error message stating that 'Activity instance failed' ?

You may find that some of the documents make the transition and repeated requests to force the transition of the other documents eventually succeeds.For each method, the server launches a process to run it. For workflow automatic activity methods, the process is always launched synchronously. That means, if the process does not finish within time_out, the server kills the process. The time_out value comes from exec_time_out attribute of dm_activity, and it must fall between timeout_min and timeout_max attributes of the dm_method. Therefore this value can exist between 30 seconds and 7 days.

To resolve the above issue increase the timeout for automatic activities in your workflow. By default when creating an automatic activity the timeout for is 60 seconds.

How to know the r_runtime_state of the failed activites?
select r_runtime_state from dm_workflow where r_object_id in (select router_id from dmi_queue_item where where message like 'Activity instance%failed%' and router_id!='0000000000000000')


select a.r_object_id,a.object_name,a.process_id,a.supervisor_name,a.r_runtime_state,b.task_number,b.task_state,b.task_name,b.message,b.sent_by,b.date_sent from dm_workflow a,dmi_queue_item b where a.r_object_id=b.router_id and a.r_object_id in (select router_id from dmi_queue_item where message like 'Activity instance%failed%' and router_id!='0000000000000000')

How to track Workflows?

You could turn on the dm_all_workflow Audit trail event. In this case all workflows events are recorded in the dm_audittrail records. Timestamps are recorded in milliseconds, only Documentum Administrator does not return that so you have to analyze the results with an SQL client (SQL Developer for example) directly in the database (looking at dm_audittrail_s table).

select * from dmi_registry (to see the events that are being audited)

audit,c,dm_all_workflow (to audit all workflow events


How can I make sure that my documents will be audited correctly when I register
Keep in mind that auditing is designed for registering events for documents that are either a subtype of dm_document or dm_sysobject .

1) For verification create a subtype of dm_document and register an event against this subtype.

2) Then create a document whose attribute has this subtype.

3) Next register the events you would like to monitor.

- Launch Documentum Administrator

- Select auditing

- Clicking on the selection, "Register Events for all objects of a type". It is important that when this step is done, that you navigate to the correct folder such as dm_document and highlight the dm_type which you are registering the event against.

Then launch Intranet Client and trigger an event such as check in or check out. You should now have generated an event and possibly an email if your email service has been properly configured.



An event may not be viewable on the Audit Detail page in Workflow Reporting unless you turn on auditing for workflow template.

To turn on the workflow template audit, check Template Audit Record Setting option on Properties dialog box which is from File > Template Properties in Web Workflow Manager.

In addition, to turn on the workflow template audit, the user must have 'Config Audit' privilege. If the user does not have this privilege then this option is not available for this user on the workflow template properties dialog box.

System Event: System-recognized actions performed on specific documents, folders, cabinets, or other objects. Content Server supports a large number of system-defined events, representing operations such as checkins, promotions, and demotions. Refer to the Audit section of the Content Server API Reference Manual for tables listing all System Events. Also, Appendix B (System Events) of the Content Server API Reference provides an exhaustive list of the System Events.



Application Event: Actions which are not inherently recognized by the Content Server. Applications decide which of these non-system actions need to support audit and add code to specifically handle those actions. For example, an application may choose to provide audit capability for an advanced search function, capturing useful data in the audit such as the user executing the search and the search parameters.



Event Target: The primary entity acted upon by an event. Examples of targets for System Events include specific instances of an object, types of an object, users, groups, policies, methods, workflows, activites, etc. Application Events have their targets defined by their application.



Audit Event: An Audit Event is either a System Event or an Application Event. The term Audit Event is introduced as a generalization for convenience.



Audit Method: An Audit Method is used to request an audit of a specific event. The arguments to the Audit Method specify the event to audit, the target of the event, and the names of the attributes whose values are to be audited. Audit methods can be used to request audits for System Events or Application Events. A user must have the extended privilege Config Audit in order to invoke an Audit Method.



Audit Trail: An Audit Trail is the history of instances of an Audit Event. Each instance of an Audit Event results in the creation of one Audit Trail entry which stores information about the event (such as the time of the event, which objects were involved, who performed the actions, and which values were affected).



Tamper-Evident Auditing: The Content Server provides the ability to "sign" each Audit Trail entry, which will cause a cryptographic hash to be computed for the contents of the entry. The hash itself is then encrypted using the Application Encryption Key (AEK) for the Content Server and stored back in the audit_signature attribute of the entry. At any point in the future, a client can validate the entry, which will recompute the hash and compare it to the hash stored in the attribute. Due to the way hashes work, if any information in the entry has been changed since the original hash was computed, then the new hash will not match the original, and the client knows that the entry has been tampered.

Audit Trail is docbase specific
dmc_completed_workflow - completed workflow object stores information from audit trail records about a completed workflow. The objects are created by the dm_WfReporting job and used by Webtop’s aggregrate workflow reporting tool.
To fully populate the attributes in these objects, you must be auditing all workflow events.
The object type is created by a script when Content Server is installed.
When you clone, the Audit info will not be cloned because it is specific to the docbase. Setup auditing and perform a WF, you should see the results

What is the difference between Register and Audit API?
Register API: Registers the current user to receive notification when the specified event occurs.

When you register for an event, the server puts a notification in your inbox when the
event occurs. With two exceptions (dm_fetch and dm_acquire), you receive notification
each time the event occurs in the same or different repository sessions. For dm_fetch
and dm_acquire, you receive only one notification in a repository session even if the
event occurs multiple times in the session.
If you register for an event on an object type, you receive a notification every time that
event occurs for an object of the type.
Registering for an event doesn't create audit trail entries when the event occurs. To create
audit trail entries for an event, you must use an Audit method to initiate auditing. You
can both register for an event and audit the event. (Using Audit requires Config_audit
privileges.)



Audit API: Initiates auditing of an event.

You must have Config_audit privileges to use this method.
Executing Audit creates a dmi_registry object that records the auditing request. The
values you include in the Audit arguments are recorded in the registry object. Thereafter,
if the event is a system event, Content Server creates an audit trail entry whenever
the event occurs. If the event is an application event, the application can examine the
registry to determine whether to create an audit trail entry for the event.


Both API create a dmi_registry entry but have different purposes.



To check what are events are registered for a user or audited run this DQL:

DQL > select r_object_id, user_name, registered_id, event, is_audittrail from dmi_registry



If the event is audited, is_auditrail should be set to 1 and when the event occurs an entry will be created in the dm_audittrail table.

How to attach a document in already installed workflow?

This isn't too hard actually. As long as whatever document you are attaching meets the package criteria, you shouldn't have a problem. By that I mean, make sure that if you are adding an object of type dm_document, the package isn't looking for dm_xyz_custom_type.



If you already have a running workflow, you'll need to know the associated package or packages.



Note: It's confusing, but the IDfWorkflow.addAttachment method is not what you're looking for. Documents are routed through packages (IDfPackage).



You didn't mention how you needed to add a document to a workflow; either manually or through a workflow activity. I'll try and cover the bases. First, the easiest way is to add a document to a running workflow is find whoever has a pending workitem, open that task through their Documentum inbox and click the 'Add attachment' button. Problem solved. Ah, but it's never that easy. If you needed to manually slip in a document into a package, you can use a little DQL to find the most recent package for your workflow (providing you have the workflow_id):



SELECT r_object_id from dmi_package where r_workflow_id = '4d_yaddayadda'



Then a quick little API insert command setting the r_component_id to the r_object_id of the document you wish to insert (along with the other attributes you need) will also do the trick. However, you asked for a DFC method! Ah, you're in luck. I'm going to assume that you want to do this in a workflow activity of some such. That's good because it's easy to do so if you already have the workitem object. Here's some quick psuedocode that shows how to get a workflow package and insert the document you need. (I'm still rusty from the holidays so my code is going to be horrible.)



IDfWorkitem wi = (IDfWorkitem)sess.getObject(tasks.getId("item_id"));
IDfActivity act = wi.getActivity();
for (int i = 0; i < act.getPackageCount(); i++) // If you have more than one package
{
IDfId packageId = act.getPackageId(i);
IDfPackage packageObj = wi.getPackage(packageId);
String packageName = packageObj.getPackageName();

if (packageName.equals "Package0")

packageObj.appendId(r_component_id, object_id value of document being added to package)

packageObj.appendId(r_component_chron_id, object_id value of document being added to package)

packageObj.appendString(r_component_name, name of document being added to package)

packageObj.appendString(r_package_label, CURRENT)

}



That'll should do the trick. You don't want to use wi.addPackage because that would add an additional package to the workflow and you don't really want that if you can avoid it. That can cause all sorts of issues later on in the workflow. Hope this helps.

What happens if automatic activity fails, and what should I do to get out of it

Solution and Explanation:
If workflow is initiated, workflow instance is created and set to state running state. As the workflow progresses, the runtime activity information is updated in dm_workflow object and workitem objects corresponding to the runtime activity staus are created.
After workflow is successfully completed, the workflow object and workitem objects are removed from Docbase.

If a method fails for a automatic activity, then workflow stays active state, but runtime activity goes to terminated state and workitem goes to Apaused.
You can restart the failed activity, with restart API. Resume API does not work in this case, since workflow and activity were not halted. SN 14292 is helpful to call restart.


State information for workflow, runtime activity are kept in dm_workflow object, and that for woritem are kept in workitem object.

<< workflow >>

r_runtime_state : current state of the workflow.

0, meaing dormant
1, meaning running
2, meaning finished
3, meaning halted
4, meaning terminated

r_act_name (R) : activity identifier as defined in dm_process object

r_act_state (R) : activity's current status

0, meaing dormant
1, meaning active
2, meaning finished
3, meaning halted
4, meaning terminated

<< work item >>

r_runtime_state

0, meaning dormant
1, meaning acquired
2, meaning finished
3, meaning paused
4, meaning Dpaused (a work item in the dormant state is paused)
5, meaning Apaused (a work item in the acquired state is paused)
6, meaning Ppaused (a work item in the paused state is paused)
Values 4-6 occur when a workflow containing the work item is halted.

Why are my automatic activities failing when using my custom methods on ContentServer

When an automatic activity is started, it is placed on a queue for execution.

The server facility responsible for executing automatic jobs wakes up at regular intervals and executes the activities in the order in which they appear on the queue. By default, the facility handles all queued activities before going to sleep.

Occasionally, we may encounter a problem with a custom method used in a Workflow Template. We may notice that the custom code associated with the method object is not running. But in many cases, we don't know where to begin to determine where the problem is.

Below is a series of tests that can be run to determine if it's your custom method that is failing or if it's something else on your eContent Server.

1. If your custom method is failing, most likely you will see 'paused' tasks in the Inbox of the user or users following the automatic activity.

This is typically the result of a syntax error in the code of the method. Verify that the method can run outside of the Workflow Template using an Apply API. Or run the method from the Documentum Administrator and test the code.

2 Replace your custom method with the dm_noop_auto method in your Workflow Template and run the Workflow again. If this works, that will indicate there is a problem with your custom method.

3. Turn Trace Launch on the custom method object and run the Workflow again, any errors that are encountered with the method will be displayed in the Server log. Review the server log and the trace launch information, check the method verb used.

4. Consider moving the method content file into the Docbase versus say placing it in the \bin directory on the eContent Server1.

If you do this, make sure, you update your method verb; it will not be necessary to include the '-f' for the file. Only the '-e' Entry Point will be necessary.

5. Check the header of the custom method code. Every method used in a Workflow Template needs the following parameters:

Sample below:

Sub Entry_Point (ByVal stub1 As String, _ ' -docbase

ByVal docbase As String, _

ByVal stub2 As String, _ ' -user

ByVal username As String, _

ByVal stub3 As String, _ ' -packageID

ByVal workitemID As String, _

ByVal stub4 As String, _ ' -ticket

ByVal ticket As String, _

ByVal stub5 As String, _ ' -mode

ByVal mode As String)

It doesn't have to be called 'Entry Point' but it does need to contain parameters listed.

6. Verify that the following APIs are used in the method content code.

- Connect, Acquire, and Complete

If you're uncertain about the syntax of these APIs, refer to the Server Reference Manual.

If one of these APIs is missing in your method code, your custom method will not work.

If you're looking for a code sample, view the method content for the 'dm_noop_auto' method.

This method object contains all of the APIs needed. When using this method object, it connects, acquires and performs a complete.

7. Check your method verb, for misspellings and case sensitivity.

8. Check if the agent exec process is running on the eContent Server. This will determine if the Workflow agent is running as well.

Note: you will not a see a workflow agent process id or process on your server.

9. Check the return value of the automatic method. See example below.

If the custom method exited with zero, server treated it as successful completion.

Therefore, if anything fails within the custom method, the method should exit with non-zero value. Then server knows the completion of the automatic activity failed and server will do something about the automatic workitem accordingly.

e.g. The following is part of the sample script dm_noop_auto_method, if the acquire failed, this script did exit with 10.

status = dmAPIExec("acquire," & sess & "," & packageID)

If status = False Then

Print "The acquire did not succeed"

Print dmAPIGet("getmessage," & sess)

dmExit(10)

End If

10. Are you trying to run this custom method with multiple packages?

There is a known bug on this issue. When attempting to use WF_PromoteLifecycle it fails to promote the optional packages and only promotes the required package. BUG#42500.

11. Setup and configure java.ini file, if Content Server runs on Unix platform.

This is documented in the Server installation guide on how to configure this file correctly.

Documentum provides a sample file called 'java_def.ini'. This file can be copied and modified.

Java.ini file needs to be under $DM_HOME\bin.

12. Update the Java Runtime Environment (JRE).

If using Content Server 5.1, java needs to be at 1.3.1.04 or 1.4.

If using eContent Server 4.2, java needs to be at 1.2.2

Code sample to run workflow with API

Symptom: how to create and handle workflow instance, when a workflow
template (process) is defined by graphical Workflow Manager in
Desktop Client.

Solution:

1. Create Workflow definition (dm_process) with Desktop.
Initiate --> AutoFirst --> ManualSecond --> End
AutoFirst : Automatic Activity run with dm_noop_auto_method.
ManualSecond : Manual Activity run by a single user.
Package from Initiate to AutoFirst, optional package Package0
is defined.Package from AutoFirst to ManualSecond, optional
package Package0 is defined.

So this workflow can be started with no package added. If the
package is set to CURRENT, then this workflow can be started
only with attached package.

2. Get r_object_id of this Workflow definition (Process)
DQL> select r_object_id, object_name from dm_process

3. Create a workflow instance
API> create,c,dm_workflow

4d00800080000911

API> set,c,l,process_id
SET> 4b0080008000392a
OK
API> set,c,l,object_name
SET> mySimpleWF
OK
API> save,c,l
OK

You can see a Workflow instance is created.
DQL> select * from dm_workflow

r_object_id object_name process_id
------------------- --------------- -------------------
4d00800080000911 mySimpleWF 4b0080008000392a

4. Start the workflow
API> execute,c,4d00800080000911

But there is no dmi_workitem or dmi_package.
DQL> select r_object_id, r_act_seqno, r_runtime_state from dmi_workitem
r_object_id r_act_seqno r_runtime_state
---------------- ---------------------- ----------------------
(0 rows affected)
DQL> select r_object_id, r_workflow_id, r_package_name from dmi_package
r_object_id r_workflow_id r_package_name
--------------- ---------------- ----------------
(0 rows affected)
5. Add package to the first activity of workflow
API> addpackage,c,4d00800080000911,AutoFirst,Input:0,Package0,dm_sysobject,,,090080008000193f
...
4900800080000924
Where
AutoFirst : Activity name in Workflow instance
Input:0 : Input port name for the first activity

Package0 : Package name

090080008000193f : r_object_id of document object which shall be delivered to next activity.

4900800080000924 is the r_object_id of the dmi_package object, just created. And dmi_workitem and dmi_package objects are created, according to the workflow definition.

I watched that dmi_workitem objects are created only after all the dmi_package objects are attached for the first activity.

DQL> select r_object_id, r_act_seqno, r_runtime_state, r_auto_method_id from dmi_workitem

r_object_id r_act_seqno r_runtime_state r_auto_method_id
---------------- ---------------------- ---------------------- ----------------
4a0080008000091f 1 0 0000000000000000
4a0080008000091e 0 2 1000800080003528
DQL> select r_object_id, r_workflow_id, r_package_name from dmi_package
r_object_id r_workflow_id r_package_name
---------------- ---------------- ----------------
4900800080000925 4d00800080000911 Package0

4900800080000924 4d00800080000911 Package0

The package name should be defined in workflow definition, otherwise
addpackage API fails.

API> addpackage,c,l,AutoFirst,Input:0,Package1,dm_sysobject,,,090080008000193f

[DM_WORKFLOW_E_INVALID_PACKAGE_INPUT_PORT]error: "The package, Package1, is not in the specified input port, Input:0."

6. Acquire the workitem for corresponding to the second activity of the workflow

API> acquire,c,4a0080008000091f
DQL> select r_object_id, r_act_seqno, r_runtime_state, r_auto_method_id from dmi_workitem

r_object_id r_act_seqno r_runtime_state r_auto_method_id
---------------- ---------------------- ---------------------- ----------------
4a0080008000091f 1 1 0000000000000000
4a0080008000091e 0 2 1000800080003528

7. Acquire the workitem for corresponding to the second activity of the workflow
API> complete,c,4a0080008000091f
...
OK
Then the workflow, workitem and package objects are removed, since this is the next activity is End activity.

DQL> select * from dm_workflow
r_object_id object_name process_id
------------------- --------------- -------------------
(0 rows affected)

DQL> select r_object_id, r_act_seqno, r_runtime_state from dmi_workitem
r_object_id r_act_seqno r_runtime_state
--------------- ---------------------- ----------------------
(0 rows affected)

DQL> select r_object_id, r_workflow_id, r_package_name from dmi_package
r_object_id r_workflow_id r_package_name
---------------- ---------------- ----------------
(0 rows affected)

Workflow problems and resolutions

How to pause and resume a workflow?
A workflow is suspended and you would like to resume it.
The workflow was suspended because an activity failed.
Resolution 1
Please note that unfortunately, if you are not able to resume your workflows you will have to restart them.
Pausing and resuming work items
You can pause a dormant work item. Work items are dormant until they are acquired or a user delegates the work item. You cannot pause an acquired work item.
To pause a dormant work item, you must be the workflow supervisor or a user with Sysadmin or Superuser privileges. Use a Pause method to pause a work item.
Resuming a paused work item returns the work item to the dormant state. To resume a paused work item, you must be the workflow supervisor or a user with Sysadmin or Superuser privileges. Use a Resume method.
Syntax
To pause:
dmAPIExec("pause,c,workitem_id")
To resume halted workflow, halted activity, or paused work item:
dmAPIExec("resume,c,object_id")
workitem_id: specifies the work item that you want to pause.
object_id: identifies the paused workflow, activity, work item, or SysObject to resume. Use the object's object ID.
Restarting a halted ow or failed activity
Restarting a workflow removes all generated work items and packages and restarts the workflow from the beginning, with all activity instances set to dormant.
Restarting a failed activity sets the activity's state to Active.
To restart a halted workflow or failed activity, use a Restart method.
Syntax
dmAPIExec("restart,c,workflow_id")


Resolution 2
For a specific workflow, get the paused tasks by running the DQL :
select r.r_act_seqno, wf.r_object_id as workflow_id
from dmi_workitem r, dm_workflow wf, dm_activity act
where wf.r_object_id = r.r_workflow_id
and r.r_act_def_id = act.r_object_id
and r_runtime_state = 5 and wf.object_name=""

The above DQL gives u the activity sequence number and workflow id.

Then use IAPI to restart the paused task using :
restart,c,,sequence_no.


Please note: Attribute values for r_runtime_state of dmi_workitem between 4 & 6 occurs when a
workflow containing the work item is halted. So you need to restart the workflow, user restart API method to do this.

select r_object_id,r_runtime_state from dmi_workitem
r_runtime_state indicates the current state of
the workflow. Values are:
Contains the current state of the work item.
Valid value are:
0, meaning dormant 1, meaning acquired
2, meaning finished 3, meaning paused
4, meaning Dpaused (a work item in the dormant state is paused)
5, meaning Apaused (a work item in theacquired state is paused)
6, meaning Ppaused (a work item in the paused state is paused)
Values 4-6 occur when a workflow containing the work item is halted.


Is it possible to restart TERMINATED workflows?
A dm_workflow object is an instance of the dm_process Object
(The WF Template) and is used to represent an active WF. When the WF is
Terminated, these are automatically removed from the system. You can never
create, save them. These are handled primarily by the CS


I need DQL query to find all attachments names/ids from
all started workflows:

The following dql returns all the attachments
SELECT r_component_id, r_component_name
FROM dmi_wf_attachment
WHERE r_workflow_id IN(select r_object_id from dm_workflow)

The following dql returns all the packages
SELECT DISTINCT r_component_id, r_component_name
FROM dmi_package
WHERE r_workflow_id IN(select r_object_id from dm_workflow)


DQL to find no. of attachments + their names in all started workflows

the key is dmi_wf_attachment

Query to retrieve objects attached to workflows
select so.r_object_id,so.r_object_type,so.object_name,wf.object_name as wfname, wf.r_runtime_state
from dm_sysobject (ALL) so, dmi_wf_attachment wfa,dm_workflow wf
where wfa.r_component_id = so.r_object_id
and wfa.r_workflow_id = wf.r_object_id

Add any additional where clause as r_runtime_state = 1 as you please

Query to retrieve workflows with more than 1 attachment
select wf.r_object_id,wf.object_name,wf.r_runtime_state,count(wfa.r_component_id) as noofattachments
from dm_workflow wf,dmi_wf_attachment wfa
where wfa.r_workflow_id = wf.r_object_id
group by wf.r_object_id,wf.object_name,wf.r_runtime_state
having count(wfa.r_component_id) > 1

Again, add any additional where clause if required


SELECT distinct a.r_workflow_id, c.r_component_id as doc_object_id, c.r_component_name as document_name, b.object_name as activity_name
FROM dmi_workitem a, dm_activity b, dmi_package_r c, dmi_package_s d
WHERE d.r_workflow_id = a.r_workflow_id
AND c.r_object_id = d.r_object_id
AND a.r_act_def_id = b.r_object_id
ORDER BY a.r_workflow_id;
Output will be:-
r_workflow_id doc_object_id document_name activity_name

How to assign ACL to a Workflow?

Here is what you do.

1) Get the r_object_id of the workflow definition

select r_object_id from dm_process where object_name = 'name of your workflow definition';

2) use update statement to update the ACL.

update dm_process object set acl_name = 'acl you created' where r_object_id = ' from earlier query';

second way:--

One way is to login to DA and update it from there. You need to find out where the process template is store though. Get the i_folder_id of the dm_process object and then r_folder_path of dm_folder.

Login to DA and go to the specified folder path. In DA change the filter to "show all object" and find the workflow definition. Go to properties and you can assign the acl from there.

If this answers your question please mark it as answered and award points.

How to see completed workflows?

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.


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

OR



Symptoms
Completed activities will be automatically removed from inbox. So where to find the information about completed workflows?

Cause
Workflows can be reviewed after they are completed only if you enable the audit trail.
If the audit trail is enabled, we can simply check completed workflows on workflow reporting page.

Resolution
1. Steps to enable the audit trail for workflow:
* If use Process Manager: File -> Template Properties -> Template Audit Trail Setting -> Yes
* If use Process Builder: Tools -> Process Properties -> Audit Trail Setting -> Yes

2. Steps to show the workflow reporting page:
Tools -> Workflow -> Workflow Reporting -> From drop-down list on the up-right corner of the page, select Completed Workflows

DFC

http://www.bluefishgroup.com/library/2008/automated-workflow-recovery/

Workflow related queries

To know details of the tasks related with workflow
select * from dmi_queue_item where router_id='Workflow_ID'




List of component objects in user inbox
select r_object_id from dm_sysobject where r_object_id in (select r_component_id from dmi_package where r_workflow_id in (select router_id from dmi_queue_item where name='’))
List of inbox items for a given task
select count(name), name FROM dmi_queue_item WHERE task_name = ‘ and delete_flag=0 AND (task_state = ‘dormant’ OR task_state = ‘acquired’ OR task_state = ‘paused’ OR task_state = ‘ready’) AND (router_id != ‘0000000000000000′ AND router_id IS NOT NULLSTRING AND NOT (a_operations = ‘queue’)) group by name order by name

List of WF’s in a user inbox
select * from dmi_queue_item WHERE name='’ AND delete_flag=0 AND (task_state = ‘dormant’ OR task_state = ‘acquired’ OR task_state = ‘paused’ OR task_state = ‘ready’) AND (router_id != ‘0000000000000000′ AND router_id IS NOT NULLSTRING AND NOT (a_operations = ‘queue’)) UNION ALL SELECT * FROM dmi_queue_item WHERE name IN (SELECT ALL group_name FROM dm_group WHERE ANY i_all_users_names = ‘’) AND delete_flag=0 AND (task_state = ‘dormant’ OR task_state = ‘acquired’ OR task_state = ‘paused’ OR task_state = ‘ready’) AND (router_id != ‘0000000000000000′ AND router_id IS NOT NULLSTRING AND NOT (a_operations = ‘queue’))

*************************************************************
DQL to uninstall workflows
Retrieve the workflow that needs to be uninstalled

SELECT r_object_id, object_name, r_definition_state FROM dm_process WHERE object_name = ‘‘

Change the definition of the workflow to DRAFT (0 = Draft)
UPDATE dm_process OBJECT SET r_definition_state = 0 WHERE r_object_id = ‘‘
Change the definition of the activities corresponding the above
workflow to DRAFT (0 = Draft)
UPDATE dm_activity OBJECT SET r_definition_state = 0 WHERE r_object_id IN (SELECT r_object_id FROM dm_activity WHERE r_object_id IN (SELECT r_act_def_id FROM dm_process WHERE r_object_id = ‘‘))

Verify that all activities are in the DRAFT state
SELECT object_name, r_definition_state FROM dm_activity WHERE r_object_id IN (SELECT r_act_def_id FROM dm_process WHERE r_object_id = ‘‘)

*****************************************************
How do I get the complete comments of a workflow?
Note: 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 are 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.

Following the above steps, you will be able to find all
the comments carried by a specific workflow instance.
One other way to get this information is to enable the
session DMCL trace and look into the log file.

****************************************************
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)) order by
r_creation_date desc

**************************************************
DQL to list worrkflow information

select task_name, task_state, actual_start_date,dequeued_date
from dmi_queue_item where router_id = ‘workflowId’
****************************************************
To know the workflow and their respective workflows

select *,r_act_name FROM "dm_workflow"
******************************************************
This will be used to clear inbox

delete dmi_queue_item objects where delete_flag=0
******************************************************