What is a Registered Table?

A registered table is an external database table that Documentum knows about and can query into.
For example, imagine that you are creating a document and you need to enter the name of the Author of the document into
the Authors attribute. In order to prevent typos and misspellings, what you really need is a list of your company’s
employees. This way, you could just pick the author’s name from a drop-down pick list and you would avoid typing their
name wrong.

But where will you get this list? From your corporate employee database, of course. With Documentum, you can create
a registered table that points to your external employee database – even if that database is on a different machine.
When your attributes screen comes up, Documentum will query the registered table for all the employee’s names and will
use the results to populate the pick list.

How do I create a Registered Table?

Documentum interfaces with registered tables by using a using a registered table object, dm_registered.
This object describes the name of the table, its owner, and the columns of the table. It also has attributes that tell
the server who has access to query the table, insert, update and delete rows. Here’s a list of the important attributes.

• table_name: Name of the table
• column_name (Repeating): Name of each column in the table
• column_datatype (Repeating): Datatype of each column in the table
• owner_name: Name of the user that owns the registered table object and whose access is determined by
  the owner_table_permit attribute
• group_name: Name of the group whose access is determined by the group_table_permit
• owner_table_permit: Determines the level of access that the owner of the registered table has to this table
• group_table_permit: Determines the level of access that the members of the registered table’s group have to this
  table
• world_table_permit: Determines the level of access that other users in the docbase have to this table

To register a table, you must create a registered table object using DQL. Documentum will link the registered table
object into the system cabinet. Here’s the syntax of the DQL statement and an example.

REGISTER TABLE [ owner_name.] table_name ( column_def {,column_def})
[[WITH] KEY ( column_list)] [SYNONYM [FOR] ' table_identification']
REGISTER TABLE employee (first_name CHAR(32), last_name CHAR(32), employment_status CHAR(16))

Querying the Contents of a Registered Table

Querying a registered table in Documentum is just like querying a normal table in your RDBMS (Oracle, Sybase, etc.)
The standard SQL syntax applies. For example, to return the first and last names of the employees in our employee
database, you would issue the following query.

SELECT first_name, last_name FROM dm_dbo.employee
WHERE employment_status = 'active' ORDER BY last-name

Inserting, updating and deleting rows are also the same as inserting, updating and deleting rows directly in your database.

Note that the external table does not have to exist when you create the registered table object. Documentum will not check for its existence until you try to use it (by querying for its contents).

Registered Table Tips &Tricks

The Table Does Not Need to Exist when you Register it

When you create a registered table, Documentum does not validate that the table really exists. This means you can
register a table before your DBA even creates it. Documentum does not check for the existence of the table until you try
to query it.

Use a Synonym to Trick Documentum into Thinking that it Owns the External Table

The docbase owner does not have to be the owner of the external table, and the table can exist outside of the docbase
owner’s tablespace. However, if it doesn’t, I find it best to create a synonym inside the docbase owner’s tablespace
that points to the external table. This will make Documentum think that it owns the table. This way, when you query the
table, you can use Documentum’s built-in alias for the docbase downer, dm-dbo, as the table’s owner name.
Your code will be more maintainable and reusable since you are not hard-coding the actual owner name.

You Don’t Need to Specify the Columns in the Table

When you create a registered table, you do not have to specify all of the column names in the table.
Documentum’s manuals say that you only need to specify the columns you will use, but I have found that you don’t even
have to do that. I am now in the habit of creating all my registered tables with a single column named UNKNOWN.
It saves me time and my queries still work fine.

Use a Special Group to Control Access to Your Registered Table

Documentum introduced support for Access Control Lists in version 3.0 of their server, but Registered tables still
contain a throwback to the old owner/group/world permissions found in pre 3.0 docbases. Although you can use an ACL to
control access to the registered table object itself, you can not use ACLs to control access to the underlying Database
table. To do this, you must use the table-access attributes (owner-table-access, group-table-access, would-table-access,
and table-group-home). Here’s how it works.

The value of owner-table access attribute controls what the owner of the registered table can do to the table.

• 0 (None): No access is permitted
• 1 (Select) : The user can retrieve data from the table
• 2 (Update): The user can update existing data in the table
• 4 (Insert): The user can insert new data into the table
• 8 (Delete): The user can delete rows from the table

If you want the owner to be able to insert and delete, add the permissions: 4 + 8 = 12.

Note that the underlying database must also allow the docbase owner the same level of access.

Registered tables are usually maintained by the external group that owns the table (HR updates the employed table whenever someone is hired or fired).
In this case, Documentum would only have read-access to the table.

But sometimes, you need a database table the Documentum can update. In this case, I normally define a special group
named registered_table_admins. I set the group_name attribute of the registered table object to registered_table_admins
and the group_table_permit attribute to 15. This will let any member of the registered_table_admins select, insert,
update and delete rows in the table.