Posts Tagged ‘dm_sysobject’

We are working on a demo application and we had a need to retrieve the comments on an activity in a workflow. Today morning one of my colleagues approached me regarding the same. I was not of much help to her. Few hours later she figured out that the comments were to be retrieved from the package (dmi_package). I was disappointed because I realized that I knew it but it was lost somewhere in my mind. I decided to once again explore the comments in a workflow.

I remember that the comments are stored as objects of type dm_note. I fired a simple select query on my docbase for retrieving all the objects of type dm_note.

SELECT * FROM dm_note

While scrolling through the result, I noticed that the column for a_content_type was displaying the value ‘crtext’ for all the results. The content type ‘crtext’ is used for the txt/notepad documents and I recalled immediately that the comments are stored in the file store as txt files.
Thus, in a way dm_note is similar to dm_document. I was eager to check the supertype of dm_note. I used the following query for the same.

SELECT super_name FROM dm_type WHERE name = 'dm_note'

The result was dm_sysobject. I was on the right track. Even the txt file for dm_note should be stored as dmr_content. I was happy to find that the i_contents_id of a dm_note object was of the format ’06XXXXXXXXXXXXXX’. I used the same query to get the content file path on the file system which I have mentioned in my earlier post regarding dmr_content.


I got the following file path as a result:

I navigated to the mentioned file path on my file store and as expected 3c.txt had the comments that were entered in the workflow task.

As I knew that the attached documents in a workflow are actually linked to the package which in turn is linked to workflow, it was quiet logical that even the comments (the dm_note objects) are linked to the package. I found that the dmi_package has an attribute called r_note_id and it was repeating. This is the attribute which links the comments with the package. I checked the Object Relation diagram to confirm it.



I was correct in saying that the comments are linked to package much in a similar way as attached documents are linked to it.

I realize that I have left a gap regarding the linkage of documents in a workflow (package) and workflow in general. I will try to bridge it in my next post.

Read Full Post »

Here is a new attempt to implement Alias Set and ACL Template in a way that can be more meaningful. The Major drawback in the previous implementation was that the Alias Set was associated with a single user. As the ACL Template was always being assigned by the same user, every time Alias was resolved to the same value unless the user was associated with a new Alias Set.

Alias Sets can be used in a much better way if the Alias can be resolved to different values at different instances. One of the easiest ways to achieve it is by associating the Alias Set directly with the Sysobject. If it was followed in previous case, different ACLs could have been applied to different Sysobjects while using only one ACL Template and multiple Alias Sets.

The attribute of dm_sysobject that is responsible for its relation with an Alias Set is r_alias_set_id. It stores the r_object_id of the Alias Set. It appeared to me that the only possible (and meaningful) way of associating a Sysobject and an Alias Set should be through DFC. I started my search for a method of IDfSysObject that can be used for the purpose. But I failed myserably in my effort. I couldn’t find a method as per my expectation. Finally I resolved to my ultimate rescuer, Google. I found a post regarding Permission Set Template by Johnny. In his comments he has mentioned, “I manually set the r_alias_set_id directly against the object that I am assigning the PST to.” I was surprised at the ease and simplicity of his statement. I was trying to achieve the same. I had an understanding that the r_ attributes are read-only with r_version_label being an exception. Still I descided to try the setString method of IDfSysObject.

The Field Setup:

Five Users created: TestUser1, TestUser2, TestUser3, TestUser4, TestUser5.
Five Alias Sets: TestUserAlias01, TestUserAlias02 …. TestUserAlias05.
An ACL Template: TestACLTemplate

Each Alias Set has a corresponding user name as the Alias Value. The process of creating the set-up was similar to that in the earlier case.

The Game:
Following is the fragment of DFC code used. Five objects of type dm_document are created, they are associated with corresponding Alias Sets and finally the ACL Template is applied on the newly created objects.


