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:
- 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
- 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;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;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;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;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;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;lt;/pre&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;pre&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;lt;/pre&amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; &amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;p&amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;select * from dm_type ;&amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;br /&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;lt;br /&amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;unaudit,c,030xxxxxxxxxf0,dm_save&amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;br /&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;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;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;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