JavaBlog.fr / Java.lu DCTM : DQL / API

DCTM : DQL / API


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


• Config folder Security

DQL : select folder_security from dm_docbase_config;
1 = enable

• Locales of docbase “Data Dictionary Locales”:

DQL : select  dd_locales from dm_docbase_config;
fr
en

• ACL default mode => 1 : folder

DQL : select default_acl from dm_server_config;

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

DQL : select owner_name  from dm_docbase_config;
- DEV : DEMAT_ADMIN_DEV
- PROD : DEMAT_ADMIN_PROD

SELECT DISTINCT r_install_owner FROM dm_server_config;
- DEV : mygedadm <br /><br />select owner_name  from dm_docbase_config;<br />- DEV : MY_DOCBASE_NAME </pre>
<pre>select operator_name  from dm_server_config;
- DEV : DEMAT_ADMIN_DEV
- PROD : DEMAT_ADMIN_PROD

SELECT * from dm_user where user_name = 'dm_dbo';
OR SELECT * from dm_user where user_name = 'DEMAT_ADMIN_DEV';
OR SELECT * from dm_user where user_name = 'DEMAT_ADMIN_PROD'; 

- DEV :
r_object_id=110157xxxxxxxxx1
user_name=DEMAT_ADMIN_DEV
user_os_name=DEMAT_ADMIN_DEV
user_group_name=docu

- PROD :
r_object_id=1101c0xxxxxxxxx1
user_name=DEMAT_ADMIN_PROD
user_os_name=DEMAT_ADMIN_PROD
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:

DQL : select object_name, file_system_path, path_type, r_object_type from dm_location order by 2 ;
auth_plugin		D:\Documentum\dba\auth	directory	dm_location

assume_user		D:\Documentum\dba\dm_assume_user.exe	file		dm_location

change_password		D:\Documentum\dba\dm_change_password.exe	file		dm_location

config			D:\Documentum\dba\config	directory	dm_location

validate_user		D:\Documentum\dba\dm_check_password.exe	file		dm_location

check_signature		D:\Documentum\dba\dm_check_password.exe	file		dm_location

convert			D:\Documentum\product\7.1\convert	directory	dm_location

dsearch			D:\Documentum\fulltext\dsearch	directory	dm_location

dm_ca_store_fetch_location	D:\Documentum\share\temp\dm_ca_store	directory	dm_location

ldapcertdb_loc		D:\Documentum\dba\secure\ldapdb	directory	dm_location

log			D:\Documentum\dba\log	directory	dm_location

dm_dba			D:\Documentum\dba	directory	dm_location

nls_chartrans		D:\Documentum\product\7.1\install\external_apps\nls_chartrans	directory	dm_location

temp			D:\Documentum\share\temp	directory	dm_location

my_store		\\MYFILESSERVER\DEV\Data\MY_DOCBASE_DEV\my_store	directory	dm_location

test_my			\\MYFILESSERVER\DEV\Data\MY_DOCBASE_DEV\test_my		directory	dm_location

encrypted_storage_01	\\MYFILESSERVER2\Data\MY_DOCBASE_DEV\encrypted_storage_01 	directory	dm_location

storage_01		\\MYFILESSERVER3\data\MY_DOCBASE_DEV\content_storage_01	directory	dm_location

replica_storage_01	\\MYFILESSERVER3\data\MY_DOCBASE_DEV\replica_content_storage_01directory	dm_location

replicate_location	\\MYFILESSERVER3\data\MY_DOCBASE_DEV\replicate_temp_store	directory	dm_location

streaming_storage_01	\\MYFILESSERVER3\data\MY_DOCBASE_DEV\streaming_storage_01	directory	dm_location

thumbnail_storage_01	\\MYFILESSERVER3\data\MY_DOCBASE_DEV\thumbnail_storage_01	directory	dm_location

common			data\common	directory	dm_location

events			data\events	directory	dm_location

• Mount point (sharing):

DQL : select object_name, host_name, file_system_path from dm_mount_point ;
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).

DQL : select object_name, acs_base_url from dm_acs_config ;
MYDCTMSERVERACS1
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

API :  apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T
API : close,c,q0

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


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

API : begintran,c
API : create,c,my_huo_custom_type
...
0xxxxxxxxxxcf7
API : set,c,l,object_name
My object for document 09xxxxxx61
...
OK
API : set,c,l,acl_domain
dm_dbo
...
OK
API : set,c,l,acl_name
my_acl_business
...
OK
API : set,c,l,my_tgt_object_id
09xxxxxx61
...
OK
API : set,c,l,my_date,'dd/mm/yyyy hh:mi:ss'
27/02/2016 15:23:37
...
OK
API : set,c,l,my_title
Title reclassification
...
OK
API : set,c,l,my_description
From 'AAAAA' / 'BBBBB' / 'CCCCC'
...
OK
API : save,c,l
...
OK
API : commit,c

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

API : create,c,my_huo_custom_type
...
09xxxxxxxdf
API : set,c,l,ref_doc,'SCAN-HUO-JAVA123456789'
...
OK
API : set,c,l,acl_name,'MY-ACL-HUO'
...
OK
API : set,c,l,acl_domain,dm_dbo
...
OK
API : set,c,l,status,6
...
OK
API : set,c,l,category,2
...
OK
API : set,c,l,is_reprise,0
...
OK
API : set,c,l,my_repeat_field1[0],'JAVALU123'
...
OK
API : set,c,l,my_repeat_field1[1],'JAVALU456'
...
OK
API : set,c,l,my_repeat_field2[0],'009'
...
OK
API : set,c,l,my_date1,DATE(now)
...
OK
API : set,c,l,my_date2,DATE(’05/31/2115’)
...
OK
API : set,c,l,my_date3,DATE('NULLDATE')
...
OK
API : set,c,l,object_name,'MY_DOC_JAVALU123456'
...
OK
API : set,c,l,a_content_type
pdf
...
OK
API : setfile,c,l,T:\HUO\myPDFfile.pdf
...
OK
API : link,c,l,'/Cabinet1/Folder1/Folder12/01 - Mon Dossier 1'
...
OK
API : link,c,l,'/Cabinet1/Folder2/Folder22/01 - Mon Dossier 2'
...
OK
API : save,c,l
...
OK

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

API : create,c,my_huo_custom_type
...
090xxxxxx08
API : set,c,l,acl_domain
dm_dbo
...
OK
API : set,c,l,acl_name
MY-ACL-HUO
...
OK
API : set,c,l,status
6
...
OK
API : set,c,l,my_repeat_field1[0]
70130069
...
OK
API : set,c,l,my_repeat_field1[1]
70017877
...
OK
API : set,c,l,my_repeat_field2[0]
009
...
OK
API : set,c,l,my_date1
31/05/2016
...
OK
API : set,c,l,my_date2
31/05/2115
...
OK
API : set,c,l,my_date3
NULLDATE
...
OK
API : set,c,l,ref_doc
SCAN-HUO-JAVA123456789
...
OK
API : set,c,l,is_reprise
0
...
OK
API : set,c,l,object_name
MY_DOC_JAVALU123456
...
OK
API : set,c,l,category
2
...
OK
API : link,c,l,'/Cabinet1/Folder1/Folder12/01 - Mon Dossier 1'
...
OK
API : link,c,l,'/Cabinet1/Folder2/Folder22/01 - Mon Dossier 2'
...
OK
API : set,c,l,a_content_type
pdf
...
OK
API : setfile,c,l,T:\HUO\myPDFfile.pdf
...
OK
API : save,c,l
...
OK

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

API : create,c,my_huo_custom_type
...
090xxxxxca

090xxxxxca = r_object_id of the newly created object

API : set,c,090xxxxxca,object_name
DATA=MY_DOC_JAVALU123456
...
API : set,c,090xxxxxca,a_content_type
DATA=pdf
...
API : setfile,c,090xxxxxca,T:\HUO\myPDFfile.pdf
...
API : link,c,090xxxxxca,'/Temp'
...
API : save,c,090xxxxxca
...
[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

API : reset,c,090xxxxxca
...
OK
API : save,c,090xxxxxca
...
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

 

DQL : select r_object_id, i_contents_id from my_huo_custom_type WHERE r_object_id = '090xxxxxca';
090xxxxxca	060yyyyyyyyyyyb8

DQL : select r_object_id, parent_id  from dmr_content WHERE r_object_id = '060yyyyyyyyyyyb8';
060yyyyyyyyyyyb8	090xxxxxca

DQL : execute get_path for '060yyyyyyyyyyyb8';
D:\Documentum\data\mydocbase\content_storage_01\xxxxxx\8092482\6c\6b.pdf

DQL : SELECT  * FROM dm_document WHERE folder('/Temp');
090xxxxxca	MY_DOC_JAVALU123456 ....

DQL : DELETE my_huo_custom_type objects where r_object_id = '090xxxxxca';
1

DQL : select r_object_id, i_contents_id from my_huo_custom_type WHERE r_object_id = '090xxxxxca';
___nothing

DQL : select r_object_id, parent_id  from dmr_content WHERE r_object_id = '060yyyyyyyyyyyb8';
060yyyyyyyyyyyb8 ''

DA : EXECUTION DU JOB dm_DMCLEAN

DQL : select r_object_id, parent_id  from dmr_content WHERE r_object_id = '060yyyyyyyyyyyb8';
___nothing

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


• Updating a document via API using DQL

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'
close,c,q0
...
OK

• Updating a document in pure API (SOLUTION 1)

API : fetch,c,0901xxxxxf
...
OK
API : set,c,l,object_name
MY_DOC_PIECE IDENTITE
...
OK
API : set,c,l,my_attr1,0
...
OK
API : set,c,l,my_attr2,1
...
OK
API : set,c,l,my_attr3,1
...
OK
API : save,c,l
...
OK

• Updating a document in pure API (SOLUTION 2)

API : retrieve,c,my_doc_pj where r_object_id = '090xxxxxxxx6df'
...
090xxxxxxxx6df
API : set,c,l,my_attr1,0
...
OK
API : set,c,l,my_attr2,1
...
OK
API : set,c,l,my_attr3,1
...
OK
API : set,c,l,object_name
MY_DOC_PIECE IDENTITE
...
OK
API : save,c,l
...
OK

• Updating a document in pure API (SOLUTION 3)

API : set,c,080xxxxxxxxxx7a,event_date,'dd/mm/yyyy hh:mi:ss'
27/02/2017 13:11:19
...
OK
API : save,c,080xxxxxxxxxx7a
...
OK
API : set,c,080xxxxxx1d,event_date,'dd/mm/yyyy hh:mi:ss'
27/02/2017 13:11:19
...
OK
API : save,c,080xxxxxx1d
...
OK
API : set,c,080xxxxxxc50,event_date,'dd/mm/yyyy hh:mi:ss'
27/02/2017 13:11:19
...
OK
API : save,c,080xxxxxxc50
...
OK

• Updating a document in pure DQL

DQL : UPDATE my_doc_pj  (ALL) OBJECTS set owner_name='myuser1' where owner_name='myuser2';
UPDATE my_doc_pj  OBJECTS set my_attr10='6' where r_object_id ='[id]';

• Updating a repeating attribute of document in pure DQL

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

API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating1[1]
...
OK
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating1[2]
...
[DM_OBJECT_W_DELETE_ATTR_POSITION_ERROR]warning: attempt to delete non-existent attribute 86 
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating1[1]
...
OK
API : save,c,0901xxxxxxxxxxx62
...
OK
API : remove,c,0901xxxxxxxxxxx62,my_attr_repeating2[1]
...
OK
API : save,c,0901xxxxxxxxxxx62
...
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.

API : set,c,{r_object_id},r_immutable_flag
SET : 0
...
OK
API : insert,c,{r_object_id},my_huo_aspect.projects_number[{pos}]
SET : {project_number}
...
OK
API : set,c,{r_object_id},my_attribute1
SET : my_value1
...
OK
API : save,c,{r_object_id}
...
OK
API : set,c,{r_object_id},r_immutable_flag
SET : 1
...
OK
API : save,c,{r_object_id}
...
OK

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


• Get the content file of a document via API:

#----- DOCUMENT and CONTENT :
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);
...
090xxxxxx8f	060xxxxa84


