How to change object type

Documentum allows creation of custom types, which defines custom attributes in addition to the inherited ones. Once your custom type is defined and created in the repository, you can create objects - which are instances of that custom type.
If you have created objects of a certain (sub) type, you can still change their type. The easiest way is to use the following DQL:
CHANGE current_type [(ALL)] objects to new_type [update_list] [WHERE qualification]
* ALL - change type of all object versions
* update list - list of updates on objects attributes to perform during change type operation

Obviously current_type must be the supertype or subtype of the new_type. Thus, you can move the object instances through the types hierarchy up and down.
Ok, if you move from a supertype to subtype you get additional attributes that are empty. But what happens when moving from subtype to supertype? The custom attributes' values of the subtype are lost (you can use the update list if you want to copy the values to other attributes, that are not lost).

You should take note of the following constraints on this operation:
1. You can only change the type of objects that are subtypes of dm_sysobject. (If you want to change the object type of not a dm_sysobject, follow the procedure described here: URL).
2. The object's current type and the new type must have the same type identifier (which is the first two characters of the object ID. For example, dm_document has 09).
3. The old and new types can't be at the same level in the type hierarchy. The new type must be a supertype or subtype of the current type.
For example, you have a my_car and its subtypes my_toyota and my_lexus. In order to change an object of my_toyota to type my_lexus, you must do it in 2 steps:
change objects my_ford to my_car;
go;
change objects my_car to my_lexus;
go;

4. You must have DELETE permission on the objects you are changing.

Useful DQL queries

DQL to get all empty (sub)folders in a cabinet:
select * from dm_folder where r_link_cnt=0 and folder('/Temp',descend)

DQL to get list of documents and their folder path:
select distinct d.r_object_id,d.object_name,f.r_folder_path from dm_document d, dm_folder f where any d.i_folder_id=f.r_object_id and r_folder_path is not nullstring enable(ROW_BASED)

DQL to display the supertypes hierarchy brach of the specified type:

select r_supertype from dmi_type_info where r_type_id = (select r_object_id from dm_type where name='my_type')

DQL to get number of modified documents for each month:
select datetostring(r_modify_date,'mm/yyyy'),count(*)from dm_document [WHERE condition] group by datetostring(r_modify_date,'mm/yyyy')

DQL to execute an SQL query:
execute exec_sql with query = 'create or replace my_view (cod) as (select some_id from my_table)'

DQL to get the object type of a document:
select r_object_type from dm_document where r_object_id='092e6adc800001f0'

DQL to get the number of sysobjects for each object type:
select count(*),r_object_type from dm_sysobject group by r_object_type

DQL to create a DB index on a type attribute:
EXECUTE make_index WITH type_name='dmi_workitem',attribute='r_workflow_id'

DQL to see Documentum sessions on current Content Server:
execute show_sessions

DQL to get ids of documents deleted in a time interval:
select * from dm_audittrail where event_name='dm_destroy' where time_stamp > date('date before') and time_stamp < date('date after')

DQL to get the user that deleted a document:
select * from dm_user where r_object_id= (select user_id from dm_audittrail where event_name='dm_destroy' and audited_obj_id='ID OF DELETED OBJECT')

Manage Folders with DQL

Create a folder and link it:

CREATE dm_folder object

set object_name='FolderName',

set owner_name='owner',

set acl_name='ACLName',

set acl_domain='ACLDomain'

link '/Path'

How to find empty folders in the entire docbase:

a)
select r_object_id, object_name, r_object_type, r_folder_path FROM dm_folder f WHERE r_object_id NOT IN (SELECT distinct i_folder_id FROM dm_sysobject WHERE any i_folder_id = f.r_object_id)
b)
select r_object_id, object_name, r_object_type, r_folder_path FROM dm_folder f WHERE r_link_count=0

Get count of documents by custom type for each folder:
select f.r_object_id, f.r_folder_path, d.r_object_type, count(d.r_object_id) FROM dm_folder f, dm_document d WHERE any d.i_folder_id=f.r_object_id group by f.r_object_id, d.r_object_type

How to find empty folders only in a cabinet?

