Sample DQLs


I know this page was not organized properly for a long time and it was hard for the users to get the most  out of this page. So I thought of revamping the layout of this page and make it more users friendly. What I am doing here is moving the queries into various groups.
Please note that these examples are meant only 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 illustrates how to modify a 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 a object type mucustomtype with super type 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 constrains on modifying an existing attribute Refer to DQL Manual for more information)

Modity 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 a Object type
DELETE my_custom_object (all) objects
To Find all attributes of an Object Type
DESCRIBE mycustomtype


Object Modification

These sample scripts illustrates 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 [0]= '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 a 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'


Selecting Object attributes

These are sample queries which will select one or more attributes from a Object and illustrates different where clauses

Select Single value attributes of a 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 a 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 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 has 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 has 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 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 a 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 were 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
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 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

2 >

The work around to continue with this query is typing go and pressing Enter again

DQL> select * from dm_document

2 > go

Advertisements

8 thoughts on “Sample DQLs

  1. plotus

    Hi Ajith,

    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?

  2. pavlinpatel

    Hello there.

    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?

  3. 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)

  4. abeishbabu

    Hi Ajith,

    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)

  5. abeishbabu

    Hi Ajith

    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.

  6. Pingback: Ресурсы, которые могут быть полезны по EMC Documentum | Советы самому себе

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s