Inside Documentum

What SQL is being used?

What SQL is generated by the following DQL query:

select object_name from dm_document

Well the answer depends on a number of things:

• What user I am
• What version of Content Server I’m using
• Certain server.ini parameters
• etc

The most important one is the first one, and particularly whether the user is a superuser or not. If I am a superuser then the query will look something like this:

select     all dm_document.object_name 
from       dm_document_sp dm_document 
where    (    dm_document.i_has_folder = 1 
             and dm_document.i_is_deleted = 0)

But if I don’t have superuser privileges it will look something like this:

select     all dm_document.object_name 
from       dm_document_sp dm_document 
where      (    dm_document.i_has_folder = 1 
            and dm_document.i_is_deleted = 0) 
and        (  (   dm_document.owner_name = 'Robin East' 
               or dm_document.owner_name in ('administrator')
              ) 
            or exists (select    * 
                       from      dm_acl_sp, 
                                 dm_acl_rp 
                       where     dm_acl_sp.r_object_id = dm_acl_rp.r_object_id 
                       and       dm_document.acl_domain = dm_acl_sp.owner_name 
                       and       dm_document.acl_name = dm_acl_sp.object_name 
                       and       dm_acl_rp.r_accessor_permit >= 2 
                       and       (    dm_acl_rp.r_accessor_name = 'dm_world' 
                                   or (    dm_document.owner_name = 'Robin East' 
                                       and dm_acl_rp.r_accessor_name = 'dm_owner'
                                      ) 
                                   or dm_acl_rp.r_accessor_name = 'Robin East' 
                                   or (    dm_acl_rp.r_is_group = 1 
                                       and dm_acl_rp.r_accessor_name in ('administrator')
                                      )
                                 )
                       )
             )

All that extra stuff results in joins or subqueries on the dm_acl tables to enforce permissions; superusers always have read access to everything so there is no need for the additional predicates.

The upshot is: be very careful when tuning DQL queries by turning them into SQL. If you have a DQL that is slow for a non-superuser and you log in to Administrator as dmadmin to identify the SQL being generated you will not be using the same SQL!!!

Here are some ways to make sure you are getting the right SQL:

1. login to DA as the user – you’ll need her password! Now you can run the query and use ‘Show SQL’ to get the right SQL
2. If you know how, run the API trace,c,10,,SQL_TRACE from her session. In WDK apps you can do this by accessing the API tester component. All SQL statements generated for her session will appear in the user’s session log on the Content Server
3. The following API commands can be run by a superuser in IAPI:

   getlogin,c,user (this returns a ticket for the user)
   connect,docbase,user,ticket
run query
?,c,execute get_last_sql

execute get_last_sql will give you the SQL for the DQL command just run, this is the command that is run when you click ‘Show SQL’ in the DA query interface.