Sample DQLs

Introduction to Documentum Query Language (DQL)

Documentum Query Language (DQL) is a powerful query language specifically designed for interacting with the Documentum content management system. DQL is an extension of the Structured Query Language (SQL), tailored to efficiently manage, search, and manipulate documents and data within the Documentum repository. Its syntax and operations are optimized to work seamlessly with Documentum’s underlying data model, enabling users to perform complex document and data management tasks with ease.

Key Features of DQL:

• Rich Query Capabilities: DQL provides a comprehensive set of commands that allow for advanced querying, including document retrieval, metadata searches, and full-text search capabilities. It supports a wide range of SQL-like syntax, such as SELECT, INSERT, UPDATE, DELETE, and JOIN operations, but with extensions to accommodate Documentum-specific features.
• Content Management Operations: Beyond traditional query operations, DQL offers specialized commands for managing the lifecycle of documents, including version control, permission management, and workflow operations. This makes it a versatile tool for administrators and developers alike to automate and streamline content management processes.
• Efficient Data Access: DQL is designed to interact directly with the Documentum content server, providing efficient access to documents and their metadata. It leverages Documentum’s indexing and storage mechanisms to perform fast and efficient searches across vast repositories of documents.
• Customization and Extensibility: With DQL, developers can create custom queries to meet specific application requirements. This includes the ability to define virtual documents, execute stored procedures, and integrate with external databases, offering a flexible and powerful toolset for custom development.

Document Retrieval

• Retrieve documents with specific metadata

SELECT object_name, 
r_creation_date 
FROM dm_document 
WHERE title = 'Annual Report'

• Full-text search within documents

SELECT object_name 
FROM dm_document SEARCH DOCUMENT 
CONTAINS 'budget forecast'

• Join operations to fetch related documents

SELECT doc.object_name, 
folder.r_folder_path
FROM dm_document doc, 
dm_folder folder
WHERE doc.i_folder_id = folder.r_object_id AND 
folder.object_name = 'Finance'

Selecting Object attributes

These are sample queries which will select one or more attributes from objects and a good demonstration of different types of 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

This will return object id and name of all the objects that was created between today and 01-01-2008

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')

• Usage of DATEDIFF Function

This query will return all dm_documents that are created between today and the last 2 weeks

SELECT * 
FROM dm_document 
WHERE 
DATEDIFF(week, "r_creation_date" , DATE(TODAY))<=2

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.

DATEDIFF(date_part, date1, date2)

• 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

Object type Modification

Object types are an important facet of Documentum as they define the structure and behavior of content stored within the repository. They enable users to specify attributes, methods, and type hierarchies that reflect the relationships and nature of the stored content. This flexible categorization and management system supports complex content management scenarios and ensures that data is organized, accessible, and secure according to business requirements, allowing for the management of diverse document types. These scripts illustrates how to create or modify a Object type using DQL

• Create a Simple Object Type
  This script creates an object type mucustomtype with supertype as dm_document 

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

• 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)
  • Data Type Changes: You cannot change an attribute’s data type after it has been created.
  • Inheritance Impact: Modifications must not disrupt the inheritance hierarchy of object types.
  • Permissions: Changes are subject to the permissions set on the object type.

ALTER TYPE mycustomtype
MODIFY firstname string (121)

• Modify a Simple Object Type (Deleting an Attribute)

ALTER TYPE mycustomtype drop weight

• Dropping an Object type

DROP Type "my_custom_object"

• To Find all attributes of an Object Type

DESCRIBE mycustomtype

• Delete All Objects of an Object type

DELETE my_custom_object (all) objects

Object Modification

• Create a new Object and set its attributes
  (In this example phone is a repeated attribute, age is a integer and firstname and country are strings)

CREATE
mycustomtype OBJECT
SET "firstname" = 'Hello World',
SET "country"= 'US',
SET "age"=20 SET phone [0]= '1111111111'

• 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[0]='value'
WHERE r_object_id='09xxxxxx'

• Delete an Object from repository

DELETE
dm_document object
WHERE object_name='object_name'

Administrative Tasks

• Retrieve user information

SELECT user_name, 
user_privileges 
FROM dm_user 
WHERE user_name = 'jdoe'

• 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'

• To Return only specified number of Records
  This RETURN_TOP hint returns the number of records specified.

SELECT  * 
FROM  dm_sysobject 
ENABLE  (RETURN_TOP 10)
#In this example, it will return only 10 records.

 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 workaround to continue with this query is typing go and pressing Enter again.

DQL> select * from dm_document

2 >go