DQL to list workitems of a document

select i.r_object_id, a.object_name, i.r_performer_name, wf.object_name as work_flow_name, wf.r_object_id as workflow_id
from dmi_workitem i, dm_activity a, dm_workflow wf
where i.r_act_def_id = a.r_object_id
and i.r_workflow_id = wf.r_object_id
and wf.r_object_id in
(select r_workflow_id from dmi_package
where any r_component_id in
(select r_object_id from dm_sysobject (all)
where i_chronicle_id in
(select i_chronicle_id
from dm_sysobject where r_object_id in (SELECT r_object_id FROM dm_document
WHERE object_name LIKE 'N0050675%'
)
)
)
)
order by wf.r_object_id

Documentum Transformation Service (DTS) rendition fails intermittently

Once in a while the DTS to transform documents would fail without any special reason.
This error appears in the DTS logs: "Error: The remote server machine does not exist or is unavailable"

A machine reboot is needed in order to get services back to normal. However, this does not completely resolve the problem and the same thing happens again after some time.

Workaround:
1. Open the registry editor (Start->Run->Regedit).
2. Navigate to the following key HKEY_LOCAL_MACHINE\SOFTWARE\Adlib\Adlib FMR\Adlib Express\Default.
3. On the right hand pane, set the value of AvailMemUsageLimit to 0 (Double click and type the value in).
4. Stop all services (adlib and CTS):
- Documentum Content Transformation Services (also stop Documentum Content Transformation Monitor Services)
- Documentum CTS Admin. Agent
- Adlib Exponent Connector
- Adlib Exponent Manager
- Adlib Express Server
- Adlib FMR

5. Restart the CTS services(cts will restart adlib)
- Documentum Content Transformation Monitor Services (Monitor will restart Documentum Content Transformation Services)
- Documentum CTS Admin. Agent

DQL to get all renditions associated with a document

Was trying to deleted unwanted renditions in Documentum when I came across this annoying message:
"You cannot delete the primary rendition"

So it happens that I have TWO pdf renditions for one document and I am unable to remove the older one as both renditions had the same name. I ended up having to delete it using DQL Editor.

1. Get the r_object_id of document
select * from dm_document where object_name = 'XXXXX.doc'

2. Get all renditions associated with that document
select * from dmr_content where any parent_id='09000578802351ee' and full_format='pdf'

3. Identify the non-primary rendition(s). The Physical path is probably pointing to some cache directory in the results from (2).

4. To remove any unwanted renditions:
DELETE dmr_content object WHERE r_object_id = '06000578802136ef'

DQL to get workflow of document

Given the document name XXX018.doc

SELECT r_object_id FROM dm_document WHERE object_name = 'XXX018.doc'
or
SELECT r_object_id FROM dm_document WHERE object_name LIKE 'XXX018%'

select * from dm_workflow
where r_object_id in (select r_workflow_id
from dmi_package
where any r_component_id in (select r_object_id
from dm_sysobject (all)
where i_chronicle_id in (select i_chronicle_id
from dm_sysobject where r_object_id='09000578802335ad'))) and r_runtime_state='1'

DQL to find details based on workflow item

Based on this information, get the workflow details and document.
Started workitem: "4a000578800d9915" in docbase "ecmdocbase"

1. Get workflow id from package id
select r_workflow_id from dmi_workitem where r_object_id = '4a000578800d9915'

2. Get workflow details
select * from dm_workflow where r_object_id = '4d0005788004d500'

3. Get the document id from workflow package
select r_component_id from dmi_package where r_workflow_id = '4d0005788004d500'

4. get the document name
select * from dm_document where r_object_id = '0900057880210948'