Inside Documentum

One of the useful features of DQL is its ability to group repeating attributes for a single object into a single row of a result set. For example the following query on my system just returns the repeating authors attribute:

1> select authors from dm_document where any
r_version_label = 'flag1'
2> go
authors
------------------------------------------------
tom
dick
harry
tom
dick
harry
tom
dick
harry
(9 rows affected)

Listing of authors isn’t that interesting but if we add in r_object_id to the select column:

1> select r_object_id,authors from dm_document
where any r_version_label = 'flag1' order by r_object_id
2> go
r_object_id       authors
----------------  ------------------------------------------------
0900022b80010513  tom
                  dick
                  harry
0900022b80010514  tom
                  dick
                  harry
0900022b80010515 tom
                  dick
                  harry
(3 rows affected)

This time there are only 3 “rows”, one for each of the objects found in the query, and each repeating attribute for an object is returned with all the other repeating attributes for that object.

Notice that I added in an “order by r_object_id”. This is essential to ensure that the grouping works as expected. This is a point that is not particularly clear in the documentation and it is somewhat limiting, as it prevents the user from ordering the objects in some other useful way like by object_name (or UPPER(object_name) as so beloved of most of Documentum’s user interfaces these days).

The implementation of repeating attribute grouping assumes that the rows will be returned from the database in r_object_id order. However it is easy to forget this and not even notice in development or testing, as many times the database will naturally return rows in r_object_id order. However with a little effort it is possible to produce a test case like the following:

1> select r_object_id,authors from dm_document
   where any r_version_label = 'flag1'
2> go

r_object_id       authors
----------------  ------------------------------------------------
0900022b80010513  tom
                  dick
0900022b80010514  tom
                  dick
0900022b80010515  tom
                  dick
0900022b80010513  harry
0900022b80010514  harry
0900022b80010515  harry
(6 rows affected)

All I have done is remove the “order by” clause from the query. In this particular case we now have 6 rows with each object reported twice! I leave details of what is going on to the next post but I’ll leave you with a little puzzle. I can “fix” this particular query by applying some hints:

1> select r_object_id,authors from dm_document
   where any r_version_label = 'flag1'
   enable(oracle('use_nl(dm_repeating.WE_) use_nl(dm_document.VE_)'))
2> go

r_object_id       authors
----------------  ------------------------------------------------
0900022b80010513  tom
                  dick
                  harry
0900022b80010514  tom
                  dick
                  harry
0900022b80010515  tom
                  dick
                  harry
(3 rows affected)

Why does this work?

Note I’m not suggesting this as a reliable way to solve the problem (in general there isn’t one short of having r_object_id as the first column in the order by) but it gives a clue as to how Documentum implements this feature.