#----- Example 1: 090xxxxxx8f = ID of dm_document
API : getfile,c,090xxxxxx8f,'C:\temp\toto3.pdf',pdf
...

#----- Example 2: 090xxxxxx8f = ID of dm_document
API : getfile,c,090xxxxxx8f
...
D:\_____MyUser1dData____\DqMan 5.0.0.7\dmcl....\PJ_8140.pdf

• Document with a XML rendition

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';
...
060xxxxxxxxxxx50	0	1	11739	pdf	270xxxxxxxxxxxxc9	0		280xxxxxxxxxx100
060xxxxxxxxxxx51	2	1	2447	xml	270xxxxxxxxxxxxff	0		280xxxxxxxxxx100

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

API : getfile,c,09xxxxxxxxxxxxxxx4
...
C:\APP\dqMan\MYSERVER\dmcl\...0xxxx5\....\mydoc.pdf

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

API : getfile,c,09xxxxxxxxxxxxxxx4,,xml
...
C:\APP\dqMan\MYSERVER\dmcl\...\0xxxx5\....\mydoc.xml

• Count of content by subtype of dm_document:

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;
dm_document	21366
my_subytpe1_document	66
my_subytpe2_document	185
...

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

DQL : select sum(content_size)/(1024*1024) from dmr_content;
TEST = 6388.72165775299 (~6Go)
PROD = 1021340.92345428 (~1To)
...
DQL : select sum(r_full_content_size)/(1024*1024) from dm_document;
42646.2632694244
...
DQL : select sum(r_full_content_size)/(1024*1024) from dm_document (ALL);
59575.4518547058
...
select (((sum(full_content_size)/1024)/1024)/1024) as sizein_GB from dmr_content
sizein_GB
73.3642595726997

• Number of objects by type that have content:

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;
...
dmc_jar	1107
dmc_preset_package	2
dmc_rps_schema	2
dmc_tcf_activity_template	20
dmc_xfm_adaptor_config	7
dmc_xfm_layout	5
dm_docset	1
dm_document	21352
dm_esign_template	1
dm_format_preferences	1
dm_menu_system	2
dm_plugin	2
dm_xfm_form	5
dm_xfm_instance	4
dm_xfm_resource	53
dm_xfm_schema	5
dm_xml_config	6
huo_client_document	631340
huo_company_document	66

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

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;
...
dmc_jar	13259.1457748413
dmc_preset_package	0.0616598129272461
dmc_rps_schema	0.0339202880859375
dmc_tcf_activity_template	0.0231971740722656
dmc_xfm_adaptor_config	0.00547695159912109
dmc_xfm_layout	0.0469779968261719
dm_docset	0.000401496887207031
dm_document	1978.51619911194
dm_esign_template	0.0441122055053711
dm_format_preferences	0.000110626220703125
dm_menu_system	0.345424652099609
dm_plugin	0.1298828125
dm_xfm_form	0.0764303207397461
dm_xfm_instance	0.00278568267822266
dm_xfm_resource	0.0879192352294922
dm_xfm_schema	0.0179119110107422
dm_xml_config	0.0766468048095703
huo_client_document	61000.0035915375
huo_company_document	48.2166175842285

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

# 090xxxxxxxx27= ID of dm_document
API : getpath,c,090xxxxxxxx27
...
\\MYFILESERVER\data\MY_DOCBASE_DEV\content_storage_01\...\00xxxx5\yy\xx\ce\8e.xls

# 090xxxxxxxxxc13= ID of dm_document
API : getpath,c,090xxxxxxxxxc13,,xml
...
\\MYDCTMSERVER\data\MY_DOCBASE_DEV\content_storage_01\...\00XXXX5\80\22\e3\98.xml

# 060xxxxx12 = ID of dm_content
DQL : execute get_path for '060xxxxx12';
d:\Documentum\data\mydocbase\content_storage_01\...\4f\4c.pdf

# 060xxxxxx5 = ID of dm_content
DQL : execute get_path for '060xxxxxx5';
d:\Documentum\data\mydocbase\content_storage_01\...\0\3c.txt

### Get the physical folderpath of CONTENT on CENTERA
# 090xxxxxxxxfc= ID of dm_document
API : getpath,c,090xxxxxxxxfc
...
8HP8XXXXXXXXXXXXXXPU

 

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

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 
where a_content_type = 'dxl' and r_object_id IN (ID('09xxxxxxxxxxxxxa47'));
# r_page_cnt : 1
# r_content_size : 19484
# a_content_type : dxl
# i_contents_id : 06xxxxxxxxxxx65

######### DMR CONTENT
select * from dmr_content where r_object_id = '06xxxxxxxxxxx65';
# parent_id [0]: 09xxxxxxxxxxxxxa47
# page [0]: 0
# rendition : 0
# parent_count : 1

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';
# 06xxxxxxxxxxx65 0 1 19484 dxl 27xxxxxxxxxxxxx2c9 0 2xxxxxxxxxxxxxxx100
# 06xxxxxxxxxxx66 2 1 1694 xml 27xxxxxxxxxxxxx1ff 0 2xxxxxxxxxxxxxxx100

API : getfile,c,09xxxxxxxxxxxxxa47
C:\APP\dqMan\....\....\My FILE.dxl

API : getfile,c,09xxxxxxxxxxxxxa47,,dxl
C:\APP\dqMan\....\....\My FILE.dxl

API : getfile,c,09xxxxxxxxxxxxxa47,,dxl,0
C:\APP\dqMan\....\....\My FILE.dxl

API : getfile,c,09xxxxxxxxxxxxxa47,,xml
C:\APP\dqMan\....\....\My FILE.xml

API : getfile,c,09xxxxxxxxxxxxxa47,,xml,0
C:\APP\dqMan\....\....\My FILE.xml

API : getfile,c,09xxxxxxxxxxxxxa47,,dxl,1
[DM_SYSOBJECT_E_INVALID_PAGE_NUM]error:  "Invalid page number = 1 for My FILE sysobject.  Maximum page count is 1."