SELECT r_object_id, object_name, r_object_type FROM dm_folder f WHERE r_object_id NOT IN (SELECT distinct i_folder_id FROM dm_sysobject WHERE any i_folder_id = f.r_object_id AND FOLDER(‘/cabinet_name’, DESCEND)) AND FOLDER(‘/cabinet_name’, DESCEND)

Queues Management with DQL

1. FullText Queue

Objects in FullTextIndex queue (requsts for fulltext indexing):

select name,task_state,count(r_object_id),min(date_sent) from dmi_queue_item where name like '%index%' and dequeued_date is nulldate group by name,task_state order by name,task_state

Object types registered for indexing:

select name from dm_type where r_object_id in (select distinct registered_id from dmi_registry where user_name like '%fulltext%')

Resubmit items to FullTextIndex queue:

update dmi_queue_item objects set task_state=' ', set sign_off_user=' ', set message=' ', set dequeued_by=' ', set date_sent=DATE(NOW) where name like 'dm_fulltext_index_user%' and task_state='failed'

Remove items with warnings from FTI queue:

delete dmi_queue_item objects where name like 'dm_fulltext_index_user%' and task_state='warning'

2. Renditions (Content Transformation) queue

Objects in CTS queue (requests for rendition creation):

select * from dmi_queue_item where event='rendition'

Documents which don’t have a rendition:

select * from dm_document where r_object_id not in (select parent_id from dmr_content where rendition>0)

Renditions generated today:

select * from dmr_content where rendition>0 and set_time > DATE(TODAY)

Manage custom types with DQL

I. Create custom type

CREATE TYPE "custom_document"

("booleanAttr" bool REPEATING,

"integerAttr" integer,

"commentAttr" string(10),

"numberAttr" string(20),

"idAttr" id,

"dateAttr" date,

"doubleAttr" double,

"charAttr" CHAR(20))

WITH SUPERTYPE dm_document

publish;

II. Modify custom type

General tab settings

alter type custom_type

set label_text = 'Title' ,

set help_text = 'some hint' ,

set comment_text = 'some comment'

set DEFAULT BUSINESS POLICY='dm_policy.r_object_id' VERSION 'NONE'

SET DEFAULT STORAGE='filestore_01'

Display configuration

a) “Display Configuration” tab (for webtop application)

Get type configuration for webtop application (tabs and position)

select distinct r_object_id, display_config, i_is_replica, i_vstamp, parent_id, scope_class, scope_value from dm_scope_config where r_object_id in (select distinct scope_config from dmi_dd_type_info where type_name='myType') and any scope_value = 'webtop'

Get the position and display mode of the attributes on all tabs

select r_object_id, attribute_display_hint, attribute_name, attribute_source, fixed_display, i_config_identifier, i_is_replica, i_vstamp, object_name from dm_display_config

where r_object_id in (select display_config from dm_scope_config where r_object_id in (select distinct scope_config from dmi_dd_type_info where type_name='type_name')

and any scope_value = 'webtop')

b) Modify display config for custom type

update dm_display_config objects

set attribute_source='custom_type'

set attribute_name[0]='r_object_id'

set attribute_display_hint[0]=1

set attribute_name[1]='attr_name'

set attribute_display_hint[1]=1

where r_object_id = '6bab413080033559'

c) Create a new display configuration (a new tab on properties page)

create dm_display_config object

set "object_name"='newTab'

set "attribute_source"='custom_type'

set "fixed_display"=0

append attribute_name='attr_name'

append attribute_display_hint=1

d) Add the new display config to the application (desktop, webtop, etc.)

UPDATE dm_scope_config OBJECTS

INSERT "display_config"[0] = (select r_object_id from dm_display_config where attribute_source='myType' and object_name='newTab') WHERE r_object_id in (select distinct scope_config from dmi_dd_type_info where type_name='custom_type') and any scope_value='webtop'

e) Remove a display configuration

UPDATE dm_scope_config OBJECTS

REMOVE "display_config"[index]

WHERE r_object_id in (select distinct scope_config from dmi_dd_type_info where type_name='myType') and any scope_value='webtop'

*The index must be known

Security tab

Set default ACL

ALTER TYPE type_name SET DEFAULT ACL acl_name [IN acl_domain]

