DQL

 

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)