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?

Answer 1

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.