ALTER TYPE type_name SET DEFAULT ACL NULL

Constraints tab

ALTER TYPE type_name [FOR POLICY policy_id STATE state_name] ADD constraint_specification

a) Primary Key

ALTER TYPE type_name

add PRIMARY KEY ("notnullAttr1")

REPORT 'TEXT'

ENFORCE BY APPLICATION

publish;

ALTER TYPE type_name

drop PRIMARY KEY

publish;

b) Unique Key

ALTER TYPE type_name

ADD UNIQUE ("notnullAttr4")

REPORT 'TEXT'

ENFORCE BY APPLICATION

publish;

ALTER TYPE type_name

DROP UNIQUE ("notnullAttr4")

publish;

c) Foreign Key

ALTER TYPE type_name1

ADD FOREIGN KEY("notnullAttr4") REFERENCES type_name2 ("pk2")

REPORT 'TEXT'

ENFORCE BY APPLICATION

publish;

ALTER TYPE type_name1

DROP FOREIGN KEY("notnullAttr4") REFERENCES type_name2 ("pk2")

publish;

Events tab

Add application events:

ALTER TYPE type_name

APPEND auditable_appevents = 'dm_ev0',

APPEND auditable_appevents = 'dm_ev1'

publish;

Add system events:

ALTER TYPE type_name

APPEND auditable_sysevents = 'dm_sysev0',

APPEND auditable_sysevents = 'dm_sysev1'

publish;

III. Modify custom type attributes settings

General tab

alter type typeName

modify (“rg_contry” (

set label_text='Country Name:',

set help_text = 'The name of the country' ,

set comment_text = 'comment',

set ignore_immutable = TRUE,

set is_hidden = TRUE,

set is_required = TRUE,

set not_null = TRUE,

set not_null_msg = 'L’attributo deve essere valorizzato',

set read_only = TRUE,

DEFAULT='DEFAULT VALUE'

))

Advanced tab

alter type typeName

modify “rg_contry” (

set is_searchable = TRUE,

append allowed_search_ops = 1,

append allowed_search_ops = 2,

append allowed_search_ops = 3,

set format_pattern = 'pattern',

set format_pattern_tag = 1,

set category_name = 'categorie',

set default_search_arg = 'somevalue',

set default_search_op = 3

)

Constraints tab

alter type type_name

modify “rg_contry” (

set ignore_constraints = TRUE,

set val_constraint='expression',

set val_constraint_dep = '????',

set val_constraint_enf = '????',

set val_constraint_msg = 'Error message for constraint violation'

)

ALTER TYPE type_name

MODIFY "pk" (

ADD PRIMARY KEY | UNIQUE KEY | NOT NULL

REPORT 'error message' on violation

ENFORCE BY APPLICATION

)

ALTER TYPE type_name

MODIFY "pk" (

DROP PRIMARY KEY

)

publish;

ALTER TYPE type_name1

MODIFY "pk" (ADD FOREIGN KEY REFERENCES type_name2("pk2")

REPORT 'message_string' ON VIOLATION ENFORCE BY APPLICATION)

ALTER TYPE type_name1

MODIFY "pk" (DROP FOREIGN KEY REFERENCES type_name2("pk2"))

Value Assistance tab

a) Set query

ALTER TYPE type_name

MODIFY “rg_country” (

VALUE ASSISTANCE IS QRY 'select distinct attribute_name from type_name order by 1'

qry attr = attribute_name allow caching is complete)

b) Set list

ALTER TYPE type_name

modify "countrycode" (VALUE ASSISTANCE is LIST ('ONE', 'TWO', 'THREE') IS COMPLETE)

c) Drop

ALTER TYPE type_name MODIFY "countrycode" (DROP VALUE ASSISTANCE)

Value Mapping tab

Add mapping

ALTER TYPE type_name

MODIFY "countrycode" (

MAPPING TABLE (

VALUE='UK'

DISPLAY='United Kingdom'

COMMENT='comment1',

VALUE='US'

DISPLAY='United States'

COMMENT='comment2'

))

publish;

Drop mapping

ALTER TYPE type_name MODIFY "countrycode" (DROP MAPPING TABLE)