Documentum DQL cheatsheet

DQL is SQL-shaped and object-aware. Default SELECT on versioned types returns the CURRENT version only. Use (ALL) when you need the whole tree. Paste a statement into the DQL formatter to pretty-print it.

SELECT from common types

select r_object_id, object_name, r_creation_date
from dm_document
where object_name like 'Invoice%'

select r_object_id, object_name, r_folder_path
from dm_folder
where object_name = 'Temp'

select user_name, user_os_name, user_state, user_group_name
from dm_user
where user_state = 0

select group_name, owner_name, group_class
from dm_group
where group_name = 'docu'

select object_name, owner_name, r_accessor_name, r_accessor_permit
from dm_acl
where object_name = 'dm_4500_read'

select name, super_name
from dm_type
where name = 'dm_document'

select object_name, method_name, a_last_completion, is_inactive
from dm_job
where is_inactive = false

select r_object_id, parent_id, set_file, full_format
from dmr_content
where parent_id = '09xxxxxxx'

Limit rows

select r_object_id, object_name
from dm_document
enable(return_top 10)

FOLDER and TYPE

select r_object_id, object_name
from dm_document
where folder('/Temp', descend)

select r_object_id, object_name
from dm_sysobject
where folder('/Temp')

select r_object_id, object_name, r_object_type
from dm_sysobject
where folder(id('0bxxxxxxx'), descend)
and type(dm_document)

FOLDER('/path') is that folder only. DESCEND includes subfolders. TYPE(dm_document) restricts to that type and its subtypes. Path is /cabinet/folder/....

Dates

select r_object_id, object_name, r_creation_date
from dm_document
where r_creation_date > date('01/01/2024 00:00:00')

select r_object_id, object_name
from dm_document
where datediff(day, r_creation_date, date(now)) <= 7

select r_object_id, object_name
from dm_document
where datediff(week, r_creation_date, date(today)) <= 2

DATEDIFF(date_part, date1, date2) is date2 - date1. Units: year, month, week, day. Return type (int vs float) depends on the underlying RDBMS.

Repeating attributes

select r_object_id, object_name, r_version_label
from dm_document
where any r_version_label = 'CURRENT'

select r_object_id, object_name
from dm_document
where any keywords = 'urgent'
and folder('/Temp', descend)

Without ANY, a predicate on a repeating attribute is easy to get wrong. Prefer ANY attr = 'value'.

IDs you actually filter on

select r_object_id, i_chronicle_id, i_folder_id, r_version_label
from dm_document
where r_object_id = '09xxxxxxx'

select r_object_id, r_version_label
from dm_document (all)
where i_chronicle_id = '09xxxxxxx'

i_folder_id is repeating (all folders the object is linked to). See object IDs.

Current user

select user_name, user_os_name, user_state
from dm_user
where user_name = user

UPDATE / DELETE / CREATE

update dm_document objects
set subject = 'restored'
where r_object_id = '09xxxxxxx'

create dm_document object
set object_name = 'hello.txt'
set title = 'hello'

delete dm_document objects
where r_object_id = '09xxxxxxx'

Registered tables

REGISTER TABLE publishes an RDBMS table as dm_registered so DQL can see it. Query with owner prefix. You need access on the registered-table object (typically at least READ to use it).

register table dm_dbo.my_lookup (
  code string(32),
  label string(128)
)

select code, label
from dm_dbo.my_lookup
where code = 'US'

Habits that save you

• Do not SELECT * on large types without a tight WHERE. Name the columns.
• Add ENABLE(RETURN_TOP n) while exploring.
• CURRENT-only is the default; (ALL) for history, i_chronicle_id for the tree.
• Quote mixed-case / reserved names. Lowercase unquoted identifiers are the usual style in these notes.
• ACL grants are DFC work, not a DQL hobby. See ACL permissions.