API : getfile,c,09xxxxxxxxxxxxxa47,,xml,1
[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:

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 
where r_object_id IN (ID('46xxxxxxxxxxxxxxxb02'));
# r_page_cnt                 : 2
# r_content_size             : 5
# a_content_type             : text
# i_contents_id              : 06xxxxxxxxxxxx56e
######### DMR CONTENT
select * from dmr_content where  r_object_id = '06xxxxxxxxxxxx56e';
# parent_id               [0]: 46xxxxxxxxxxxxxxxb02
# page                    [0]: 0
# rendition                  : 0
# parent_count               : 1

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';
# 06xxxxxxxxxxxx56e	0	1	5	text	27xxxxxxxxxxxxxxxd8	0		2xxxxxxxxxxxxxxx100
# 060220c58000056f	0	1	5	text	27xxxxxxxxxxxxxxxd8	1		2xxxxxxxxxxxxxxx100

API : getfile,c,46xxxxxxxxxxxxxxxb02
C:\APP\dqMan\....\....\5 Phases + Final.txt

API : getfile,c,46xxxxxxxxxxxxxxxb02,,text
C:\APP\dqMan\....\....\5 Phases + Final.txt

API : getfile,c,46xxxxxxxxxxxxxxxb02,,text,0
C:\APP\dqMan\....\....\5 Phases + Final.txt

API : getfile,c,46xxxxxxxxxxxxxxxb02,,text,1
C:\APP\dqMan\....\....\5 Phases + Final16806a9f.txt

API : getfile,c,46xxxxxxxxxxxxxxxb02,,text,2
[DM_SYSOBJECT_E_INVALID_PAGE_NUM]error:  "Invalid page number = 3 for 5 Phases + Final sysobject.  Maximum page count is 2."</pre>
<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

### CENTERA store
select r_object_id from dm_ca_store;
...
r_object_id
6d0xxxxxx114  

### dm_store
API : dump,c,6d0xxxxxx114
...
  r_object_id                     : 6d0xxxxxx114
  name                       : centera_store_no_retention
  a_plugin_id                     : 670xxxxxx100
  a_storage_params             [0]: myserver1.mydomain.lu,myserver2.mydomain.lu?D:\Documentum\dba\Centera\MYDEMAT.pea
                               [1]: Read_option:read_ahead_size:170
                               [2]: Pool_option:max_connections:1
  a_content_attr_name           []: none
  a_retention_attr_name           :
  a_content_attr_desc           []: none
  a_retention_attr_required       : F
  a_default_retention_date        : nulldate

### dm_plugin
API : dump,c,670xxxxxx100
...
  r_object_id                : 670xxxxxx100
  object_name                : CSEC Plugin
  r_object_type              : dm_plugin
  i_contents_id              : 060xxxxxxxx13d

### dmr_content
API : dump,c,060xxxxxxxx13d
...
  r_object_id                : 060xxxxxxxx13d
  parent_id               [0]: 670xxxxxx100
  set_file                   : D:\Documentum\product\7.1\bin\emcplugin.dll
  i_format                [0]: 27xxxxxxxxx1ec

### dm_format
API : dump,c,27xxxxxxxxx1ec
...
  r_object_id                : 27xxxxxxxxx1ec
  name                       : win32shrlib

  description                : Windows Shared Library

  can_index                  : F

  topic_transform            : F

Use of Centera

### STORE
select * from dm_store ;
280220c580000100	filestore_01
6d0220c580000128	centera_store_no_retention

### DOC SUR FILESTORE
select r_object_id, i_contents_id from dm_document where a_storage_type = 'filestore_01' AND r_object_id  = '090220c5800001b2';
...
  r_object_id                : 090220c5800001b2
  a_storage_type             : filestore_01
  i_contents_id              : 060220c580000114

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';
...
  r_object_id                : 060220c580000114
  parent_id               [0]: 090220c5800001b2
  storage_id                 : 280220c580000100

### DOC SUR CENTERA
select r_object_id, i_contents_id from dm_document where a_storage_type = 'centera_store_no_retention' AND r_object_id  = '090220c5804eba70';
...
  r_object_id                : 090220c5804eba70
  a_storage_type             : centera_store_no_retention
  i_contents_id              : 060220c580481fd5  

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';
...
  r_object_id                : 060220c580481fd5
  parent_id               [0]: 090220c5804eba70
  storage_id                 : 6d0220c580000128

 

</pre>
<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>
<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>
<pre>

 




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


• Count of subdirectories in a directory

DQL : SELECT count(object_name) FROM dm_folder WHERE FOLDER('/Temp') AND object_name LIKE 'dm%' ;
0
DQL : SELECT count(object_name) FROM dm_folder WHERE FOLDER('/Temp',descend) AND object_name LIKE 'dm%' ;
42

• Count of documents (all versions) in a directory

DQL : SELECT count(mytype.r_object_id) FROM my_subtype_document (ALL) mytype, dm_folder dmfolder
WHERE FOLDER('/MY_CLIENTS',descend)
	AND any mytype.i_folder_id=dmfolder.r_object_id
	AND dmfolder.r_folder_path IS NOT NULLSTRING
	AND dmfolder.r_folder_path like '/MY_CLIENTS/1789%'
ENABLE (ROW_BASED);
123

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


• Use of LEFT OUTER JOIN in DQL:

DQL : select d.r_object_id, p.project_id, c.company_id
from my_document d
left outer join dm_dbo.myproject p on d.project_id = p.project_id
left outer join dm_dbo.mycompany c on d.company_id = c.company_id
where r_object_id IN(
'09xxxxxxxxxxx3'
) order by r_modify_date desc;
...
r_object_id=09xxxxxxxxxxx3
project_id=
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’:

DQL : select alias_set.r_object_id, myfirstalias.pos as myfirstalias_pos, mysecondalias.pos as mysecondalias_pos
from dm_alias_set alias_set
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 

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 

where alias_set.object_name LIKE 'AS\_project\_%'  escape '\' AND  LOWER(alias_set.object_name) NOT LIKE '%_archived' ;

............
66xxxxxxxxxx11	0	2
66xxxxxxxxxx01	1	3
66xxxxxxxxxxe2	1	3

...........
#Generation of API script via dqMan:

remove,c,{r_object_id},alias_name[{myfirstalias_pos}]
remove,c,{r_object_id},alias_value[{myfirstalias_pos}]
remove,c,{r_object_id},alias_category[{myfirstalias_pos}]
remove,c,{r_object_id},alias_usr_category[{myfirstalias_pos}]
remove,c,{r_object_id},alias_description[{myfirstalias_pos}]
remove,c,{r_object_id},alias_name[{mysecondalias_pos}]
remove,c,{r_object_id},alias_value[{mysecondalias_pos}]
remove,c,{r_object_id},alias_category[{mysecondalias_pos}]
remove,c,{r_object_id},alias_usr_category[{mysecondalias_pos}]
remove,c,{r_object_id},alias_description[{mysecondalias_pos}]
append,c,{r_object_id},alias_name
MyNewAlias
append,c,{r_object_id},alias_value
all_teams_users
append,c,{r_object_id},alias_category
2
append,c,{r_object_id},alias_usr_category
-1
append,c,{r_object_id},alias_description
Alias entry for all teams
save,c,{r_object_id}


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


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

DQL : describe dm_dbo.dm_alias_set_r;
...
alias_category	Integer
alias_description	Char(255)
alias_name	Char(32)
alias_usr_category	Integer
alias_value	Char(255)
i_position	Integer
r_object_id	Char(16)	


DQL : describe dm_dbo.dm_alias_set_s;
...
i_is_replica	Integer
i_vstamp	Integer
object_description	Char(128)
object_name	Char(32)
owner_name	Char(255)
r_object_id	Char(16)	

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;
...
660xxxxxxxxxxx36	16	Editors
660xxxxxxxxxxx36	14	Readers
660xxxxxxxxxxxc7	3	Editors
660xxxxxxxxxxxc7	1	Readers

API : dump,c,660xxxxxxxxxxxc7
...
USER ATTRIBUTES
  owner_name                 : mysuper001
  object_name                : AS_myproject_123456
  object_description         : Alias set for my docs of project 123456
  alias_name             [ 0]: OtherTeamMembers
                         [ 1]: Readers
                         [ 2]: Contributors
                         [ 3]: Editors
                         [ 4]: Administrators
  alias_value            [ 0]: my_grp_nobody
                         [ 1]: my_grp_all
                         [ 2]: my_grp_nobody
                         [ 3]: my_grp_nobody
                         [ 4]: my_grp_nobody
  alias_category         [ 0]: 2
                         [ 1]: 2
                         [ 2]: 2
                         [ 3]: 2
                         [ 4]: 2
  alias_usr_category     [ 0]: -1
                         [ 1]: -1
                         [ 2]: -1
                         [ 3]: -1
                         [ 4]: -1
  alias_description      [ 0]:
                         [ 1]:
                         [ 2]:
                         [ 3]:
                         [ 4]:
SYSTEM ATTRIBUTES
  r_object_id                : 660xxxxxxxxxxxc7
APPLICATION ATTRIBUTES
INTERNAL ATTRIBUTES
  i_is_replica               : F
  i_vstamp                   : 15

 

Other example:
<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)

API : dump,c,4c01cxxxxxxae
...
API : get,c,4c01cxxxxxxae,_dump
#System.out.println(dfDocument.getString("_dump"));
...
r_object_id : 4c01cxxxxxxae
object_name : test name
title :
...

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

API : get,c,090XXXXXXXXXXXXXXX03,_xml_string
# System.out.println(dfDocument.getString("_xml_string"));
...
...

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

DQL : genesis my_type;
...
CREATE TYPE my_type (
id_client CHAR(32) REPEATING,
format INT,
date_reception DATE,
date_creation DATE,
statut_valid INT,
type_doc CHAR(7) REPEATING,
category INT,
refs_operation CHAR(25) REPEATING,
reprise BOOL,
date_validite DATE,
ref_doc CHAR(100),
scan_date DATE,
my_comments_hist CHAR(255) REPEATING,
merging_parents_id ID REPEATING,
code CHAR(32),
my_comment CHAR(255)
)
WITH SUPERTYPE dm_document
go

ALTER TYPE my_type MODIFY
my_comments_hist CHAR(512),
my_comment CHAR(512)
go

• Display the attribute informations of a custom type

DQL : describe my_type;
or
API : describe,c,type,my_type;
.....
DQL : describe my_type;
Type Name:	my_type
SuperType Name:	dm_document

Attributes:	   112

object_name                       CHAR(255)
r_object_type                     CHAR(32)
title                             CHAR(400)
subject                           CHAR(192)
authors                           CHAR(48)    REPEATING
keywords                          CHAR(48)    REPEATING
a_application_type                CHAR(32)
a_status                          CHAR(16)
r_creation_date                   TIME
r_modify_date                     TIME
r_modifier                        CHAR(32)
r_access_date                     TIME
a_is_hidden                       BOOLEAN
i_is_deleted                      BOOLEAN
a_retention_date                  TIME
a_archive                         BOOLEAN
a_compound_architecture           CHAR(16)
a_link_resolved                   BOOLEAN
i_reference_cnt                   INTEGER
i_has_folder                      BOOLEAN
i_folder_id                       ID          REPEATING
r_composite_id                    ID          REPEATING
[...]
r_immutable_flag                  BOOLEAN
r_frozen_flag                     BOOLEAN
r_has_events                      BOOLEAN
acl_domain                        CHAR(32)
acl_name                          CHAR(32)
[...]
i_partition                       INTEGER
i_is_replica                      BOOLEAN
i_vstamp                          INTEGER
[...]
id_client CHAR(32)                REPEATING
format                            INTEGER
date_reception                    DATE
date_creation                     DATE
statut_valid                      INTEGER
type_doc                          CHAR(7) REPEATING
category                          INTEGER
refs_operation                    CHAR(25) REPEATING
reprise                           BOOL
date_validite                     DATE
ref_doc                           CHAR(100)
scan_date                         DATE
my_comments_hist                  CHAR(512) REPEATING
merging_parents_id                ID REPEATING
code                              CHAR(32)
my_comment                        CHAR(512)
API : get,c,090XXXXXXXXXXXXXXX03,_typestring
# System.out.println(dfDocument.getString("_typestring"));
...
3
TYPE dm_sysobject 030XXXXXXXXXXXXXX105
 NULL
