dm_notes: Documentum Notes

June 5, 2008

Hierarchical list of Documentum types

Filed under: dql, notes — Tags: , , — Raj V @ 8:06 pm

Came across a simple (useful) query that displays the types and thier hierarchy.

DQL>describe hierarchy persistent.

Displays a hierarchial structure  of all “Persistent Objects”.

DQL> describe hierarchy dm_sysobject
Object hierarchy list
\ dmi_expr_code
\ dm_mount_point
\ dm_location
\ dm_docbase_config
\ dm_server_config
\ dm_policy
\ dm_registered
\ dm_folder
\ dm_cabinet
\ dm_xml_application
\ dm_category
\ dmc_topic
\ dmc_room
\ dmc_module
\ dmc_aspect_type
\ dmc_validation_module
P.S.: Found it through samson (Just below the toolbar : Query Topics -> Type Management + List tree of types known in docbase –> Generate Query)

Analyzing the above query, I gave a DQL to find out hierarchy of dm_sysobject and it works perfect. cool

DQL>describe hierarchy dm_sysobject

(lists hierarchically all the sub types of sysobject ).

This will be handy sometimes to see who all extend a custom object type.

Here is the DQL to find direct sub types of a given type
DQL> select name from dm_type where super_name =’dm_sysobject’;
(This query doesn’t list the indirect sub types)

December 28, 2007

DQL: List of groups a user belongs to ..

Filed under: dm_group, dql, notes — Tags: , , , — Raj V @ 9:44 pm

Here is the DQL to query the list of groups a user belongs to:

select group_name from dm_group where any i_all_users_names = ‘<user id>’;

This gives the list of all groups a user belongs to directly or indirectly.
i_all_users_names is a computed value.

If you want to query for list of groups the logged in user belongs to:

select group_name from dm_group where any i_all_users_names = USER ;

The USER is a place holder for the current logged in User.

Querying for ‘users_names’ attribute instead of i_all_users_names will return only the groups where the user is directly part of that group (no sub groups)

December 5, 2007

DQL to list of users in a group

Filed under: dm_group, dm_user, dql — Tags: , , — Raj V @ 5:42 pm

DQL to query the list of users in a group

  • select users_names from dm_group where group_name = ‘mygroup’;

DQL to query the list of active users (Users who can log in) in a group:

  • select user_name from dm_user where user_state = 0 and user_name in (select users_names from dm_group where group_name = ‘mygroup’);
    user_state of dm_user indicates the user’s current state.
    Valid alues are:

    • 0, indicating a user who can log in.
    • 1 indicating a user who cannot log in.
    • 2 indicating a user who is locked.

October 16, 2007

Object Fetch Vs DQL Fetch Performance

Filed under: dfc, notes — Tags: , , — Raj V @ 7:22 am

A Object Fetch call would retrieve all attribute information of the object from server, this information will then be cached on the client side DMCL cache.

A DQL query will only retrieve the attributes specified in the “select” statement of the query.

A dm_document object has around 70+ attributes. If you are only interested in few attributes of an object you should use a DQL statement to avoid retrieving unnecessary information, this becomes significant especially in a low bandwidth environment.

Object Fetch should used when most attributes of an object are wanted, and/or that attribute information is repeatedly needed in multiple places.

Note that a DFC Session.getObject() call is effectively a fetch call, you should avoid creating a IDfSysobject with session.getObject() just to look at a couple attributes of the object, use query.execute(iDfSession, IDfQuery_DF_READ_QUERY) and specify the attributes of interest in the select statement of the query instead.

October 5, 2007

Why do DQL queries sometime return blank duplicate rows?

Filed under: support note — Tags: , , — Raj V @ 12:39 pm

This may happen if you select from a repeating attribute (which is stored in the same underlying database table) and use the ORDER BY clause on the repeating column. Therefore, if one repeating attribute does not repeat as many times as another, it will have a “null” value for all rows where it has no value until it reaches the maximum number of rows for all repeating attributes of the object type.Note: Even if you specify “DISTINCT” in the query, there may appear to be duplicate rows, containing blank values as part of the repeating attribute column. However, this display is not the result of an error with the query. “DISTINCT” operates on all of the columns you specify within the “select” action; each of those columns in the result set, therefore is unique for the values in each row returned.

When executing a DQL query, sorting on a repeating value (e.g. “r_folder_path”) may cause “duplicates” of the objects to be returned such as those returned by the sample query below:

select object_name, r_folder_path from dm_folder ORDER BY r_folder_path or

select object_name, r_version_label from dm_document ORDER BY object_name

Returns “duplicate” rows of results in WorkSpace. The first half of the return values have the path field filled in. The second half of the returned rows do not. However, as explained above, the second half of the return can also be considered unique rows if the values in their corresponding columns are compared (e.g. a blank value is not the same as an actual value).

This is a side effect of the way DQL/SQL joins or maps the single and repeating-value tables when you employ the “ORDER BY” clause on the repeating field. The way DQL was intended is that every object in the Docbase has both an explicit “r_version_label” (e.g.: ‘draft’, ‘approved’, ‘new’) and an implicit “r_version_label” (e.g.: 1.0, 2.0, etc.) When the single and repeating tables are joined in a one-to-many relationship, at least two rows will result for each object.

To avoid this side effect, include in your “select” statement the “r_object_id” attribute and “order by r_object_id” clause. This pairing will force the master-detail relationship to be invoked since this attribute is common to both database tables and will result in only one row per object being returned. Notice that “r_object_id” in the example code below does not need to be first in either the “select” statement or the “order by” clause, but only be present somewhere in both elements:

select object_name, r_object_id, r_version_label from dm_document where owner_name = ‘charless’ order by object_name, r_object_id

This returns the object names, all sorted, with one object per row (no duplicates) and the “r_version” label information all concatenated onto one line. Instead of a row with “1.0” and another row with “CURRENT”, we now have “1.0,CURRENT” This is a feature of WorkSpace, which intelligently combines the results of the result set to remove duplicates if “r_object_id” is specified as an “order by” object.

Note: This only works if a single-valued attribute or “r_object_id” is first in the “order by” clause. If the repeating value is first, then all the values, including duplicates or blanks, are displayed in the result set.

Blog at