for(int i=1; i<= 5; i++){
IDfDocument document = (IDfDocument) dfSession
if (document != null) {
// can be a business logic
("dm_alias_set where object_name = "


The main concern was the highlighted statement. Before reading Johnny’s post there was no reason to believe that it can ever work.

It was established that the understanding regarding r_ attributes was a misconception. At the least there is one more attribute which is an exception to the rule. Updating r_alias_set_id through DQL was also successful.

Anyway, my part in the game was over and it was time to verify the results.



Consider a case where the permission of all the users has to be changed from Write to Version. In the current set-up it can be achieved by updating only the Template Permission Set. If instead ACLs were used, all of them would have to be updated individually.

Related Articles:

Read Full Post »

Where are the documents (contents) stored in repository? Is it possible to find the documents by navigating to them in the file system? The question has been bothering me for quiet some time. I have heard lot many answers regarding this but I was never convinced. Till few weeks ago, in my earlier organization I was loaded with so much of work that I could never explore it myself.

Couple of weeks ago in an interview one of my colleagues asked the candidate about dmr_content. Later when I asked him he told me that the contents of renditions are stored as dmr_content. In an another incident that happened yesterday another colleague asked me about dmr_content while I was making a presentation on Object and Types. I recalled that a friend had told me once that the content of a deleted document can be retrieved using dmr_content. She was not satisfied with the response.

These incidents had developed enough curiosity in me about dmr_content. It was now my turn to explore it myself.

I checked the Object Relational Model. The i_contents_id of dm_sysobject (or it’s subtypes such as dm_document) is linked to the r_object_id of dmr_content. dmr_content also has a repeating attribute called parent_id which is linked to the r_object_id of dm_sysobject.

I was able to establish the relation between dm_document and dmr_content. The actual document or the content is stored as dmr_content and it is linked to dm_document which stores the metadata. In fact all the contents are stored as objects of dmr_content. That is the reason for creation of a new dmr_content object when we create a rendition.

But why is the parent_id a repeating attribute? It should mean that many dm_document objects can be linked to a single dmr_content object. Does that make sense? I performed the following operations on a document in order to check that.

  1. Check out a document and check it back in as a new version without making any changes to it.

  2. Create a copy of a document.

In both the cases a new object of dmr_content was created and the new document/version got linked to it. Interestingly even when I checked in the document as the same version, a new dmr_content object was created. The deletion of dm_document did not delete the associated dmr_content object either. Both of the above mentioned operations had resulted in the creation of orphan dmr_content objects. I read on a forum that the dm_clean job removes all such orphan objects. So, until the dm_clean job runs, it is possible to retrieve the content of a deleted document. Further the content files on the host file system which doesn’t have a referring dmr_content object are deleted by dm_filescan job.

Here is the most interesting part and the answer to the introductory question: I found this DQL query in the discussion forum of powerlink.

This was the thing I was looking for. In order to verify it I imported an image file named object_type.jpg in my repository using DA. I used the following query to find the r_object_id of the dmr_content it was associated with.

SELECT r_object_id
FROM dmr_content
WHERE any parent_id in
(SELECT r_object_id
FROM dm_document
WHERE object_name = 'object_type.jpg')

Alternatively, the following query can also be used.

FROM dm_document
WHERE object_name = 'object_type.jpg'

The return value of the query was ‘060003f08000c93e’. This value was used as input for the second query.

FOR '060003f08000c93e'

Any guesses? The query returned me the following content path.


The e2.jpg was same as the object_type.jpg which I had imported. In other words, the file was stored in the host file system but it was renamed. GET_PATH is an Administration method and can be found under Administration >> Job Management >> Administration Method node in Documentum Administrator.

I am sorry but I don’t want to indulge in any more puzzles now. You can get some help in Robin’s Post if you want to understand the logic of resolving the content path of dmr_content. As per Object Relational Diagram, the storage_id and data_ticket attributes are used to refer to the content stored in the host file system.

In the end I got the answer for the long standing question. But one question remained unanswered. Why is parent_id a repeating attribute? Any thoughts on that?

Related Articles:

Read Full Post »

Few days ago my friend asked me a dql query. He wanted to have a list of all the documents and their folder paths in the repository.

It didn’t appear to be a tough one. I knew that the i_folder_id of dm_document is mapped with the r_object_id of the dm_folder to which the document is linked to. A document can be linked to multiple folders and that explains why this i_folder_id is a repeating attribute. The dm_folder has another repeating attribute r_folder_path which stores the folder path of that particular folder. The reason for r_folder_path being repeating is same as that for i_folder_id in case of dm_document.

I told him this dql to get the folder path of all the documents.

SELECT r_folder_path
FROM dm_folder
WHERE r_object_id in
(SELECT distinct i_folder_id
FROM dm_document)

He didn’t look happy enough. What he actually wanted was a list of documents with the folder path. I told him that it is not a problem; we can put a join and get the object_name from dm_document.

SELECT doc.object_name,fol.r_folder_path
FROM dm_document doc, dm_folder fol
WHERE any doc.i_folder_id = fol.r_object_id

The query looked very simple to me. He seemed to be happy and he told he will try it out. I was happy that I have given him a solution. I was a bit surprised when he told me that he was getting an error while executing this query. I didn’t expect this. The error was:

    :[DM_QUERY2_E_REPEAT_TYPE_JOIN]error: “Your query is selecting repeating attributes and joining types.”

The above query works fine if I select only doc.object_name; but it was not allowing me to select fol.r_folder_path which is a repeating attribute. The conclusion was that a repeating valued attribute cannot be selected in a normal query which uses repeating valued attribute in a join. It can be achieved by using DQL Hints mentioned later. But this friend of mine needs both Document Name and Folder Path together. I asked him to write a dfc code which gets all the documents, finds their folder path and writes this information to a file. But my friend is a real lazy guy. The bad part being that I am also no different.

I thought a lot and in the end I got an idea. I asked him to fire a query on the RDBMS tables. The object types are represented as _s and _r tables in RDBMS for their single and repeating valued attribute respectively. The query has to be fired on dm_document_s, dm_document_r, dm_folder_s and dm_folder_r tables.

So, the new query which I formed was:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_document_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

But when I fire this query on my docbase I again got an error.

    :[DM_QUERY2_E_TABLE_NOT_FOUND]error: “The database table or view was not found in the database. Error from the database was: ‘ — The database object is invalid — STATE=S0002, CODE=208, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘dbo.dm_document_r’.'”

I realized that there is no such table called dm_document_r as dm_document doesn’t have any repeating attribute of its own. But in that case from where should I get the value for i_folder_id? If i_folder_id is not an attribute of dm_document then it should be inherited from dm_sysobject. Isn’t so?

So I had my new dql:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

I was so confident. This is the ultimate query which will get me the result. When I fired this query I got the following result.

    :[DM_QUERY_E_CURSOR_ERROR]error: “A database error has occurred during the creation of a cursor (‘ STATE=S0022, CODE=207, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ‘object_name’.’).”

How could I forget that object_name is also an attribute of dm_sysobject. Actually the dm_document_s has just one attribute and that is the r_object_id. As a matter of fact dm_document doesn’t have any attribute of its own. Then what is the use of having the object_type dm_document? I leave this question to be answered by you. This has to be my final query.

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

Ultimately I got the result without any errors. But I wasn’t too happy. Many records in object_name as well as r_folder_path were showing as empty.
I did a quick fix:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring

The r_folder_path was not showing any empty records but the case was not so with object_name. Something clicked in my mind. My mind had found a flaw in the query which was looking fine to me till now. My friend was interested in documents; not the sysobjects. I was actually giving him a lot of garbage.

And thus I got my next query:

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_type = 'dm_document'

Cool….. My friend looks to be the happiest person on earth. He got the result he was looking for. But I still want to do one more small change.

SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_type = 'dm_document'
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id

The result is same as the earlier query but somehow I look more satisfied with this query. I am not sure how good or how efficient this query is but my friend got what he wanted and I was satisfied that I was able to help him out.

The story is not over yet. I just checked the sql equivalent of this query in the dql component of webtop.

Here is the result:

SELECT all doc_s.object_name, fol_r.r_folder_path
FROM dbo.dm_sysobject_s doc_s, dbo.dm_sysobject_r doc_r, dbo.dm_folder_r fol_r
WHERE ((doc_s.r_object_type=N'dm_document')
AND fol_r.r_folder_path != ' '
AND (doc_s.r_object_id=doc_r.r_object_id)
AND (doc_r.i_folder_id=fol_r.r_object_id))

Do you know what the most interesting part is?

After doing all this crap, I posted it on my blog. I had added tags like DQL, Documentum, etc. When I clicked on the DQL link (The tag which I had added) I got Rajendra’s blog in the result. He has posted a query there.

SELECT A.r_object_id, A.object_name, B.r_folder_path
FROM dm_document A, dm_folder_r B
WHERE any A.i_folder_id = B.r_object_id

Doesn’t that look much simple? :D:D:D

Here is another query from inthewoods. It uses DQL Hint and appears to be a better option than the earlier two.

SELECT doc.r_object_id, doc.object_name, fld.r_folder_path
FROM dm_document doc, dm_folder fld
WHERE doc.i_folder_id = fld.r_object_id ENABLE(ROW_BASED)

So.. Thats the whole story. Hope you enjoyed it.

*Guess it would be a nice idea to add ‘fld.r_folder_path is not nullstring’ and ‘order by r_object_id’ to the final query.

Read Full Post »