86
  object_name  STRING S 255
  r_object_type  STRING S 32
  title  STRING S 400
  subject  STRING S 192
  authors  STRING R 48
  keywords  STRING R 48
  a_application_type  STRING S 32
  a_status  STRING S 16
  r_creation_date  TIME S 0
  r_modify_date  TIME S 0
  r_modifier  STRING S 255
  r_access_date  TIME S 0
  a_is_hidden  BOOL S 0
  i_is_deleted  BOOL S 0
  a_retention_date  TIME S 0
  a_archive  BOOL S 0
  a_compound_architecture  STRING S 16
  a_link_resolved  BOOL S 0
  i_reference_cnt  INT S 0
  i_has_folder  BOOL S 0
  i_folder_id  ID R 0
  r_composite_id  ID R 0
  r_composite_label  STRING R 32
  r_component_label  STRING R 32
  r_order_no  INT R 0
  r_link_cnt  INT S 0
  r_link_high_cnt  INT S 0
  r_assembled_from_id  ID S 0
  r_frzn_assembly_cnt  INT S 0
  r_has_frzn_assembly  BOOL S 0
  resolution_label  STRING S 32
  r_is_virtual_doc  INT S 0
  i_contents_id  ID S 0
  a_content_type  STRING S 32
  r_page_cnt  INT S 0
  r_content_size  INT S 0
  a_full_text  BOOL S 0
  a_storage_type  STRING S 64
  i_cabinet_id  ID S 0
  owner_name  STRING S 255
  owner_permit  INT S 0
  group_name  STRING S 255
  group_permit  INT S 0
  world_permit  INT S 0
  i_antecedent_id  ID S 0
  i_chronicle_id  ID S 0
  i_latest_flag  BOOL S 0
  r_lock_owner  STRING S 255
  r_lock_date  TIME S 0
  r_lock_machine  STRING S 80
  log_entry  STRING S 120
  r_version_label  STRING R 32
  i_branch_cnt  INT S 0
  i_direct_dsc  BOOL S 0
  r_immutable_flag  BOOL S 0
  r_frozen_flag  BOOL S 0
  r_has_events  BOOL S 0
  acl_domain  STRING S 255
  acl_name  STRING S 32
  a_special_app  STRING S 32
  i_is_reference  BOOL S 0
  r_creator_name  STRING S 255
  r_is_public  BOOL S 0
  r_policy_id  ID S 0
  r_resume_state  INT S 0
  r_current_state  INT S 0
  r_alias_set_id  ID S 0
  a_effective_date  TIME R 0
  a_expiration_date  TIME R 0
  a_publish_formats  STRING R 32
  a_effective_label  STRING R 32
  a_effective_flag  STRING R 8
  a_category  STRING S 64
  language_code  STRING S 5
  a_is_template  BOOL S 0
  a_controlling_app  STRING S 32
  r_full_content_size  DOUBLE S 0
  a_extended_properties  STRING R 32
  a_is_signed  BOOL S 0
  a_last_review_date  TIME S 0
  i_retain_until  TIME S 0
  r_aspect_name  STRING R 64
  i_retainer_id  ID R 0
  i_partition  INT S 0
  i_is_replica  BOOL S 0
  i_vstamp  INT S 0
TYPE dm_document 030XXXXXXXXXX129
dm_sysobject
0
TYPE my_cpy_document 030XXXXXXX59
dm_document

4
  my_metadata1_id  INT S 0
  my_metadata1_name  STRING S 160
  my_metadata2_id  INT S 0
  my_metadata2_name  STRING S 160
TYPE my_document 030XXXXXXXXXXXXX25a

my_cpy_document
3
  my_metadata3_id  INT S 0
  my_metadata3_name  STRING S 255
  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:

update dm_folder objects  move to '/New_Cabinet/New_Folder' where
r_object_id in (select r_object_id from dm_folder where object_name = 'MySubFolder' and any r_folder_path like '/Current_Cabinet/MyFolder%')
OR
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))
;

• Request to move documents:

update dm_document object move to '/New_Cabinet/New_Folder/SubFolderName' where r_object_id='090xxxxxxx140';

update dm_document (all) objects move to '/New_Cabinet/New_Folder/SubFolderName' where FOLDER('/Current_Cabinet/Current_Folder',descend)
	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') ;

update my_huo_document (all) OBJECTS move to '/Current_Cabinet/MyFolder/MySubFolder' where r_object_id IN ('090xxxxxx811', '090xxxxxxxd03');
;

 

• 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:

 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:

 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:

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:
    DQL : select i_attr_def from dmc_aspect_type   where object_name = 'my_huo_aspect';
    dmi_030xxxxxx0b
    
  2. Make the index
    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.

 DQL : EXECUTE drop_index WITH name=’user_index’
DQL : EXECUTE drop_index FOR ’1f00000011231563a’

• List the indexes on my_huo_type type

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
from dmi_index i
where any i.attribute &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;= 0 and i.index_type in (select t.r_object_id from dm_type t where t.name='my_huo_type');
...
1fxxxxx00	030xxxxxx25a	117	1	IDX_HUO_TYPE_MY_HUO_FIELD_1 DM_MY_DOCBASE_DEV_INDEX
1fxxxxx52	030xxxxxx25a	101,114	2	IDX_HUO_TYPE_MY_HUO_FIELD_1_AND_3	DM_MY_DOCBASE_DEV_INDEX

select * from dm_type t where t.name='my_huo_type';
  name                       : my_huo_type
  attr_name             [117]: my_huo_field1
                        [101]: my_huo_field2
                        [114]: my_huo_field3

• List the indexes on my_huo_aspect ASPECT:

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
from dmi_index i
where any i.attribute &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;= 0 and i.index_type in (select t.r_object_id from dm_type t where t.name='dmi_03xxxxxx57'); 

1f0xxxxxxxxe45	030xxxxxxx257	2	1	IDX_MAILASP_APPROVERS	DM_MY_DOCBASE_DEV_INDEX
1f0xxxxxxxxe46	030xxxxxxx257	3	1	IDX_MAILASP_REQUESTER	DM_MY_DOCBASE_DEV_INDEX
1f0xxxxxxxxe48	030xxxxxxx257	3	2	IDX_MAILASP_STATUS_REQ	DM_MY_DOCBASE_DEV_INDEX
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:

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
from dmi_index i
where any i.attribute &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;= 0 and i.index_type in (select t.r_object_id from dm_type t where t.name='my_huo_type')
and any i.attribute IN (
	select -(r.i_position+1)
	from dm_dbo.dm_type_r r, dm_dbo.dm_type_s s
	where r.attr_name in ('my_huo_field123')
		and r.r_object_id = s.r_object_id
	and s.name = 'my_huo_type'
); 


——————— 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.

select * from dm_process where r_definition_state=2;
...
r_definition_state
0 = Draft
1 = Validated
2 = Installed

• Workflow instance informations:

select * from dm_process where object_name  like 'my_demat_wkf%';
  r_object_id                : 4bxxxxxxxxxx29b
  r_object_type              : dm_process
  object_name                : my_demat_wkf
  r_act_def_id = dm_activity.r_object_id
    [0]: 4c02xxxxx0291
    [1]: 4c02xxxxxx292
    [2]: 4c02xxxxxx293
    [3]: 4c02xxxxxx294
    [4]: 4c02xxxxxx295
    [5]: 4c02xxxxxx296
    [6]: 4c02xxxxxx297

----- dm_process.r_act_def_id = : 4c02xxxxx0291 = dm_activity.r_object_id

• Activity instance informations:

SELECT r_object_id, object_name, exec_method_id FROM dm_activity where r_object_id = '4c02xxxxx0291';
  r_object_id                : 4c02xxxxx0291
  object_name                : Inclassable
  exec_method_id             : 10xxxxxx622a

----- 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.

SELECT stamp, item_id, item_name, date_sent, priority FROM dm_queue WHERE name = 'myuser1';

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

select count(*), r_runtime_state   from dm_workflow group by r_runtime_state;

• Workflows with an activity in error (200 wkf)

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

• Documents without workflow:

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:

select w.object_name from dm_workflow w, dmi_package p, dm_document d
	where w.r_object_id=p.r_workflow_id
	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):

select r_object_id as workitem_id, r_performer_name, r_queue_item_id
	from dmi_workitem where r_workflow_id = 'workflowId'
	and r_queue_item_id != '0000000000000000'
	and r_queue_item_id not in (select r_object_id from dmi_queue_item);

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

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):

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:

select r_object_id, object_name, title, owner_name,r_object_type, r_creation_date, r_modify_date, a_content_type
	from dm_document
	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:

select r_component_id from dmi_package p where
	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:

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

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

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'
...
# r_object_id  item_id  item_name  task_name  task_state  actual_start_date  dequeued_date
# 1b0xxxxxxxxx993a  4a0xxxxxxx915  paused  1  paused

• Find the WORKITEM, ACTIVITY of a workflow:

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,
	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
	from dmi_workitem r, dm_workflow wf, dm_activity act
	where wf.r_object_id = r.r_workflow_id
		and r.r_act_def_id = act.r_object_id
		and wf.r_object_id = '4d0xxxxxx4905';

...
# workitem_id  workitem_state  workitem_performer_name  workitem_queue_item_id  r_act_seqno  workflow_id  activity_id  activity_name
# 4a0xxxxxxx911  2  DMAT_ADMIN  0000000000000000  0  4d0xxxxxx4905  4c01xxxxxxxx12ca  Restart
# 4a0xxxxxxx915  5  DMAT_ADMIN  1b0xxxxxxxxx993a  1  4d0xxxxxx4905  4c0xxxxxxxxx12d1  1

• Find an activity

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

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


• Transaction management in pure API.

