OpenText Documentum – Important DQL queries & DQL Editors

Documentum Query Language(DQL) is a derivative of SQL which can be used to interact with Documentum repository. DQL is used to query Documentum which is a content management system used to create, manage, deliver, and archive all types of content. 

DQL uses syntax that is a super set of ANSI-standard SQL (Structured Query Language) DQL statements operate on objects and sometimes on tables/rows but SQL statements operate only on tables/rows. In fact, part of your DQL statements are translated automatically into SQL before being executed by the eContent Server

For more information on DQL, please refer to latest version of Content Server DQL Reference guide

How DQL query works?

A DQL query is sent to the eContent Server using one of four API methods (readquery, execquery, query, or cachequery)

A DQL engine on eContent Server generates
– SQL queries for the RDBMS
– Verity queries for the full-text search engine

The results of the query are stored on the server in a non-persistent collection object. Typically, a Documentum client will automatically present the results of the query to the user in some useful way. Alternatively, a Developer may want to use the resulting collection object or manipulate the results programming languages like Java.

DQL Editors

• IDQL & IAPI Utility: IDQL (and IAPI) is available on machine where Content Server is installed under ‘bin’ directory(<$DOCUMENTUM>/product/<version>/bin OR <$DM_HOME>/bin directory).
• Documentum Administrator: The DQL Enter Query page enables you to test whether a DQL SELECT statement returns the expected values. Use this page as a tool for testing DQL. The number of rows returned by a DQL statement is limited based on the width of the rows requested. The query results may be truncated. When this happens, a warning message appears.

1. Select Tools > Dql Editor.
2. Type the query in the text box.
3. To display the SQL statement produced by the query, select Show the SQL.
4. Click Execute.The query results are returned.

Object Related DQL queries

These examples are meant to give you a head-start on the basic DQL syntax.

Create a Object Type

This script creates an object type edoccustomtype with supertype as dm_document

CREATE TYPE "edoccustomtype" (firstname string(64), country string(64) , age int, height double, phone string(10) REPEATING) WITH SUPERTYPE "dm_document" PUBLISH

Modify a Object Type: Add new Attributes

ALTER TYPE "edoccustomtype" Add lastname string (60), height double, weight double PUBLISH

Modify a Object type: Modify existing attribute

There are many constraints on modifying an existing attribute Refer to DQL Manual for more information

ALTER TYPE edoccustomtype MODIFY firstname String (200)

Modify a Simple Object Type: Delete an Attribute

ALTER TYPE edoccustomtype drop weight

Dropping an Object type

DROP Type "edoc_custom_object"

Delete all Objects of an Object type

Delete all objects

DELETE edoc_custom_object objects

Delete all versions of of all objects

DELETE edoc_custom_object (all) objects

Find all attributes of an Object Type

DESCRIBE edoccustomtype

Create a new Object and set its attributes

CREATE edoccustomtype OBJECT SET firstname = 'First Name', SET  country= 'US', SET  age=30 SET phone [0]= '12345678'

phone is a repeated attribute, age is a integer and firstname and country are strings

Update an existing Object’s Attribute

UPDATE dm_document object SET object_name = 'new_name' WHERE object_name = 'old_name'

Add a value to an existing object’s repeated attribute

UPDATE dm_document objects SET keywords[0]='value' WHERE r_object_id='09xxxxxx'

Delete an Object from repository

DELETE dm_document object WHERE object_name='object_name'

Select Single value attributes of an Object with single attribute in where clause

SELECT r_object_id, object_name FROM dm_document WHERE object_name='test'

Select a Repeated Attributes of an Object with repeated attribute in where clause

SELECT r_version_label FROM dm_document WHERE any r_version_label in ('CURRENT')

Find all versions of an object

SELECT r_object_id, r_version_label FROM dm_document (ALL) WHERE object_name='MyDocument'

Usage of Dates in Where Clause

SELECT r_object_id, object_name FROM dm_document WHERE r_modify_date > DATE('01/01/2008 00:00:00','MM/DD/YYYY hh:mm:ss') AND r_modify_date < DATE('TODAY')<br>(This will return object id and name of all the objects that was created between today and 01-01-2008)

Usage of DATEDIFF Function

DATEDIFF(date_part, date1, date2) – DATEDIFF function returns the number that represents the difference between given two dates. Valid values for date_part are a year, month, week, and day and date1 is subtracted from date2.

SELECT * FROM dm_document WHERE DATEDIFF(week, "r_creation_date" , DATE(TODAY))<=2<br>(This query will return all dm_documents that are created between today and the last 2 weeks)

Usage of As in the Query

SELECT object_name as name FROM dm_document WHERE r_object_id='09xxxxxx'

Usage of Distinct

This returns all the unique object names of type dm_document.

SELECT DISTINCT object_name FROM dm_document

Usage of NULLSTRING

This Query will return Object name of all the objects that have my_attributes as blank

SELECT object_name FROM my_object_type WHERE my_attribute is NULLSTRING

This Query will return Object name of all the objects that have some values in my_attributes (non-blanks)

SELECT object_name FROM my_object_type WHERE my_attribute is not NULLSTRING

Query to search a document (with full-text indexing )

SELECT * FROM dm_document SEARCH document contains 'test'

Find all groups a user belongs to

SELECT group_name FROM dm_group WHERE any users_names in ('user_name')

Find Folder Path of an Object

SELECT r_folder_path FROM dm_folder WHERE r_object_id in (SELECT i_folder_id FROM dm_document WHERE object_name='object_name')Find Objects those have Same (Duplicate) Name

SELECT object_name, count(*) FROM dm_document GROUP BY object_name HAVING count (*) > 1 ORDER BY object_name

Find all locked object that was locked one day before

SELECT r_object_id, r_lock_date FROM dm_document WHERE r_lock_owner is not NULLSTRING and r_lock_date is NOT NULLDATE and r_lock_machine is not NULLSTRING and DATEDIFF(day, r_lock_date, DATE(TODAY))<=1 ORDER BY r_lock_date

To Return only specified number of Records

SELECT * FROM dm_sysobject ENABLE (RETURN_TOP 10)<br>This RETURN_TOP hint returns the number of records specified.

This query will return only 10 records.

User & Group Related DQL Queries

Find all Users created after a specific day

SELECT user_name, user_os_name, user_address, user_group_name, r_modify_date FROM dm_user WHERE r_modify_date > date('12/16/2008 00:00:00')

Admin & System Related DQL Queries

Query to find out the log location of Documentum Content server

SELECT file_system_path FROM dm_location WHERE object_name='log'