Documentum : Repeating and Single Value Attributes in Database, Tables, Views, Aspects, i_position

Hello,

This post is relating to repeating and single attributes in underlying database. Documentum objects support 2 types of attributes:

• Single Value Attributes
  Single Value properties are properties that can hold a single value at a time like the attributes r_object_id, object_name,…etc.
• Repeating Attributes
  Repeating Attributes are properties that can hold multiple values like the attributes Keywords, i_folder_id, r_aspect_name…etc.

TABLES
All objects/properties/attributes are saved in underlying database. Depending of object in which the attribute is defined and its type (single/repeating), the attribute is stored either in table “object_type_s” (or single) or “object_type_r” (for repeating). For example, the attributes defined in the dm_sysobject type are stored in the tables dm_sysobject_s and dm_sysobject_r.
 

If my_huo_object has two custom single single_attr_1 and single_attr_2 and 2 repeating value attributes repeat_attr_1 and repeat_attr_2, then:

• the my_huo_object_s table will be the columns single_attr_1 and single_attr_2
• the my_huo_object_r table will be the columns repeat_attr_1 and repeat_attr_2

So, each single value attribute has single column in the “_s” table. However, the repeating value attribute with others important colmuns as i_position which determines the position of the value in repeating attribute to which it belongs.
For our previous example, the my_huo_object_r table will be the columns repeat_attr_1, repeat_attr_2 and i_postion.
 

In this example, the custom accelera_doc object type has the supertype, dm_document. Therefore, the following total set of database tables are used to store the properties of accelera_doc objects:

 
VIEWS
Concerning the views, each object type has 4 views named:

• _sv and _sp for all Single value attributes in conjunction with all super types
• _rv and _rp for all repeating value attributes in conjunction with all super types

The two _sp and _rp views are used by DQL and the two _sv and _rv views are used by the Object Manager. So, in case of requiring to query the table rather than the object use the _sp, _rp views rather than making your own joins between _s or _r tables up the inheritance hierarchy.

For example, the type dm_document has four views: dm_document_sp, dm_document_sv, dm_document_rp and dm_document_rv. If my_huo_object extends dm_document, its views (my_huo_object_sp, my_huo_object_sv, my_huo_object_rp and my_huo_object_rv) will have all the single/repeating attributes of dm_sysobject, dm_document and my_huo_object. Note : dm_document doesn’t have any single attribute or repeating attribute.

 
CALCULATE REPEATING VALUE POSITION
Some notes concerning th calculate of repeating’s index:

• i_position uses negative numbers for indexing. so, -1 is the first element, -2 is the second, and so on.
  The expression “(i_position*-1)-1 as index” and “-(i_position+1) as sameindex” are equivalent.
   
• Example 1: for standard object

  // Retrieve document id and index position where repeating attribute 'r_aspect_name' contains 'aspect_java_lu'
  
  # For repeating attribute
  describe dm_sysobject_r;
  r_object_id	Char(16)	
  i_position	Integer	
  i_partition	Integer	
  r_aspect_name	Char(64)	
  i_folder_id	Char(16)	
  authors	Char(48)	
  a_effective_date	Time/Date	
  a_effective_flag	Char(8)	
  a_effective_label	Char(32)	
  a_expiration_date	Time/Date	
  a_extended_properties	Char(32)	
  a_publish_formats	Char(32)	
  keywords	Char(48)	
  r_component_label	Char(32)	
  r_composite_id	Char(16)	
  r_composite_label	Char(32)	
  r_order_no	Integer	
  r_property_bag	Char(2048)	
  r_version_label	Char(32)	
  
  # For single attribute
  describe dm_sysobject_s;
  r_object_id	Char(16)	
  i_position	Integer	
  i_partition	Integer	
  acl_domain	Char(255)	
  acl_name	Char(32)	
  a_application_type	Char(32)	
  ...
  
  select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex
  from dm_document
  where r_aspect_name = 'aspect_java_lu'
  enable (row_based);
  OR
  select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex
  from dm_sysobject
  where r_aspect_name = 'aspect_java_lu'
  enable (row_based);
  OR
  select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex
  from dm_sysobject_r
  where r_aspect_name = 'aspect_java_lu'
  enable (row_based);
  
  r_object_id		my_aspect		index 	sameindex
  09xxxxxxxxxxxxxa	aspect_java_lu	0	0
  09xxxxxxxxxxxxxb	aspect_java_lu	1	1
  09xxxxxxxxxxxxxc	aspect_java_lu	1	1
  09xxxxxxxxxxxxxd	aspect_java_lu	0	0
  
  

   

• Example 2: for the ASPECT

   Where do Aspects Store Attribute Values?
  The dmc_aspect_type_s and dmc_aspect_type_r tables contains the definitions of the aspect. The i_attr_def column contains the name of the table that holds the attributes and values for the Aspect. 
  select i_attr_def from dmc_aspect_type   where object_name = 'my_aspect';
  i_attr_def = dmi_03xxxxxxxxxxxc
  
  # For repeating attribute
  describe dm_dbo.dmi_03xxxxxxxxxxxc_r;
  r_object_id	Char(16)	
  i_position	Integer	
  i_partition	Integer	
  additionnal_approvers	Char(50)	
  contacts	Char(50)	
  ...
  
  # For single attribute
  describe dm_dbo.dmi_03xxxxxxxxxxxc_s;
  r_object_id	Char(16)	
  i_position	Integer	
  i_partition	Integer	
  afs_request_num	Char(12)	
  request_status	Char(25)	
  approved_rejected_date	Time/Date	
  ...	
  
  
  #
  select r_object_id, contacts as my_contact, (i_position*-1)-1 as index, -(i_position+1) as sameindex
  from dm_dbo.dmi_03xxxxxxxxxxxc_r
  where contacts like 'Huseyin%'
  order by r_object_id, i_position desc;
  
  r_object_id	my_contact	index	sameindex
  09xxxxxxxxxxxxx9	Huseyin OZVEREN	1	1
  09xxxxxxxxxxxxxa	Huseyin Ozveren	3	3
  09xxxxxxxxxxxxxe	Huseyin Me	0	0
  
  

Below, the useful link to the Documentum 6 Object-Relational Model :
https://community.emc.com/servlet/JiveServlet/previewBody/1269-102-1-1396/DocumentumContentServer6ORDiagram.pdf

That’s all!!!

Huseyin OZVEREN