DCTM : DQL / API

---

——————— INSTALLATION / SERVER CONFIG ————————–

---

• Config folder Security

view source
print?
1
DQL : select folder_security from dm_docbase_config;
2
1 = enable

• Locales of docbase “Data Dictionary Locales”:

view source
print?
1
DQL : select  dd_locales from dm_docbase_config;
2
fr
3
en

• ACL default mode => 1 : folder

view source
print?
1
DQL : select default_acl from dm_server_config;

• DOCBASE OWNER (alias “dm_dbo”) represents the docbase owner.

view source
print?
01
DQL : select owner_name  from dm_docbase_config;
02
- DEV : DEMAT_ADMIN_DEV
03
- PROD : DEMAT_ADMIN_PROD
04
 
05
SELECT DISTINCT r_install_owner FROM dm_server_config;
06
- DEV : mygedadm <br /><br />select owner_name  from dm_docbase_config;<br />- DEV : MY_DOCBASE_NAME </pre>
07
<pre>select operator_name  from dm_server_config;
08
- DEV : DEMAT_ADMIN_DEV
09
- PROD : DEMAT_ADMIN_PROD
10
 
11
SELECT * from dm_user where user_name = 'dm_dbo';
12
OR SELECT * from dm_user where user_name = 'DEMAT_ADMIN_DEV';
13
OR SELECT * from dm_user where user_name = 'DEMAT_ADMIN_PROD'; 
14
 
15
- DEV :
16
r_object_id=110157xxxxxxxxx1
17
user_name=DEMAT_ADMIN_DEV
18
user_os_name=DEMAT_ADMIN_DEV
19
user_group_name=docu
20
 
21
- PROD :
22
r_object_id=1101c0xxxxxxxxx1
23
user_name=DEMAT_ADMIN_PROD
24
user_os_name=DEMAT_ADMIN_PROD
25
user_group_name=docu

• DOCBASE ID from r_object_id : In Documentum every object has a unique identifier (r_object_id) in the repository for accessing it and this unique identifier is composed of a 16 digit hexadecimal(4-bits) value (8 bytes). The structure of the object id is composed of 2-digit hexadecimal id (object type – not the same as r_object_type attribute of an object), a 6 digit hexadecimal docbase id and a 8 digit unique identifier for the corresponding object.

• Each Object Type is defined by a 2 digit hexadecimal identifier by Documentum Server internally : ’09’ for dm_document type, ‘0b’ for dm_folder type etc.
• Each Object has the next 6 digits as the docbase id to which it belongs to. The docbase id is theoretically unique globally.
• The rest of the 8 digit hexadecimal identifier defines a unique identifier to the object in the repository.

To get the DocbaseId :
– get a r_object_id : 090220c58052xxxx
– extract the 6 digits corresponding to the docbase id : 0220c5
– convert this hexa value to decimal : 0220c5 (HEXA) => 139461 (DECIMAL) (http://www.binaryhexconverter.com/hex-to-decimal-converter)
• Server installation folders:

view source
print?
01
DQL : select object_name, file_system_path, path_type, r_object_type from dm_location order by 2 ;
02
auth_plugin     D:\Documentum\dba\auth  directory   dm_location
03
 
04
assume_user     D:\Documentum\dba\dm_assume_user.exe    file        dm_location
05
 
06
change_password     D:\Documentum\dba\dm_change_password.exe    file        dm_location
07
 
08
config          D:\Documentum\dba\config    directory   dm_location
09
 
10
validate_user       D:\Documentum\dba\dm_check_password.exe file        dm_location
11
 
12
check_signature     D:\Documentum\dba\dm_check_password.exe file        dm_location
13
 
14
convert         D:\Documentum\product\7.1\convert   directory   dm_location
15
 
16
dsearch         D:\Documentum\fulltext\dsearch  directory   dm_location
17
 
18
dm_ca_store_fetch_location  D:\Documentum\share\temp\dm_ca_store    directory   dm_location
19
 
20
ldapcertdb_loc      D:\Documentum\dba\secure\ldapdb directory   dm_location
21
 
22
log         D:\Documentum\dba\log   directory   dm_location
23
 
24
dm_dba          D:\Documentum\dba   directory   dm_location
25
 
26
nls_chartrans       D:\Documentum\product\7.1\install\external_apps\nls_chartrans   directory   dm_location
27
 
28
temp            D:\Documentum\share\temp    directory   dm_location
29
 
30
my_store        \\MYFILESSERVER\DEV\Data\MY_DOCBASE_DEV\my_store    directory   dm_location
31
 
32
test_my         \\MYFILESSERVER\DEV\Data\MY_DOCBASE_DEV\test_my     directory   dm_location
33
 
34
encrypted_storage_01    \\MYFILESSERVER2\Data\MY_DOCBASE_DEV\encrypted_storage_01   directory   dm_location
35
 
36
storage_01      \\MYFILESSERVER3\data\MY_DOCBASE_DEV\content_storage_01 directory   dm_location
37
 
38
replica_storage_01  \\MYFILESSERVER3\data\MY_DOCBASE_DEV\replica_content_storage_01directory    dm_location
39
 
40
replicate_location  \\MYFILESSERVER3\data\MY_DOCBASE_DEV\replicate_temp_store   directory   dm_location
41
 
42
streaming_storage_01    \\MYFILESSERVER3\data\MY_DOCBASE_DEV\streaming_storage_01   directory   dm_location
43
 
44
thumbnail_storage_01    \\MYFILESSERVER3\data\MY_DOCBASE_DEV\thumbnail_storage_01   directory   dm_location
45
 
46
common          data\common directory   dm_location
47
 
48
events          data\events directory   dm_location

• Mount point (sharing):

view source
print?
1
DQL : select object_name, host_name, file_system_path from dm_mount_point ;
2
share   MYAPPDEV    d:\Documentum\share

• Configuration of Accelerated Content Server (ACS).
Reminder : ACS is a content server dedicated to serving content to requests from client applications (Application Server for web application OR User Station for WDK application).

view source
print?
1
DQL : select object_name, acs_base_url from dm_acs_config ;
2
MYDCTMSERVERACS1
3
MYDCTMSERVERACS1    http://myDctmServer:9080/ACS/servlet/ACS

---

——————— SERVER CONFIG LOG ————————–

---

• SET_OPTIONS : Turns tracing options off or on via the “sqltrace” = Traces SQL commands sent to the underlying RDBMS for subsequent sessions

view source
print?
1
API :  apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T
2
API : close,c,q0

---

——————— CREATION DOCUMENT ————————–

---

• Creation instance of custom type via pure API (example 1 : with transaction)

view source
print?
01
API : begintran,c
02
API : create,c,my_huo_custom_type
03
...
04
0xxxxxxxxxxcf7
05
API : set,c,l,object_name
06
My object for document 09xxxxxx61
07
...
08
OK
09
API : set,c,l,acl_domain
10
dm_dbo
11
...
12
OK
13
API : set,c,l,acl_name
14
my_acl_business
15
...
16
OK
17
API : set,c,l,my_tgt_object_id
18
09xxxxxx61
19
...
20
OK
21
API : set,c,l,my_date,'dd/mm/yyyy hh:mi:ss'
22
27/02/2016 15:23:37
23
...
24
OK
25
API : set,c,l,my_title
26
Title reclassification
27
...
28
OK
29
API : set,c,l,my_description
30
From 'AAAAA' / 'BBBBB' / 'CCCCC'
31
...
32
OK
33
API : save,c,l
34
...
35
OK
36
API : commit,c

• Creation instance of custom type via pure API (example 2 : one-line commands, repeating, setfile)

view source
print?
01
API : create,c,my_huo_custom_type
02
...
03
09xxxxxxxdf
04
API : set,c,l,ref_doc,'SCAN-HUO-JAVA123456789'
05
...
06
OK
07
API : set,c,l,acl_name,'MY-ACL-HUO'
08
...
09
OK
10
API : set,c,l,acl_domain,dm_dbo
11
...
12
OK
13
API : set,c,l,status,6
14
...
15
OK
16
API : set,c,l,category,2
17
...
18
OK
19
API : set,c,l,is_reprise,0
20
...
21
OK
22
API : set,c,l,my_repeat_field1[0],'JAVALU123'
23
...
24
OK
25
API : set,c,l,my_repeat_field1[1],'JAVALU456'
26
...
27
OK
28
API : set,c,l,my_repeat_field2[0],'009'
29
...
30
OK
31
API : set,c,l,my_date1,DATE(now)
32
...
33
OK
34
API : set,c,l,my_date2,DATE(’05/31/2115’)
35
...
36
OK
37
API : set,c,l,my_date3,DATE('NULLDATE')
38
...
39
OK
40
API : set,c,l,object_name,'MY_DOC_JAVALU123456'
41
...
42
OK
43
API : set,c,l,a_content_type
44
pdf
45
...
46
OK
47
API : setfile,c,l,T:\HUO\myPDFfile.pdf
48
...
49
OK
50
API : link,c,l,'/Cabinet1/Folder1/Folder12/01 - Mon Dossier 1'
51
...
52
OK
53
API : link,c,l,'/Cabinet1/Folder2/Folder22/01 - Mon Dossier 2'
54
...
55
OK
56
API : save,c,l
57
...
58
OK

• Creation instance of custom type via pure API (example 3 : repeating, setfile)

view source
print?
01
API : create,c,my_huo_custom_type
02
...
03
090xxxxxx08
04
API : set,c,l,acl_domain
05
dm_dbo
06
...
07
OK
08
API : set,c,l,acl_name
09
MY-ACL-HUO
10
...
11
OK
12
API : set,c,l,status
13
6
14
...
15
OK
16
API : set,c,l,my_repeat_field1[0]
17
70130069
18
...
19
OK
20
API : set,c,l,my_repeat_field1[1]
21
70017877
22
...
23
OK
24
API : set,c,l,my_repeat_field2[0]
25
009
26
...
27
OK
28
API : set,c,l,my_date1
29
31/05/2016
30
...
31
OK
32
API : set,c,l,my_date2
33
31/05/2115
34
...
35
OK
36
API : set,c,l,my_date3
37
NULLDATE
38
...
39
OK
40
API : set,c,l,ref_doc
41
SCAN-HUO-JAVA123456789
42
...
43
OK
44
API : set,c,l,is_reprise
45
0
46
...
47
OK
48
API : set,c,l,object_name
49
MY_DOC_JAVALU123456
50
...
51
OK
52
API : set,c,l,category
53
2
54
...
55
OK
56
API : link,c,l,'/Cabinet1/Folder1/Folder12/01 - Mon Dossier 1'
57
...
58
OK
59
API : link,c,l,'/Cabinet1/Folder2/Folder22/01 - Mon Dossier 2'
60
...
61
OK
62
API : set,c,l,a_content_type
63
pdf
64
...
65
OK
66
API : setfile,c,l,T:\HUO\myPDFfile.pdf
67
...
68
OK
69
API : save,c,l
70
...
71
OK

• Creation instance of custom type via pure API (example 4 : DM_API_E_CANT_UPDATE error, m_DMCLEAN, )

view source
print?
1
API : create,c,my_huo_custom_type
2
...
3
090xxxxxca

090xxxxxca = r_object_id of the newly created object

view source
print?
01
API : set,c,090xxxxxca,object_name
02
DATA=MY_DOC_JAVALU123456
03
...
04
API : set,c,090xxxxxca,a_content_type
05
DATA=pdf
06
...
07
API : setfile,c,090xxxxxca,T:\HUO\myPDFfile.pdf
08
...
09
API : link,c,090xxxxxca,'/Temp'
10
...
11
API : save,c,090xxxxxca
12
...
13
[DM_API_E_CANT_UPDATE]error: Cannot Save/Check-in object because the object is marked invalid.  This is most likely due to an error from a prior operation performed on the object.

As explained in https://community.emc.com/thread/132068?tstart=0, This error occurs if the previous operation on this object Id has got into trouble due to Network / docbase errors.
To get around this clear the _status attribute of the object by reset, c, Object_Id

view source
print?
1
API : reset,c,090xxxxxca
2
...
3
OK
4
API : save,c,090xxxxxca
5
...
6
OK

…checking of creation and deleting.
Note : The deletion of a N version is possible only if the current user has at least a BROWSE access pon the antecedent version N-1. If the current user has not access no rights on the N-1 version of document, the deletion of version N is refused by DCTM because the previous is invisible to this user with the below error:
Emc.Documentum.FS.Runtime.SerializableException]: “Delete” operation failed for object: 0902XXXXXXXXXXXX4b2. [DM_SYSOBJECT_E_CANT_DESTROY]error: “Cannot destroy MyDocument.pdf sysobject.”. [DM_SYSOBJECT_E_CANT_DESTROY]error: “Cannot destroy MyDocument.pdf sysobject.” (Fault Detail is equal to Emc.Documentum.FS.Runtime.SerializableException).

Example of a document with 3 versions (version 2 and 3 with same ACL):
+ VERSION 1 – no access for user (ACL_1)
+ VERSION 2 – DELETE access for user (ACL_2) => Deletion impossible (ERROR) => Deletion possible by the user after obtaining at least BROWSE access on version 1 to the USER (Rollback)
+ VERSION 3 – DELETE access for user (ACL_2) => Removal performed by the user without problems

 

view source
print?
01
DQL : select r_object_id, i_contents_id from my_huo_custom_type WHERE r_object_id = '090xxxxxca';
02
090xxxxxca  060yyyyyyyyyyyb8
03
 
04
DQL : select r_object_id, parent_id  from dmr_content WHERE r_object_id = '060yyyyyyyyyyyb8';
05
060yyyyyyyyyyyb8    090xxxxxca
06
 
07
DQL : execute get_path for '060yyyyyyyyyyyb8';
08
D:\Documentum\data\mydocbase\content_storage_01\xxxxxx\8092482\6c\6b.pdf
09
 
10
DQL : SELECT  * FROM dm_document WHERE folder('/Temp');
11
090xxxxxca  MY_DOC_JAVALU123456 ....
12
 
13
DQL : DELETE my_huo_custom_type objects where r_object_id = '090xxxxxca';
14
1
15
 
16
DQL : select r_object_id, i_contents_id from my_huo_custom_type WHERE r_object_id = '090xxxxxca';
17
___nothing
18
 
19
DQL : select r_object_id, parent_id  from dmr_content WHERE r_object_id = '060yyyyyyyyyyyb8';
20
060yyyyyyyyyyyb8 ''
21
 
22
DA : EXECUTION DU JOB dm_DMCLEAN
23
 
24
DQL : select r_object_id, parent_id  from dmr_content WHERE r_object_id = '060yyyyyyyyyyyb8';
25
___nothing

---

——————— UPDATE DOCUMENT ————————–

---

• Updating a document via API using DQL

view source
print?
1
API : execquery,c,F,UPDATE my_doc_pj OBJECTS SET my_attr1=0, SET my_attr2=1, SET my_attr3=1, SET object_name='MY_DOC PIECE IDENTITE' WHERE r_object_id = '0901xxxxxxxf'
2
close,c,q0
3
...
4
OK

• Updating a document in pure API (SOLUTION 1)