begintran,c
#
retrieve,c,my_custom_type where r_object_id = '090xxxxxxxxxd' AND my_title = '123456 JAVA.LU'
set,c,l,acl_name
my_acl_huo123
save,c,l
#
commit,c
### abort,c

• Transaction management in pure DQL.

########### Example 1 : NO TRANSACTION - COMMIT AUTO
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');

########### Example 2 : COMMIT AUTO
BEGIN TRAN;
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');

########### Example 3 : EXPLICIT TRANSACTION COMMITED
BEGIN TRAN;
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');
COMMIT TRAN;

########### Example 4 : EXPLICIT TRANSACTION ABORTED
BEGIN TRAN;
UPDATE my_custom_document objects SET company_name='ICAP PlcXXXXX' WHERE project_id=0 AND r_object_id IN ('090220c5804ebb5d','090220c5804ebb5e','090220c5804ebb61');
ABORT TRAN;

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


• Example 1:

select r_object_id, count(my_repeat_field1), count(my_repeat_field2)
from my_subtype_document
group by r_object_id
HAVING count(my_repeat_field1)&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;1 AND count(my_repeat_field2)&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;1
enable (ROW_BASED) ;

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


• Creation of AuditTrail object via pure API:

create,c,dm_audittrail
set,c,l,event_name
my_event_huo_name
set,c,l,event_source
My process
set,c,l,user_name
My process
set,c,l,audited_obj_id
09xxxxxx62
set,c,l,string_1
Value Str 1
set,c,l,string_2
Value Str 2
set,c,l,string_3
Value Str 3
set,c,l,id_1
09xxxxx621
set,c,l,id_2
09xxxxxx622
set,c,l,sid_3
09xxxxxx623
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:

API : retrieve,c,dm_audittrail where r_object_id ='5f0xxxxxx7'
...
5f0xxxxxx7
API : destroy,c,l
...
OK

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

DQL : UPDATE dm_audittrail OBJECTS set user_name='huseyin1' where event_name = 'my_event' and r_object_id ='5f02xxxxxxxa'
...
[DM_QUERY_E_UP_NOT_UPDATEABLE]error: You have specified a non updateable type name (dm_audittrail).
...

API : fetch,c,5f02xxxxxxxa
...
OK
API : set,c,l,user_name
huseyin1
...
OK
API : save,c,l
...
[DM_AUDITTRAIL_E_CANT_MODIFY]error: You can not modify any existing AuditTrail object 5f02xxxxxxxa.

• Number of audittrail events:

DQL : select event_name, count(*) from dm_audittrail group by event_name order by 1;
...
event_name 			count(*)
...
Calling VersioningMethod 	659.0
Executing WKF_FinishMethod 		340326.0
Executing WKF_InclassableMethod 	2422.0
Executing WKF_VersioningMethod 	659.0
Executing WKF_WorkflowMethod 	792367.0
dm_audit 	1.0
dm_checkin 	860420.0
dm_checkout 	861471.0
dm_destroy 	91128.0
dm_link 	801360.0
dm_lock 	861471.0
dm_logon_failure 	6599.0
dm_save 	3663335.0
dm_setfile 	1192112.0
dm_unlink 	387180.0
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]")
select * from dm_type ;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/pre&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;pre&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;# 030xxxxxxxxxf0	my_huo_document	my_root_document
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]]”)

&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/pre&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;
&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;p&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;select * from dm_type ;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;br /&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;# 030xxxxxxxxxf0 my_huo_document my_root_document&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;br /&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;unaudit,c,030xxxxxxxxxf0,dm_save&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;br /&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;

 

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:

create,c,dm_user
set,c,l,user_name
my_browse_user
set,c,l,user_os_name
my_browse_user
set,c,l,user_privileges
0
set,c,l,client_capability
0
save,c,l

• Group creation via API:

create,c,dm_group
set,c,l,group_name
access-browse-my-group
set,c,l,owner_name
dm_dbo
set,c,l,is_private
F
set,c,l,globally_managed
F
set,c,l,group_class
group
set,c,l,description
Browse access to all documents
save,c,l

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

execquery,c,F,alter group 'access-main-delete-group' add 'my-sub-group-1';
close,c,q0
execquery,c,F,alter group 'access-main-delete-group' add 'MyHuoUser1';
close,c,q0
...
# access-main-delete-group / groups_names            []: none
retrieve,c,dm_group where group_name = 'access-main-delete-group'
append,c,l,groups_names
my-sub-group-1
append,c,l,groups_names
my-sub-group-2
save,c,l

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

execquery,c,F,alter group 'access-main-delete-group' drop 'my-sub-group-1';
close,c,q0
execquery,c,F,alter group 'access-main-delete-group' drop 'MyHuoUser1';
close,c,q0
...
# access-main-delete-group / groups_names            [0]: my-sub-group-1 [1]: my-sub-group-2
retrieve,c,dm_group where group_name = 'access-main-delete-group'
remove,c,l,groups_names[1]
save,c,l

• The groups of an user via DQL:

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');
...
development_team
ecm_team

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

select count(u.user_name), g.group_name from dm_group g, dm_user u
where any g.users_names = u.user_name and (g.group_name like 'ecm_team%')
group by g.group_name;
...
10	ecm_team

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

SELECT count(DISTINCT i_all_users_names),group_name FROM dm_group g
WHERE (g.group_name like 'grp-%' or group_name like 'ecm_team%')
group by g.group_name;
...
80	ecm_team

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

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';
huseyin1
...
10 rows

• Users in a group including subgroup users via DQL:

select i_all_users_names from dm_group g where g.group_name = 'ecm_team';
huseyin1
...
80 rows

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

select distinct group_name, groups_names, i_supergroups_names from DM_GROUP where group_name IN ('grp_all') ENABLE (ROW_BASED);
...
grp_all		ecm_team	grp_all

select distinct group_name, groups_names, i_supergroups_names from DM_GROUP where group_name IN ('ecm_team') ENABLE (ROW_BASED);
...
ecm_team	____		grp_all

• Number of users according to each state via DQL:

select count(user_name), user_state from dm_user where user_name not  like 'dm_%' group by user_state;
...
count(dm_user.user_name)  user_state
5128	1
131205	0
2	3
...
0, indicating a user who can log in (= active)
1, indicating a user who cannotlog in (= inactive)
2, meaning a user who is locked
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:

########## PST my_acl_pst_1 for user 'myuser001'

select * from dm_acl where object_name = 'my_acl_pst_1';
...
# 450XXXXXXXXXXX7b	my_acl_pst_1

### analyse of accesses via PST for 'myuser001'
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';
...
# 120xxxxxxxxxxxxe18	group1
# 120xxxxxxxxxxxx15	group2

### analyse of accesses via instances of PST for 'myuser001'
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';
...
# 120xxxxxxxxxxxxe18	group1
# 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:

select owner_name  from dm_docbase_config;
DEMAT_ADMIN_DEV
....
select operator_name  from dm_server_config;
DEMAT_ADMIN_DEV
....
SELECT * from dm_group where group_name = 'docu';
  i_all_users_names       [0]: DEMAT_ADMIN_DEV
                          [1]: dm_autorender_mac
                          [2]: mysuperuser
                          [3]: dm_mediaserver
                          [4]: dm_autorender_win31

  users_names             [0]: DEMAT_ADMIN_DEV
                          [1]: mysuperuser
                          [2]: dm_autorender_win31
                          [3]: dm_autorender_mac
                          [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.

select owner_name  from dm_docbase_config;
DEMAT_ADMIN_DEV
....
select operator_name  from dm_server_config;
DEMAT_ADMIN_DEV
....
SELECT * from dm_group where group_name = 'admingroup';
  users_names             [1]: DEMAT_ADMIN_DEV
                          [2]: dm_fulltext_index_user
                          [3]: mysuperuser

  i_all_users_names       [0]: mysuperuser
                          [1]: dm_fulltext_index_user
                          [2]: DEMAT_ADMIN_DEV

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


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

API : begintran,c
...
OK
API : create,c,dm_acl
...
450xxxxxxxxxxxx23
API : set,c,l,object_name
TEST ACL HUO World Write
...
OK
API : set,c,l,owner_name
dm_dbo
...
OK
API : set,c,l,r_is_internal
FALSE
...
OK


API : set,c,l,globally_managed
FALSE
...
OK
API : set,c,l,acl_class
0
...
OK
API : set,c,l,description
All Users have Write Access
...
OK
API : grant,c,l,dm_world,AccessPermit,,6
...
OK
API : grant,c,l,dm_owner,AccessPermit,,7
...
OK
API : grant,c,l,mysuperuser,AccessPermit,,7
...
OK
API : save,c,l
...
OK
API : commit,c
...
OK

#
# Others instructions:
#
#set,c,l,r_accessor_permit[0]
#3
#set,c,l,r_accessor_xpermit[0]
#1048576
#set,c,l,r_accessor_permit[1]
#1
#set,c,l,r_accessor_xpermit[1]
#3

#append,c,l,r_accessor_name
#docu
#append,c,l,r_is_group
#TRUE
#append,c,l,r_accessor_permit
#7
#append,c,l,r_accessor_xpermit
#0

#append,c,l,r_accessor_name
#my-grp-wkf
#append,c,l,r_is_group
#TRUE
#append,c,l,r_accessor_permit
#6
#append,c,l,r_accessor_xpermit
#0

#append,c,l,r_accessor_name
#my_all_users
#append,c,l,r_is_group
#TRUE
#append,c,l,r_accessor_permit
#6
#append,c,l,r_accessor_xpermit
#0

API : dump,c,450xxxxxxxxxxxx23
...
USER ATTRIBUTES

  object_name                : TEST ACL HUO World Write
  description                : All Users have Write Access
  owner_name                 : MYDOCBASEDEV
  globally_managed           : F
  acl_class                  : 0

SYSTEM ATTRIBUTES

  r_object_id                : 450xxxxxxxxxxxx23
  r_is_internal              : F
  r_accessor_name         [0]: dm_world
                          [1]: dm_owner
                          [2]: mysuperuser
  r_accessor_permit       [0]: 6
                          [1]: 7
                          [2]: 7
  r_accessor_xpermit      [0]: 0
                          [1]: 0
                          [2]: 0
  r_is_group              [0]: F
                          [1]: F
                          [2]: F
  r_has_events               : F
  r_permit_type           [0]: 0
                          [1]: 0
                          [2]: 0
  r_application_permit    [0]:
                          [1]:
                          [2]:
  r_template_id              : 0000000000000000
  r_alias_set_id             : 0000000000000000

APPLICATION ATTRIBUTES

INTERNAL ATTRIBUTES

  i_has_required_groups      : F
  i_has_required_group_set   : F
  i_has_access_restrictions  : F
  i_partition                : 0
  i_is_replica               : F
  i_vstamp                   : 0

• Modification of previous ACL via API

API : retrieve,c,dm_acl where object_name = 'TEST ACL HUO World Write'
...
450xxxxxxxxxxxx23
API : grant,c,l,dm_world,AccessPermit,,1
...
OK
API : save,c,l
...
OK

#
# Others instructions:
#
#grant,c,l,dm_world,AccessPermit,,1
#grant,c,l,my_grp1,AccessPermit,,7
#grant,c,l,my_grp2,AccessPermit,,5
#revoke,c,l,my_grp1,AccessPermit,,7
#revoke,c,l,dm_world,ExtendedPermit,,EXECUTE_PROC
#grant,c,l,my_grp1,ExtendedPermit,,CHANGE_LOCATION
#grant,c,l,my_grp2,ExtendedPermit,,CHANGE_PERMIT

#dump,c,r_object_id_ofACL
#grant,c,l,dm_world_GROUPE,PERMIT(1à7),XPERMIT(chiffre)
#OR
#grant,c,l,dm_world_GROUPE,PERMIT(1à7),XPERMIT(nom_de_xpermit)

API : dump,c,450xxxxxxxxxxxx23
...
USER ATTRIBUTES

  object_name                : TEST ACL HUO World Write
  description                : All Users have Write Access
  owner_name                 : MYDOCBASEDEV
  globally_managed           : F
  acl_class                  : 0

SYSTEM ATTRIBUTES

  r_object_id                : 450xxxxxxxxxxxx23
  r_is_internal              : F
  r_accessor_name         [0]: dm_world
                          [1]: dm_owner
                          [2]: mysuperuser
  r_accessor_permit       [0]: 1		--- modified ---
                          [1]: 7
                          [2]: 7
  r_accessor_xpermit      [0]: 0
                          [1]: 0
                          [2]: 0
  r_is_group              [0]: F
                          [1]: F
                          [2]: F
  r_has_events               : F
  r_permit_type           [0]: 0
                          [1]: 0
                          [2]: 0
  r_application_permit    [0]:
                          [1]:
                          [2]:
  r_template_id              : 0000000000000000
  r_alias_set_id             : 0000000000000000

APPLICATION ATTRIBUTES

INTERNAL ATTRIBUTES

  i_has_required_groups      : F
  i_has_required_group_set   : F
  i_has_access_restrictions  : F
  i_partition                : 0
  i_is_replica               : F
  i_vstamp                   : 2

• Deleting of ACL via API

API : retrieve,c,dm_acl where object_name = 'TEST ACL HUO World Write'
...
450xxxxxxxxxxxx23
API : destroy,c,l
...
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.

DQL : SELECT * FROM my_huo_document  WHERE r_object_id = '09XXXXXXX9b' AND CHECK_ACL('acl_domain', 'acl_name', 'myuser001');

# BROWSE, READ, RELATE, VERSION, WRITE, DELETE
DQL : SELECT FOR READ *  FROM my_huo_document  WHERE r_object_id = '09XXXXXXX9b' AND CHECK_ACL('acl_domain', 'acl_name', 'myuser001');
DQL : SELECT FOR DELETE *  FROM my_huo_document  WHERE r_object_id = '09XXXXXXX9b' AND CHECK_ACL('acl_domain', 'acl_name', 'myuser001');

# 1 for None, 2 for Browse, 3 for Read, 4 for Relate, 5 for Version, 6 for Write, 7 for Delete,
DQL : EXECUTE check_security WITH user_name='myuser001', level=3,object_list='09XXXXXXX9b';
DQL : EXECUTE check_security WITH user_name='myuser001', level=7,object_list='09XXXXXXX9b';


# TESTS : access via which group ?
### huo001 : OK
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';
# 120XXXXXXXXXXXX18	access_all
# 120XXXXXXXXXXXXc5	access-delete-group
SELECT * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXX1cc' AND CHECK_ACL('acl_domain', 'acl_name', 'huo001');
SELECT FOR BROWSE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXX1cc' AND CHECK_ACL('acl_domain', 'acl_name', 'huo001');
# 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

############### With USER user_delete_right
# This APi instruction allows to know the rights of a connected user "user_delete_right"
API&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; get,c,090XXXXXXXXXXX,_permit
...
7

SELECT FOR DELETE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX';
# 090XXXXXXXXXXX	My_doc_title.pdf

SELECT FOR VERSION * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'my_super_user');
# nothing
SELECT FOR READ * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'my_super_user');
# 090XXXXXXXXXXX	My_doc_title.pdf

############### With USER my_super_user
# This APi instruction allows to know the rights of a connected user "user_delete_right"
API&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; get,c,090XXXXXXXXXXX,_permit
...
5

SELECT FOR VERSION * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX';
# 090XXXXXXXXXXX	My_doc_title.pdf
SELECT FOR WRITE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX';
# nothing

SELECT FOR DELETE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'user_delete_right');
# nothing
SELECT FOR WRITE * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'user_delete_right');
# nothing
SELECT FOR VERSION * FROM my_huo_document  WHERE r_object_id = '090XXXXXXXXXXX' AND CHECK_ACL('acl_domain', 'acl_name', 'user_delete_right');
# 090XXXXXXXXXXX	My_doc_title.pdf

 

 


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


• Creation of Alias Set (dm_alias_set) via API

API : begintran,c
...
OK
API : create,c,dm_alias_set
...
660xxxxxxd3e
API : set,c,l,object_name
TEST AS HUO Simple
...
OK
API : set,c,l,owner_name
dm_dbo
...
OK
API : set,c,l,object_description
Desc 4 TEST AS HUO Simple
...
OK
API : append,c,l,alias_name
AS4MyGroup
...
OK
API : append,c,l,alias_value
huo_grp_all_users
...
OK
API : append,c,l,alias_category
2
...
OK
API : append,c,l,alias_usr_category
-1
...
OK
API : append,c,l,alias_description
Entry for a group alias (huo_grp_all_users)
...
OK
API : append,c,l,alias_name
AS4SuperUser
...
OK
API : append,c,l,alias_value
myuser001
...
OK
API : append,c,l,alias_category
1
...
OK
API : append,c,l,alias_usr_category
-1
...
OK
API : append,c,l,alias_description
Entry for a user alias (myuser001)
...
OK
API : save,c,l
...
OK
API : commit,c
...
OK


API : dump,c,660xxxxxxd3e
...
USER ATTRIBUTES
  owner_name                 : dm_dbo
  object_name                : TEST AS HUO Simple
  object_description         : Desc 4 TEST AS HUO Simple
  alias_name              [0]: AS4MyGroup
                          [1]: AS4SuperUser
  alias_value             [0]: huo_grp_all_users
                          [1]: myuser001
  alias_category          [0]: 2
                          [1]: 1
  alias_usr_category      [0]: -1
                          [1]: -1
  alias_description       [0]: Entry for a group alias (huo_grp_all_users)
                          [1]: Entry for a user alias (myuser001)

SYSTEM ATTRIBUTES
  r_object_id                : 660xxxxxxd3e

APPLICATION ATTRIBUTES

INTERNAL ATTRIBUTES
  i_is_replica               : F
  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

API : begintran,c
...
OK
API : create,c,dm_acl
...
450xxxxxxx94f
API : set,c,l,object_name
TEST PST HUO Simple
...
OK
API : set,c,l,owner_name
dm_dbo
...
OK
API : set,c,l,acl_class
1
...
OK
API : set,c,l,description
Desc 4 TEST PST HUO Simple
...
OK
API : grant,c,l,dm_world,AccessPermit,,3
...
OK
API : grant,c,l,dm_owner,AccessPermit,,3
...
OK
API : grant,c,l,%AS4MyGroup,AccessPermit,,6
...
OK
API : grant,c,l,%AS4SuperUser,AccessPermit,,7
...
OK
API : save,c,l
...
OK
API : commit,c
...
OK

API : dump,c,450xxxxxxx94f
...
USER ATTRIBUTES
  object_name                : TEST PST HUO Simple
  description                : Desc 4 TEST PST HUO Simple
  owner_name                 : MYDOCBASEDEV
  globally_managed           : F
  acl_class                  : 1

SYSTEM ATTRIBUTES
  r_object_id                : 450xxxxxxx94f
  r_is_internal              : F
  r_accessor_name         [0]: dm_world
                          [1]: dm_owner
                          [2]: %AS4MyGroup
                          [3]: %AS4SuperUser
  r_accessor_permit       [0]: 3
                          [1]: 3
                          [2]: 6
                          [3]: 7
  r_accessor_xpermit      [0]: 0
                          [1]: 0
                          [2]: 0
                          [3]: 0
  r_is_group              [0]: F
                          [1]: F
                          [2]: F
                          [3]: F
  r_has_events               : F
  r_permit_type           [0]: 0
                          [1]: 0
                          [2]: 0
                          [3]: 0
  r_application_permit    [0]:
                          [1]:
                          [2]:
                          [3]:
  r_template_id              : 0000000000000000
  r_alias_set_id             : 0000000000000000

APPLICATION ATTRIBUTES

INTERNAL ATTRIBUTES
  i_has_required_groups      : F
  i_has_required_group_set   : F
  i_has_access_restrictions  : F
  i_partition                : 0
  i_is_replica               : F
  i_vstamp                   : 0

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

