Accessing Documentum database tables

Objects and their Tables

As shared earlier, everything in Documentum(Almost everything as far as I understand) is an object and the best thing is there is always tables for each object.

All the tables created by Documentum internally usually start from dm(ex dm_document, dm_sysobject etc.)

Registered Table

We can also create a table as per our requirement in any repository, however if you want to access it using code(any customization or Configuration) you will have to register it first. That way only Documentum will know there is a table with this name. These tables are know as registered tables. Registered tables can only be accessed within the repository these are registered.

Accessing Tables

The object details can be accessed using dql using tools, like dqman or samson as their structure allows to interact with content in the perfect way. However these object details can also be accessed using tools like sql developer which uses sql to interact.

SQL vs DQL table difference

sql interacts in a different way than dql. In sql it usually devides all tables in two parts by appending _sp and _rp.
Ex. For accessing dm_document table using sql, you need to fire query to "dm_document_sp" and "dm_document_rp" tables. (here "rp" stands for repeating) and usually contains those data or attributes which can be multiple for single object.

For example(just an example, this object or table might not exists), lets say there is one person object 'abc', and there is a table for the object 'dm_persons'.
Usually every one can have multiple names(like nick names). if we want to save all names in sql, we will have to put multiple rows for 'abc', this way each row will have 'abc' with unique identifier and will have separate nicknames on separate rows

abc - nickname1
abc - nickname2
.
.

However in Documentum it is handled by allowing multiple values in single column(if the attribute is set as repeating).

Now if you simply fire the following query in dqman()
select * from dm_persons where name = ''abc".

This will return all the values, you can try dumping as well.

However for checking all possible values using sql, you will have to fire 2 seperate queries
select * from dm_persons_sp where name = ''abc"
select * from dm_persons_rp where name = ''abc"  :::  this will return repeating attributes.

usually the common attribute in both "_rp" and "_sp" table is r_object_id as it can uniquely identify each object.

Note: DQL queries can be easily converted to sql queries. Just login to DA(Documentum Administrator, Select Tools -> dql editor), Enter the dql query in the query box, check the checkbox 'Show the sql', and execute the query.
It will return the result of the executed query along with the sql equivalent query.

Commonly used tables are

dm_user - stores user objects(can also be accessed using Documentum Administrator application)
dm_document - store Document objects
dm_folder - stores all folder objects.
dm_jobs - store Job Objects
dm_acl -stores ACL(permission set) objects
dm_groups - stores group objects

Visit Blog Index.