view source
print?
01
API : fetch,c,0901xxxxxf
02
...
03
OK
04
API : set,c,l,object_name
05
MY_DOC_PIECE IDENTITE
06
...
07
OK
08
API : set,c,l,my_attr1,0
09
...
10
OK
11
API : set,c,l,my_attr2,1
12
...
13
OK
14
API : set,c,l,my_attr3,1
15
...
16
OK
17
API : save,c,l
18
...
19
OK

• Updating a document in pure API (SOLUTION 2)

view source
print?
01
API : retrieve,c,my_doc_pj where r_object_id = '090xxxxxxxx6df'
02
...
03
090xxxxxxxx6df
04
API : set,c,l,my_attr1,0
05
...
06
OK
07
API : set,c,l,my_attr2,1
08
...
09
OK
10
API : set,c,l,my_attr3,1
11
...
12
OK
13
API : set,c,l,object_name
14
MY_DOC_PIECE IDENTITE
15
...
16
OK
17
API : save,c,l
18
...
19
OK

• Updating a document in pure API (SOLUTION 3)

view source
print?
01
API : set,c,080xxxxxxxxxx7a,event_date,'dd/mm/yyyy hh:mi:ss'
02
27/02/2017 13:11:19
03
...
04
OK
05
API : save,c,080xxxxxxxxxx7a
06
...
07
OK
08
API : set,c,080xxxxxx1d,event_date,'dd/mm/yyyy hh:mi:ss'
09
27/02/2017 13:11:19
10
...
11
OK
12
API : save,c,080xxxxxx1d
13
...
14
OK
15
API : set,c,080xxxxxxc50,event_date,'dd/mm/yyyy hh:mi:ss'
16
27/02/2017 13:11:19
17
...
18
OK
19
API : save,c,080xxxxxxc50
20
...
21
OK

• Updating a document in pure DQL

view source
print?
1
DQL : UPDATE my_doc_pj  (ALL) OBJECTS set owner_name='myuser1' where owner_name='myuser2';
2
UPDATE my_doc_pj  OBJECTS set my_attr10='6' where r_object_id ='[id]';

• Updating a repeating attribute of document in pure DQL

view source
print?
1
DQL : UPDATE my_doc_pj  (ALL) OBJECTS SET my_attr_repeating1[0]='0111', SET my_attr_repeating1[1] = '7878', SET my_attr_repeating1[2] = '4545', SET my_attr_repeating2[0] = '814', SET my_attr_repeating2[1] = '001', SET my_attr10=5  where r_object_id='090xxxxxxxxx62';

• Deleting a value in a repeating attribute of document in pure API

view source
print?
01
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating1[1]
02
...
03
OK
04
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating1[2]
05
...
06
[DM_OBJECT_W_DELETE_ATTR_POSITION_ERROR]warning: attempt to delete non-existent attribute 86 
07
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating1[1]
08
...
09
OK
10
API : save,c,0901xxxxxxxxxxx62
11
...
12
OK
13
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating2[1]
14
...
15
OK
16
API : save,c,0901xxxxxxxxxxx62
17
...
18
OK

• Updating a value in simple attribute, repeating attribute and attached aspect’s attribute of a immutable document in pure API.
Note: For the aspect’s attributes, it is necessary to execute these command through DFC (via DA), NOT through DLL (dqMan) otherwise a “[DM_API_E_BADATTRNAME]error: “Bad attribute name ‘my_huo_afs.my_attr’ for document/object.”” occurs.

view source
print?
01
API : set,c,{r_object_id},r_immutable_flag
02
SET : 0
03
...
04
OK
05
API : insert,c,{r_object_id},my_huo_aspect.projects_number[{pos}]
06
SET : {project_number}
07
...
08
OK
09
API : set,c,{r_object_id},my_attribute1
10
SET : my_value1
11
...
12
OK
13
API : save,c,{r_object_id}
14
...
15
OK
16
API : set,c,{r_object_id},r_immutable_flag
17
SET : 1
18
...
19
OK
20
API : save,c,{r_object_id}
21
...
22
OK

---

——————— CONTENT/RENDITON ————————–

---

• Get the content file of a document via API:

view source
print?
01
#----- DOCUMENT and CONTENT :
02
DQL : select r_object_id, i_contents_id from my_huo_document WHERE r_object_id = '090xxxxxx8f' order by r_creation_date desc enable(RETURN_TOP 50);
03
...
04
090xxxxxx8f 060xxxxa84
05
 
06
 
07
#----- Example 1: 090xxxxxx8f = ID of dm_document
08
API : getfile,c,090xxxxxx8f,'C:\temp\toto3.pdf',pdf
09
...
10
 
11
#----- Example 2: 090xxxxxx8f = ID of dm_document
12
API : getfile,c,090xxxxxx8f
13
...
14
D:\_____MyUser1dData____\DqMan 5.0.0.7\dmcl....\PJ_8140.pdf

• Document with a XML rendition

view source
print?
1
DQL : select r_object_id, rendition, parent_count, content_size, full_format, format, page, page_modifier, storage_id from dmr_content where any parent_id = '09xxxxxxxxxxxxxxx4';
2
...
3
060xxxxxxxxxxx50    0   1   11739   pdf 270xxxxxxxxxxxxc9   0       280xxxxxxxxxx100
4
060xxxxxxxxxxx51    2   1   2447    xml 270xxxxxxxxxxxxff   0       280xxxxxxxxxx100

### Get the CONTENT (or primary rendition) of first logical page

view source
print?
1
API : getfile,c,09xxxxxxxxxxxxxxx4
2
...
3
C:\APP\dqMan\MYSERVER\dmcl\...0xxxx5\....\mydoc.pdf

### Get the CONTENT of a XML rendition of first logical page

view source
print?
1
API : getfile,c,09xxxxxxxxxxxxxxx4,,xml
2
...
3
C:\APP\dqMan\MYSERVER\dmcl\...\0xxxx5\....\mydoc.xml

• Count of content by subtype of dm_document:

view source
print?
1
DQL : select d.r_object_type, count(d.i_contents_id) from dmr_content c, dm_document d where any c.parent_id = d.r_object_id group by d.r_object_type;
2
dm_document 21366
3
my_subytpe1_document    66
4
my_subytpe2_document    185
5
...

• Size of all contents in repository/docbase in Mo, only of CURRENT version of dm_document, only of all versions of dm_document:

view source
print?
01
DQL : select sum(content_size)/(1024*1024) from dmr_content;
02
TEST = 6388.72165775299 (~6Go)
03
PROD = 1021340.92345428 (~1To)
04
...
05
DQL : select sum(r_full_content_size)/(1024*1024) from dm_document;
06
42646.2632694244
07
...
08
DQL : select sum(r_full_content_size)/(1024*1024) from dm_document (ALL);
09
59575.4518547058
10
...
11
select (((sum(full_content_size)/1024)/1024)/1024) as sizein_GB from dmr_content
12
sizein_GB
13
73.3642595726997

• Number of objects by type that have content:

view source
print?
01
DQL : select d.r_object_type, count(*) from dmr_content c, dm_document d where any c.parent_id = d.r_object_id group by d.r_object_type;
02
...
03
dmc_jar 1107
04
dmc_preset_package  2
05
dmc_rps_schema  2
06
dmc_tcf_activity_template   20
07
dmc_xfm_adaptor_config  7
08
dmc_xfm_layout  5
09
dm_docset   1
10
dm_document 21352
11
dm_esign_template   1
12
dm_format_preferences   1
13
dm_menu_system  2
14
dm_plugin   2
15
dm_xfm_form 5
16
dm_xfm_instance 4
17
dm_xfm_resource 53
18
dm_xfm_schema   5
19
dm_xml_config   6
20
huo_client_document 631340
21
huo_company_document    66

• File size of all versions of the docbase in MB (by type) (ALL):

view source
print?
01
DQL : select d.r_object_type, sum(content_size)/(1024*1024) from dmr_content c, dm_document (ALL) d  where any c.parent_id = d.r_object_id group by d.r_object_type;
02
...
03
dmc_jar 13259.1457748413
04
dmc_preset_package  0.0616598129272461
05
dmc_rps_schema  0.0339202880859375
06
dmc_tcf_activity_template   0.0231971740722656
07
dmc_xfm_adaptor_config  0.00547695159912109
08
dmc_xfm_layout  0.0469779968261719
09
dm_docset   0.000401496887207031
10
dm_document 1978.51619911194
11
dm_esign_template   0.0441122055053711
12
dm_format_preferences   0.000110626220703125
13
dm_menu_system  0.345424652099609
14
dm_plugin   0.1298828125
15
dm_xfm_form 0.0764303207397461
16
dm_xfm_instance 0.00278568267822266
17
dm_xfm_resource 0.0879192352294922
18
dm_xfm_schema   0.0179119110107422
19
dm_xml_config   0.0766468048095703
20
huo_client_document 61000.0035915375
21
huo_company_document    48.2166175842285

• Get the physical folderpath of CONTENT (or primary rendition) of first logical page

view source
print?
01
# 090xxxxxxxx27= ID of dm_document
02
API : getpath,c,090xxxxxxxx27
03
...
04
\\MYFILESERVER\data\MY_DOCBASE_DEV\content_storage_01\...\00xxxx5\yy\xx\ce\8e.xls
05
 
06
# 090xxxxxxxxxc13= ID of dm_document
07
API : getpath,c,090xxxxxxxxxc13,,xml
08
...
09
\\MYDCTMSERVER\data\MY_DOCBASE_DEV\content_storage_01\...\00XXXX5\80\22\e3\98.xml
10
 
11
# 060xxxxx12 = ID of dm_content
12
DQL : execute get_path for '060xxxxx12';
13
d:\Documentum\data\mydocbase\content_storage_01\...\4f\4c.pdf
14
 
15
# 060xxxxxx5 = ID of dm_content
16
DQL : execute get_path for '060xxxxxx5';
17
d:\Documentum\data\mydocbase\content_storage_01\...\0\3c.txt
18
 
19
### Get the physical folderpath of CONTENT on CENTERA
20
# 090xxxxxxxxfc= ID of dm_document
21
API : getpath,c,090xxxxxxxxfc
22
...
23
8HP8XXXXXXXXXXXXXXPU

 

• Renditions / contents for a document with XML rendition with 1 logical page:

view source
print?
01
select distinct r_object_id, object_name, r_object_type, r_version_label, r_modify_date, title, r_creation_date, subject, owner_name, r_page_cnt, r_content_size, a_content_type, i_contents_id from my_huo_document 
02
where a_content_type = 'dxl' and r_object_id IN (ID('09xxxxxxxxxxxxxa47'));
03
# r_page_cnt : 1
04
# r_content_size : 19484
05
# a_content_type : dxl
06
# i_contents_id : 06xxxxxxxxxxx65
07
 
08
######### DMR CONTENT
09
select * from dmr_content where r_object_id = '06xxxxxxxxxxx65';
10
# parent_id [0]: 09xxxxxxxxxxxxxa47
11
# page [0]: 0
12
# rendition : 0
13
# parent_count : 1
14
 
15
select r_object_id, rendition, parent_count, content_size, full_format, format, page, page_modifier, storage_id from dmr_content where any parent_id = '09xxxxxxxxxxxxxa47';
16
# 06xxxxxxxxxxx65 0 1 19484 dxl 27xxxxxxxxxxxxx2c9 0 2xxxxxxxxxxxxxxx100
17
# 06xxxxxxxxxxx66 2 1 1694 xml 27xxxxxxxxxxxxx1ff 0 2xxxxxxxxxxxxxxx100
18
 
19
API : getfile,c,09xxxxxxxxxxxxxa47
20
C:\APP\dqMan\....\....\My FILE.dxl
21
 
22
API : getfile,c,09xxxxxxxxxxxxxa47,,dxl
23
C:\APP\dqMan\....\....\My FILE.dxl
24
 
25
API : getfile,c,09xxxxxxxxxxxxxa47,,dxl,0
26
C:\APP\dqMan\....\....\My FILE.dxl
27
 
28
API : getfile,c,09xxxxxxxxxxxxxa47,,xml
29
C:\APP\dqMan\....\....\My FILE.xml
30
 
31
API : getfile,c,09xxxxxxxxxxxxxa47,,xml,0
32
C:\APP\dqMan\....\....\My FILE.xml
33
 
34
API : getfile,c,09xxxxxxxxxxxxxa47,,dxl,1
35
[DM_SYSOBJECT_E_INVALID_PAGE_NUM]error:  "Invalid page number = 1 for My FILE sysobject.  Maximum page count is 1."
36
 
37
API : getfile,c,09xxxxxxxxxxxxxa47,,xml,1
38
[DM_SYSOBJECT_E_INVALID_PAGE_NUM]error:  "Invalid page number = 1 for My FILE sysobject.  Maximum page count is 1."

 

• Renditions / contents for a document WITHOUR  rendition but with 2 logical pages:

view source
print?
01
select distinct r_object_id, object_name, r_object_type, r_version_label, r_modify_date, title, r_creation_date, subject, owner_name, r_page_cnt, r_content_size, a_content_type, i_contents_id from  dm_policy 
02
where r_object_id IN (ID('46xxxxxxxxxxxxxxxb02'));
03
# r_page_cnt                 : 2
04
# r_content_size             : 5
05
# a_content_type             : text
06
# i_contents_id              : 06xxxxxxxxxxxx56e
07
######### DMR CONTENT
08
select * from dmr_content where  r_object_id = '06xxxxxxxxxxxx56e';
09
# parent_id               [0]: 46xxxxxxxxxxxxxxxb02
10
# page                    [0]: 0
11
# rendition                  : 0
12
# parent_count               : 1
13
 
14
select r_object_id, rendition, parent_count, content_size, full_format, format, page, page_modifier, storage_id from dmr_content where any parent_id = '46xxxxxxxxxxxxxxxb02';
15
# 06xxxxxxxxxxxx56e 0   1   5   text    27xxxxxxxxxxxxxxxd8 0       2xxxxxxxxxxxxxxx100
16
# 060220c58000056f  0   1   5   text    27xxxxxxxxxxxxxxxd8 1       2xxxxxxxxxxxxxxx100
17
 
18
API : getfile,c,46xxxxxxxxxxxxxxxb02
19
C:\APP\dqMan\....\....\5 Phases + Final.txt
20
 
21
API : getfile,c,46xxxxxxxxxxxxxxxb02,,text
22
C:\APP\dqMan\....\....\5 Phases + Final.txt
23
 
24
API : getfile,c,46xxxxxxxxxxxxxxxb02,,text,0
25
C:\APP\dqMan\....\....\5 Phases + Final.txt
26
 
27
API : getfile,c,46xxxxxxxxxxxxxxxb02,,text,1
28
C:\APP\dqMan\....\....\5 Phases + Final16806a9f.txt
29
 
30
API : getfile,c,46xxxxxxxxxxxxxxxb02,,text,2
31
[DM_SYSOBJECT_E_INVALID_PAGE_NUM]error:  "Invalid page number = 3 for 5 Phases + Final sysobject.  Maximum page count is 2."</pre>
32
<pre>

 

• Content Services for EMC Centera (CSEC)
Example of 2 instances/servers CENTERA (myserver1.mydomain.lu and myserver2.mydomain.lu) used by DCTM server MYDCTMSERVER1 via:

• 1 license file : D:\Documentum\dba\castore_license
• 1 fichier PEA dans D:\Documentum\dba\Centera\MYDEMAT.pea
• 1 dm_ca_store configured on the MY_DOCBASE docbase pointing on D:\Documentum\dba\Centera\MYDEMAT.pea on myserver1.mydomain.lu and myserver2.mydomain.lu servers

Configuration of Centera

view source
print?
01
### CENTERA store
02
select r_object_id from dm_ca_store;
03
...
04
r_object_id
05
6d0xxxxxx114  
06
 
07
### dm_store
08
API : dump,c,6d0xxxxxx114
09
...
10
  r_object_id                     : 6d0xxxxxx114
11
  name                       : centera_store_no_retention
12
  a_plugin_id                     : 670xxxxxx100
13
  a_storage_params             [0]: myserver1.mydomain.lu,myserver2.mydomain.lu?D:\Documentum\dba\Centera\MYDEMAT.pea
14
                               [1]: Read_option:read_ahead_size:170
15
                               [2]: Pool_option:max_connections:1
16
  a_content_attr_name           []: none
17
  a_retention_attr_name           :
18
  a_content_attr_desc           []: none
19
  a_retention_attr_required       : F
20
  a_default_retention_date        : nulldate
21
 
22
### dm_plugin
23
API : dump,c,670xxxxxx100
24
...
25
  r_object_id                : 670xxxxxx100
26
  object_name                : CSEC Plugin
27
  r_object_type              : dm_plugin
28
  i_contents_id              : 060xxxxxxxx13d
29
 
30
### dmr_content
31
API : dump,c,060xxxxxxxx13d
32
...
33
  r_object_id                : 060xxxxxxxx13d
34
  parent_id               [0]: 670xxxxxx100
35
  set_file                   : D:\Documentum\product\7.1\bin\emcplugin.dll
36
  i_format                [0]: 27xxxxxxxxx1ec
37
 
38
### dm_format
39
API : dump,c,27xxxxxxxxx1ec
40
...
41
  r_object_id                : 27xxxxxxxxx1ec
42
  name                       : win32shrlib
43
 
44
  description                : Windows Shared Library
45
 
46
  can_index                  : F
47
 
48
  topic_transform            : F

Use of Centera

view source
print?
01
### STORE
02
select * from dm_store ;
03
280220c580000100    filestore_01
04
6d0220c580000128    centera_store_no_retention
05
 
06
### DOC SUR FILESTORE
07
select r_object_id, i_contents_id from dm_document where a_storage_type = 'filestore_01' AND r_object_id  = '090220c5800001b2';
08
...
09
  r_object_id                : 090220c5800001b2
10
  a_storage_type             : filestore_01
11
  i_contents_id              : 060220c580000114
12
 
13
select r_object_id, parent_id, storage_id from dmr_content where storage_id IN (select r_object_id from dm_store) AND any parent_id = '090220c5800001b2' AND r_object_id = '060220c580000114';
14
...
15
  r_object_id                : 060220c580000114
16
  parent_id               [0]: 090220c5800001b2
17
  storage_id                 : 280220c580000100
18
 
19
### DOC SUR CENTERA
20
select r_object_id, i_contents_id from dm_document where a_storage_type = 'centera_store_no_retention' AND r_object_id  = '090220c5804eba70';
21
...
22
  r_object_id                : 090220c5804eba70
23
  a_storage_type             : centera_store_no_retention
24
  i_contents_id              : 060220c580481fd5  
25
 
26
select r_object_id, parent_id, storage_id from dmr_content where storage_id IN (select r_object_id from dm_ca_store) AND any parent_id = '090220c5804eba70' AND r_object_id = '060220c580481fd5';
27
...
28
  r_object_id                : 060220c580481fd5
29
  parent_id               [0]: 090220c5804eba70
30
  storage_id                 : 6d0220c580000128

 

view source
print?
1
</pre>
2
<p>select r_object_id, object_name, file_system_path, path_type, r_object_type from dm_location where LOWER(file_system_path) like '%javalufil007%';<br /># 3a0XXXXXXXXXXXXXXXXaf storage_04 \\JAVALUFIL007\E$\Documentum\MY_DOCBASE_PROD\storage_04 directory dm_location<br /># 3aXXXXXXXXXXXXX9b3 storage_03 \\JAVALUFIL007\DATA\MY_DOCBASE_PROD directory dm_location</p>
3
<p>select r_object_id, name, current_use, full_current_use, root, base_url from dm_filestore where root IN ('storage_04','storage_03');<br /># 280XXXXXXXXXx198 filestore_04 -1776072175 2961751362065 storage_04<br /># 28XXXXXXXXXXX146 filestore_03 -853300949 2782285506859 storage_03<br />select r_object_id, name, current_use, full_current_use, base_url from dm_store where name IN ('filestore_03','filestore_04');<br /># 28XXXXXXXXXXX146 filestore_03 -853300949 2782285506859<br /># 280XXXXXXXXXx198 filestore_04 -1743348149 2961784086091</p>
4
<pre>

 

---

——————— FOLDER/CABINET ————————–

---

• Count of subdirectories in a directory

view source
print?
1
DQL : SELECT count(object_name) FROM dm_folder WHERE FOLDER('/Temp') AND object_name LIKE 'dm%' ;
2
0
3
DQL : SELECT count(object_name) FROM dm_folder WHERE FOLDER('/Temp',descend) AND object_name LIKE 'dm%' ;
4
42

• Count of documents (all versions) in a directory

view source
print?
1
DQL : SELECT count(mytype.r_object_id) FROM my_subtype_document (ALL) mytype, dm_folder dmfolder
2
WHERE FOLDER('/MY_CLIENTS',descend)
3
    AND any mytype.i_folder_id=dmfolder.r_object_id
4
    AND dmfolder.r_folder_path IS NOT NULLSTRING
5
    AND dmfolder.r_folder_path like '/MY_CLIENTS/1789%'
6
ENABLE (ROW_BASED);
7
123

---

——————— LEFT OUTER JOIN ————————–

---

• Use of LEFT OUTER JOIN in DQL:

view source
print?
01
DQL : select d.r_object_id, p.project_id, c.company_id
02
from my_document d
03
left outer join dm_dbo.myproject p on d.project_id = p.project_id
04
left outer join dm_dbo.mycompany c on d.company_id = c.company_id
05
where r_object_id IN(
06
'09xxxxxxxxxxx3'
07
) order by r_modify_date desc;
08
...
09
r_object_id=09xxxxxxxxxxx3
10
project_id=
11
company_id=123456

• Use of LEFT OUTER JOIN in DQL with sub-query calculating the position of 2 aliasnames ‘MyFirstAlias’ and ‘MySecondAlias’ in aliasset whose name begins with ‘AS_project_%’ and does not end with ‘% _archived’:

view source
print?
01
DQL : select alias_set.r_object_id, myfirstalias.pos as myfirstalias_pos, mysecondalias.pos as mysecondalias_pos
02
from dm_alias_set alias_set
03
left outer join (select r_object_id, -(i_position+1) as pos from dm_dbo.dm_alias_set_r where alias_name in ('MyFirstAlias')) myfirstalias on alias_set.r_object_id = myfirstalias.r_object_id 
04
 
05
left outer join (select r_object_id, -(i_position+1) as pos from dm_dbo.dm_alias_set_r where alias_name in ('MySecondAlias')) mysecondalias on alias_set.r_object_id = mysecondalias.r_object_id 
06
 
07
where alias_set.object_name LIKE 'AS\_project\_%'  escape '\' AND  LOWER(alias_set.object_name) NOT LIKE '%_archived' ;
08
 
09
............
10
66xxxxxxxxxx11  0   2
11
66xxxxxxxxxx01  1   3
12
66xxxxxxxxxxe2  1   3
13
 
14
...........
15
#Generation of API script via dqMan:
16
 
17
remove,c,{r_object_id},alias_name[{myfirstalias_pos}]
18
remove,c,{r_object_id},alias_value[{myfirstalias_pos}]
19
remove,c,{r_object_id},alias_category[{myfirstalias_pos}]
20
remove,c,{r_object_id},alias_usr_category[{myfirstalias_pos}]
21
remove,c,{r_object_id},alias_description[{myfirstalias_pos}]
22
remove,c,{r_object_id},alias_name[{mysecondalias_pos}]
23
remove,c,{r_object_id},alias_value[{mysecondalias_pos}]
24
remove,c,{r_object_id},alias_category[{mysecondalias_pos}]
25
remove,c,{r_object_id},alias_usr_category[{mysecondalias_pos}]
26
remove,c,{r_object_id},alias_description[{mysecondalias_pos}]
27
append,c,{r_object_id},alias_name
28
MyNewAlias
29
append,c,{r_object_id},alias_value
30
all_teams_users
31
append,c,{r_object_id},alias_category
32
2
33
append,c,{r_object_id},alias_usr_category
34
-1
35
append,c,{r_object_id},alias_description
36
Alias entry for all teams
37
save,c,{r_object_id}

---

——————— REPEATING ATTRIBUTE ————————–

---

• Calculate the POSITION of aliasnames ‘Readers’ and ‘Editors’ in ALIAS SETs (repeating attribute):

view source
print?
01
DQL : describe dm_dbo.dm_alias_set_r;
02
...
03
alias_category  Integer
04
alias_description   Char(255)
05
alias_name  Char(32)
06
alias_usr_category  Integer
07
alias_value Char(255)
08
i_position  Integer
09
r_object_id Char(16)    
10
 
11
 
12
DQL : describe dm_dbo.dm_alias_set_s;
13
...
14
i_is_replica    Integer
15
i_vstamp    Integer
16
object_description  Char(128)
17
object_name Char(32)
18
owner_name  Char(255)
19
r_object_id Char(16)    
20
 
21
DQL : select r_object_id, -(i_position+1) as pos, alias_name from dm_dbo.dm_alias_set_r where alias_name in ('Readers','Editors') order by 1 asc, 2 desc;
22
...
23
660xxxxxxxxxxx36    16  Editors
24
660xxxxxxxxxxx36    14  Readers
25
660xxxxxxxxxxxc7    3   Editors
26
660xxxxxxxxxxxc7    1   Readers
27
 
28
API : dump,c,660xxxxxxxxxxxc7
29
...
30
USER ATTRIBUTES
31
  owner_name                 : mysuper001
32
  object_name                : AS_myproject_123456
33
  object_description         : Alias set for my docs of project 123456
34
  alias_name             [ 0]: OtherTeamMembers
35
                         [ 1]: Readers
36
                         [ 2]: Contributors
37
                         [ 3]: Editors
38
                         [ 4]: Administrators
39
  alias_value            [ 0]: my_grp_nobody
40
                         [ 1]: my_grp_all
41
                         [ 2]: my_grp_nobody
42
                         [ 3]: my_grp_nobody
43
                         [ 4]: my_grp_nobody
44
  alias_category         [ 0]: 2
45
                         [ 1]: 2
46
                         [ 2]: 2
47
                         [ 3]: 2
48
                         [ 4]: 2
49
  alias_usr_category     [ 0]: -1
50
                         [ 1]: -1
51
                         [ 2]: -1
52
                         [ 3]: -1
53
                         [ 4]: -1
54
  alias_description      [ 0]:
55
                         [ 1]:
56
                         [ 2]:
57
                         [ 3]:
58
                         [ 4]:
59
SYSTEM ATTRIBUTES
60
  r_object_id                : 660xxxxxxxxxxxc7
61
APPLICATION ATTRIBUTES
62
INTERNAL ATTRIBUTES
63
  i_is_replica               : F
64
  i_vstamp                   : 15

 

Other example:

view source
print?
1
<br />select r_object_id, r_accessor_name, -(i_position+1) as pos from dm_acl where r_accessor_name = '%Editors' and acl_class = 1 enable (ROW_BASED) ;<br /># 450XXXXXXXXXXXX104   %Editors    21<br /># 450XXXXXXXXXXXX104  %Editors    20<br /># 450XXXXXXXXXXXX973  %Editors    10<br /># 450XXXXXXXXXXXX46   %Editors    10<br /># 450XXXXXXXXXXXXa6   %Editors    10<br /># 450XXXXXXXXXXXX3a   %Editors    8<br /># 450XXXXXXXXXXXX3b    %Editors    8<br /># 450XXXXXXXXXXXX37    %Editors    8<br /># 450XXXXXXXXXXXX38    %Editors    8<br />

 

---

——————— DOCUMENT PROPERTIES ————————–

---

• Displaying all the attributes of an object (except of aspect’s attributes attached)

view source
print?
1
API : dump,c,4c01cxxxxxxae
2
...
3
API : get,c,4c01cxxxxxxae,_dump
4
#System.out.println(dfDocument.getString("_dump"));
5
...
6
r_object_id : 4c01cxxxxxxae
7
object_name : test name
8
title :
9
...

• Displaying xml of an object (except of aspect’s attributes attached)

view source
print?
1
API : get,c,090XXXXXXXXXXXXXXX03,_xml_string
2
# System.out.println(dfDocument.getString("_xml_string"));
3
...
4
...

• Generate the query in order to create a custom type (via SAMSON)

view source
print?
01
DQL : genesis my_type;
02
...
03
CREATE TYPE my_type (
04
id_client CHAR(32) REPEATING,
05
format INT,
06
date_reception DATE,
07
date_creation DATE,
08
statut_valid INT,
09
type_doc CHAR(7) REPEATING,
10
category INT,
11
refs_operation CHAR(25) REPEATING,
12
reprise BOOL,
13
date_validite DATE,
14
ref_doc CHAR(100),
15
scan_date DATE,
16
my_comments_hist CHAR(255) REPEATING,
17
merging_parents_id ID REPEATING,
18
code CHAR(32),
19
my_comment CHAR(255)
20
)
21
WITH SUPERTYPE dm_document
22
go
23
 
24
ALTER TYPE my_type MODIFY
25
my_comments_hist CHAR(512),
26
my_comment CHAR(512)
27
go

• Display the attribute informations of a custom type

view source
print?
01
DQL : describe my_type;
02
or
03
API : describe,c,type,my_type;
04
.....
05
DQL : describe my_type;
06
Type Name:  my_type
07
SuperType Name: dm_document
08
 
09
Attributes:    112
10
 
