DQL update statement. Behind the scenes.

In Documentum 6.0 EMC announced following feature:


If you what to disable this feature for certain DQL updates you can take advantage of EXEC RPC-command using following way:

apply,c,,EXEC,QUERY,S,query stmt,BOF_DQL,B,F

Is this feature useful?

Actually, I have checked a couple of our projects and found out that we use neither CREATE, nor UPDATE, nor DELETE statements, so it seems that usefulness of this feature looks at least doubtful. Initially I thought that it could be useful for administration routines (i.e. preventing administrators from breaking business logic), but due to inability to get information about progress of update statement it is much better to generate a set of API-scripts rather than execute DQL-update. For example, let’s imagine that I want to link all dm_document objects to /System folder, so I can execute following update statement:

update dm_document objects link '/System' where not folder('/System')

or generate api-scenario using following SQL:

SELECT    'fetch,c,'
       || r_object_id
       || CHR (10)
       || 'link,c,l,/System'
       || CHR (10)
       || 'save,c,l'
  FROM dm_document_sp dm_document
 WHERE     (NOT (dm_document.r_object_id IN
                    (SELECT dm_sysobject_r2.r_object_id
                       FROM dm_sysobject_r dm_sysobject_r2
                      WHERE dm_sysobject_r2.i_folder_id = '0c01ffd780000106')))
       AND (dm_document.i_has_folder = 1 AND dm_document.i_is_deleted = 0)

at first glance second option looks too complex, but imagine that you need to delete a set of objects and some of those objects are checked out:

1> delete dm_document objects where folder('/Temp/test folder')
2> go
[DM_QUERY_F_UP_DESTROY]fatal:  
   "DELETE:  An error has occurred during a destroy operation."

[DM_SYSOBJECT_E_CANT_DELETE_LOCKED]error:  
    "Cannot delete sysobject  since its locked by user dmadmin."
1>

– DQL DELETE statement does not have any “force” option and now you should somehow investigate what objects are locked and then unlock locked objects, or use generation approach:

SELECT    'fetch,c,'
       || r_object_id
       || CHR (10)
       || CASE WHEN r_lock_owner <> ' ' THEN 'unlock,c,l' || CHR (10) END
       || 'destroy,c,l'
  FROM dm_document_sp dm_document
 WHERE     (dm_document.r_object_id IN
               (SELECT dm_sysobject_r2.r_object_id
                  FROM dm_sysobject_r dm_sysobject_r2
                 WHERE dm_sysobject_r2.i_folder_id = '0b01ffd7803a9257'))
       AND (dm_document.i_has_folder = 1 AND dm_document.i_is_deleted = 0)

Now such approach looks more reasonable, doesn’t it?

What happens when Content Server performs DQL update?

Lets try to obtain SQL trace for session:

API> create,c,dm_document
...
0901ffd7803a949a
API> save,c,l
...
OK
API> connect,ssc_dev,dmadmin,dmadmin
...
s1
API> get,c,sessionconfig,session_id
...
0101ffd78008b26e
API> apply,c,,SQL_TRACE,SESSION_ID,S,0101ffd78008b26e,LEVEL,I,10
...
q0
API> ?,c,update dm_document object set object_name='test' 
             where r_object_id='0901ffd7803a949a'
objects_updated
---------------
              1
(1 row affected)
[DM_QUERY_I_NUM_UPDATE]info:  "1 objects were affected by your UPDATE statement."


