Documentum -- Custom queries

Question

I am new to learning Documentum and we came across this query being run by the system that we are looking at how to potentially speed up:

    SELECT ALL dm_document.r_object_id
    FROM dm_document_sp dm_document
    WHERE (
      dm_document.object_name = :"SYS_B_0"
      AND dm_document.r_object_id IN (
        SELECT r_object_id
        FROM dm_sysobject_r
        WHERE i_folder_id = :"SYS_B_1"
      )
    )
    AND (
      dm_document.i_has_folder = :"SYS_B_2"
      AND dm_document.i_is_deleted = :"SYS_B_3"
    )

We looked at adding an index or using a SQL profile. However, the index would be somewhat large and will continue to grow. The SQL profile also would need to be re-examined periodically.

We thought it would be better to look at re-writing the SQL itself. Is there a way to override the system to use custom SQL (i.e. SQL written by the developers) for specific queries that Documentum auto-generates?

why do you think that SQL generated by the Documentum is bad? What is your DQL? — Miki, Aug 07 '19 at 07:36
I added the query to the original post. I'm not saying the DQL/SQL is necessarily bad, but we are looking at different ways to optimize it. — , Aug 07 '19 at 07:57
Adding indexes directly to DB columns was the way we did it. Although Documentum has the possibility to add it through Documentum, outcome is the same. btw it seems that query is simple as it could be. — Miki, Aug 07 '19 at 08:58
OK, thanks. I think you are right, the query is pretty straightforward. — , Aug 07 '19 at 10:50

score 2 · Answer 1 · answered Aug 07 '19 at 06:56

Unfortunately there is no way how to alter the default Documentum behavior of translation of DQL into result SQL.

But you can directly execute SQL in your custom applications, jobs, BOFs, components, etc using JDBC. For other than SELECT queries can be also used DQL EXECUTE statement like this:

EXECUTE exec_sql WITH query = 'sql_query'

Another option is to register specific *_s or *_r tables and access them directly in DQL. For example you can register dm_sysobject_s like this:

REGISTER TABLE dm_dbo.dm_sysobject_s ("r_object_id" CHAR(16))

And then you can use it in DQL:

SELECT object_name FROM dm_sysobject_s

And you can also normally join the registered table with Documentum types in DQL, for example:

SELECT object_name FROM dm_sysobject_s s, dmi_queue_item q WHERE s.r_object_id = q.item_id

But keep in mind that this is not recommended approach by Documentum to directly access their internal tables but when you really need to speed up your application then you have to use alternative ways.

Anyway I would recommend to use indexes at first and if it is not suficent then you can continue with steps described above.

OK, thanks for the suggestions. I agree, better to not muck around too much with the inner workings of Documentum if possible. — , Aug 07 '19 at 10:53

Documentum -- Custom queries

1 Answers1