11
object_name                       CHAR(255)
12
r_object_type                     CHAR(32)
13
title                             CHAR(400)
14
subject                           CHAR(192)
15
authors                           CHAR(48)    REPEATING
16
keywords                          CHAR(48)    REPEATING
17
a_application_type                CHAR(32)
18
a_status                          CHAR(16)
19
r_creation_date                   TIME
20
r_modify_date                     TIME
21
r_modifier                        CHAR(32)
22
r_access_date                     TIME
23
a_is_hidden                       BOOLEAN
24
i_is_deleted                      BOOLEAN
25
a_retention_date                  TIME
26
a_archive                         BOOLEAN
27
a_compound_architecture           CHAR(16)
28
a_link_resolved                   BOOLEAN
29
i_reference_cnt                   INTEGER
30
i_has_folder                      BOOLEAN
31
i_folder_id                       ID          REPEATING
32
r_composite_id                    ID          REPEATING
33
[...]
34
r_immutable_flag                  BOOLEAN
35
r_frozen_flag                     BOOLEAN
36
r_has_events                      BOOLEAN
37
acl_domain                        CHAR(32)
38
acl_name                          CHAR(32)
39
[...]
40
i_partition                       INTEGER
41
i_is_replica                      BOOLEAN
42
i_vstamp                          INTEGER
43
[...]
44
id_client CHAR(32)                REPEATING
45
format                            INTEGER
46
date_reception                    DATE
47
date_creation                     DATE
48
statut_valid                      INTEGER
49
type_doc                          CHAR(7) REPEATING
50
category                          INTEGER
51
refs_operation                    CHAR(25) REPEATING
52
reprise                           BOOL
53
date_validite                     DATE
54
ref_doc                           CHAR(100)
55
scan_date                         DATE
56
my_comments_hist                  CHAR(512) REPEATING
57
merging_parents_id                ID REPEATING
58
code                              CHAR(32)
59
my_comment                        CHAR(512)

view source
print?
001
API : get,c,090XXXXXXXXXXXXXXX03,_typestring
002
# System.out.println(dfDocument.getString("_typestring"));
003
...
004
3
005
TYPE dm_sysobject 030XXXXXXXXXXXXXX105
006
 NULL
007
86
008
  object_name  STRING S 255
009
  r_object_type  STRING S 32
010
  title  STRING S 400
011
  subject  STRING S 192
012
  authors  STRING R 48
013
  keywords  STRING R 48
014
  a_application_type  STRING S 32
015
  a_status  STRING S 16
016
  r_creation_date  TIME S 0
017
  r_modify_date  TIME S 0
018
  r_modifier  STRING S 255
019
  r_access_date  TIME S 0
020
  a_is_hidden  BOOL S 0
021
  i_is_deleted  BOOL S 0
022
  a_retention_date  TIME S 0
023
  a_archive  BOOL S 0
024
  a_compound_architecture  STRING S 16
025
  a_link_resolved  BOOL S 0
026
  i_reference_cnt  INT S 0
027
  i_has_folder  BOOL S 0
028
  i_folder_id  ID R 0
029
  r_composite_id  ID R 0
030
  r_composite_label  STRING R 32
031
  r_component_label  STRING R 32
032
  r_order_no  INT R 0
033
  r_link_cnt  INT S 0
034
  r_link_high_cnt  INT S 0
035
  r_assembled_from_id  ID S 0
036
  r_frzn_assembly_cnt  INT S 0
037
  r_has_frzn_assembly  BOOL S 0
038
  resolution_label  STRING S 32
039
  r_is_virtual_doc  INT S 0
040
  i_contents_id  ID S 0
041
  a_content_type  STRING S 32
042
  r_page_cnt  INT S 0
043
  r_content_size  INT S 0
044
  a_full_text  BOOL S 0
045
  a_storage_type  STRING S 64
046
  i_cabinet_id  ID S 0
047
  owner_name  STRING S 255
048
  owner_permit  INT S 0
049
  group_name  STRING S 255
050
  group_permit  INT S 0
051
  world_permit  INT S 0
052
  i_antecedent_id  ID S 0
053
  i_chronicle_id  ID S 0
054
  i_latest_flag  BOOL S 0
055
  r_lock_owner  STRING S 255
056
  r_lock_date  TIME S 0
057
  r_lock_machine  STRING S 80
058
  log_entry  STRING S 120
059
  r_version_label  STRING R 32
060
  i_branch_cnt  INT S 0
061
  i_direct_dsc  BOOL S 0
062
  r_immutable_flag  BOOL S 0
063
  r_frozen_flag  BOOL S 0
064
  r_has_events  BOOL S 0
065
  acl_domain  STRING S 255
066
  acl_name  STRING S 32
067
  a_special_app  STRING S 32
068
  i_is_reference  BOOL S 0
069
  r_creator_name  STRING S 255
070
  r_is_public  BOOL S 0
071
  r_policy_id  ID S 0
072
  r_resume_state  INT S 0
073
  r_current_state  INT S 0
074
  r_alias_set_id  ID S 0
075
  a_effective_date  TIME R 0
076
  a_expiration_date  TIME R 0
077
  a_publish_formats  STRING R 32
078
  a_effective_label  STRING R 32
079
  a_effective_flag  STRING R 8
080
  a_category  STRING S 64
081
  language_code  STRING S 5
082
  a_is_template  BOOL S 0
083
  a_controlling_app  STRING S 32
084
  r_full_content_size  DOUBLE S 0
085
  a_extended_properties  STRING R 32
086
  a_is_signed  BOOL S 0
087
  a_last_review_date  TIME S 0
088
  i_retain_until  TIME S 0
089
  r_aspect_name  STRING R 64
090
  i_retainer_id  ID R 0
091
  i_partition  INT S 0
092
  i_is_replica  BOOL S 0
093
  i_vstamp  INT S 0
094
TYPE dm_document 030XXXXXXXXXX129
095
dm_sysobject
096
0
097
TYPE my_cpy_document 030XXXXXXX59
098
dm_document
099
 
100
4
101
  my_metadata1_id  INT S 0
102
  my_metadata1_name  STRING S 160
103
  my_metadata2_id  INT S 0
104
  my_metadata2_name  STRING S 160
105
TYPE my_document 030XXXXXXXXXXXXX25a
106
 
107
my_cpy_document
108
3
109
  my_metadata3_id  INT S 0
110
  my_metadata3_name  STRING S 255
111
  is_permanent  BOOL S 0

---

——————— ACTIONS DOCUMENT / FOLDER ————————–

---

• Request to move directories:
#### Via DA: Identify folders that need to be moved, then manually add those folders to Clipboard (Edit –> Add to Clipboard), go to the new folder location then “Edit –> Move Here” using Webtop or DA.

#### via DQL:

view source
print?
1
update dm_folder objects  move to '/New_Cabinet/New_Folder' where
2
r_object_id in (select r_object_id from dm_folder where object_name = 'MySubFolder' and any r_folder_path like '/Current_Cabinet/MyFolder%')
3
OR
4
r_object_id in (select r_object_id from dm_folder where r_creation_date < DATE('date_value', 'mm/dd/yyyy') and FOLDER('/Current_Cabinet/Current_Folder', descend))
5
;

• Request to move documents:

view source
print?
1
update dm_document object move to '/New_Cabinet/New_Folder/SubFolderName' where r_object_id='090xxxxxxx140';
2
 
3
update dm_document (all) objects move to '/New_Cabinet/New_Folder/SubFolderName' where FOLDER('/Current_Cabinet/Current_Folder',descend)
4
    AND r_modify_date >= date('2015-01-01 00:00:00','yyyy-mm-dd hh:mi:ss')  AND r_modify_date <= date('2015-01-31 23:59:59','yyyy-mm-dd hh:mi:ss') ;
5
 
6
update my_huo_document (all) OBJECTS move to '/Current_Cabinet/MyFolder/MySubFolder' where r_object_id IN ('090xxxxxx811', '090xxxxxxxd03');
7
;

 

• Freeze, checkin, checkout, lock,…a document:

http://www.javablog.fr/documentum-freeze-checkin-checkout-lock.html

---

——————— INDEX ————————–

---

During the creation of INDEX in Documentum, it is possible to precise the tablespace used. If the tablespace is unspecified, the default is the tablespace or segment associated with the repository. The behaviour is same during the creation of a type.

Same problem occurs, if the DBA creates the indexes. In Oracle, if the tablespace is unspecified, the INDEXEs are created in the same tablespace used for the table.

An example of error would be :
[DM_QUERY_E_CURSOR_ERROR]error: “A database error has occurred during the creation of a cursor (‘ORA-01652: unable to extend temp segment by 128 in tablespace TEMP’).”

So, if the INDEXEs are created in DOCUMENTUM, there are some DQL methods to move, drop INDEX to TABLESPACES.

