{"id":2175,"date":"2014-08-07T08:37:00","date_gmt":"2014-08-07T06:37:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/"},"modified":"2014-08-07T08:37:00","modified_gmt":"2014-08-07T06:37:00","slug":"documentum-useful-queries-dqliapi","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/","title":{"rendered":"Documentum: some useful queries (DQL\/IAPI)"},"content":{"rendered":"

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.<\/p>\n

1. xPlore<\/h3>\n

It regroups both the search engine and the index management.<\/p>\n

\nDQL Dsearch<\/legend>\n

select * from dm_document search document contains ‘manual.doc’<\/fieldset>\n

 <\/p>\n

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.<\/p>\n

\nIndex Waiting<\/legend>\n

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<\/strong>*1\/24\/60) AND (a.task_state is NULL or a.task_state = ‘ ‘)<\/fieldset>\n

 <\/p>\n

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.<\/p>\n

\nIndex In Progress<\/legend>\n

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<\/strong>*1\/24\/60) AND a.task_state = ‘acquired’<\/fieldset>\n

 <\/p>\n

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.<\/p>\n

\nIndex By State<\/legend>\n

select task_state,count(*) from dmi_queue_item where name = ‘dm_fulltext_index_user’ group by task_state<\/fieldset>\n

 <\/p>\n

