Documentum Query Language is a derivative of SQL which can be used to interact with Documentum repository. These examples are meant to give you a head-start on the basic DQL syntax.
For more information on DQL, please refer to Content Server DQL Reference guide
Object type Modification
These scripts illustrate how to modify an Object type using DQL
Create a Simple Object Type
CREATE TYPE "mycustomtype" (firstname string(64), country string(64) , age int, height double, phone string(10) REPEATING) WITH SUPERTYPE "dm_document" PUBLISH
(This script creates an object type mucustomtype with supertype as dm_document )
Modify a Simple Object Type (Adding new Attributes)
ALTER TYPE "mycustomtype" Add lastname string (64), height double, weight double PUBLISH
Modify a Simple Object type (Modify existing attribute)
ALTER TYPE mycustomtype MODIFY firstname string (121)
(There are many constraints on modifying an existing attribute Refer to DQL Manual for more information)
Modify a Simple Object Type (Deleting an Attribute)
ALTER TYPE mycustomtype drop weight
Dropping an Object type
DROP Type "my_custom_object"
Delete All Objects of an Object type
DELETE my_custom_object (all) objects
To Find all attributes of an Object Type
These sample scripts illustrate various object manipulations.
Create a new Object and set its attributes
CREATE mycustomtype OBJECT SET "firstname" = 'Hello World', SET "country"= 'US', SET "age"=20 SET phone = '1111111111'
(In the above example 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'
Adding a value to an existing object’s repeated attribute
UPDATE dm_document objects SET keywords='value' WHERE r_object_id='09xxxxxx'
Delete an Object from repository
DELETE dm_document object WHERE object_name='object_name'
Selecting Object attributes
These are sample queries which will select one or more attributes from an Object and illustrates different where clauses
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')
To find all versions of an object (Usage of All)
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')
(This will return object id and name of all the objects that was created between today and 01-01-2008)
Usage of DATEDIFF Function
SELECT * FROM dm_document WHERE DATEDIFF(week, "r_creation_date" , DATE(TODAY))<=2
(This query will return all dm_documents that are created between today and the last 2 weeks)
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.
Usage of As in the Query
SELECT object_name as name FROM dm_document WHERE r_object_id='09xxxxxx'
Usage of Distinct
SELECT DISTINCT object_name FROM dm_document
This returns all the unique object names of type dm_document.
Usage of NULLSTRING
SELECT object_name FROM my_object_type WHERE my_attribute is 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 not NULLSTRING
This Query will return Object name of all the objects that have some values in my_attributes (non-blanks)
Query to search a document (with full-text indexing )
SELECT * FROM dm_document SEARCH document contains 'test'
Some Useful DQL Queries
These are a collection of some useful queries.
Finding all groups a user belongs to
SELECT group_name FROM dm_group WHERE any users_names in ('user_name')
To 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')
To 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 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')
Query to find out the log location of Documentum Content server
SELECT file_system_path FROM dm_location WHERE object_name='log'
DQL Hint Usages
These samples will illustrate usages of Various DQL hints
To Return only specified number of Records
SELECT * FROM dm_sysobject ENABLE (RETURN_TOP 10)
This RETURN_TOP hint returns the number of records specified.
In this example, it will return only 10 records.
More to come soon
Tip on Samson
Many of you must be using Samson to do your DQL queries. In Samson, if you forgot to terminate the query with a semicolon ( ; ) you will get a prompt like shown below
DQL> select * from dm_document
The workaround to continue with this query is typing go and pressing Enter again
DQL> select * from dm_document
2 > go
9 thoughts on “Sample DQLs”
I am working in a Documentum Support Team and this DQLs are really helpful to me. Thanks. Prashil.
Could you pl. help? I am a total newbie at this and found your extremely useful blog with related matters and hope you can help. I tried to reassign a group to a new group and the job worked except that it did not update one folder. This one folder still has the old group and the new group in the permissions list. Any effort to remove it fails with this error: ‘dm_acl_e_bad_state’; should I remove the old group from the acl?
You get DM_ACL_E_BAD_STATE when you have an ACL that points to a group that dont exist,
Brand new to Documentum. Can you please let me know if there is way to convert an ASCII file to pdf and file to Documentum so that a user and search on it. Also is there any way to remove certain characters from ASCII file before creating PDf file?
You can create a PDF rendition of a Acsii file, for that you need a PDF converter plugin, for searching within the contents of a file you should have full text search enabled on that content server. removing characters from ASCII files you can use create a TBO (Type based object)
I have the following query to list all documents and folders in a cabinet.
select object_name,r_object_type from dm_sysobject where folder(‘/Test’) and r_object_type IN (‘dm_document’,’dm_folder’)
But how can I list folders and documents in the same way as it is listing in Webtop ( folders are listed first in sorted order, then documents are listed in sorted order)
Is it possible using DQL query to see whether an attribute name for an object type is a repeating attribute or single attribute?
Any suggestions would be helpful.