—— MAKE_INDEX
Creates an index for a persistent object type.
EXECUTE make_index
WITH type_name=’object_type’,attribute=’property_name’
{,attribute=’property_name’}
[,unique=true|false][,index_space=’name’][,index_name=’name’]
[,use_id_col=true|false][,use_pos_col=true|false]
[,global_index=true|false]
[,PARALLEL_DEGREE=<integer value>][,NOLOGGING=[true|false][,ONLINE =true|false]

+ INDEX_SPACE STRING Name of tablespace or segment : Specifies the tablespace or segment in which to store the index. If unspecified, the default is the tablespace or segment associated with the repository.

+ Permissions = You must have Superuser privileges to use this method.

—— MOVE_INDEX
Moves an existing object type index from one tablespace or segment to another.
EXECUTE move_index FOR ‘dmi_index_obj_id’ WITH name = ‘new_home’ [,global_index=true|false]

+ NAME STRING new_home
Identifies the new tablespace or segment for the index. Use the name of the tablespace or segment.

+ GLOBAL_INDEX BOOLEAN TRUE or FALSE
Indicates whether the index being created is a global index. This parameter can only be used for partitioned types. The default is FALSE.

+ Permissions = You must have Sysadmin or Superuser privileges to use this method.

—— DROP_INDEX
Destroys a user-defined object type index.
EXECUTE drop_index [[FOR] ‘dmi_index_id’] [WITH name = ‘index_name’]
Do not include the FOR clause if you include the NAME argument.

+ NAME STRING index_name
Identifies the index by the name of its index (dmi_index) object.

+ Permissions = You must have Superuser privileges to use this method.

 

 

• Creation of DCTM INDEX via MAKE_INDEX DQL command. The Superuser privileges is needed to use this method.
The attribute <bmy_huo_field1< b=””> belongs to my_huo_type type, creation of simple index via the following sentence:

view source
print?
1
DQL : EXECUTE make_index WITH type_name = 'my_huo_type', attribute = 'my_huo_field1'

The attributes <bmy_huo_field12< b=””> and <bmy_huo_field34< b=””> belong to my_huo_type type, creation of composite index via the following sentence:

view source
print?
1
DQL : EXECUTE make_index WITH type_name = 'my_huo_type', attribute = 'my_huo_field12', attribute = 'my_huo_field34'

IN ORACLE : Creation of an index (DataBase level) in my table MY_TABLE whith the next command:

view source
print?
1
CREATE INDEX indice_huo_1 ON MY_HUO_TABLE_S (ID_OBJECT,MY_HUO_FIELD2)

</bmy_huo_field34<></bmy_huo_field12<></bmy_huo_field1<>

Creation of index on attributes my_huo_field96 belongs to my_huo_aspect ASPECT, via the following sentence:

1. Find the internal name (aspect table) of an aspect:

   view source
   print?
   1
   DQL : select i_attr_def from dmc_aspect_type   where object_name = 'my_huo_aspect';
   2
   dmi_030xxxxxx0b

2. Make the index

   view source
   print?
   1
   DQL : EXECUTE make_index WITH type_name = 'dmi_030xxxxxx0b', attribute='my_huo_field96'

• Destroying a user-defined object type index via DROP_INDEX DQL command :
The Superuser privileges is needed to use this method.
These examples illustrate using EXECUTE to drop a user-defined index on the dm_user object type. The first example identifies the index by its name, user_index, and the second example identifies the index by its object ID.

view source
print?
1
DQL : EXECUTE drop_index WITH name=’user_index’
2
DQL : EXECUTE drop_index FOR ’1f00000011231563a’

• List the indexes on my_huo_type type

view source
print?
01
DQL : select i.r_object_id as index_id, i.index_type as type_id, i.attribute as attributes, i.attr_count, i.name as index_name, i.data_space
02
from dmi_index i
03
where any i.attribute >= 0 and i.index_type in (select t.r_object_id from dm_type t where t.name='my_huo_type');
04
...
05
1fxxxxx00   030xxxxxx25a    117 1   IDX_HUO_TYPE_MY_HUO_FIELD_1 DM_MY_DOCBASE_DEV_INDEX
06
1fxxxxx52   030xxxxxx25a    101,114 2   IDX_HUO_TYPE_MY_HUO_FIELD_1_AND_3   DM_MY_DOCBASE_DEV_INDEX
07
 
08
select * from dm_type t where t.name='my_huo_type';
09
  name                       : my_huo_type
10
  attr_name             [117]: my_huo_field1
11
                        [101]: my_huo_field2
12
                        [114]: my_huo_field3

• List the indexes on my_huo_aspect ASPECT:

view source
print?
1
DQL : select i.r_object_id as index_id, i.index_type as type_id, i.attribute as attributes, i.attr_count, i.name as index_name, i.data_space
2
from dmi_index i
3
where any i.attribute >= 0 and i.index_type in (select t.r_object_id from dm_type t where t.name='dmi_03xxxxxx57'); 
4
 
5
1f0xxxxxxxxe45  030xxxxxxx257   2   1   IDX_MAILASP_APPROVERS   DM_MY_DOCBASE_DEV_INDEX
6
1f0xxxxxxxxe46  030xxxxxxx257   3   1   IDX_MAILASP_REQUESTER   DM_MY_DOCBASE_DEV_INDEX
7
1f0xxxxxxxxe48  030xxxxxxx257   3   2   IDX_MAILASP_STATUS_REQ  DM_MY_DOCBASE_DEV_INDEX
8
1f0xxxxxxxxe48  030xxxxxxx257   0   2   IDX_MAILASP_STATUS_REQ  DM_MY_DOCBASE_DEV_INDEX

• Check if an index exists on an attribute my_huo_field123 of my_huo_type:

view source
print?
01
DQL : select i.r_object_id as index_id, i.index_type as type_id, i.attribute as attributes, i.attr_count, i.name as index_name, i.data_space
02
from dmi_index i
03
where any i.attribute >= 0 and i.index_type in (select t.r_object_id from dm_type t where t.name='my_huo_type')
04
and any i.attribute IN (
05
    select -(r.i_position+1)
06
    from dm_dbo.dm_type_r r, dm_dbo.dm_type_s s
07
    where r.attr_name in ('my_huo_field123')
08
        and r.r_object_id = s.r_object_id
09
    and s.name = 'my_huo_type'
10
); 

---

——————— WORKFLOW / PROCESS / ACTIVITY / METHOD ————————–

---

• Getting a List of All the Installed Workflows.
->Workflow template must be “installed”, which means that all of its associated activities are all valid and installed.

view source
print?
1
select * from dm_process where r_definition_state=2;
2
...
3
r_definition_state
4
0 = Draft
5
1 = Validated
6
2 = Installed

• Workflow instance informations:

view source
print?
01
select * from dm_process where object_name  like 'my_demat_wkf%';
02
  r_object_id                : 4bxxxxxxxxxx29b
03
  r_object_type              : dm_process
04
  object_name                : my_demat_wkf
05
  r_act_def_id = dm_activity.r_object_id
06
    [0]: 4c02xxxxx0291
07
    [1]: 4c02xxxxxx292
08
    [2]: 4c02xxxxxx293
09
    [3]: 4c02xxxxxx294
10
    [4]: 4c02xxxxxx295
11
    [5]: 4c02xxxxxx296
12
    [6]: 4c02xxxxxx297
13
 
14
----- dm_process.r_act_def_id = : 4c02xxxxx0291 = dm_activity.r_object_id

• Activity instance informations:

view source
print?
1
SELECT r_object_id, object_name, exec_method_id FROM dm_activity where r_object_id = '4c02xxxxx0291';
2
  r_object_id                : 4c02xxxxx0291
3
  object_name                : Inclassable
4
  exec_method_id             : 10xxxxxx622a
5
 
6
----- dm_activity.exec_method_id : 10xxxxxx622a = dm_method.r_object_id

• To view task in inbox
The following query will return the items in myuser1’s inbox. In this query, the item_id is the r_object_id of the document in the inbox and the stamp is the r_object_id of the dmi_queue_item that represents the work item.

view source
print?
1
SELECT stamp, item_id, item_name, date_sent, priority FROM dm_queue WHERE name = 'myuser1';

• Workflows according to their state => ALL RUNNING (400 wkf)

view source
print?
1
select count(*), r_runtime_state   from dm_workflow group by r_runtime_state;

• Workflows with an activity in error (200 wkf)

view source
print?
1
select * from dm_workflow where any r_act_state in(-1,3,4,0);

• Documents without workflow:

view source
print?
1
SELECT r_object_id, object_name, r_modify_date FROM huo_my_document WHERE i_chronicle_id not in (select r_component_chron_id FROM dmi_package);

• Document as part of a workflow:

view source
print?
1
select w.object_name from dm_workflow w, dmi_package p, dm_document d
2
    where w.r_object_id=p.r_workflow_id
3
    and any p.r_component_chron_id = d.r_object_id

• For a workflow, see the workitems that has a non existing dmi_queue_item reference ( = r_object_id of workflow = 4d0xxxxxx4905):

view source
print?
1
select r_object_id as workitem_id, r_performer_name, r_queue_item_id
2
    from dmi_workitem where r_workflow_id = 'workflowId'
3
    and r_queue_item_id != '0000000000000000'
4
    and r_queue_item_id not in (select r_object_id from dmi_queue_item);

• List workflow attachments ( = r_object_id of workflow = 4d0xxxxxx4905):

view source
print?
1
select r_component_id, r_component_name from dmi_wf_attachment where r_workflow_id = 'workflowId';

• List workflow information ( = r_object_id of workflow = 4d0xxxxxx4905):

view source
print?
1
select task_name, task_state, actual_start_date, dequeued_date from dmi_queue_item where router_id = 'workflowId';

• Find active workflows, supervisors of the workflows:

view source
print?
1
select r_object_id, object_name, title, owner_name,r_object_type, r_creation_date, r_modify_date, a_content_type
2
    from dm_document
3
    where r_object_id in(select r_component_id from dmi_package where r_workflow_id in (select r_object_id from dm_workflow where r_runtime_state = 1));

• Select the components of a workitem:

view source
print?
1
select r_component_id from dmi_package p where
2
    exists (select r_object_id from dmi_workitem w where w.r_object_id = and w.r_workflow_id = p.r_workflow_id and w.r_act_seq_no = p.r_act_seq_no);

• Find workflows of a document:

view source
print?
1
select r_object_id, object_name, process_id, r_runtime_state, supervisor_name, parent_id, parent_act_name, r_act_state, parent_act_seqno
2
    from dm_workflow where r_object_id in (select r_workflow_id from dmi_package where any r_component_id
3
    in (select r_object_id from dm_sysobject (all) where i_chronicle_id
4
        in (select i_chronicle_id from dm_sysobject where r_object_id='090xxxxxx178a'))) and r_runtime_state='1';
5
...
6
# Workflow.r_object_id = '4d0xxxxxx4905'
7
# Workflow.r_runtime_state=1 = RUNNING
8
# Workflow.r_act_state = '2,4' --- Finished, Failed'

• Find the QUEUE_ITEM of a document workflow ( = r_object_id of workflow = 4d0xxxxxx4905):

view source
print?
1
select r_object_id, item_id, item_name, task_name, task_state, actual_start_date, dequeued_date from dmi_queue_item where router_id = 'workflowId'
2
...
3
# r_object_id  item_id  item_name  task_name  task_state  actual_start_date  dequeued_date
4
# 1b0xxxxxxxxx993a  4a0xxxxxxx915  paused  1  paused

• Find the WORKITEM, ACTIVITY of a workflow:

view source
print?
01
select r.r_object_id as workitem_id, r.r_runtime_state as workitem_state, r.r_performer_name as workitem_performer_name, r.r_queue_item_id as workitem_queue_item_id,
02
    r.r_act_seqno, wf.r_object_id as workflow_id, r.r_act_def_id as activity_id, act.object_name as activity_name
03
    from dmi_workitem r, dm_workflow wf, dm_activity act
04
    where wf.r_object_id = r.r_workflow_id
05
        and r.r_act_def_id = act.r_object_id
06
        and wf.r_object_id = '4d0xxxxxx4905';
07
 
08
...
09
# workitem_id  workitem_state  workitem_performer_name  workitem_queue_item_id  r_act_seqno  workflow_id  activity_id  activity_name
10
# 4a0xxxxxxx911  2  DMAT_ADMIN  0000000000000000  0  4d0xxxxxx4905  4c01xxxxxxxx12ca  Restart
11
# 4a0xxxxxxx915  5  DMAT_ADMIN  1b0xxxxxxxxx993a  1  4d0xxxxxx4905  4c0xxxxxxxxx12d1  1

• Find an activity

view source
print?
1
select * from dm_activity where r_object_id ='4c0xxxxxxxxx12d1'
2
# Set the state of workitem '4a0xxxxxxx915' from 5 (A-HALTED) to 1 (ACQUIRED) ?
3
# Set the state of acivity '4c0xxxxxxxxx12d1' from 4 (FAILED) to 1 (ACTIVE) ?

---

——————— TRANSACTION ————————–

---

• Transaction management in pure API.

view source
print?
1
begintran,c
2
#
3
retrieve,c,my_custom_type where r_object_id = '090xxxxxxxxxd' AND my_title = '123456 JAVA.LU'
4
set,c,l,acl_name
5
my_acl_huo123
6
save,c,l
7
#
8
commit,c
9
### abort,c

• Transaction management in pure DQL.

view source
print?
01
########### Example 1 : NO TRANSACTION - COMMIT AUTO
02
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');
03
 
04
########### Example 2 : COMMIT AUTO
05
BEGIN TRAN;
06
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');
07
 
08
########### Example 3 : EXPLICIT TRANSACTION COMMITED
09
BEGIN TRAN;
10
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');
11
COMMIT TRAN;
12
 
13
########### Example 4 : EXPLICIT TRANSACTION ABORTED
14
BEGIN TRAN;
15
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');
16
ABORT TRAN;

---

——————— COUNT HAVING ————————–

---

• Example 1:

view source
print?
1
select r_object_id, count(my_repeat_field1), count(my_repeat_field2)
2
from my_subtype_document
3
group by r_object_id
4
HAVING count(my_repeat_field1)>1 AND count(my_repeat_field2)>1
5
enable (ROW_BASED) ;

---

——————— AUDIT TRAIL ————————–

---

• Creation of AuditTrail object via pure API:

view source
print?
01
create,c,dm_audittrail
02
set,c,l,event_name
03
my_event_huo_name
04
set,c,l,event_source
05
My process
06
set,c,l,user_name
07
My process
08
set,c,l,audited_obj_id
09
09xxxxxx62
10
set,c,l,string_1
11
Value Str 1
12
set,c,l,string_2
13
Value Str 2
14
set,c,l,string_3
15
Value Str 3
16
set,c,l,id_1
17
09xxxxx621
18
set,c,l,id_2
19
09xxxxxx622
20
set,c,l,sid_3
21
09xxxxxx623
22
save,c,l

The informations user_id, session_id, owner_name, time_stamp, time_stamp_utc, host_name are filled automatically by Content Server. Yet, the informations acl_name, acl_domain, chronicle_id, object_type, version_label, object_name are extracted from the audited object (audited_obj_id).

• Deleting of existing AuditTrail object via pure API:

view source
print?
1
API : retrieve,c,dm_audittrail where r_object_id ='5f0xxxxxx7'
2
...
3
5f0xxxxxx7
4
API : destroy,c,l
5
...
6
OK

• Updating of existing AuditTrail object via DQL or API => It is not possible.

view source
print?
01
DQL : UPDATE dm_audittrail OBJECTS set user_name='huseyin1' where event_name = 'my_event' and r_object_id ='5f02xxxxxxxa'
02
...
03
[DM_QUERY_E_UP_NOT_UPDATEABLE]error: You have specified a non updateable type name (dm_audittrail).
04
...
05
 
06
API : fetch,c,5f02xxxxxxxa
07
...
08
OK
09
API : set,c,l,user_name
10
huseyin1
11
...
12
OK
13
API : save,c,l
14
...
15
[DM_AUDITTRAIL_E_CANT_MODIFY]error: You can not modify any existing AuditTrail object 5f02xxxxxxxa.

• Number of audittrail events:

view source
print?
01
DQL : select event_name, count(*) from dm_audittrail group by event_name order by 1;
02
...
03
event_name          count(*)
04
...
05
Calling VersioningMethod    659.0
06
Executing WKF_FinishMethod      340326.0
07
Executing WKF_InclassableMethod     2422.0
08
Executing WKF_VersioningMethod  659.0
09
Executing WKF_WorkflowMethod    792367.0
10
dm_audit    1.0
11
dm_checkin  860420.0
12
dm_checkout     861471.0
13
dm_destroy  91128.0
14
dm_link     801360.0
15
dm_lock     861471.0
16
dm_logon_failure    6599.0
17
dm_save     3663335.0
18
dm_setfile  1192112.0
19
dm_unlink   387180.0
20
dm_unlock   860499.0

 

• Activate/Unactivate the audit_trail via pure API:

API>Audit
Purpose Initiates auditing of an event.
Syntax 
dmAPIExec("audit,session[,object_id],event_name
[,audit_subtypes][,controlling_app]
[,policy_id[,state_name]][,sign_audit][,authentication]
[,event_description][,attribute_list]
[,esignature_required]")

view source
print?
1
select * from dm_type ;</pre><pre># 030xxxxxxxxxf0  my_huo_document my_root_document
2
audit,c,030xxxxxxxxxf0,dm_save,F

API>Unaudit
Purpose Stops auditing of an event.
Syntax
dmAPIExec(“unaudit,session[,object_id],event_name
[,controlling_app][,policy_id ][,state_name]]”)

view source
print?
1
</pre>
2
<p>select * from dm_type ;<br /># 030xxxxxxxxxf0 my_huo_document my_root_document<br />unaudit,c,030xxxxxxxxxf0,dm_save<br />

 

EX: dm_audittrail “dm_save” when a document has been modified
event_name : dm_save
audited_obj_id : 0902XXXXXXXXXXX2
attribute_list : my_attribute_1=1,…
attribute_list_old : my_attribute_1=0,…

 

 

 

---

——————— GROUP / USER ————————–

---

• User creation via API:

view source
print?
01
create,c,dm_user
02
set,c,l,user_name
03
my_browse_user
04
set,c,l,user_os_name
05
my_browse_user
06
set,c,l,user_privileges
07
0
08
set,c,l,client_capability
09
0
10
save,c,l

• Group creation via API:

view source
print?
01
create,c,dm_group
02
set,c,l,group_name
03
access-browse-my-group
04
set,c,l,owner_name
05
dm_dbo
06
set,c,l,is_private
07
F
08
set,c,l,globally_managed
09
F
10
set,c,l,group_class
11
group
12
set,c,l,description
13
Browse access to all documents
14
save,c,l

• Add group and user to group via API/DQL:

view source
print?
01
execquery,c,F,alter group 'access-main-delete-group' add 'my-sub-group-1';
02
close,c,q0
03
execquery,c,F,alter group 'access-main-delete-group' add 'MyHuoUser1';
04
close,c,q0
05
...
06
# access-main-delete-group / groups_names            []: none
07
retrieve,c,dm_group where group_name = 'access-main-delete-group'
08
append,c,l,groups_names
09
my-sub-group-1
10
append,c,l,groups_names
11
my-sub-group-2
12
save,c,l

• Remove subgroup and user from group via API/DQL:

view source
print?
1
execquery,c,F,alter group 'access-main-delete-group' drop 'my-sub-group-1';
2
close,c,q0
3
execquery,c,F,alter group 'access-main-delete-group' drop 'MyHuoUser1';
4
close,c,q0
5
...
6
# access-main-delete-group / groups_names            [0]: my-sub-group-1 [1]: my-sub-group-2
7
retrieve,c,dm_group where group_name = 'access-main-delete-group'
8
remove,c,l,groups_names[1]
9
save,c,l

• The groups of an user via DQL:

view source
print?
1
select g.group_name from dm_group g, dm_user u where any g.users_names = u.user_name and u.user_name IN ('huseyin1');
2
...
3
development_team
4
ecm_team

• Number of users in a group (not including subgroup users) via DQL:

view source
print?
1
select count(u.user_name), g.group_name from dm_group g, dm_user u
2
where any g.users_names = u.user_name and (g.group_name like 'ecm_team%')
3
group by g.group_name;
4
...
5
10  ecm_team

• Number of users in a group including subgroup users via DQL:

view source
print?
1
SELECT count(DISTINCT i_all_users_names),group_name FROM dm_group g
2
WHERE (g.group_name like 'grp-%' or group_name like 'ecm_team%')
3
group by g.group_name;
4
...
5
80  ecm_team

• Users in a group (not including subgroup users) via DQL:

view source
print?
1
select u.user_name from dm_group g, dm_user u  where any g.users_names = u.user_name and g.group_name = 'ecm_team';
2
huseyin1
3
...
4
10 rows

• Users in a group including subgroup users via DQL:

view source
print?
1
select i_all_users_names from dm_group g where g.group_name = 'ecm_team';
2
huseyin1
3
...
4
80 rows

• Subgroup and supergroup of ‘grp_all’ and ‘ecm_team’ groups via DQL:

view source
print?
1
select distinct group_name, groups_names, i_supergroups_names from DM_GROUP where group_name IN ('grp_all') ENABLE (ROW_BASED);
2
...
3
grp_all     ecm_team    grp_all
4
 
5
select distinct group_name, groups_names, i_supergroups_names from DM_GROUP where group_name IN ('ecm_team') ENABLE (ROW_BASED);
6
...
7
ecm_team    ____        grp_all

• Number of users according to each state via DQL:

view source
print?
01
select count(user_name), user_state from dm_user where user_name not  like 'dm_%' group by user_state;
02
...
03
count(dm_user.user_name)  user_state
04
5128    1
05
131205  0
06
2   3
07
...
08
0, indicating a user who can log in (= active)
09
1, indicating a user who cannotlog in (= inactive)
10
2, meaning a user who is locked
11
3, meaning a user who is locked and inactive

• Find the accesses of users using the ACL template (PST) or instances of PST via DQL:

view source
print?
01
########## PST my_acl_pst_1 for user 'myuser001'
02
 
03
select * from dm_acl where object_name = 'my_acl_pst_1';
04
...
05
# 450XXXXXXXXXXX7b  my_acl_pst_1
06
 
07
### analyse of accesses via PST for 'myuser001'
08
select * from dm_group where group_name in (select r_accessor_name from dm_acl where object_name = 'my_acl_pst_1') and any i_all_users_names = 'myuser001';
09
...
10
# 120xxxxxxxxxxxxe18    group1
11
# 120xxxxxxxxxxxx15 group2
12
 
13
### analyse of accesses via instances of PST for 'myuser001'
14
select * from dm_group where group_name in (select r_accessor_name from dm_acl where r_template_id = '450XXXXXXXXXXX7b') and any i_all_users_names = 'myuser001';
15
...
16
# 120xxxxxxxxxxxxe18    group1
17
# 120xxxxxxxxxxxx15 group2

• Group docu : The docu group is the default documentum group which contains repository_owner, installation_owner, dm_autorender_win32, dm_autorender_mac, dm_mediaserver, superusers:

view source
print?
01
select owner_name  from dm_docbase_config;
02
DEMAT_ADMIN_DEV
03
....
04
select operator_name  from dm_server_config;
05
DEMAT_ADMIN_DEV
06
....
07
SELECT * from dm_group where group_name = 'docu';
08
  i_all_users_names       [0]: DEMAT_ADMIN_DEV
09
                          [1]: dm_autorender_mac
10
                          [2]: mysuperuser
11
                          [3]: dm_mediaserver
12
                          [4]: dm_autorender_win31
13
 
14
  users_names             [0]: DEMAT_ADMIN_DEV
15
                          [1]: mysuperuser
16
                          [2]: dm_autorender_win31
17
                          [3]: dm_autorender_mac
18
                          [4]: dm_mediaserver

• Group admingroup = When you grant Superuser privileges to a user, you may also need to add that user to the admingroup group to enable them to run jobs or administration methods. The admingroup group is created at server installation or upgrade, when the administration tool suite is installed. It contains all the Superusers in the repository. At server installation, there are the repository owner and installation owner. After an upgrade, it contains all Superusers in the repository. Members of the admingroup group have no inherent privileges other than those they have as Superusers. However, administrative jobs, methods, and other related objects use an ACL which restricts their use to the admingroup group.

view source
print?
01
select owner_name  from dm_docbase_config;
02
DEMAT_ADMIN_DEV
03
....
04
select operator_name  from dm_server_config;
05
DEMAT_ADMIN_DEV
06
....
07
SELECT * from dm_group where group_name = 'admingroup';
08
  users_names             [1]: DEMAT_ADMIN_DEV
09
                          [2]: dm_fulltext_index_user
10
                          [3]: mysuperuser
11
 
12
  i_all_users_names       [0]: mysuperuser
13
                          [1]: dm_fulltext_index_user
14
                          [2]: DEMAT_ADMIN_DEV

---

——————— ACL ————————–

---

• Creation of User ACL (acl_class=0) via API

view source
print?
001
API : begintran,c
002
...
003
OK
004
API : create,c,dm_acl
005
...
006
450xxxxxxxxxxxx23
007
API : set,c,l,object_name
008
TEST ACL HUO World Write
009
...
010
OK
011
API : set,c,l,owner_name
012
dm_dbo
013
...
014
OK
015
API : set,c,l,r_is_internal
016
FALSE
017
...
018
OK
019
 
020
 
021
API : set,c,l,globally_managed
022
FALSE
023
...
024
OK
025
API : set,c,l,acl_class
026
0
027
...
028
OK
029
API : set,c,l,description
030
All Users have Write Access
031
...
032
OK
033
API : grant,c,l,dm_world,AccessPermit,,6
034
...
035
OK
036
API : grant,c,l,dm_owner,AccessPermit,,7
037
...
038
OK
039
API : grant,c,l,mysuperuser,AccessPermit,,7
040
...
041
OK
042
API : save,c,l
043
...
044
OK
045
API : commit,c
046
...
047
OK
048
 
049
#
050
# Others instructions:
051
#
052
#set,c,l,r_accessor_permit[0]
053
#3
054
#set,c,l,r_accessor_xpermit[0]
055
#1048576
056
#set,c,l,r_accessor_permit[1]
057
#1
058
#set,c,l,r_accessor_xpermit[1]
059
#3
060
 
061
#append,c,l,r_accessor_name
062
#docu
063
#append,c,l,r_is_group
064
#TRUE
065
#append,c,l,r_accessor_permit
066
#7
067
#append,c,l,r_accessor_xpermit
068
#0
069
 
070
#append,c,l,r_accessor_name
071
#my-grp-wkf
072
#append,c,l,r_is_group
073
#TRUE
074
#append,c,l,r_accessor_permit
075
#6
076
#append,c,l,r_accessor_xpermit
077
#0
078
 
079
#append,c,l,r_accessor_name
080
#my_all_users
081
#append,c,l,r_is_group
082
#TRUE
083
#append,c,l,r_accessor_permit
084
#6
085
#append,c,l,r_accessor_xpermit
086
#0
087
 
088
API : dump,c,450xxxxxxxxxxxx23
089
...
090
USER ATTRIBUTES
091
 
092
  object_name                : TEST ACL HUO World Write
093
  description                : All Users have Write Access
094
  owner_name                 : MYDOCBASEDEV
095
  globally_managed           : F
096
  acl_class                  : 0
097
 
098
SYSTEM ATTRIBUTES
099
 
100
  r_object_id                : 450xxxxxxxxxxxx23
101
  r_is_internal              : F
102
  r_accessor_name         [0]: dm_world
103
                          [1]: dm_owner
104
                          [2]: mysuperuser
105
  r_accessor_permit       [0]: 6
106
                          [1]: 7
107
                          [2]: 7
108
  r_accessor_xpermit      [0]: 0
109
                          [1]: 0
110
                          [2]: 0
111
  r_is_group              [0]: F
112
                          [1]: F
113
                          [2]: F
114
  r_has_events               : F
115
  r_permit_type           [0]: 0
116
                          [1]: 0
117
                          [2]: 0
118
  r_application_permit    [0]:
119
                          [1]:
120
                          [2]:
121
  r_template_id              : 0000000000000000
122
  r_alias_set_id             : 0000000000000000
123
 
124
APPLICATION ATTRIBUTES
125
 
126
INTERNAL ATTRIBUTES
127
 
128
  i_has_required_groups      : F
129
  i_has_required_group_set   : F
130
  i_has_access_restrictions  : F
131
  i_partition                : 0
132
  i_is_replica               : F
133
  i_vstamp                   : 0

• Modification of previous ACL via API

view source
print?
01
API : retrieve,c,dm_acl where object_name = 'TEST ACL HUO World Write'
02
...
03
450xxxxxxxxxxxx23
04
API : grant,c,l,dm_world,AccessPermit,,1
05
...
06
OK
07
API : save,c,l
08
...
09
OK
10
 
11
#
12
# Others instructions:
13
#
14
#grant,c,l,dm_world,AccessPermit,,1
15
#grant,c,l,my_grp1,AccessPermit,,7
16
#grant,c,l,my_grp2,AccessPermit,,5
17
#revoke,c,l,my_grp1,AccessPermit,,7
18
#revoke,c,l,dm_world,ExtendedPermit,,EXECUTE_PROC
19
#grant,c,l,my_grp1,ExtendedPermit,,CHANGE_LOCATION
20
#grant,c,l,my_grp2,ExtendedPermit,,CHANGE_PERMIT
21
 
22
#dump,c,r_object_id_ofACL
23
#grant,c,l,dm_world_GROUPE,PERMIT(1à7),XPERMIT(chiffre)
24
#OR
25
#grant,c,l,dm_world_GROUPE,PERMIT(1à7),XPERMIT(nom_de_xpermit)
26
 
27
API : dump,c,450xxxxxxxxxxxx23
28
...
29
USER ATTRIBUTES
30
 
31
  object_name                : TEST ACL HUO World Write
32
  description                : All Users have Write Access
33
  owner_name                 : MYDOCBASEDEV
34
  globally_managed           : F
35
  acl_class                  : 0
36
 
37
SYSTEM ATTRIBUTES
38
 
39
  r_object_id                : 450xxxxxxxxxxxx23
40
  r_is_internal              : F
41
  r_accessor_name         [0]: dm_world
42
                          [1]: dm_owner
43
                          [2]: mysuperuser
44
  r_accessor_permit       [0]: 1        --- modified ---
45
                          [1]: 7
46
                          [2]: 7
47
  r_accessor_xpermit      [0]: 0
48
                          [1]: 0
49
                          [2]: 0
50
  r_is_group              [0]: F
51
                          [1]: F
52
                          [2]: F
53
  r_has_events               : F
54
  r_permit_type           [0]: 0
55
                          [1]: 0
56
                          [2]: 0
57
  r_application_permit    [0]:
58
                          [1]:
59
                          [2]:
60
  r_template_id              : 0000000000000000
61
  r_alias_set_id             : 0000000000000000
62
 
63
APPLICATION ATTRIBUTES
64
 
65
INTERNAL ATTRIBUTES
66
 
67
  i_has_required_groups      : F
68
  i_has_required_group_set   : F
69
  i_has_access_restrictions  : F
70
  i_partition                : 0
71
  i_is_replica               : F
72
  i_vstamp                   : 2

• Deleting of ACL via API

view source
print?
1
API : retrieve,c,dm_acl where object_name = 'TEST ACL HUO World Write'
2
...
3
450xxxxxxxxxxxx23
4
API : destroy,c,l
5
...
6
OK

• CHECK_ACL and CHECK_SECURITY via DQL
CHECK_ACL : provides similar security enforcement for queries against registered tables as DQL does for queries against sysobjects. When a non-superuser issues a DQL statement against dm_sysobject or its subtypes, an ACL security-checking clause is appended at the end of the SQL statement issued against the database. The CHECK_ACL function is used similarly to enforce security with registered tables. Since there is no security-checking clause added to DQL statements run against registered tables, the CHECK_ACL function must be ANDed with the other qualifications in the statement. The registered table must contain a column that has the ACL domain and the ACL name for each table row to use for the security check.

CHECK_SECURITY : Checks a user’s or group’s access permissions on one or more objects or checks a user’s or group’s permission level in one or more ACLs.

view source
print?
01
DQL : SELECT * FROM my_huo_document  WHERE r_object_id = '09XXXXXXX9b' AND CHECK_ACL('acl_domain', 'acl_name', 'myuser001');
02
 
03
# BROWSE, READ, RELATE, VERSION, WRITE, DELETE
04
DQL : SELECT FOR READ *  FROM my_huo_document  WHERE r_object_id = '09XXXXXXX9b' AND CHECK_ACL('acl_domain', 'acl_name', 'myuser001');
05
DQL : SELECT FOR DELETE *  FROM my_huo_document  WHERE r_object_id = '09XXXXXXX9b' AND CHECK_ACL('acl_domain', 'acl_name', 'myuser001');
06
 
07
# 1 for None, 2 for Browse, 3 for Read, 4 for Relate, 5 for Version, 6 for Write, 7 for Delete,
08
DQL : EXECUTE check_security WITH user_name='myuser001', level=3,object_list='09XXXXXXX9b';
09
DQL : EXECUTE check_security WITH user_name='myuser001', level=7,object_list='09XXXXXXX9b';
10
 
11
 
12
# TESTS : access via which group ?
13
### huo001 : OK
14
select r_object_id, group_name from dm_group  where LOWER(group_name) IN(select LOWER(r_accessor_name) from dm_acl where object_name IN (select acl_name from my_huo_document where r_object_id IN('090XXXXXXXXXX1cc'))) AND any i_all_users_names = 'huo001';
15
# 120XXXXXXXXXXXX18 access_all
16
# 120XXXXXXXXXXXXc5 access-delete-group
17
SELECT * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXX1cc' AND CHECK_ACL('acl_domain', 'acl_name', 'huo001');
18
SELECT FOR BROWSE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXX1cc' AND CHECK_ACL('acl_domain', 'acl_name', 'huo001');
19
# 090XXXXXXXXXX1cc  My Document Name

 

Warning : The superuser has the permission of document’s OWNER permission unless he has direct permission. For example, if the OWNER has the permission VERSION on a document, the superuser will not have the permission WRITE/DELETE on this document.
So, the superuser will not can update its properties without creating new version.

Example : document associated with ACL which has the following permissions:
r_accessor_name [0]: dm_world
[1]: dm_owner
[2]: user_delete_right
r_accessor_permit [0]: 1
[1]: 5
[2]: 7

view source
print?
01
############### With USER user_delete_right
02
# This APi instruction allows to know the rights of a connected user "user_delete_right"
03
API> get,c,090XXXXXXXXXXX,_permit
04
...
05
7
06
 
07
SELECT FOR DELETE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX';
08
# 090XXXXXXXXXXX    My_doc_title.pdf
09
 
10
SELECT FOR VERSION * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'my_super_user');
11
# nothing
12
SELECT FOR READ * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'my_super_user');
13
# 090XXXXXXXXXXX    My_doc_title.pdf
14
 
