Documentum DQL Queries

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

Click here to download this study note(PDF)