API : begintran,c
API : retrieve,c,dm_acl where r_object_id='450xxxxxxx4c’
API : revoke,c,l,%AS4MyGroup,AccessPermit,,6
API : revoke,c,l,%AS4SuperUser,AccessPermit,,7
API : save,c,l
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.

API : begintran,c
...
OK
API : create,c,dm_document
...
090xxxxxx321
API : set,c,l,object_name
Test DOC HUO WITH AS AND PST
...
OK
API : set,c,l,a_content_type
pdf
...
OK
API : setfile,c,l,C:\temp\test.pdf
...
OK
API : link,c,l,'/Temp'
...
OK
API : set,c,l,r_alias_set_id
660xxxxxxd3e
...
OK
API : set,c,l,acl_domain
dm_dbo
...
OK
API : set,c,l,acl_name
TEST PST HUO Simple
...
OK
API : save,c,l
...
OK
API : commit,c
...
OK

API : dump,c,090xxxxxx321
...
USER ATTRIBUTES
  object_name                : Test DOC HUO WITH AS AND PST
  acl_domain                 : MYDOCBASEDEV
  acl_name                   : dm_450xxxxxxx94_xxxxd3e

SYSTEM ATTRIBUTES
  r_object_id                : 090xxxxxx321
  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.

API : dump,c,450xxxxx950
...
USER ATTRIBUTES
  object_name                : dm_450xxxxxxx94_xxxxd3e
  description                : dm_450xxxxxxx94_xxxxd3e
  owner_name                 : MYDOCBASEDEV
  globally_managed           : F
  acl_class                  : 2

SYSTEM ATTRIBUTES
  r_object_id                : 450xxxxx950
  r_is_internal              : T
  r_accessor_name         [0]: dm_world
                          [1]: dm_owner
                          [2]: huo_grp_all_users
                          [3]: myuser001
  r_accessor_permit       [0]: 3
                          [1]: 3
                          [2]: 6
                          [3]: 7
  r_accessor_xpermit      [0]: 0
                          [1]: 0
                          [2]: 0
                          [3]: 0
  r_is_group              [0]: F
                          [1]: F
                          [2]: F
                          [3]: F
  r_has_events               : F
  r_permit_type           [0]: 0
                          [1]: 0
                          [2]: 0
                          [3]: 0
  r_application_permit    [0]:
                          [1]:
                          [2]:
                          [3]:
  r_template_id              : 450xxxxxxx94f
  r_alias_set_id             : 660xxxxxxd3e

APPLICATION ATTRIBUTES

INTERNAL ATTRIBUTES
  i_has_required_groups      : F
  i_has_required_group_set   : F
  i_has_access_restrictions  : F
  i_partition                : 0
  i_is_replica               : F
  i_vstamp                   : 0

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

## Give DELETE permission to super user on TEMPLATE ACL (PST) in order to delete the elements
API : retrieve,c,dm_acl where object_name = 'TEST PST HUO Simple'
...
450xxxxxxx94f
API : grant,c,l,mysuper001,AccessPermit,,7
...
OK
API : save,c,l
...
OK

API : retrieve,c,dm_document where object_name like 'Test DOC HUO WITH AS AND PST';
...
090xxxxxx321
API : destroy,c,l
...
OK
API : retrieve,c,dm_acl where object_name = 'dm_450xxxxxxx94_xxxxd3e'
...
450xxxxx950
API : destroy,c,l
...
OK
API : retrieve,c,dm_acl where object_name = 'TEST PST HUO Simple'
...
450xxxxxxx94f
API : destroy,c,l
...
OK
API : retrieve,c,dm_alias_set where object_name = 'TEST AS HUO Simple'
...
660xxxxxxd3e
API : destroy,c,l
...
OK

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


• Link a document to other folder:

API : retrieve,c,my_custom_type where r_object_id = '090xxxxxxxx6df'
...
090xxxxxxxx6df
API : set,c,l,my_attr1,0
...
OK
API : set,c,l,my_attr2,1
...
OK
API : set,c,l,my_attr3,1
...
OK
API : link,c,l,'/MYCABINET/My Folder1, Fondation/SubFolder2 of Folder1'
...
OK
API : save,c,l
...
OK

DQL : UPDATE my_custom_type OBJECTS LINK '/MYCABINET/My Folder1, Fondation/SubFolder2 of Folder1' WHERE r_object_id = '090xxxxxxxx6df'

DQL : select r_object_id, count(i_folder_id) from my_custom_type group by r_object_id having count(i_folder_id)&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;1 enable (ROW_BASED) ;

090xxxxxxxx6df
i_folder_id = Parent Folder IDs - REPEATING of ID
i_cabinet_id = Primary Cabinet - ID

i_folder_id[0]: 0b0xxxxxx16 = /MYCABINET/My Folder1, Fondation/SubFolder1 of Folder1
i_folder_id[1]: 0b0xxxxxxaa = /MYCABINET/My Folder1, Fondation/SubFolder2 of Folder1
i_cabinet_id : 0c0xxxxxx02 = /MYCABINET

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


• Values mapping of custom type:

DQL : select label_text, map_display_string, map_data_string from dm_nls_dd_info
where parent_id in (select r_object_id from dm_aggr_domain where type_name='my_subtype_document' and any attr_name='doc_type')
#and any map_display_string like '____-%'
;
...
select * from dm_nls_dd_info where parent_id in (select r_object_id from dm_aggr_domain where type_name='my_subtype_document');
...

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

DQL : select resync_needed  From dmi_dd_attr_info Where type_name=’type’ AND attr_name=’attr’;

Exemple: Select resync_needed  From dmi_dd_attr_info Where type_name='my_subtype_document' AND attr_name='type_doc';
0 = no needed
1 = needed

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


• Get informations concerning types (supertype…):

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')
;
...
2exxxxxx105	dm_sysobject	dm_sysobject
2exxxxx0129	dm_document	dm_document,dm_sysobject
2exxxxx4500	my_company_document	dm_sysobject,my_company_document,dm_document
2exxxxx4501	my_client_document	my_company_document,my_client_document,dm_document,dm_sysobject
...

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

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');
...
030xxxxxx129	dm_document	dm_sysobject	76	76
030xxxxx0105	dm_sysobject		76	0
030xxxxxx308	dmi_030xxxxxx308	6	0
030xxxxx025a	my_client_document	my_company_document	123	91
030xxxxx0259	my_company_document	dm_document	91	76
...

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


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

DQL : select i_attr_def from dmc_aspect_type   where object_name = 'my_huo_aspect'
;
...
dmi_030xxxxxx0b
...

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

DQL : select i_attr_def from dmc_aspect_type   where object_name = 'my_huo_aspect2'
;
...
dmi_03xxxxxx30c
...
DQL : describe dmi_03xxxxxx30c_r;
....
i_partition	Integer
i_position	Integer
r_object_id	Char(16)
my_repeating_field1	Integer
my_repeating_field2	Char(255)
...
DQL : describe dmi_03xxxxxx30c_s;
....
i_partition	Integer
r_object_id	Char(16)
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.

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')
;
...
2exxxxxx105	dm_sysobject	dm_sysobject
2exxxxx0129	dm_document	dm_document,dm_sysobject
2exxxxx4500	my_company_document	dm_sysobject,my_company_document,dm_document
2exxxxx4501	my_client_document	my_company_document,my_client_document,dm_document,dm_sysobject
2exxxxxx901	dmi_030xxxxxx30a	dmi_030xxxxxx30a
2exxxxxxd00	dmi_0302xxxxx30b	dmi_0302xxxxx30b
...

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


• Example for a SBO “MySecurityPolicyServiceBO” sur GLOBALR:

# Last versions of Module : Definition of java class
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 ;
0bxxxxxxx542	com.huo.lu.business.sbo.ecm.security.IMySecurityPolicyService	23/11/2011 09:05:58	17/03/2017 14:38:05	1.0,CURRENT


# Last versions of JARS (Java Method):
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 ;
090xxxxx8775	security-policy-impl	17/03/2017 14:38:02	17/03/2017 14:38:02	1.130,CURRENT
090xxxxx8777	security-policy	17/03/2017 14:38:02	17/03/2017 14:38:02	1.130,CURRENT

# Last versions of DAR:
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 ;
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:

# Last versions of Module : Definition of java class
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 ;
0bxxxxxxx9a9	my_huo_document	21/06/2010 09:30:48	21/03/2017 11:34:24	CURRENT,1.0

# Last versions of JARS (Java Method):
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 ;
-

# Last versions of DAR:
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 ;
080xxxxx484	MyHuoBO	21/03/2017 11:34:17	21/03/2017 11:34:17	1.372,CURRENT



# Last versions of TBO:
SELECT   * FROM  dm_document WHERE  folder('/System/Modules/TBO/my_custom_document') ORDER BY   r_creation_date DESC;
090XXXXb11	my_custom_document
090XXXXb0f	my_custom_document-Impl

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

# Last versions of Java Methods:
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;
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
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
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
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
...

# Last versions of JARS (Java Method):
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 ;
090xxxxx35ba	WorkflowMethod	10/04/2014 18:02:58	10/04/2014 18:02:59	CURRENT,1.35
090xxxxxx5b4	FinishMethod	10/04/2014 18:02:57	10/04/2014 18:02:58	1.35,CURRENT
090xxxxxx5b6	InclassableMethod	10/04/2014 18:02:58	10/04/2014 18:02:58	1.35,CURRENT
090xxxxx35b8	VersioningMethod	10/04/2014 18:02:58	10/04/2014 18:02:58	CURRENT,1.35
...

# Last versions of Module : Definition of java class
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 ;
0b0xxxxxx93a	com.huo.lu.myged.persistence.documentum.bof.FinishMethod	07/01/2013 17:27:24	10/04/2014 18:03:00	1.0,CURRENT
0b01xxxx093b	com.huo.lu.myged.persistence.documentum.bof.InclassableMethod	07/01/2013 17:27:24	10/04/2014 18:03:00	1.0,CURRENT
0b0xxxxx093e	com.huo.lu.myged.persistence.documentum.bof.VersioningMethod	07/01/2013 17:27:25	10/04/2014 18:03:00	1.0,CURRENT
0b01xxxx0941	com.huo.lu.myged.persistence.documentum.bof.WorkflowMethod	07/01/2013 17:27:25	10/04/2014 18:03:00	1.0,CURRENT

