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 a.name=’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 a.name=’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

apply,c,NULL,FTINDEX_AGENT_ADMIN,NAME,S,DOCBASE_ftindex_01,AGENT_INSTANCE_NAME,S, HOST._9200_IndexAgent,ACTION,S,status
next,c,q0
get,c,q0,status
close,c,q0

 

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

queue,c,09xxxxxxxxxxxxxx,dm_fulltext_index_user,dm_force_ftindex

 

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/xplore.properties | 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

queue,c,09xxxxxxxxxxxxxx,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf

 

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

flush,c,ddcache,dm_type
flush,c,ddcache,dmi_type_info
flush,c,ddcache,dm_aggr_domain
flush,c,ddcache,dm_domain
flush,c,ddcache,dm_dd_info
flush,c,ddcache,dm_nls_dd_info
flush,c,ddcache,dm_foreign_key
flush,c,persistentcache
publish_dd,c
reinit,c

 

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

java com.documentum.fc.tools.RegistryPasswordUtils PASSWORD

 

This one encrypts the dm_bof_registry password in order to use it in dfc.properties. 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.

Post Views: 9,293
Thumbnail [60x60]
by
Middleware Team