This query lists the number of indexes by state:<\/p>\n

    \n
  • blank -> awaiting indexing<\/li>\n
  • aquired -> in progress<\/li>\n
  • warning<\/li>\n
  • error<\/li>\n
  • done<\/li>\n<\/ul>\n
    \nDelete Indexing Request<\/legend>\n

    delete dmi_queue_item object where item_id=’09xxxxxxxxxxxxxx’<\/fieldset>\n

     <\/p>\n

    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.<\/p>\n

    \nIndex Agent Info<\/legend>\n

    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<\/fieldset>\n

     <\/p>\n

    This query returns your configured index agent information. It is useful for the IAPI command returning the index agent status (see below).<\/p>\n

    \nIndex Agent Status<\/legend>\n

    apply,c,NULL,FTINDEX_AGENT_ADMIN,NAME,S,DOCBASE_ftindex_01<\/strong>,AGENT_INSTANCE_NAME,S, HOST._9200_IndexAgent<\/strong>,ACTION,S,status
    \nnext,c,q0
    \nget,c,q0,status
    \nclose,c,q0<\/fieldset>\n

     <\/p>\n

    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.<\/p>\n

    \nManually Queue Index Request<\/legend>\n

    queue,c,09xxxxxxxxxxxxxx<\/strong>,dm_fulltext_index_user,dm_force_ftindex<\/fieldset>\n

     <\/p>\n

    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.<\/p>\n

    \nDisplay Dsearch Port<\/legend>\n

    cat $DOCUMENTUM\/dsearch\/admin\/xplore.properties | grep port<\/fieldset>\n

     <\/p>\n

    For this one you have to go to the xPlore server, it shows the configured dsearch port.<\/p>\n

    2. Rendering (ADTS)<\/h3>\n

    The following queries concern the rendition component. It regroups the rendition queue check and the way to manually ask a rendition through IAPI.<\/p>\n

    \nManually Queue Rendering Request<\/legend>\n

    queue,c,09xxxxxxxxxxxxxx<\/strong>,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf<\/fieldset>\n

     <\/p>\n

    As the indexing request, this one puts a PDF rendition request in the queue. It can be useful when scripting or in DFC programs.<\/p>\n

    \nRendering By State<\/legend>\n

    select task_state,count(*) from dmi_queue_item where name = ‘dm_autorender_win31’ group by task_state<\/fieldset>\n

     <\/p>\n

    This returns the rendition requests by state.<\/p>\n

    \nRendering Queue<\/legend>\n

    select * from dmi_queue_item where name =’dm_autorender_win31′ order by date_sent desc<\/fieldset>\n

     <\/p>\n

    This query returns all documents present in the rendering queue. That means all document waiting for rendition.<\/p>\n

    \nRendition Failed<\/legend>\n

    select r_object_id,item_id,name,item_name,date_sent from dmi_queue_item where event =’DTS’ order by date_sent desc<\/fieldset>\n

     <\/p>\n

    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.<\/p>\n

    \nCheck Rendition Successful<\/legend>\n

    select r_object_id from dm_document where object_name=’DOCUMENT<\/strong>‘ and exists(select * from dmr_content where any parent_id=dm_document.r_object_id and full_format=’pdf’)<\/fieldset>\n

     <\/p>\n

    This query checks if a rendition is present for the given DOCUMENT<\/strong> name. If the pdf rendition exists, it returns its r_object_id. If no rendition is present for the given document, it returns nothing.<\/p>\n

    3. Audit Trail<\/h3>\n
    \nFailed Login Since 1h<\/legend>\n

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

     <\/p>\n

    This query displays the number of failed logons in the docbase per user since 60 minutes. The parameter 60 can be changed.<\/p>\n

    \nPurge Logon Failure<\/legend>\n

    EXECUTE purge_audit WITH delete_mode=’PREDICATE’, dql_predicate=’dm_audittrail where event_name=”dm_logon_failure”’<\/fieldset>\n

     <\/p>\n

    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.<\/p>\n

    \nNumber Of Logon Failure<\/legend>\n

    select count(*) as logon_failure from dm_audittrail where event_name=’dm_logon_failure’<\/fieldset>\n

     <\/p>\n

    This query simply shows the number of logon failures in the queue.<\/p>\n

    4. Miscellaneous<\/h3>\n
    \nIAPI Purge Caches<\/legend>\n

    flush,c,ddcache,dm_type
    \nflush,c,ddcache,dmi_type_info
    \nflush,c,ddcache,dm_aggr_domain
    \nflush,c,ddcache,dm_domain
    \nflush,c,ddcache,dm_dd_info
    \nflush,c,ddcache,dm_nls_dd_info
    \nflush,c,ddcache,dm_foreign_key
    \nflush,c,persistentcache
    \npublish_dd,c
    \nreinit,c<\/fieldset>\n

     <\/p>\n

    This query flushes caches, it can be used when trying to install ADTS dars and fails due to version mismatch.<\/p>\n

    \nCheck ADTS Installer Version<\/legend>\n

    java -cp adtsWinSuiteSetup.jar DiShowVersion
    \nMulti-installer Suite 6.7.2000.42
    \nInstaller-Version: 6.7.2000.42 build 1
    \nInstaller-Build-Date: 1\/11\/13 12:28 AM<\/span><\/span><\/fieldset>\n

     <\/p>\n

    Go to the ADTS installer directory and issue this query. It shows the version of the installer.<\/p>\n

    \nEncrypt dm_bof_registry Password<\/legend>\n

    java com.documentum.fc.tools.RegistryPasswordUtils PASSWORD<\/strong><\/fieldset>\n

     <\/p>\n

    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.<\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":40,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[129,232,233,234,235],"type_dbi":[],"class_list":["post-2175","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","tag-documentum","tag-dql","tag-iapi","tag-queries","tag-useful"],"acf":[],"yoast_head":"\nDocumentum: some useful queries (DQL\/IAPI) - dbi Blog<\/title>\n<meta name=\"description\" content=\"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.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Documentum: some useful queries (DQL\/IAPI)\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-08-07T06:37:00+00:00\" \/>\n<meta name=\"author\" content=\"Middleware Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Middleware Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\"},\"author\":{\"name\":\"Middleware Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1\"},\"headline\":\"Documentum: some useful queries (DQL\/IAPI)\",\"datePublished\":\"2014-08-07T06:37:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\"},\"wordCount\":1212,\"commentCount\":2,\"keywords\":[\"Documentum\",\"DQL\",\"iapi\",\"queries\",\"useful\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\",\"name\":\"Documentum: some useful queries (DQL\/IAPI) - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-08-07T06:37:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1\"},\"description\":\"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.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Documentum: some useful queries (DQL\/IAPI)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1\",\"name\":\"Middleware Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g\",\"caption\":\"Middleware Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/middleware-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Documentum: some useful queries (DQL\/IAPI) - dbi Blog","description":"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.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/","og_locale":"en_US","og_type":"article","og_title":"Documentum: some useful queries (DQL\/IAPI)","og_description":"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.","og_url":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/","og_site_name":"dbi Blog","article_published_time":"2014-08-07T06:37:00+00:00","author":"Middleware Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Middleware Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/"},"author":{"name":"Middleware Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1"},"headline":"Documentum: some useful queries (DQL\/IAPI)","datePublished":"2014-08-07T06:37:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/"},"wordCount":1212,"commentCount":2,"keywords":["Documentum","DQL","iapi","queries","useful"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/","url":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/","name":"Documentum: some useful queries (DQL\/IAPI) - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-08-07T06:37:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1"},"description":"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.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/documentum-useful-queries-dqliapi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Documentum: some useful queries (DQL\/IAPI)"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1","name":"Middleware Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g","caption":"Middleware Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/middleware-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2175"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2175\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2175"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}