15
############### With USER my_super_user
16
# This APi instruction allows to know the rights of a connected user "user_delete_right"
17
API> get,c,090XXXXXXXXXXX,_permit
18
...
19
5
20
 
21
SELECT FOR VERSION * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX';
22
# 090XXXXXXXXXXX    My_doc_title.pdf
23
SELECT FOR WRITE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX';
24
# nothing
25
 
26
SELECT FOR DELETE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'user_delete_right');
27
# nothing
28
SELECT FOR WRITE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'user_delete_right');
29
# nothing
30
SELECT FOR VERSION * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'user_delete_right');
31
# 090XXXXXXXXXXX    My_doc_title.pdf

 

 

---

——————— ALIAS SET / TEMPLATE ACL (PERMISSION SET TEMPLATE) ————————–

---

• Creation of Alias Set (dm_alias_set) via API

view source
print?
01
API : begintran,c
02
...
03
OK
04
API : create,c,dm_alias_set
05
...
06
660xxxxxxd3e
07
API : set,c,l,object_name
08
TEST AS HUO Simple
09
...
10
OK
11
API : set,c,l,owner_name
12
dm_dbo
13
...
14
OK
15
API : set,c,l,object_description
16
Desc 4 TEST AS HUO Simple
17
...
18
OK
19
API : append,c,l,alias_name
20
AS4MyGroup
21
...
22
OK
23
API : append,c,l,alias_value
24
huo_grp_all_users
25
...
26
OK
27
API : append,c,l,alias_category
28
2
29
...
30
OK
31
API : append,c,l,alias_usr_category
32
-1
33
...
34
OK
35
API : append,c,l,alias_description
36
Entry for a group alias (huo_grp_all_users)
37
...
38
OK
39
API : append,c,l,alias_name
40
AS4SuperUser
41
...
42
OK
43
API : append,c,l,alias_value
44
myuser001
45
...
46
OK
47
API : append,c,l,alias_category
48
1
49
...
50
OK
51
API : append,c,l,alias_usr_category
52
-1
53
...
54
OK
55
API : append,c,l,alias_description
56
Entry for a user alias (myuser001)
57
...
58
OK
59
API : save,c,l
60
...
61
OK
62
API : commit,c
63
...
64
OK
65
 
66
 
