Documentum Query Language Syntax

Question

I would like to know if there is a way in DQL to fetch the rows based on the start and end row values. (Like row number 1 - 1000, 1001 - 2000). ( Similar to what rownumber in oracle queries).

This input will be of great help.

Answer 1

I do not believe this is possible using DQL. However, you can consult the DQL Reference Guide (check Powerlink), which contains information about DQL hints (there is a section on them). There is a discussion of passthrough hints that allow you to pass hints through to the underlying RDBMS. The hints available depend on whether it is Oracle, SQL Server, DB2, etc.

This is an excerpt from that section:

Passthrough hints are hints that are passed to the RDBMS server. They are not handled by Content Server.

SQL Server and Sybase have two kinds of hints: those that apply to individual tables and those that apply globally, to the entire statement. To accommodate this, you can include passthrough hints in either a SELECT statement’s source list or at the end of the statement. The hints you include in the source list must be table‑specific hints. The hints you include at the end of the statement must be global hints. For example, the following statement includes passthrough hints for Sybase at the table level and the statement level:

SELECT "r_object_id" FROM "dm_document" WITH
(SYBASE('NOHOLDLOCK')) WHERE "object_name"='test' ENABLE (FORCE_PLAN)

For DB2 and Oracle, include passthrough hints only at the end of the SELECT statement.

Answer 2

For Documentum DQL query Pagination you can (should) use RETURN RANGE hint, like this

select * from dm_document where object_name like 'ABC%' enable(RETURN_RANGE 1001 2000 1000 'object_name ASC' )

it will sort documents by object_name and then return up to 1K rows, starting from the row number 1001 ending to 2000, optimized for 1K top (sorted) rows.

Syntax is RETURN_RANGE starting_row ending_row [optimize_top_row] 'sorting_clause'

It works since Content Server CS 6.6 with any underlying database.

Documentum Community Ref