and analyze every SQL statements in session log:

  1. Content Server translates DQL update statement to SQL select:
      SELECT ALL dm_document.r_object_id,
                 dm_document.r_object_type "r_object_type",
                 dm_document.i_vstamp,
                 dm_repeating.r_aspect_name,
                 dm_document.i_is_replica,
                 dm_document.i_is_reference,
                 dm_document.object_name
        FROM dm_document_sp dm_document, dm_document_rp dm_repeating
       WHERE     ( ( (dm_document.r_object_id = '0901ffd7803a949a')))
             AND (dm_document.i_has_folder = 1 AND dm_document.i_is_deleted = 0)
             AND dm_repeating.r_object_id = dm_document.r_object_id
    ORDER BY 1
    
  2. Then fetches result(s) from database and tries to figure out whether object(s) to be updated have associated TBO or not (actually, queries performed by Content Server are inconsistent with DFC implementation – DFC also recognizes dm_persistent_object as potential TBO type):
    SELECT r_object_id
      FROM dm_folder_r
     WHERE r_folder_path = '/System/Modules/TBO';
    
    SELECT i_cabinet_id, i_vstamp
      FROM dm_sysobject_s
     WHERE r_object_id = '0b01ffd78000018c';
    
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_SYSOBJECT_S dm_dbalias_B
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '0b01ffd78000018c'
            AND dm_dbalias_B.I_VSTAMP = 1);
    
    SELECT ALL dmc_module.r_object_id
      FROM dmc_module_sp dmc_module, dm_sysobject_r dm_sysobject_r2
     WHERE     (    (dmc_module.object_name = 'dm_document')
                AND dmc_module.r_object_id = dm_sysobject_r2.r_object_id
                AND dm_sysobject_r2.i_folder_id = '0b01ffd78000018c')
           AND (dmc_module.i_has_folder = 1 AND dmc_module.i_is_deleted = 0);
    
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_SYSOBJECT_S dm_dbalias_B
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '0b01ffd78000018c'
            AND dm_dbalias_B.I_VSTAMP = 1);
    
    SELECT ALL dmc_module.r_object_id
      FROM dmc_module_sp dmc_module, dm_sysobject_r dm_sysobject_r2
     WHERE     (    (dmc_module.object_name = 'dm_sysobject')
                AND dmc_module.r_object_id = dm_sysobject_r2.r_object_id
                AND dm_sysobject_r2.i_folder_id = '0b01ffd78000018c')
           AND (dmc_module.i_has_folder = 1 AND dmc_module.i_is_deleted = 0);
    
  3. Then fetches (if CS cache is stale) object’s snapshot from database and tries to figure out whether object has attached aspects or object’s type has default aspects:
    SELECT dm_dbalias_B.I_TYPE
      FROM ssc_dev.DMI_OBJECT_TYPE dm_dbalias_B
     WHERE dm_dbalias_B.R_OBJECT_ID = '0901ffd7803a949a';
    
      SELECT *
        FROM DM_DOCUMENT_RV dm_dbalias_B, DM_DOCUMENT_SV dm_dbalias_C
       WHERE (    dm_dbalias_C.R_OBJECT_ID = '0901ffd7803a949a'
              AND dm_dbalias_C.R_OBJECT_ID = dm_dbalias_B.R_OBJECT_ID)
    ORDER BY dm_dbalias_B.R_OBJECT_ID, dm_dbalias_B.I_POSITION;
    
    
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_ACL_S dm_dbalias_B
     WHERE (    dm_dbalias_B.OWNER_NAME = 'dadmin'
            AND dm_dbalias_B.OBJECT_NAME = 'dm_4501ffd780000101');
    
      SELECT *
        FROM DM_ACL_RV dm_dbalias_B, DM_ACL_SV dm_dbalias_C
       WHERE (    dm_dbalias_C.R_OBJECT_ID = '4501ffd780000101'
              AND dm_dbalias_C.R_OBJECT_ID = dm_dbalias_B.R_OBJECT_ID)
    ORDER BY dm_dbalias_B.R_OBJECT_ID, dm_dbalias_B.I_POSITION;
    
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_ACL_S dm_dbalias_B
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '4501ffd780000101'
            AND dm_dbalias_B.I_VSTAMP = 2);
    
    SELECT 1
      FROM dmi_type_info_s ti_s, dmi_type_info_r ti_r
     WHERE     ti_s.r_type_name = 'dm_document'
           AND ti_s.r_object_id = ti_r.r_object_id
           AND ti_r.default_aspects IS NOT NULL;
    
    SELECT 1
      FROM dmi_type_info_s ti_s, dmi_type_info_r ti_r
     WHERE     ti_s.r_type_name = 'dm_sysobject'
           AND ti_s.r_object_id = ti_r.r_object_id
           AND ti_r.default_aspects IS NOT NULL;
    
    
  4. Some CS magic – three identical queries:
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_ACL_S dm_dbalias_B
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '4501ffd780000101'
            AND dm_dbalias_B.I_VSTAMP = 2);
    
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_ACL_S dm_dbalias_B
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '4501ffd780000101'
            AND dm_dbalias_B.I_VSTAMP = 2);
    
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_ACL_S dm_dbalias_B
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '4501ffd780000101'
            AND dm_dbalias_B.I_VSTAMP = 2);
    
  5. Writes changes to database:
    UPDATE ssc_dev.DM_SYSOBJECT_S dm_dbalias_B
       SET OBJECT_NAME = 'test',
           R_MODIFY_DATE =
              TO_DATE ('2014/09/16.01.26.38', 'YYYY/MM/DD.HH24.MI.SS'),
           R_MODIFIER = 'dmadmin',
           I_VSTAMP = 1
     WHERE (    dm_dbalias_B.R_OBJECT_ID = '0901ffd7803a949a'
            AND dm_dbalias_B.I_VSTAMP = 0);
    
  6. Tries to figure out whether dm_save event is audited or not:
      SELECT a.r_object_id,
             ...
         FROM dmi_registry_s a, dmi_registry_r b
       WHERE     a.r_object_id = b.r_object_id
             AND a.registered_id = '0301ffd780000129'
             AND (   a.event = 'all'
                  OR a.event = 'dm_all'
                  OR a.event = 'dm_default_set'
                  OR a.event = 'dm_save')
    ORDER BY a.is_audittrail DESC,
             a.event DESC,
             a.r_object_id,
             b.i_position DESC;
    
      SELECT a.r_object_id,
             ...
        FROM dmi_registry_s a, dmi_registry_r b
       WHERE     a.r_object_id = b.r_object_id
             AND a.registered_id = '0301ffd780000105'
             AND (   a.event = 'all'
                  OR a.event = 'dm_all'
                  OR a.event = 'dm_default_set'
                  OR a.event = 'dm_save')
    ORDER BY a.is_audittrail DESC,
             a.event DESC,
             a.r_object_id,
             b.i_position DESC;
    
  7. Notifies index-agent:
    SELECT dm_dbalias_B.R_OBJECT_ID
      FROM ssc_dev.DM_GROUP_S dm_dbalias_B
     WHERE dm_dbalias_B.GROUP_NAME = 'dm_fulltext_index_user';
    
    SELECT s.is_dynamic, s.group_class
      FROM dm_group_s s
     WHERE s.group_name = 'dm_fulltext_index_user';
    
    INSERT INTO ssc_dev.DMI_QUEUE_ITEM_S
         VALUES ('1b01ffd780679a64',
                 'dm_fulltext_index_user',
                 ...             
                 0);
    
  8. Writes audit (weird way to get current UTC time):
    SELECT TO_DATE (
              TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP), 'DD-MM-YY HH24.MI.SS'),
              'DD-MM-YY HH24.MI.SS')
      FROM dm_docbase_config_s;
    
    INSERT INTO ssc_dev.DM_AUDITTRAIL_S
         VALUES ('5f01ffd7807c08c9',
                 'dm_save',
                 ...            
                 0);
    
    INSERT INTO dmi_object_type (r_object_id, i_type, i_partition)
         VALUES ('5f01ffd7807c08c9', -2147483309, 0);
    

