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
  view source

  print?

  01	// Retrieve document id and index position where repeating attribute 'r_aspect_name' contains 'aspect_java_lu'

  02

  03	# For repeating attribute

  04	describe dm_sysobject_r;

  05	r_object_id Char(16)

  06	i_position  Integer

  07	i_partition Integer

  08	r_aspect_name   Char(64)

  09	i_folder_id Char(16)

  10	authors Char(48)

  11	a_effective_date    Time/Date

  12	a_effective_flag    Char(8)

  13	a_effective_label   Char(32)

  14	a_expiration_date   Time/Date

  15	a_extended_properties   Char(32)

  16	a_publish_formats   Char(32)

  17	keywords    Char(48)

  18	r_component_label   Char(32)

  19	r_composite_id  Char(16)

  20	r_composite_label   Char(32)

  21	r_order_no  Integer

  22	r_property_bag  Char(2048)

  23	r_version_label Char(32)

  24

  25	# For single attribute

  26	describe dm_sysobject_s;

  27	r_object_id Char(16)

  28	i_position  Integer

  29	i_partition Integer

  30	acl_domain  Char(255)

  31	acl_name    Char(32)

  32	a_application_type  Char(32)

  33

  ...

  34

  35	select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex

  36	from dm_document

  37	where r_aspect_name = 'aspect_java_lu'

  38	enable (row_based);

  39

  OR

  40	select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex

  41	from dm_sysobject

  42	where r_aspect_name = 'aspect_java_lu'

  43	enable (row_based);

  44

  OR

  45	select r_object_id, r_aspect_name as my_aspect, (i_position*-1)-1 as index, -(i_position+1) as sameindex

  46	from dm_sysobject_r

  47	where r_aspect_name = 'aspect_java_lu'

  48	enable (row_based);

  49

  50	r_object_id     my_aspect       index   sameindex

  51	09xxxxxxxxxxxxxa    aspect_java_lu  0   0

  52	09xxxxxxxxxxxxxb    aspect_java_lu  1   1

  53	09xxxxxxxxxxxxxc    aspect_java_lu  1   1

  54	09xxxxxxxxxxxxxd    aspect_java_lu  0   0

   

• Example 2: for the ASPECT
  view source

  print?

  01	Where do Aspects Store Attribute Values?

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

  03	select i_attr_def from dmc_aspect_type   where object_name = 'my_aspect';

  04	i_attr_def = dmi_03xxxxxxxxxxxc

  05

  06	# For repeating attribute

  07	describe dm_dbo.dmi_03xxxxxxxxxxxc_r;

  08	r_object_id Char(16)

  09	i_position  Integer

  10	i_partition Integer

  11	additionnal_approvers   Char(50)

  12	contacts    Char(50)

  13

  ...

  14

  15	# For single attribute

  16	describe dm_dbo.dmi_03xxxxxxxxxxxc_s;

  17	r_object_id Char(16)

  18	i_position  Integer

  19	i_partition Integer

  20	afs_request_num Char(12)

  21	request_status  Char(25)

  22	approved_rejected_date  Time/Date

  23

  ...

  24

  25

  26

  #

  27	select r_object_id, contacts as my_contact, (i_position*-1)-1 as index, -(i_position+1) as sameindex

  28	from dm_dbo.dmi_03xxxxxxxxxxxc_r

  29	where contacts like 'Huseyin%'

  30	order by r_object_id, i_position desc;

  31

  32	r_object_id my_contact  index   sameindex

  33	09xxxxxxxxxxxxx9    Huseyin OZVEREN 1   1

  34	09xxxxxxxxxxxxxa    Huseyin Ozveren 3   3

  35	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