In this blog post I want to sharing some useful DQL and IAPI queries that I am using all the time. They are more dedicated to Documentum support/debug and grouped by components. In order to use them, I recommend Qdman: it is the best tool I know to use DQL and IAPI scripts.

1. xPlore

It regroups both the search engine and the index management.

DQL Dsearch

select * from dm_document search document contains ‘manual.doc’


This query will perform a search just as if you put the ‘manual.doc’ in the search field of DA. It can be used to check if the dsearch is working fine and if the indexing has been performed correctly. If not, it will return 0 results for a document that you know does exist in the docbase.

Index Waiting

select count(*) as awaiting_15 FROM dmi_queue_item a, dm_sysobject (all) b WHERE b.r_object_id = a.item_id AND’dm_fulltext_index_user’ AND date_sent > date(now)-(15*1/24/60) AND (a.task_state is NULL or a.task_state = ‘ ‘)


This query will return the number of items waiting to be indexed since 15 minutes. The parameter can be changed to 60 minutes or whatever, you just have to change the ’15’ in bold in the previous query.

Index In Progress

select count(*) as in_progress_15 FROM dmi_queue_item a, dm_sysobject (all) b WHERE b.r_object_id = a.item_id AND’dm_fulltext_index_user’ AND date_sent > date(now)-(15*1/24/60) AND a.task_state = ‘acquired’


This one is similar to the previous, but it returns the number of ‘in progress’ indexing requests. Note that the parameter can still ne changed.

Index By State

select task_state,count(*) from dmi_queue_item where name = ‘dm_fulltext_index_user’ group by task_state


This query lists the number of indexes by state:

  • blank -> awaiting indexing
  • aquired -> in progress
  • warning
  • error
  • done
Delete Indexing Request

delete dmi_queue_item object where item_id=’09xxxxxxxxxxxxxx’


Sometimes I noticed there are indexing requests on deleted documents. In fact, it can happen if someone saved a document, then deleted it right after. The indexing request remains in the queue for life. Thus, you may want to delete it. First, check if the file is deleted by running the IPAI: dump,c,09xxxxxxxxxxxxxx. If an error occurs telling the document doesn’t exist anymore, you can delete it.

Index Agent Info

select fti.index_name,iac.object_name as instance_name from dm_fulltext_index fti, dm_ftindex_agent_config iac where fti.index_name = iac.index_name and fti.is_standby = false and iac.force_inactive = false


This query returns your configured index agent information. It is useful for the IAPI command returning the index agent status (see below).

Index Agent Status



This script returns the Index Agent Status (Running, Stopped, and so on). Note that you have to replace the Indey Agent information in the first line by your Index Agent. You can get these information thanks to the DQL query above.

Manually Queue Index Request



This one simply puts an indexing request in the queue. You have to replace 09xxxxxxxxxxxxxxx by the r_object_id of the document you want to queue.

Display Dsearch Port

cat $DOCUMENTUM/dsearch/admin/ | grep port


For this one you have to go to the xPlore server, it shows the configured dsearch port.

2. Rendering (ADTS)

The following queries concern the rendition component. It regroups the rendition queue check and the way to manually ask a rendition through IAPI.

Manually Queue Rendering Request



As the indexing request, this one puts a PDF rendition request in the queue. It can be useful when scripting or in DFC programs.

Rendering By State

select task_state,count(*) from dmi_queue_item where name = ‘dm_autorender_win31’ group by task_state


This returns the rendition requests by state.

Rendering Queue

select * from dmi_queue_item where name =’dm_autorender_win31′ order by date_sent desc


This query returns all documents present in the rendering queue. That means all document waiting for rendition.

Rendition Failed

select r_object_id,item_id,name,item_name,date_sent from dmi_queue_item where event =’DTS’ order by date_sent desc


This Query returns the failed renditions. Be aware of the date_sent field, because this queue is not cleared. This means that if a rendition request failed 3 times in a row and succeed the last time, there will be 3 rows in the failed queue, but the rendition did succeed. So you should verify that the rendition did succeed and if so, you can delete the row form of the failed queue.

Check Rendition Successful

select r_object_id from dm_document where object_name=’DOCUMENT‘ and exists(select * from dmr_content where any parent_id=dm_document.r_object_id and full_format=’pdf’)


This query checks if a rendition is present for the given DOCUMENT name. If the pdf rendition exists, it returns its r_object_id. If no rendition is present for the given document, it returns nothing.

3. Audit Trail

Failed Login Since 1h

select user_name,count(*) as logon_failure from dm_audittrail where event_name=’dm_logon_failure’ and time_stamp > (date(now)-(60*1/24/60)) group by user_name order by 2


This query displays the number of failed logons in the docbase per user since 60 minutes. The parameter 60 can be changed.

Purge Logon Failure

EXECUTE purge_audit WITH delete_mode=’PREDICATE’, dql_predicate=’dm_audittrail where event_name=”dm_logon_failure”’


This statement purges the audit trail queue by deleting all logon failure entries. Be aware that it can take a while depending on the number of entries you have.

Number Of Logon Failure

select count(*) as logon_failure from dm_audittrail where event_name=’dm_logon_failure’


This query simply shows the number of logon failures in the queue.

4. Miscellaneous

IAPI Purge Caches



This query flushes caches, it can be used when trying to install ADTS dars and fails due to version mismatch.

Check ADTS Installer Version

java -cp adtsWinSuiteSetup.jar DiShowVersion
Multi-installer Suite 6.7.2000.42
Installer-Version: 6.7.2000.42 build 1
Installer-Build-Date: 1/11/13 12:28 AM


Go to the ADTS installer directory and issue this query. It shows the version of the installer.

Encrypt dm_bof_registry Password



This one encrypts the dm_bof_registry password in order to use it in Not that the encryption process is different on xPlore and ADTS but you can use it on the content server and all DFC related programs. Replace the PASSWORD in the query by your clear password.