What happens if at step 2 or 3 Content Server finds out that object to be updated has TBO or aspect? Actually, it’s hard to demonstrate using api, because iapi somehow handles update statement, but very simple using dctmpy:

def main():
    session = DocbaseClient(host="192.168.2.56", port=12000)
    session.authenticate("dmadmin", "dmadmin")
    for q in session.query("UPDATE bv_document object "
                           "SET object_name='new object name', "
                           "SET title='new title', "
                           "TRUNCATE authors, "
                           "LINK '/System' "
                           "WHERE r_object_id='0901ffd78033a81c'"
            , True, 50, True):
        print q.dump()


ATTRIBUTES:
 i_is_reference             : 0
 i_is_replica               : 0
 i_vstamp                   : 2
 r_object_type              : bv_contract
 title                      : Договор № 14-13
 object_name                : test1
 r_object_id                : 0901ffd78033a81c
 r_aspect_name           [0]: IBVDocumentAspect
                         [1]: ISIInheritableAccessDocumentAspect
 authors                  []: <none>
EXTENDED:
 COUNT                      : 4
 INDEX                   [0]: 0
                         [1]: 0
                         [2]: 0
                         [3]: 0
 ATTRIBUTE               [0]: object_name
                         [1]: title
                         [2]: authors
                         [3]: 
 DQL_STMT                   : update_object
 VALUE                   [0]: new object name
                         [1]: new title
                         [2]: 
                         [3]: 0c01ffd780000106
 CONTENT_FORMAT          [0]: 
                         [1]: 
                         [2]: 
                         [3]: 
 COMMIT_FREQUENCY           : 0
 VALUE_TYPE              [0]: 1
                         [1]: 1
                         [2]: 0
                         [3]: 1
 OPERATION               [0]: set
                         [1]: set
                         [2]: truncate
                         [3]: link
 ATTR_TYPE               [0]: 2
                         [1]: 2
                         [2]: 0
                         [3]: 3
 PAGE_NO                 [0]: 0
                         [1]: 0
                         [2]: 0
                         [3]: 0
