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.
Reminder: how to request a rendition
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:
queue,c,doc_id,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf
Example:
?,c,select r_object_id from dm_document enable(return_top 10)
r_object_id
----------------
0900c350800001d0
6700c35080000100
6700c35080000101
0900c350800001ff
0900c35080000200
0900c35080000201
0900c35080000202
0900c35080000203
0900c35080000204
0900c350800001da
(10 rows affected)
API> queue,c,0900c350800001d0,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf
...
1b00c35080009504
dump,c,1b00c35080009504
...
USER ATTRIBUTES
name : dm_autorender_win31
stamp : 1b00c35080009504
sent_by : dmadmin
date_sent : 8/11/2018 06:27:41
due_date : nulldate
event : rendition
item_name : Default Signature Page Template
item_id : 0900c350800001d0
item_type : dm_esign_template
content_type : pdf
message : rendition_req_ps_pdf
router_id : 0000000000000000
supervisor_name : dm_autorender_win31
task_number :
task_name : event
task_type :
task_state :
dependency_type :
next_tasks_type :
instruction_page : 0
plan_start_date : nulldate
actual_start_date : nulldate
read_flag : F
delete_flag : F
priority : 0
position : 0
dequeued_by :
dequeued_date : nulldate
sign_off_required : F
sign_off_user :
sign_off_date : nulldate
source_docbase :
target_docbase :
remote_pending : F
source_event : 0000000000000000
source_stamp : 0
task_subject :
event_detail :
SYSTEM ATTRIBUTES
r_object_id : 1b00c35080009504
APPLICATION ATTRIBUTES
a_content_type :
a_operations : queue
INTERNAL ATTRIBUTES
i_event_flags : 0
i_partition : 0
i_is_replica : F
i_vstamp : 0
I voluntarily showed the whole generated queue entry because we’ll have to emulate it precisely with SQL later.
There 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.:
apply,c,r_object_id,transcode_content,message,S,message,source_format,S,source_format,target_format,S,target_format
apply,c,r_object_id,register_asset
or in DQL:
EXECUTE transcode_content FOR 'r_object_id' WITH message='message',source_format='format_name', target_format=format_name'
EXECUTE register_asset FOR object_id
The SQL statement behind addqueue
If we activate SQL tracing, we can see what SQL statement the content server sends to the database:
trace,c,1,,SQL_TRACE
and check the generated SQL statement, we notice that it looks as expected:
2018-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
2018-08-11T06:38:28.314420 27106[27106] 0100c3508000fd67 24 EXEC 0.0006860000
2018-08-11T06:38:28.314467 27106[27106] 0100c3508000fd67 24 FETCH(1) 0.0000020000
2018-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
2018-08-11T06:38:28.314953 27106[27106] 0100c3508000fd67 24 EXEC 0.0003500000
2018-08-11T06:38:28.314977 27106[27106] 0100c3508000fd67 24 FETCH(0) 0.0000010000
2018-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
2018-08-11T06:38:28.315441 27106[27106] 0100c3508000fd67 24 EXEC 0.0004170000
2018-08-11T06:38:28.315463 27106[27106] 0100c3508000fd67 24 FETCH(0) 0.0000070000
2018-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)
2018-08-11T06:38:28.317123 27106[27106] 0100c3508000fd67 24 EXEC 0.0015610000
2018-08-11T06:38:28.317173 27106[27106] 0100c3508000fd67 24 commit
The 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.
However, 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:
select max(r_object_id), max(stamp) from dmi_queue_item
max(dmi_queue_item.r_object_id) max(dmi_queue_item.stamp)
------------------------------- -------------------------
1b00c35080009509 1b00c35080009509
(1 row affected)
And 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.
The 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, here, and here; an account at OpenText is needed to access those links) but it is still far from being usable.
I 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.
Fortunately, a search on-line brought the following document here.
NEXT_ID_LIST
This apply method reserves a list of object IDs from the repository.
…
Each ingested object requires a
unique object ID, and this method allows you to reserve a set of IDs. Once reserved, the IDs will not
be used by the repository, so you will not ingest an object ID in use by another object.
…
apply,c,NULL,NEXT_ID_LIST,TAG,I,08,HOW_MANY,I,100
Just what the doctor ordered !
In our case, we are dealing with dmi_queue_item. Its tag according to the System Object manual is 1b, i.e. 27.
Example of call:
apply,c,,NEXT_ID_LIST,TAG,I,27,HOW_MANY,I,1
next,c,q0
get,c,q0,next_id
1b00c3508000ad21
Note the 2-character type prefix in the returned id: 1b, i.e. 27 decimal, as requested.
This 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.
Since 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’être. A custom doctype could even reserve a special, dedicated attribute for this usage.
If 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).
The implementation
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:
create or replace trigger ask_rendition after insert or update on dm_sysobject_s for each row when (
(
new.r_object_id = '0900c350800001db'
) and
(
new.r_object_type in ('dm_document')
) and
(
new.a_content_type in ('crtext', 'doc', 'powerpoint', 'text', 'vsd') or
new.a_content_type like 'excel%' or
new.a_content_type like 'ppt%' or
new.a_content_type like 'msw%'
)
)
DECLARE
stampId DMI_QUEUE_ITEM_S.r_object_id%type;
format constant STRING(16) := 'XXXXXXXXXXXXXXXX';
BEGIN
stampID := :new.LOG_ENTRY;
-- has a rendition been requested ?
-- if so, the LOG_ENTRY attribute must contain a dmi_queue_item id, and nothing else;
IF 0 = regexp_instr(stampID, '^1b[0-9a-f]{14}$') THEN
-- no rendition requested, quit now;
RETURN;
END IF;
INSERT INTO DMI_QUEUE_ITEM_S (
R_OBJECT_ID,
NAME,
STAMP,
SENT_BY,
DATE_SENT,
DUE_DATE,
EVENT,
ITEM_NAME,
ITEM_ID,
ITEM_TYPE,
CONTENT_TYPE,
MESSAGE,
ROUTER_ID,
SUPERVISOR_NAME,
TASK_NUMBER,
TASK_NAME,
TASK_TYPE,
TASK_STATE,
DEPENDENCY_TYPE,
NEXT_TASKS_TYPE,
INSTRUCTION_PAGE,
PLAN_START_DATE,
ACTUAL_START_DATE,
READ_FLAG,
DELETE_FLAG,
PRIORITY,
POSITION,
DEQUEUED_BY,
DEQUEUED_DATE,
SIGN_OFF_REQUIRED,
SIGN_OFF_USER,
SIGN_OFF_DATE,
A_CONTENT_TYPE,
A_OPERATIONS,
SOURCE_DOCBASE,
TARGET_DOCBASE,
REMOTE_PENDING,
SOURCE_EVENT,
SOURCE_STAMP,
TASK_SUBJECT,
I_EVENT_FLAGS,
EVENT_DETAIL,
I_PARTITION,
I_IS_REPLICA,
I_VSTAMP)
VALUES (
stampId,
'dm_autorender_win31',
stampId,
'dmadmin',
sysdate,
TO_DATE('0001/01/01.00.00.00','YYYY/MM/DD.HH24.MI.SS'),
'rendition',
:new.object_name,
:new.r_object_id,
:new.r_object_type,
:new.a_content_type,
'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);
END;
/
show errors
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.
Lines 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.
Lines 9 to 12 are restrictions on the content type; others types can be included if needed.
Line 19 extracts the id to use as the queue’s r_object_id/stampid. As said above, we use the attribute LOG_ENTRY.
Lines 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.
Lines 26 to 117 set the values taken from the document into the queue’s entry columns.
Lines 78, 94, 95, 101 and 104: this is how Documentum represents nulldates; we comply with it instead of storing NULLs.
As shown, the trigger is fairly standard, no surprises here.
Usage
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.
# get an ID for the new queue entry; API> apply,c,,NEXT_ID_LIST,TAG,I,27,HOW_MANY,I,1 ... q0 API> next,c,q0 ... OK API> get,c,q0,next_id ... 1b00c3508000ad20 API> close,c,q0 ... OK # use document with id 0900c350800001db as test document; # set its LOG_ENTRY to that ID; fetch,c,0900c350800001db set,c,l,log_entry 1b00c3508000ad20 save,c,l # the trigger is fired right away; check the newly queue entry; dump,c,1b00c3508000ad20 ... USER ATTRIBUTES name : dm_autorender_win31 stamp : 1b00c3508000ad20 sent_by : dmadmin date_sent : 9/16/2018 14:17:20 due_date : nulldate event : rendition item_name : Blank PowerPoint Pre-3.0 Presentation item_id : 0900c350800001db item_type : dm_document content_type : powerpoint message : rendition_req_ps_pdf router_id : 0000000000000000 supervisor_name : dm_autorender_win31 ... # enqueue a request through the traditional way; queue,c,0900c350800001d0,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf ... 1b00c3508000ad21 # the next ID has been taken, no uniqueness violation;
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.:
API> fetch,c,0900c350800001db
...
OK
API> dump,c,l
...
USER ATTRIBUTES
object_name : Blank PowerPoint Pre-3.0 Presentation
title : TTT
subject :
authors []:
keywords []:
resolution_label :
owner_name : dmadmin
owner_permit : 7
group_name : docu
group_permit : 5
world_permit : 3
log_entry : 1b00c3508000ad20
...
API> set,c,l,title
SET> Burn !
...
OK
API> save,c,l
...
[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
ORA-06512: at "DMTEST.ASK_RENDITION", line 8
ORA-04088: error during execution of trigger 'DMTEST.ASK_RENDITION'"
-- let's retry but with a new id this time;
API> reset,c,l
...
OK
API> apply,c,,NEXT_ID_LIST,TAG,I,27,HOW_MANY,I,1
...
q0
API> next,c,q0
...
OK
API> get,c,q0,next_id
...
1b00c3508000ad28
API> set,c,l,log_entry
SET> 1b00c3508000ad28
...
OK
API> save,c,l
...
OK
-- any conflict ?
API> queue,c,0900c350800001d0,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf
...
1b00c3508000ad29
-- nope, it's fine;
However, 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).
Conclusion
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.
But 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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/CEC_web-min-scaled.jpg)