67
API : dump,c,660xxxxxxd3e
68
...
69
USER ATTRIBUTES
70
  owner_name                 : dm_dbo
71
  object_name                : TEST AS HUO Simple
72
  object_description         : Desc 4 TEST AS HUO Simple
73
  alias_name              [0]: AS4MyGroup
74
                          [1]: AS4SuperUser
75
  alias_value             [0]: huo_grp_all_users
76
                          [1]: myuser001
77
  alias_category          [0]: 2
78
                          [1]: 1
79
  alias_usr_category      [0]: -1
80
                          [1]: -1
81
  alias_description       [0]: Entry for a group alias (huo_grp_all_users)
82
                          [1]: Entry for a user alias (myuser001)
83
 
84
SYSTEM ATTRIBUTES
85
  r_object_id                : 660xxxxxxd3e
86
 
87
APPLICATION ATTRIBUTES
88
 
89
INTERNAL ATTRIBUTES
90
  i_is_replica               : F
91
  i_vstamp                   : 0

Concerning the the category (alias_category ) for the alias in the specified index position in the alias set. This value is set by the creator of the alias object and used by Documentum client applications. The server does not verify the category value against the alias value to ensure that alias value is in the specified category.
Currently, only the following values are allowed:

• 0 (Unknown)
• 1 (User, alias_value is a user)
• 2 (Group, alias_value is a group)
• 3 (User or Group, alias_value is either a user or a group)
• 4 (Cabinet Path, alias_value is a cabinet path)
• 5 (Folder Path, alias_value is a folder path)
• 6 (ACL Name, alias_value is an ACL name)

 

• Creation of Template ACLs or Permission Set Template (acl_class=1) via API

view source
print?
01
API : begintran,c
02
...
03
OK
04
API : create,c,dm_acl
05
...
06
450xxxxxxx94f
07
API : set,c,l,object_name
08
TEST PST HUO Simple
09
...
10
OK
11
API : set,c,l,owner_name
12
dm_dbo
13
...
14
OK
15
API : set,c,l,acl_class
16
1
17
...
18
OK
19
API : set,c,l,description
20
Desc 4 TEST PST HUO Simple
21
...
22
OK
23
API : grant,c,l,dm_world,AccessPermit,,3
24
...
25
OK
26
API : grant,c,l,dm_owner,AccessPermit,,3
27
...
28
OK
29
API : grant,c,l,%AS4MyGroup,AccessPermit,,6
30
...
31
OK
32
API : grant,c,l,%AS4SuperUser,AccessPermit,,7
33
...
34
OK
35
API : save,c,l
36
...
37
OK
38
API : commit,c
39
...
40
OK
41
 
42
API : dump,c,450xxxxxxx94f
43
...
44
USER ATTRIBUTES
45
  object_name                : TEST PST HUO Simple
46
  description                : Desc 4 TEST PST HUO Simple
47
  owner_name                 : MYDOCBASEDEV
48
  globally_managed           : F
49
  acl_class                  : 1
50
 
51
SYSTEM ATTRIBUTES
52
  r_object_id                : 450xxxxxxx94f
53
  r_is_internal              : F
54
  r_accessor_name         [0]: dm_world
55
                          [1]: dm_owner
56
                          [2]: %AS4MyGroup
57
                          [3]: %AS4SuperUser
58
  r_accessor_permit       [0]: 3
59
                          [1]: 3
60
                          [2]: 6
61
                          [3]: 7
62
  r_accessor_xpermit      [0]: 0
63
                          [1]: 0
64
                          [2]: 0
65
                          [3]: 0
66
  r_is_group              [0]: F
67
                          [1]: F
68
                          [2]: F
69
                          [3]: F
70
  r_has_events               : F
71
  r_permit_type           [0]: 0
72
                          [1]: 0
73
                          [2]: 0
74
                          [3]: 0
75
  r_application_permit    [0]:
76
                          [1]:
77
                          [2]:
78
                          [3]:
79
  r_template_id              : 0000000000000000
80
  r_alias_set_id             : 0000000000000000
81
 
82
APPLICATION ATTRIBUTES
83
 
84
INTERNAL ATTRIBUTES
85
  i_has_required_groups      : F
86
  i_has_required_group_set   : F
87
  i_has_access_restrictions  : F
88
  i_partition                : 0
89
  i_is_replica               : F
90
  i_vstamp                   : 0

• Modification of Template ACLs or Permission Set Template (acl_class=1) via API

view source
print?
1
API : begintran,c
2
API : retrieve,c,dm_acl where r_object_id='450xxxxxxx4c’
3
API : revoke,c,l,%AS4MyGroup,AccessPermit,,6
4
API : revoke,c,l,%AS4SuperUser,AccessPermit,,7
5
API : save,c,l
6
API : commit,c

• Creation of Instance of Template ACL using the PST previous and AS (acl_class=2) via API
An instance of an ACL template is created when an AliasSet and PST are associated to a document. It is not possible to create directly manually an instance of PST.
The error DM_ACL_E_CANT_CHANGE_INSTANCE occurs if the user tries to modify a instance of PST (acl_class=2). To modify the instances of PST, it is necessary to modify the PST or PST/AliasSet associated to theses instances.

view source
print?
01
API : begintran,c
02
...
03
OK
04
API : create,c,dm_document
05
...
06
090xxxxxx321
07
API : set,c,l,object_name
08
Test DOC HUO WITH AS AND PST
09
...
10
OK
11
API : set,c,l,a_content_type
12
pdf
13
...
14
OK
15
API : setfile,c,l,C:\temp\test.pdf
16
...
17
OK
18
API : link,c,l,'/Temp'
19
...
20
OK
21
API : set,c,l,r_alias_set_id
22
660xxxxxxd3e
23
...
24
OK
25
API : set,c,l,acl_domain
26
dm_dbo
27
...
28
OK
29
API : set,c,l,acl_name
30
TEST PST HUO Simple
31
...
32
OK
33
API : save,c,l
34
...
35
OK
36
API : commit,c
37
...
38
OK
39
 
40
API : dump,c,090xxxxxx321
41
...
42
USER ATTRIBUTES
43
  object_name                : Test DOC HUO WITH AS AND PST
44
  acl_domain                 : MYDOCBASEDEV
45
  acl_name                   : dm_450xxxxxxx94_xxxxd3e
46
 
47
SYSTEM ATTRIBUTES
48
  r_object_id                : 090xxxxxx321
49
  r_object_type              : dm_document

So, the association between document, AliasSet and Templace ACL generates an instance of Templace ACL (acl_class=2) with name like dm_450xxxxxxx94_xxxxd3e. This instance is created only if its is not already exist.

view source
print?
01
API : dump,c,450xxxxx950
02
...
03
USER ATTRIBUTES
04
  object_name                : dm_450xxxxxxx94_xxxxd3e
05
  description                : dm_450xxxxxxx94_xxxxd3e
06
  owner_name                 : MYDOCBASEDEV
07
  globally_managed           : F
08
  acl_class                  : 2
09
 
10
SYSTEM ATTRIBUTES
11
  r_object_id                : 450xxxxx950
12
  r_is_internal              : T
13
  r_accessor_name         [0]: dm_world
14
                          [1]: dm_owner
15
                          [2]: huo_grp_all_users
16
                          [3]: myuser001
17
  r_accessor_permit       [0]: 3
18
                          [1]: 3
19
                          [2]: 6
20
                          [3]: 7
21
  r_accessor_xpermit      [0]: 0
22
                          [1]: 0
23
                          [2]: 0
24
                          [3]: 0
25
  r_is_group              [0]: F
26
                          [1]: F
27
                          [2]: F
28
                          [3]: F
29
  r_has_events               : F
30
  r_permit_type           [0]: 0
31
                          [1]: 0
32
                          [2]: 0
33
                          [3]: 0
34
  r_application_permit    [0]:
35
                          [1]:
36
                          [2]:
37
                          [3]:
38
  r_template_id              : 450xxxxxxx94f
39
  r_alias_set_id             : 660xxxxxxd3e
40
 
41
APPLICATION ATTRIBUTES
42
 
43
INTERNAL ATTRIBUTES
44
  i_has_required_groups      : F
45
  i_has_required_group_set   : F
46
  i_has_access_restrictions  : F
47
  i_partition                : 0
48
  i_is_replica               : F
49
  i_vstamp                   : 0

• Deleting of AliasSet, Template ACL (PST) and Instance of Template ACL via API:

view source
print?
01
## Give DELETE permission to super user on TEMPLATE ACL (PST) in order to delete the elements
02
API : retrieve,c,dm_acl where object_name = 'TEST PST HUO Simple'
03
...
04
450xxxxxxx94f
05
API : grant,c,l,mysuper001,AccessPermit,,7
06
...
07
OK
08
API : save,c,l
09
...
10
OK
11
 
12
API : retrieve,c,dm_document where object_name like 'Test DOC HUO WITH AS AND PST';
13
...
14
090xxxxxx321
15
API : destroy,c,l
16
...
17
OK
18
API : retrieve,c,dm_acl where object_name = 'dm_450xxxxxxx94_xxxxd3e'
19
...
20
450xxxxx950
21
API : destroy,c,l
22
...
23
OK
24
API : retrieve,c,dm_acl where object_name = 'TEST PST HUO Simple'
25
...
26
450xxxxxxx94f
27
API : destroy,c,l
28
...
29
OK
30
API : retrieve,c,dm_alias_set where object_name = 'TEST AS HUO Simple'
31
...
32
660xxxxxxd3e
33
API : destroy,c,l
34
...
35
OK

---

——————— LINKING ————————–

---

• Link a document to other folder:

view source
print?
01
API : retrieve,c,my_custom_type where r_object_id = '090xxxxxxxx6df'
02
...
03
090xxxxxxxx6df
04
API : set,c,l,my_attr1,0
05
...
06
OK
07
API : set,c,l,my_attr2,1
08
...
09
OK
10
API : set,c,l,my_attr3,1
11
...
12
OK
13
API : link,c,l,'/MYCABINET/My Folder1, Fondation/SubFolder2 of Folder1'
14
...
15
OK
16
API : save,c,l
17
...
18
OK
19
 
20
DQL : UPDATE my_custom_type OBJECTS LINK '/MYCABINET/My Folder1, Fondation/SubFolder2 of Folder1' WHERE r_object_id = '090xxxxxxxx6df'
21
 
22
DQL : select r_object_id, count(i_folder_id) from my_custom_type group by r_object_id having count(i_folder_id)>1 enable (ROW_BASED) ;
23
 
24
090xxxxxxxx6df
25
i_folder_id = Parent Folder IDs - REPEATING of ID
26
i_cabinet_id = Primary Cabinet - ID
27
 
28
i_folder_id[0]: 0b0xxxxxx16 = /MYCABINET/My Folder1, Fondation/SubFolder1 of Folder1
29
i_folder_id[1]: 0b0xxxxxxaa = /MYCABINET/My Folder1, Fondation/SubFolder2 of Folder1
30
i_cabinet_id : 0c0xxxxxx02 = /MYCABINET

view source
print?
1
 

---

——————— VALUES MAPPINGS / ASSISTANCE ————————–

---

• Values mapping of custom type:

view source
print?
1
DQL : select label_text, map_display_string, map_data_string from dm_nls_dd_info
2
where parent_id in (select r_object_id from dm_aggr_domain where type_name='my_subtype_document' and any attr_name='doc_type')
3
#and any map_display_string like '____-%'
4
;
5
...
6
select * from dm_nls_dd_info where parent_id in (select r_object_id from dm_aggr_domain where type_name='my_subtype_document');
7
...

• Check whether a specific data should be deployed via DataDictionnary:

view source
print?
1
DQL : select resync_needed  From dmi_dd_attr_info Where type_name=’type’ AND attr_name=’attr’;
2
 
3
Exemple: Select resync_needed  From dmi_dd_attr_info Where type_name='my_subtype_document' AND attr_name='type_doc';
4
0 = no needed
5
1 = needed

---

——————— TYPES ————————–

---

• Get informations concerning types (supertype…):

view source
print?
1
DQL : select r_object_id, r_type_name, r_supertype from dmi_type_info where r_type_name IN ('dm_document', 'dm_sysobject','my_client_document','my_company_document')
2
;
3
...
4
2exxxxxx105 dm_sysobject    dm_sysobject
5
2exxxxx0129 dm_document dm_document,dm_sysobject
6
2exxxxx4500 my_company_document dm_sysobject,my_company_document,dm_document
7
2exxxxx4501 my_client_document  my_company_document,my_client_document,dm_document,dm_sysobject
8
...

• Get informations concerning types / aspects (attributes…):

view source
print?
1
DQL : select r_object_id, name, super_name, attr_count, start_pos from dm_type where name IN ('dm_document', 'dm_sysobject','my_client_document','dmi_030xxxxxx308','my_company_document');
2
...
3
030xxxxxx129    dm_document dm_sysobject    76  76
4
030xxxxx0105    dm_sysobject        76  0
5
030xxxxxx308    dmi_030xxxxxx308    6   0
6
030xxxxx025a    my_client_document  my_company_document 123 91
7
030xxxxx0259    my_company_document dm_document 91  76
8
...

---

——————— ASPECTS ————————–

---

• Find the internal name (aspect table) of an aspect:

view source
print?
1
DQL : select i_attr_def from dmc_aspect_type   where object_name = 'my_huo_aspect'
2
;
3
...
4
dmi_030xxxxxx0b
5
...

• Find attributes of an ASPECT via 2 internal tables for repeating and simple fields:

view source
print?
01
DQL : select i_attr_def from dmc_aspect_type   where object_name = 'my_huo_aspect2'
02
;
03
...
04
dmi_03xxxxxx30c
05
...
06
DQL : describe dmi_03xxxxxx30c_r;
07
....
08
i_partition Integer
09
i_position  Integer
10
r_object_id Char(16)
11
my_repeating_field1 Integer
12
my_repeating_field2 Char(255)
13
...
14
DQL : describe dmi_03xxxxxx30c_s;
15
....
16
i_partition Integer
17
r_object_id Char(16)
18
my_simple_field1  Char(512)

• Default aspect of a type. Multiple default aspects can be associated with one object type. An object type inherits all the default aspects defined for its supertypes. When you add a default aspect to a type, the newly added aspect is only associated with new instances of the type or subtype created after the addition. Existing instances of the type or its subtypes are not affected. Similarly, removal of a default aspect does not affect the existing instances of the type. Default aspects for an object type are recorded in the default_aspects property in the corresponding dmi_type_info object.

view source
print?
01
DQL : select r_object_id, r_type_name, r_supertype, default_aspects from dmi_type_info where r_type_name IN ('dm_document', 'dm_sysobject','my_client_document','dmi_030xxxxxx30a','dmi_0302xxxxx30b','my_company_document')
02
;
03
...
04
2exxxxxx105 dm_sysobject    dm_sysobject
05
2exxxxx0129 dm_document dm_document,dm_sysobject
06
2exxxxx4500 my_company_document dm_sysobject,my_company_document,dm_document
07
2exxxxx4501 my_client_document  my_company_document,my_client_document,dm_document,dm_sysobject
08
2exxxxxx901 dmi_030xxxxxx30a    dmi_030xxxxxx30a
09
2exxxxxxd00 dmi_0302xxxxx30b    dmi_0302xxxxx30b
10
...

