DQL Hints

• SELECT Statement –

1. SELECT user_name, r_object_id, default_folder FROM dm_user

   WHERE user_state=0

this code selects the user_name, the object_id, and the name of the user’s default folder from among active users (indicated by the WHERE clause).

2. To determine the complete path for a folder, use:

SELECT r_object_id, r_folder_path FROM dm_folder WHERE object_name like 'Organisation%'

3. You can show information about all objects with more than 10 versions:

SELECT r_object_id, object_name, owner_name FROM dm_sysobject

WHERE _chronicle_id in (SELECT i_chronicle_id FROM dm_sysobject (ALL)

GROUP BY  i_chronicle_id HAVING count(*)>10)

4. To determine whether a document is part of virtual document set:

SELECT object_name,r_object_id FROM dm_sysobject WHERE r_object_id IN

(SELECT parent_id FROM dmr_containment WHERE component_id = (SELECT i_chronicle_id FROM dm_sysobject WHERE r_object_id = ‘<child-object-id>’))

5. locate checked-out documents in a docbase:

SELECT object_name, title FROM dm_document WHERE (r_lock_owner IS NOT NULLSTRING or r_lock_owner <> ” or r_lock_owner <> ‘ ‘)

• UPDATE Statement –

UPDATE dm_document OBJECT SET title=’some title’ WHERE r_object_id='090000000000000001'

The keyword OBJECT or OBJECTS is required to indicate that you are modifying individual objects rather than the object type itself. The SET clause identifies which columns will be updated, and the WHERE clause identifies which rows will be updated. Beware, though: If you don’t include the WHERE clause in your query, all of the rows in the table will be updated.

• DELETE Statement –

DELETE dm_document OBJECT WHERE owner_name = ‘test_user’ AND FOLDER (‘/Temp’)

• DQL hints

1. RETURN_TOP N

This hint limits the number of rows that a query returns. If you are not interested in seeing all the rows but only the top five (for example), use this code:

SELECT user_name FROM dm_user ENABLE (RETURN_TOP 5)

2. OPTIMIZE_TOP N

This hint directs the database server to return the first n rows a query returns quickly. The remaining rows are returned at the normal speed.

SELECT r_object_id FROM dm_sysobject ENABLE (OPTIMIZE_TOP 4)

3. FORCE_ORDER

The FORCE_ORDER hint controls the order in which the tables referenced in the query’s FROM clause are joined. The tables may be relational database management system tables or object-type tables.

SELECT object_name FROM dm_sysobject ENABLE (FORCE_ORDER)

Below are three DQL queries which will give you information on groups and acls. 1.This query will list the object_name and all users which may access the acl if they are in the group or not: select object_name, r_accessor_name from dm_acl where any r_accessor_name in (select group_name from dm_group) order by r_accessor_name 2. This query will select the object_name and all users/groups which may access the acl for a particular group. select object_name, r_accessor_name from dm_acl where any r_accessor_name in (select group_name from dm_group) and any r_accessor_name = 'docu' 3. This query will select only the object_name of the acl for a particular group: select object_name from dm_acl where any r_accessor_name in (select group_name from dm_group) and any r_accessor_name = 'docu' 4. Modifying the attribute configuration

ALTER TYPE ls_aconex_doc MODIFY(acnx_doc_type  (SET is_searchable= true)) 

PUBLISH