DQL – Documentum Session related queries/reports

Sample Queries:

• EXECUTE show_sessions
• EXECUTE list_sessions
• EXECUTE count_sessions
  • hot_list_size ->  No. of active content server sessions
  • warm_list_size -> Sessions that have been timed out on the server side
  • cold_list_size -> No. of free sessions.

Run the following script to see current sessions in the repository.

• $DM_HOME/bin/dmawk -f dm_who.awk <docbase> -U<username> -P<password>

Reference: https://community.emc.com/docs/DOC-9929

 

DQL: Audit Trail Report by Month

In an audit trail, I want to count the number of users who viewed (dm_getfile event) all the documents that belong to a particular object type every month. These documents are in a specific folder of a cabinet.

I made couple of assumptions in coming up with the DQL, but these are easy to adjust to suit specific needs. When looking for documents in a folder, I assumed the containment to be recursive. If you don’t need to look in the subfolders, just remove “, descend” in the folder() predicate below.

The other assumption I made was that the period of check was 1 calendar year. For example, create a report for 2009 by each month. Again, if the need is different, the just modify the date range condition.

Alter the following DQL, by replacing <OBJECT_TYPE>, <FOLDER_PATH>, <FOUR_DIGIT_YEAR> below.

SELECT DATETOSTRING(time_stamp, 'yyyy/mm') AS period, COUNT(distinct user_name) AS viewers FROM dm_audittrail WHERE object_type = '<OBJECT_TYPE>' AND event_name = 'dm_getfile' AND DATEFLOOR(year, "time_stamp") = DATEFLOOR(year, DATE('01/01/<FOUR_DIGIT_YEAR>', 'mm/dd/yyyy')) AND audited_obj_id IN ( SELECT r_object_id FROM dm_sysobject (all) WHERE folder('<FOLDER_PATH>', descend) ) GROUP BY DATETOSTRING(time_stamp, 'yyyy/mm')

A sample result from this query is shown below:

period	viewers
2009/01	3
2009/02	1
2009/04	6

 

DQL – Number of times an object is accessed

select count(distinct user_name) from dm_audittrail where event_name=’dm_fetch’ and audited_obj_id =’0bxxxxxxxxxxxxxx’

 

DQL – List of events registered in audit trail

select distinct event,r_object_id,registered_id from dmi_registry

 

DQL – All halted workflow items

select r_object_id from dmi_workitem where r_runtime_state in (3, 4, 5, 6) and r_auto_method_id <> ‘0000000000000000’

 

DQL – List of inbox items

select * from dmi_queue_item where name in (select user_name from dm_user where user_os_name = ‘<user id>’)

 

DQL – Check whether document is part of virtual document

SELECT object_name,r_object_id FROM dm_document WHERE r_object_id IN (SELECT parent_id FROM dmr_containment WHERE component_id = (SELECT i_chronicle_id FROM dm_document WHERE r_object_id = ‘<child-object-id>’))

 

DQL – Document without rendition

SELECT object_name,r_version_label,a_content_type,owner_name,r_creation_date,r_modifier,r_modify_date, r_object_id FROM clinical_document(all) WHERE r_object_id not in (select parent_id from dmr_content where rendition <> 0)

 

Brief about DQL – Documentum Query Language

Introduction to DQL –DQL is a super set of ANSI-standard SQL, which is used to create, manage and drop all documentum object types in Documentum.This is also used to register the RDBMS table in Documentum to manipulate all database functions by querying.

What is DQL?

DQL (Documentum Query Language) is a superset of SQL and which allows you to do very simple/complex queries in Documentum like

• Search objects using given properties
• Searches for Full text with the help of FT engine
• Queries the external database table by registering in to Documentum
• Does Complex queries like joins with Register table and Objects types

DQL supports most of the sql functions by its own syntax.DQL uses syntax that is a superset of ANSI-standard SQL (Structured Query Language). Infact, part of your DQL statements are translated automatically into SQL before being executed by the eContent Server

• DQL query is sent to the eContent Server using one of four API methods
readquery, execquery, query, or cachequery
• eContent Server generates executes this API methods which isactually calling the sql engine
– SQL queries for the RDBMS
– Verity queries for the full-text search engine

The results of the query are stored on the server in a non-persistent collection object. Typically, a Documentum client will automatically present the results of the query to the user in some useful way. Alternatively, a Developer may want to use the resulting collection object or
manipulate the results programmatically.

Select Statement

Select (list of properties to fetch)
From (list of object types to be search)

Using WHERE clause

Allows the search to be narrowed by imposing search conditions on your Query

select [all | distinct] value [as name] {,value [as name] } from source_list [(all)]
…
[where qualification ]

Eg 1:
SELECT object_name, title FROM dm_document WHERE FOLDER (ID(’folder id’)) AND title LIKE ’%sr%’

Searching for a document in given folder path or cabinets

The scope of the search can be specified as folder and cabinet in the where clause to provide a way to search the cabinet/folder hierarchy.
Eg 1:
SELECT object_name, title FROM dm_document WHERE FOLDER (‘/exact folder path’) AND title LIKE ’%SOP%’
Eg 2;
In case of not having folder id or exact folder path ,
SELECT object_name, title FROM dm_document WHERE FOLDER (‘/Cabinet name’,descend) AND title LIKE ’%SOP%’
Which searches the given documents with in the cabinet.

REGISTER Statement

• Registers an RDBMS table so that eContent Server can recognize the sql table
• Allows access to data in an existing RDBMS table that may be created to support additional application data part of another application owned by someone else
• Does not verify that the table or its columns exist
• All columns in a table need not be registered
• Creates a dm_registered object in the System cabinet which provides a description of the table and user access to it

REGISTER permissions
• Only the Docbase owner with a SUPERUSER privilege can register a table from
within Documentum.
• To register a table, you must have at least READ permission on the table (through the
RDBMS grant command).
• The eContent Server account (installation owner) needs the appropriate RDBMS
privileges on the table to perform different operations on rows in the table.

Steps to create Register Table

1.Register a RDBMS table by register keyword
REGISTER TABLE [owner_name.]table_name
(column_def {,column_def})
[[WITH] KEY (column_list)]
[SYNONYM [FOR] ‘table_identification’]

Eg: REGISTER TABLE johndoe.”remote1″ (“columnA” int)

Note: All columns in a table need not be registered.

2. Update appropriate permission to the users on the register table.

Eg:
Update dm_registered objects set owner_table_permit =15 set world_table_permit=15 set group_table_permit=15 where object_name=’remote1’

The above query will update maximum permission on the table.

SYNONYM
Use the SYNONYM clause to register RDBMS tables that have synonyms in the underlying tables.
SYNONYM FOR londonserver.londonremote.johndoe.myremotetable
After he registers the table, he issues the following SELECT statement:

SELECT * FROM johndoe.”remote1″

Content Server substitutes the actual table name for the synonym and the following
SQL is generated:
SELECT * FROM server.remote.johndoe.remotetable

Special note for Oracle platforms
If you create a database link between the database on which the Documentum repository
is installed and another database, and then the use SYNONYM feature to obtain
information from that second database, both databases must be in the same codepage.

 

DQL – Find empty folders

SELECT r_object_id,r_folder_path FROM dm_folder f WHERE r_object_id NOT IN (SELECT distinct i_folder_id FROM dm_sysobject WHERE any i_folder_id = f.r_object_id and folder(‘/AMG 999’,descend)) and folder(‘/AMG 999’,descend)