---

——————— DAR / JAR / MODULE / METHOD ————————–

---

• Example for a SBO “MySecurityPolicyServiceBO” sur GLOBALR:

view source
print?
01
# Last versions of Module : Definition of java class
02
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_module (all) where LOWER(object_name)  like '%security%' ORDER BY r_modify_date desc ;
03
0bxxxxxxx542    com.huo.lu.business.sbo.ecm.security.IMySecurityPolicyService   23/11/2011 09:05:58 17/03/2017 14:38:05 1.0,CURRENT
04
 
05
 
06
# Last versions of JARS (Java Method):
07
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_jar (all) where LOWER(object_name)  like '%security%' ORDER BY r_modify_date desc ;
08
090xxxxx8775    security-policy-impl    17/03/2017 14:38:02 17/03/2017 14:38:02 1.130,CURRENT
09
090xxxxx8777    security-policy 17/03/2017 14:38:02 17/03/2017 14:38:02 1.130,CURRENT
10
 
11
# Last versions of DAR:
12
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_dar (all) where LOWER(object_name)  like '%security%' ORDER BY r_modify_date desc ;
13
08xxxxa8779 MySecurityPolicyServiceBO   17/03/2017 14:38:03 17/03/2017 14:38:03 1.119,CURRENT

• Example for a TBO “MyHuoBO – my_huo_document” on MY_DOCBASE_XXX:

view source
print?
01
# Last versions of Module : Definition of java class
02
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_module (all) where LOWER(object_name)  like '%huo_document%' ORDER BY r_modify_date desc ;
03
0bxxxxxxx9a9    my_huo_document 21/06/2010 09:30:48 21/03/2017 11:34:24 CURRENT,1.0
04
 
05
# Last versions of JARS (Java Method):
06
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_jar (all) where LOWER(object_name)  like '%huo_document%' ORDER BY r_modify_date desc ;
07
-
08
 
09
# Last versions of DAR:
10
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_dar (all) where LOWER(object_name)  like '%myhuobo%' ORDER BY r_modify_date desc ;
11
080xxxxx484 MyHuoBO 21/03/2017 11:34:17 21/03/2017 11:34:17 1.372,CURRENT
12
 
13
 
14
 
15
# Last versions of TBO:
16
SELECT   * FROM  dm_document WHERE  folder('/System/Modules/TBO/my_custom_document') ORDER BY   r_creation_date DESC;
17
090XXXXb11  my_custom_document
18
090XXXXb0f  my_custom_document-Impl

• Example for Java methods called in Workflow (Workflow Manager):

view source
print?
01
# Last versions of Java Methods:
02
SELECT r_object_id, object_name, r_creation_date, r_modify_date, trace_launch,  method_type, use_method_server, method_verb, r_version_label from dm_method (all) where object_name  in ('FinishMethod','InclassableMethod','VersioningMethod','WorkflowMethod') ORDER BY r_modify_date;
03
10xxxxxx622a    InclassableMethod   24/02/2016 13:19:20 25/02/2016 15:03:19 1   java    1   com.huo.lu.myged.persistence.documentum.bof.InclassableMethod   CURRENT,1.1
04
10xxxxx4624e    FinishMethod        24/02/2016 13:19:23 25/02/2016 15:03:19 1   java    1   com.huo.lu.myged.persistence.documentum.bof.FinishMethod    CURRENT,1.1
05
10xxxxxx622c    VersioningMethod    24/02/2016 13:19:21 25/02/2016 15:03:14 1   java    1   com.huo.lu.myged.persistence.documentum.bof.VersioningMethod    1.1,CURRENT
06
10xxxxxx622e    WorkflowMethod      24/02/2016 13:19:21 25/02/2016 15:03:14 1   java    1   com.huo.lu.myged.persistence.documentum.bof.WorkflowMethod  1.1,CURRENT
07
...
08
 
09
# Last versions of JARS (Java Method):
10
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_jar (all) where object_name  in ('FinishMethod','InclassableMethod','VersioningMethod','WorkflowMethod') ORDER BY r_modify_date desc ;
11
090xxxxx35ba    WorkflowMethod  10/04/2014 18:02:58 10/04/2014 18:02:59 CURRENT,1.35
12
090xxxxxx5b4    FinishMethod    10/04/2014 18:02:57 10/04/2014 18:02:58 1.35,CURRENT
13
090xxxxxx5b6    InclassableMethod   10/04/2014 18:02:58 10/04/2014 18:02:58 1.35,CURRENT
14
090xxxxx35b8    VersioningMethod    10/04/2014 18:02:58 10/04/2014 18:02:58 CURRENT,1.35
15
...
16
 
17
# Last versions of Module : Definition of java class
18
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_module (all) where object_name  like 'com.huo.lu.myged.persistence.documentum.bof.%' ORDER BY r_modify_date desc ;
19
0b0xxxxxx93a    com.huo.lu.myged.persistence.documentum.bof.FinishMethod    07/01/2013 17:27:24 10/04/2014 18:03:00 1.0,CURRENT
20
0b01xxxx093b    com.huo.lu.myged.persistence.documentum.bof.InclassableMethod   07/01/2013 17:27:24 10/04/2014 18:03:00 1.0,CURRENT
21
0b0xxxxx093e    com.huo.lu.myged.persistence.documentum.bof.VersioningMethod    07/01/2013 17:27:25 10/04/2014 18:03:00 1.0,CURRENT
22
0b01xxxx0941    com.huo.lu.myged.persistence.documentum.bof.WorkflowMethod  07/01/2013 17:27:25 10/04/2014 18:03:00 1.0,CURRENT
23
 
24
# Last versions of DAR:
25
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_dar (all) where object_name  like 'MyDematClient%' ORDER BY r_modify_date desc ;
26
080xxxxxx5bc    MyDematClient   10/04/2014 18:02:59 10/04/2014 18:02:59 CURRENT,1.30
27
...

• Example for Custom Job:

view source
print?
01
# Last versions of Module : Definition of java class
02
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_module (all) where LOWER(object_name)  like '%customexample1%' ORDER BY r_modify_date desc ;
03
0b0xxxxx2833    HUO_CustomExample1Module    13/10/2016 13:28:03 13/10/2016 15:43:20 CURRENT,1.0
04
 
05
# Last versions of JARS (Java Method):
06
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_jar (all) where LOWER(object_name)  like '%customexample1%' ORDER BY r_modify_date desc ;
07
090xxxxx29a3    HUO_CustomExample1MethodJob 13/10/2016 14:49:32 13/10/2016 15:43:19 1.1
08
09xxxxxx2a7c    HUO_CustomExample1MethodJob 13/10/2016 15:43:19 13/10/2016 15:43:19 1.2,CURRENT
09
090xxxxx283d    HUO_CustomExample1MethodJob 13/10/2016 13:28:04 13/10/2016 14:49:32 1.0
10
 
11
# Last versions of DAR:
12
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_dar (all) where LOWER(object_name)  like '%customexample1%' ORDER BY r_modify_date desc ;
13
080xxxxx2a7e    ECMCustomExample1MethodJob  13/10/2016 15:43:19 13/10/2016 15:43:20 CURRENT,1.2
14
080xxxxxx9a5    ECMCustomExample1MethodJob  13/10/2016 14:49:33 13/10/2016 14:49:33 1.1
15
080xxxxxx848    ECMCustomExample1MethodJob  13/10/2016 13:28:09 13/10/2016 13:28:09 1.0
16
 
17
# Last versions of JARS (Java Method):
18
SELECT r_object_id, object_name, r_creation_date, r_modify_date, trace_launch,  method_type, use_method_server, method_verb, r_version_label from dm_method (all) where LOWER(object_name)  like '%customexample1%' ORDER BY r_modify_date;
19
10xxxxxxx851    HUO_CustomExample1Method    13/10/2016 13:28:08 13/10/2016 15:43:18 1   java    1   HUO_CustomExample1Module    1.0,CURRENT
20
 
21
# Last versions of Jobs:
22
SELECT r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dm_job (all) where LOWER(object_name)  like '%customexample1%' ORDER BY r_modify_date;
23
080xxxxxxx49    HUO_CustomExample1Job   13/10/2016 14:49:32 13/10/2016 15:43:18 1.0,CURRENT

• Example for Aspect:

view source
print?
01
# Last versions of Module : Definition of java class
02
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_module (all) where LOWER(object_name)  like '%aspect_myaspect1%' ORDER BY r_modify_date desc ;
03
0b0xxxxxxfc3    aspect_myaspect1    08/12/2016 10:51:06 09/12/2016 09:41:29 CURRENT,1.0
04
 
05
# Last versions of JARS (Java Method):
06
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_jar (all) where LOWER(object_name)  like '%myaspect1%' ORDER BY r_modify_date desc ;
07
090xxxxx8efd0   MyAspect1-impl  08/12/2016 10:51:23 09/12/2016 09:41:28 1.0
08
090xxxxx906f9   MyAspect1-impl  09/12/2016 09:41:28 09/12/2016 09:41:28 CURRENT,1.1
09
0902xxxx906f7   MyAspect1   09/12/2016 09:41:24 09/12/2016 09:41:27 CURRENT,1.1
10
090xxxx48efcf   MyAspect1   08/12/2016 10:51:22 09/12/2016 09:41:24 1.0
11
 
12
# Last versions of DAR:
13
select r_object_id, object_name, r_creation_date, r_modify_date, r_version_label from dmc_dar (all) where LOWER(object_name)  like '%myaspect1%' ORDER BY r_modify_date desc ;
14
080xxxxxx06fb   ECMMyAspect1    09/12/2016 09:41:29 09/12/2016 09:41:29 CURRENT,1.1
15
080xxxxx8efda   ECMMyAspect1    08/12/2016 10:51:24 08/12/2016 10:51:24 1.0
16
 
17
# Last versions of JARS (Java Method):
18
SELECT r_object_id, object_name, r_creation_date, r_modify_date, trace_launch,  method_type, use_method_server, method_verb, r_version_label from dm_method (all) where LOWER(object_name)  like '%myaspect1%' ORDER BY r_modify_date;
19
-
20
 
21
# Last versions of Aspect:
22
select r_object_id, object_name, primary_class, r_object_type, r_folder_path, i_attr_def from dmc_aspect_type where LOWER(object_name) like 'aspect_myaspect1%';
23
0b0xxxxxxfc3    aspect_myaspect1    com.huo.lu.my.ecm.aspect.impl.MyAspect1 dmc_aspect_type /System/Modules/Aspect/aspect_myaspect1 dmi_0302xxxxx0252

---

——————— SESSION ————————–

---

• Some DQL requests:

view source
print?
1
EXECUTE show_sessions;
2
...
3
EXECUTE list_sessions;
4
...
5
EXECUTE count_sessions;

---

——————— JOB ————————–

---

• Documentum : Job : Force the starting and stopping of a running job (DQL, API)
http://www.javablog.fr/documentum-job-force-the-starting-and-stopping-of-a-running-job-dql-api.html
• Modify a job via API (remove, append method arguments…):

view source
print?
01
API : begintran,c
02
...
03
OK
04
API : retrieve,c,dm_job where object_name = 'HuO_MyChangeOwner'
05
...
06
080xxxxxxxx45
07
API : remove,c,l,method_arguments
08
...
09
OK
10
API : remove,c,l,method_arguments
11
...
12
OK
13
API : append,c,l,method_arguments
14
-grace_period 1
15
...
16
OK
17
API : append,c,l,method_arguments
18
-condition my_id IN (SELECT my_id FROM dm_dbo.my_table WHERE my_condition_1 IN (12,34,56))
19
...
20
OK
21
API : save,c,l
22
...
23
OK
24
API : commit,c
25
...
26
OK
27
API : dump,c,l
28
...
29
USER ATTRIBUTES
30
 
31
  object_name                : HuO_MyChangeOwner
32
  title                      : HuO
33
  subject                    :
34
  authors                  []:
35
  keywords                 []:
36
  resolution_label           :
37
  owner_name                 : myuserprd
38
  owner_permit               : 7
39
  group_name                 : docu
40
  group_permit               : 6
41
  world_permit               : 3
42
  log_entry                  :
43
  acl_domain                 : HuOCOMPANYPROD
44
  acl_name                   : dm_4502xxxxxxxxx0102
45
  language_code              :
46
  method_name                : HuO_MyChangeOwner
47
  method_arguments        [0]: -grace_period 1
48
                          [1]: -condition my_id IN (SELECT my_id FROM dm_dbo.my_table WHERE my_condition_1 IN (12,34,56))
49
  pass_standard_arguments    : T
50
  start_date                 : 21/07/2010 00:00:00
51
  expiration_date            : 21/07/2031 00:00:00
52
  max_iterations             : 0
53
  run_interval               : 1
54
  run_mode                   : 2
55
  is_inactive                : F
56
  inactivate_after_failure   : F
57
  target_server              :
58
  method_trace_level         : 10
59
  run_now                    : F
60
  method_data              []: 
61
 
62
SYSTEM ATTRIBUTES
63
 
64
  r_object_id                : 080xxxxxxxx45
65
  r_object_type              : dm_job
66
  r_version_label         [0]: 1.0
67
                          [1]: CURRENT
68
....

---

——————— DELETE / DESTROY ————————–

---

The destroy delete only the CURRENT version, you must pass the documents in a specific order otherwise it does not work on previous versions.

Removes an object from the repository.
+ session Identifies an open repository session.
+ object_id Identifies the object that you want to remove from the repository. Use the object’s ID or an indirect reference (@object_id) that points to the object.
+ force_flag Valid only when object_id represents an ACL or lifecycle object, this flag allows Superusers to force the removal of an ACL or lifecycle that is still in use. The default is F. Set this to T to remove an ACL or lifecycle that is still in use.


The prune deletes the whole tree:

Removes unwanted versions of an object.
+ session Identifies an open repository session.
+ object_id Specifies the object whose version tree you want to prune. Use the object’s ID or an indirect reference (@object_id) that points to the object.
+ keepSLabel Directs the server to keep or remove versions having symbolic labels. The default is T. To remove versions that have symbolic labels, set this flag to F.

---

——————— SQL ————————–

---

• generate SQL of DQL request:

view source
print?
1
DQL : select * from dm_document where r_object_id = '090XXXXXXXXXXX'  ENABLE(GENERATE_SQL_ONLY);
2
 
3
-- generate_sql
4
select all dm_document.r_object_id,dm_document.object_name,dm_document.title,dm_document.subject,dm_document.resolution_label,dm_document.owner_name,dm_document.owner_permit,dm_document.group_name,dm_document.group_permit,dm_document.world_permit,dm_document.log_entry,dm_document.acl_domain,dm_document.acl_name,dm_document.language_code,dm_document.r_object_type,dm_document.r_creation_date,dm_document.r_modify_date,dm_document.a_content_type from dm_document_sp  dm_document where ((dm_document.r_object_id='090XXXXXXXXXXX')) and (dm_document.i_has_folder = 1 and dm_document.i_is_deleted = 0)

---

——————— STORE / LOCATION ————————–

---

….TO BE CONTINUED