# Last versions of DAR:
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 ;
080xxxxxx5bc	MyDematClient	10/04/2014 18:02:59	10/04/2014 18:02:59	CURRENT,1.30
...

• Example for Custom Job:

# Last versions of Module : Definition of java class
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 ;
0b0xxxxx2833	HUO_CustomExample1Module	13/10/2016 13:28:03	13/10/2016 15:43:20	CURRENT,1.0

# Last versions of JARS (Java Method):
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 ;
090xxxxx29a3	HUO_CustomExample1MethodJob	13/10/2016 14:49:32	13/10/2016 15:43:19	1.1
09xxxxxx2a7c	HUO_CustomExample1MethodJob	13/10/2016 15:43:19	13/10/2016 15:43:19	1.2,CURRENT
090xxxxx283d	HUO_CustomExample1MethodJob	13/10/2016 13:28:04	13/10/2016 14:49:32	1.0

# Last versions of DAR:
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 ;
080xxxxx2a7e	ECMCustomExample1MethodJob	13/10/2016 15:43:19	13/10/2016 15:43:20	CURRENT,1.2
080xxxxxx9a5	ECMCustomExample1MethodJob	13/10/2016 14:49:33	13/10/2016 14:49:33	1.1
080xxxxxx848	ECMCustomExample1MethodJob	13/10/2016 13:28:09	13/10/2016 13:28:09	1.0

# Last versions of JARS (Java Method):
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;
10xxxxxxx851	HUO_CustomExample1Method	13/10/2016 13:28:08	13/10/2016 15:43:18	1	java	1	HUO_CustomExample1Module	1.0,CURRENT

# Last versions of Jobs:
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;
080xxxxxxx49	HUO_CustomExample1Job	13/10/2016 14:49:32	13/10/2016 15:43:18	1.0,CURRENT

• Example for Aspect:

# Last versions of Module : Definition of java class
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 ;
0b0xxxxxxfc3	aspect_myaspect1	08/12/2016 10:51:06	09/12/2016 09:41:29	CURRENT,1.0

# Last versions of JARS (Java Method):
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 ;
090xxxxx8efd0	MyAspect1-impl	08/12/2016 10:51:23	09/12/2016 09:41:28	1.0
090xxxxx906f9	MyAspect1-impl	09/12/2016 09:41:28	09/12/2016 09:41:28	CURRENT,1.1
0902xxxx906f7	MyAspect1	09/12/2016 09:41:24	09/12/2016 09:41:27	CURRENT,1.1
090xxxx48efcf	MyAspect1	08/12/2016 10:51:22	09/12/2016 09:41:24	1.0

# Last versions of DAR:
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 ;
080xxxxxx06fb	ECMMyAspect1	09/12/2016 09:41:29	09/12/2016 09:41:29	CURRENT,1.1
080xxxxx8efda	ECMMyAspect1	08/12/2016 10:51:24	08/12/2016 10:51:24	1.0

# Last versions of JARS (Java Method):
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;
-

# Last versions of Aspect:
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%';
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:

EXECUTE show_sessions;
...
EXECUTE list_sessions;
...
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…):

API : begintran,c
...
OK
API : retrieve,c,dm_job where object_name = 'HuO_MyChangeOwner'
...
080xxxxxxxx45
API : remove,c,l,method_arguments
...
OK
API : remove,c,l,method_arguments
...
OK
API : append,c,l,method_arguments
-grace_period 1
...
OK
API : append,c,l,method_arguments
-condition my_id IN (SELECT my_id FROM dm_dbo.my_table WHERE my_condition_1 IN (12,34,56))
...
OK
API : save,c,l
...
OK
API : commit,c
...
OK
API : dump,c,l
...
USER ATTRIBUTES

  object_name                : HuO_MyChangeOwner
  title                      : HuO
  subject                    :
  authors                  []:
  keywords                 []:
  resolution_label           :
  owner_name                 : myuserprd
  owner_permit               : 7
  group_name                 : docu
  group_permit               : 6
  world_permit               : 3
  log_entry                  :
  acl_domain                 : HuOCOMPANYPROD
  acl_name                   : dm_4502xxxxxxxxx0102
  language_code              :
  method_name                : HuO_MyChangeOwner
  method_arguments        [0]: -grace_period 1
                          [1]: -condition my_id IN (SELECT my_id FROM dm_dbo.my_table WHERE my_condition_1 IN (12,34,56))
  pass_standard_arguments    : T
  start_date                 : 21/07/2010 00:00:00
  expiration_date            : 21/07/2031 00:00:00
  max_iterations             : 0
  run_interval               : 1
  run_mode                   : 2
  is_inactive                : F
  inactivate_after_failure   : F
  target_server              :
  method_trace_level         : 10
  run_now                    : F
  method_data              []: 

SYSTEM ATTRIBUTES

  r_object_id                : 080xxxxxxxx45
  r_object_type              : dm_job
  r_version_label         [0]: 1.0
                          [1]: CURRENT
....


——————— 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.
destroy,session,object_id[,force_flag]

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:
prune,session,object_id[,keepSLabel]
...
prune,c,{r_object_id_current},FALSE

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:

DQL : select * from dm_document where r_object_id = '090XXXXXXXXXXX'  ENABLE(GENERATE_SQL_ONLY);

-- generate_sql
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 ————————–




####### MY_COMPANY_UAT - huo_client_document : filesstores used
select DISTINCT a_storage_type from huo_client_document;

## Number of contents in each STORE
select s.name, count(*) as cpt from huo_client_document (all) d, dmr_content c, dm_store s where any c.parent_id=d.r_object_id and c.storage_id=s.r_object_id group by s.name;
# myas_filestore		191
# myas_filestore_01	2
# centera_store_no_retention	92
# filestore_01	136341
# filestore_02	283184
# filestore_03	41904
# filestore_04	34063
# filestore_para	2288
# thumbnail_store_01	229313



## STORE
select r_object_id, name, current_use, full_current_use, base_url from dm_store WHERE name IN (select DISTINCT a_storage_type from huo_client_document);
# 6d0XXXXXX13c	centera_store_no_retention	0	0	
# 280XXXXXX15b	myas_filestore_01			133837	133837	
# 280XXXXXX16f	filestore_04			-1434144515	93055135997	
# 280XXXXXX132	filestore_para			223420137	223420137	
# 280XXXXXX166	filestore_03			-1997491449	96786756359	
# 280XXXXXX15c	myas_filestore			1934668370	1934668370	
# 280XXXXXX100	filestore_01			-1173273693	518517769123	
# 280XXXXXX101	thumbnail_store_01		-111919445	12772982443	http://MYDCTMAPP001:8081/thumbsrv/getThumbnail?
# 280XXXXXX151	filestore_02			-1862826846	822770893986	
#
# 
select r_object_id, name, current_use, full_current_use, base_url, root from dm_filestore WHERE name IN (select DISTINCT a_storage_type from huo_client_document);
# 280XXXXXX15b	myas_filestore_01		133837	133837		myas_storage_01
# 280XXXXXX16f	filestore_04		-1433775093	93055505419		storage_04
# 280XXXXXX132	filestore_para		223420137	223420137		storage_para
# 280XXXXXX166	filestore_03		-1997491449	96786756359		storage_03
# 280XXXXXX15c	myas_filestore		1934668370	1934668370		myas_storage
# 280XXXXXX100	filestore_01		-1173273255	518517769561		storage_01
# 280XXXXXX101	thumbnail_store_01	-111919445	12772982443	http://MYDCTMAPP001:8081/thumbsrv/getThumbnail?	thumbnail_storage_01
# 280XXXXXX151	filestore_02		-1862826846	822770893986		storage_02
#
#
select r_object_id, name, current_use, full_current_use, base_url, a_storage_params from dm_ca_store WHERE name IN (select DISTINCT a_storage_type from huo_client_document);
# 6d0XXXXXX13c	centera_store_no_retention	0	0		huo1ecs01.huo.myserver.com,huo2ecs01.huo.myserver.com.....



## LOCATION of STORE 
select r_object_id, object_name, file_system_path, path_type, r_object_type from dm_location order by 2;
#
select r_object_id, object_name, file_system_path, path_type, r_object_type from dm_location where object_name IN (select DISTINCT root from dm_filestore WHERE name IN (select DISTINCT a_storage_type from huo_client_document));
# 3aXXXXXXXbef	myas_storage_01		\\myasfiles123\d$\TEST\Data\MY_COMPANY_UAT\myas_storage_01	directory	dm_location
# 3aXXXXXXX861	storage_04			\\myfiles456\d$\DATA\MY_COMPANY_UAT\content_storage_04	directory	dm_location
# 3aXXXXXXX22d	storage_03			\\myfiles456\f$\DATA\MY_COMPANY_UAT\content_storage_03	directory	dm_location
# 3aXXXXXXX13f	storage_01			\\myfiles456\d$\DATA\MY_COMPANY_UAT\content_storage_01	directory	dm_location
# 3aXXXXXXX346	storage_para			\\myfiles456\e$\DATA\ATAs							directory	dm_location
# 3aXXXXXXXbf0	myas_storage			\\myasfiles123\d$\TEST\Data\MY_COMPANY_UAT\myas_storage		directory	dm_location
# 3aXXXXXXXf4d	storage_02			\\myfiles456\e$\DATA\MY_COMPANY_UAT\content_storage_02	directory	dm_location
# 3aXXXXXXX15a	thumbnail_storage_01	\\myfiles456\d$\DATA\MY_COMPANY_UAT\thumbnail_storage_01	directory	dm_location




## Some documents CONTENT on a specific STORE
select c.r_object_id from huo_client_document (all) d, dmr_content c, dm_store s where any c.parent_id=d.r_object_id and c.storage_id=s.r_object_id AND s.name='filestore_01' enable(RETURN_TOP 50);
# 060XXXXXXXXd19
# 060XXXXXXXXd1a

….TO BE CONTINUED