Some time ago, a colleague asked around if someone knew a way to automatically request a rendition right after a document was checked in. This looked like a trigger to me and TBOs are here for that, right ? Sure but there was an additional condition: it should be set up by an administrator from the idql command-line tool as no programming was possible at that time on that system. Given that this tool, along with iapi, have been practically stagnant for years, that would be quite a surprise if it were possible at all. Admittedly, idql lets us invoke server administration methods but, according to the documentation, none of them deals directly with TBOs. Of course, I immediately thought of working at the database level and set up an INSERT or UPDATE trigger on the dm_document_s table (I suppose an Oracle database was used in that docbase; other RDBMS have equivalent concepts possibly with different implementations; the example code below was set up and tested in a repository’s Oracle schema), so let’s see how to do that. Of course, since we are bypassing the Documentum layer, the solution is a bit awkward but it looks simple and innocuous enough that no weirdness should be introduced in the repository.<\/p>\n
Usually, a rendition is explicitly requested by creating an entry in the dmi_queue_item table through the “queue” API function or its DfC counterpart: If we activate SQL tracing, we can see what SQL statement the content server sends to the database: Now that all the stumbling blocks have been removed, the implementation is pretty straightforward: we create a trigger on the dm_sysobject_s table in the repository’s schema as follows:<\/p>\n Line 1 shows the kind of trigger we want. It will be fired right after a document is saved after an INSERT (i.e. it has just been created) or an UPDATE (i.e. is has just been modified). Note that the target table is dm_sysobject_s, not dm_document_s, because all the relevant attributes are stored in the former one. In Documentum’s object-relational model, dm_document inherits its attributes from dm_sysobject and in the model’s relational database implementation the inherited attributes come from dm_sysobject_s. For the trigger to be activated, the attribute LOG_ENTRY must be initialized with the value returned by a call to NEXT_ID_LIST for a dmi_queue_item object; then, the document can be saved and as soon as this occurs, the triggers gets fired and inserts an entry for a rendition request into the queue.<\/p>\n If the document is updated but its LOG_ENTRY attribute has not been updated with a fresh value, the current id in LOG_ENTRY will be used to create a new entry in the queue, which will cause a uniqueness constraint violation and reported on stdout, e.g.: Usually, it is not recommended to bypass the Documentum API and directly access the underlying database but we do this all the time in queries and to fix the occasional object corruption. Besides, the implementation of Documentum’s object-relational model is well documented and Documentum let us also know the generated SQL statements it sends to the RDBMS. For simple, self-contained DML statements such as the solution’s INSERT into dmi_queue_item there should be no drawbacks. Some time ago, a colleague asked around if someone knew a way to automatically request a rendition right after a document was checked in. This looked like a trigger to me and TBOs are here for that, right ? Sure but there was an additional condition: it should be set up by an administrator from […]<\/p>\n","protected":false},"author":40,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,525],"tags":[],"type_dbi":[],"class_list":["post-11684","post","type-post","status-publish","format-standard","hentry","category-database-management","category-enterprise-content-management"],"acf":[],"yoast_head":"\n
\n
\nqueue,c,doc_id<\/em>,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf
\n<\/code>
\nExample:
\n
\n?,c,select r_object_id from dm_document enable(return_top 10)
\nr_object_id
\n----------------
\n0900c350800001d0<\/strong>
\n6700c35080000100
\n6700c35080000101
\n0900c350800001ff
\n0900c35080000200
\n0900c35080000201
\n0900c35080000202
\n0900c35080000203
\n0900c35080000204
\n0900c350800001da
\n(10 rows affected)
\n
\nAPI> queue,c,0900c350800001d0<\/strong>,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf
\n...
\n1b00c35080009504
\ndump,c,1b00c35080009504
\n...
\nUSER ATTRIBUTES
\n
\nname : dm_autorender_win31
\nstamp : 1b00c35080009504
\nsent_by : dmadmin
\ndate_sent : 8\/11\/2018 06:27:41
\ndue_date : nulldate
\nevent : rendition
\nitem_name : Default Signature Page Template
\nitem_id : 0900c350800001d0
\nitem_type : dm_esign_template
\ncontent_type : pdf
\nmessage : rendition_req_ps_pdf
\nrouter_id : 0000000000000000
\nsupervisor_name : dm_autorender_win31
\ntask_number :
\ntask_name : event
\ntask_type :
\ntask_state :
\ndependency_type :
\nnext_tasks_type :
\ninstruction_page : 0
\nplan_start_date : nulldate
\nactual_start_date : nulldate
\nread_flag : F
\ndelete_flag : F
\npriority : 0
\nposition : 0
\ndequeued_by :
\ndequeued_date : nulldate
\nsign_off_required : F
\nsign_off_user :
\nsign_off_date : nulldate
\nsource_docbase :
\ntarget_docbase :
\nremote_pending : F
\nsource_event : 0000000000000000
\nsource_stamp : 0
\ntask_subject :
\nevent_detail :
\n
\nSYSTEM ATTRIBUTES
\n
\nr_object_id : 1b00c35080009504
\n
\nAPPLICATION ATTRIBUTES
\n
\na_content_type :
\na_operations : queue
\n
\nINTERNAL ATTRIBUTES
\n
\ni_event_flags : 0
\ni_partition : 0
\ni_is_replica : F
\ni_vstamp : 0
\n<\/code>
\nI voluntarily showed the whole generated queue entry because we’ll have to emulate it precisely with SQL later.
\nThere are other similar requests implemented as administrative methods, such as “transcode_content” for requesting a transformation of a content from one format into another by the media server and “register_asset” to request a thumbnail of a document. Those would be called through the “apply” API method, e.g.:
\n
\napply,c,r_object_id<\/em>,transcode_content,message,S,message<\/em>,source_format,S,source_format<\/em>,target_format,S,target_format<\/em>
\napply,c,r_object_id<\/em>,register_asset
\n<\/code>
\nor in DQL:
\n
\nEXECUTE transcode_content FOR 'r_object_id<\/em>' WITH message='message<\/em>',source_format='format_name<\/em>', target_format=format_name<\/em>'
\nEXECUTE register_asset FOR object_id<\/em>
\n<\/code><\/p>\nThe SQL statement behind addqueue<\/h3>\n
\n
\ntrace,c,1,,SQL_TRACE
\n<\/code>
\nand check the generated SQL statement, we notice that it looks as expected:
\n
\n2018-08-11T06:38:28.313756 27106[27106] 0100c3508000fd67 24 select r_object_type, i_vstamp, i_antecedent_id, i_branch_cnt, i_direct_dsc, r_immutable_flag, r_has_events, i_cabinet_id, r_link_cnt, r_frzn_assembly_cnt, r_access_date, i_latest_flag from dm_sysobject_s where r_object_id = :ID PARAMS :ID=0900c350800001d0
\n2018-08-11T06:38:28.314420 27106[27106] 0100c3508000fd67 24 EXEC 0.0006860000
\n2018-08-11T06:38:28.314467 27106[27106] 0100c3508000fd67 24 FETCH(1) 0.0000020000
\n2018-08-11T06:38:28.314620 27106[27106] 0100c3508000fd67 24 SELECT dm_dbalias_B.R_OBJECT_ID FROM dmtest.DM_GROUP_S dm_dbalias_B WHERE dm_dbalias_B.GROUP_NAME=:valp PARAMS :valp=dm_autorender_win31
\n2018-08-11T06:38:28.314953 27106[27106] 0100c3508000fd67 24 EXEC 0.0003500000
\n2018-08-11T06:38:28.314977 27106[27106] 0100c3508000fd67 24 FETCH(0) 0.0000010000
\n2018-08-11T06:38:28.315037 27106[27106] 0100c3508000fd67 24 select s.is_dynamic, s.group_class from dm_group_s s where s.group_name = :p0 PARAMS :p0=dm_autorender_win31
\n2018-08-11T06:38:28.315441 27106[27106] 0100c3508000fd67 24 EXEC 0.0004170000
\n2018-08-11T06:38:28.315463 27106[27106] 0100c3508000fd67 24 FETCH(0) 0.0000070000
\n2018-08-11T06:38:28.315584 27106[27106] 0100c3508000fd67 24 INSERT INTO dmtest.DMI_QUEUE_ITEM_S VALUES ('1b00c35080009509','dm_autorender_win31','1b00c35080009509','dmadmin',TO_DATE('2018\/08\/11.04.38.28','YYYY\/MM\/DD.HH24.MI.SS'),TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),'rendition','Default Signature Page Template','0900c350800001d0','dm_esign_template','pdf','rendition_req_ps_pdf','0000000000000000','dm_autorender_win31',' ','event',' ',' ',' ',' ', 0,TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),0,0, 0,0,' ',TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),0,' ',TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),' ','queue',' ',' ',0,'0000000000000000', 0,' ', 0,' ', 0,0, 0)
\n<\/strong>
\n2018-08-11T06:38:28.317123 27106[27106] 0100c3508000fd67 24 EXEC 0.0015610000
\n2018-08-11T06:38:28.317173 27106[27106] 0100c3508000fd67 24 commit
\n<\/code>
\nThe SQL INSERT statement exactly matches one to one the queue entry data that the dump showed before so we can easily reproduce it from within a database trigger.
\nHowever, the INSERT inserts the ID ‘1b00c35080009509’ for the entry (twice, one as stampid and one as r_object_id; there is no evidence that those attributes may diverge at some point) but the SQL trace does not show its origin. Where does this id come from ? As a first approximation, I supposed that it was the next available number in the monotonically increasing sequence of the dmi_queue_item’s stamp id or r_object_id:
\n
\nselect max(r_object_id), max(stamp) from dmi_queue_item
\nmax(dmi_queue_item.r_object_id) max(dmi_queue_item.stamp)
\n------------------------------- -------------------------
\n1b00c35080009509 1b00c35080009509
\n(1 row affected)
\n<\/code>
\nAnd thus the above preliminary query would be enough to get a value for the stampid\/r_object_id of the next dmi_queue_item’s entry to create (incremented by one, of course, and with the necessary precautions to avoid a race condition). But things are not so simple here. In a first version, I used this approach in the trigger but as soon as a traditional addqueue API statement was issued later, it failed with a uniqueness violation on the r_object_id column as if the API layer were not aware that the r_object_id it used had already been grabbed earlier.
\nThe process to get a new id is not fully documented; hints in relation to the type dmi_sequence can be found in the Knowledge Base (see e.g. here<\/a>, here<\/a>, and here<\/a>; an account at OpenText is needed to access those links) but it is still far from being usable.
\nI thought logically that some database sequence was used to generate the object ids, one dedicated sequence per doctype, but could find any in the repository’s schema. However, one of the links above suggests that internal sequences are still used in the guise of the type dmi_sequence. For each doctype, the attribute dmi_sequence.i_last_no holds the starting value of a chunk of available ids; this chunk gets initialized at each server restart but there is no persistent place where the used ids (or the last used one, or the next available one) are stored. As far as I can see, it looks like the latest used r_object_ids by doctype are kept in memory, probably in a semaphore-protected shared memory area so that Documentum child processes can access it quickly and concurrently, which is out of reach of an RDBMS process.
\nFortunately, a search on-line brought the following document here.<\/a>
\nNEXT_ID_LIST
\nThis apply method reserves a list of object IDs from the repository.
\n…
\nEach ingested object requires a
\nunique object ID, and this method allows you to reserve a set of IDs. Once reserved, the IDs will not
\nbe used by the repository, so you will not ingest an object ID in use by another object.
\n…
\napply,c,NULL,NEXT_ID_LIST,TAG,I,08,HOW_MANY,I,100
\n<\/em>
\nJust what the doctor ordered !
\nIn our case, we are dealing with dmi_queue_item. Its tag according to the System Object manual is 1b, i.e. 27.
\nExample of call:
\n
\napply,c,,NEXT_ID_LIST,TAG,I,27<\/strong>,HOW_MANY,I,1
\nnext,c,q0
\nget,c,q0,next_id
\n1b00c3508000ad21
\n<\/code>
\nNote the 2-character type prefix in the returned id: 1b, i.e. 27 decimal, as requested.
\nThis call actually not only returns an id for the given type but also removes it from the pool of available ids in the chunk that is initialized by type in dmi_sequence.i_last_no at each server restart; said in other words, it officially consumes it. This resolves the issue we faced above.
\nSince this call is not available from within a database, we still need to get it from a Documentum client and pass it to the database trigger, which can be done easily by storing it into some unused attribute of dm_document before saving it. Here, we choose the attribute dm_document.LOG_ENTRY; its documented purpose is to store any user’s comment, so it kind of suits us well here without stretching too far its original raison d’\u00eatre. A custom doctype could even reserve a special, dedicated attribute for this usage.
\nIf log_entry is a no-go, an alternative but more complicated parameter-passing approach between the Documentum client and the database trigger could consist in using a database table, say ask_rendition(doc_id, queue_id).<\/p>\nThe implementation<\/h3>\n
create or replace trigger ask_rendition after insert or update on dm_sysobject_s for each row when (\n (\n new.r_object_id = '0900c350800001db'\n ) and \n (\n new.r_object_type in ('dm_document')\n ) and\n (\n new.a_content_type in ('crtext', 'doc', 'powerpoint', 'text', 'vsd') or \n new.a_content_type like 'excel%' or\n new.a_content_type like 'ppt%' or \n new.a_content_type like 'msw%'\n )\n)\nDECLARE\n stampId DMI_QUEUE_ITEM_S.r_object_id%type;\n format constant STRING(16) := 'XXXXXXXXXXXXXXXX';\nBEGIN\n stampID := :new.LOG_ENTRY;\n -- has a rendition been requested ?\n -- if so, the LOG_ENTRY attribute must contain a dmi_queue_item id, and nothing else;\n IF 0 = regexp_instr(stampID, '^1b[0-9a-f]{14}$') THEN\n -- no rendition requested, quit now;\n RETURN;\n END IF;\n INSERT INTO DMI_QUEUE_ITEM_S (\n R_OBJECT_ID,\n NAME,\n STAMP,\n SENT_BY,\n DATE_SENT,\n DUE_DATE,\n EVENT,\n ITEM_NAME,\n ITEM_ID,\n ITEM_TYPE,\n CONTENT_TYPE,\n MESSAGE,\n ROUTER_ID,\n SUPERVISOR_NAME,\n TASK_NUMBER,\n TASK_NAME,\n TASK_TYPE,\n TASK_STATE,\n DEPENDENCY_TYPE,\n NEXT_TASKS_TYPE,\n INSTRUCTION_PAGE, \n PLAN_START_DATE,\n ACTUAL_START_DATE, \n READ_FLAG,\n DELETE_FLAG,\n PRIORITY,\n POSITION,\n DEQUEUED_BY,\n DEQUEUED_DATE,\n SIGN_OFF_REQUIRED,\n SIGN_OFF_USER,\n SIGN_OFF_DATE,\n A_CONTENT_TYPE,\n A_OPERATIONS,\n SOURCE_DOCBASE,\n TARGET_DOCBASE,\n REMOTE_PENDING,\n SOURCE_EVENT,\n SOURCE_STAMP,\n TASK_SUBJECT, \n I_EVENT_FLAGS,\n EVENT_DETAIL,\n I_PARTITION,\n I_IS_REPLICA,\n I_VSTAMP) \n VALUES (\n stampId,\n 'dm_autorender_win31',\n stampId,\n 'dmadmin',\n sysdate,\n TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),\n 'rendition',\n :new.object_name,\n :new.r_object_id,\n :new.r_object_type,\n :new.a_content_type,\n 'rendition_req_ps_pdf',\n '0000000000000000',\n 'dm_autorender_win31',\n ' ',\n 'event',\n ' ',\n ' ',\n ' ',\n ' ', \n 0,\n TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),\n TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),\n 0,\n 0, \n 0,\n 0,\n ' ',\n TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),\n 0,\n ' ',\n TO_DATE('0001\/01\/01.00.00.00','YYYY\/MM\/DD.HH24.MI.SS'),\n ' ',\n 'queue',\n ' ',\n ' ',\n 0,\n '0000000000000000',\n 0,\n ' ',\n 0,\n ' ',\n 0,\n 0,\n 0);\nEND;\n\/\nshow errors\n<\/pre>\n
\nLines 2 to 4 can be removed as they are included for testing purpose only. However, they can be replaced by other constraints to better scope the set of documents that should be rendered, e.g. r_object_type set to some custom doctypes, owner_name != dmadmin to avoid system-generated documents.
\nLines 9 to 12 are restrictions on the content type; others types can be included if needed.
\nLine 19 extracts the id to use as the queue’s r_object_id\/stampid. As said above, we use the attribute LOG_ENTRY.
\nLines 20 to 25 check whether a rendition request has been requested; if so, dm_sysobject.log_entry must only contains a dmi_queue_item r_object_id (i.e. starting with 1b); any other value (or no value at all) is interpreted as no rendition request.
\nLines 26 to 117 set the values taken from the document into the queue’s entry columns.
\nLines 78, 94, 95, 101 and 104: this is how Documentum represents nulldates; we comply with it instead of storing NULLs.
\nAs shown, the trigger is fairly standard, no surprises here.<\/p>\nUsage<\/h3>\n
# get an ID for the new queue entry;\nAPI> apply,c,,NEXT_ID_LIST,TAG,I,27,HOW_MANY,I,1\n...\nq0\nAPI> next,c,q0\n...\nOK\nAPI> get,c,q0,next_id\n...\n1b00c3508000ad20\nAPI> close,c,q0\n...\nOK\n\n# use document with id 0900c350800001db as test document;\n# set its LOG_ENTRY to that ID;\nfetch,c,0900c350800001db\nset,c,l,log_entry\n1b00c3508000ad20\nsave,c,l\n\n# the trigger is fired right away; check the newly queue entry;\ndump,c,1b00c3508000ad20\n...\nUSER ATTRIBUTES\n\n name : dm_autorender_win31\n stamp : 1b00c3508000ad20\n sent_by : dmadmin\n date_sent : 9\/16\/2018 14:17:20\n due_date : nulldate\n event : rendition\n item_name : Blank PowerPoint Pre-3.0 Presentation\n item_id : 0900c350800001db\n item_type : dm_document\n content_type : powerpoint\n message : rendition_req_ps_pdf\n router_id : 0000000000000000\n supervisor_name : dm_autorender_win31\n...\n\n# enqueue a request through the traditional way;\nqueue,c,0900c350800001d0,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf\n...\n1b00c3508000ad21\n\n# the next ID has been taken, no uniqueness violation;\n<\/pre>\n
\n
\nAPI> fetch,c,0900c350800001db
\n...
\nOK
\nAPI> dump,c,l
\n...
\nUSER ATTRIBUTES
\n
\nobject_name : Blank PowerPoint Pre-3.0 Presentation
\ntitle : TTT
\nsubject :
\nauthors []:
\nkeywords []:
\nresolution_label :
\nowner_name : dmadmin
\nowner_permit : 7
\ngroup_name : docu
\ngroup_permit : 5
\nworld_permit : 3
\nlog_entry : 1b00c3508000ad20
\n...
\nAPI> set,c,l,title
\nSET> Burn !
\n...
\nOK
\nAPI> save,c,l
\n...
\n[DM_OBJ_MGR_E_SAVE_FAIL]error: \"save failed for object with handle 0900c350800001db of type dm_document: table on which save failed was DM_SYSOBJECT_S; error from database system was ORA-00001: unique constraint (DMTEST.D_1F00C3508000017B) violated
\nORA-06512: at \"DMTEST.ASK_RENDITION\", line 8
\nORA-04088: error during execution of trigger 'DMTEST.ASK_RENDITION'\"
\n
\n-- let's retry but with a new id this time;
\nAPI> reset,c,l
\n...
\nOK
\nAPI> apply,c,,NEXT_ID_LIST,TAG,I,27,HOW_MANY,I,1
\n...
\nq0
\nAPI> next,c,q0
\n...
\nOK
\nAPI> get,c,q0,next_id
\n...
\n1b00c3508000ad28
\nAPI> set,c,l,log_entry
\nSET> 1b00c3508000ad28
\n...
\nOK
\nAPI> save,c,l
\n...
\nOK
\n-- any conflict ?
\nAPI> queue,c,0900c350800001d0,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf
\n...
\n1b00c3508000ad29
\n-- nope, it's fine;
\n<\/code>
\nHowever, there are cases where a rendition is not necessary and therefore needs not be requested. In such cases, just leave log_entry empty or assign it a value that won’t match the regexp ‘^1b[0-9a-f]{14}’ and the trigger will do nothing. This also allows to continue using log_entry as before (although utterly unlikely, it cannot be completely excluded that dmi_queue_item ids get involuntary assigned to this attribute at some point, which will be interpreted as a rendition request; in order to prevent this, just fool the regexp e.g. with a leading or trailing blank).<\/p>\nConclusion<\/h3>\n
\nBut there is another cleaner alternative: let the trigger invoke a java external procedure that uses the DfC. This could be the subject of a future blog.<\/p>\n","protected":false},"excerpt":{"rendered":"