ATTRIBUTES:
 i_is_replica               : 0
 i_vstamp                   : 2
 r_object_id                : 0901ffd78033a81c
 i_is_reference             : 0
 title                      : Договор № 14-13
 object_name                : test1
 authors                  []: <none>
 r_object_type              : bv_contract
 r_aspect_name           [0]: IBVDocumentAspect
                         [1]: ISIInheritableAccessDocumentAspect
EXTENDED:
 INDEX                   [0]: 0
                         [1]: 0
                         [2]: 0
                         [3]: 0
 DQL_STMT                   : update_object
 COMMIT_FREQUENCY           : 0
 VALUE_TYPE              [0]: 1
                         [1]: 1
                         [2]: 0
                         [3]: 1
 OPERATION               [0]: set
                         [1]: set
                         [2]: truncate
                         [3]: link
 ATTR_TYPE               [0]: 2
                         [1]: 2
                         [2]: 0
                         [3]: 3
 COUNT                      : 4
 OBJECTS_UPDATED            : 1
 VALUE                   [0]: new object name
                         [1]: new title
                         [2]: 
                         [3]: 0c01ffd780000106
 CONTENT_FORMAT          [0]: 
                         [1]: 
                         [2]: 
                         [3]: 
 ATTRIBUTE               [0]: object_name
                         [1]: title
                         [2]: authors
                         [3]: 
 LAST_OBJECT                : True
 PAGE_NO                 [0]: 0
                         [1]: 0
                         [2]: 0
                         [3]: 0

So, instead of performing changes in database, CS delegates save operation to client (DFC) by sending change vector to client.

8 thoughts on “DQL update statement. Behind the scenes.

  1. Pingback: What is wrong in Documentum? Part I | Documentum in a (nuts)HELL
  2. Hi, Andrew.
    But I don`t see any criminal in such implementation. It looks like it works as expected. What are the pitfalls?

    Like

  3. No pitfalls at all, it just demonstrates how CS and DFC perform DQL updates: CS sends to DFC change vectors, DFC processes each vector and saves corresponding persistent objects. Though, I would prefer here more flexible algorithm on DFC level, smth. like:

    IDfPersistentObject object = session.getObject(objectId);
    if (object.preUpdate()) {
    processDQLChanges(object);
    object.saveDQL();
    }

    rather than current:

    IDfPersistentObject object = session.getObject(objectId);
    processDQLChanges(object);
    object.save();

    Like

  4. I mean could somebody apply lock for object after CS process UPDATE statement but before DFC do some BOF code?

    Like

  5. Pingback: Parallelism challenge | Documentum in a (nuts)HELL
  6. Pingback: Q & A. VI | Documentum in a (nuts)HELL
  7. Pingback: Workflow throughput | Documentum in a (nuts)HELL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s