Skip to content

documentum-schema-model.md

Latest commit

 

History

History
215 lines (163 loc) · 10.6 KB

documentum-schema-model.md

File metadata and controls

215 lines (163 loc) · 10.6 KB
Raw

The hidden physical model

Everything in this capability rests on one fact: the DQL you write is not the SQL that runs. This document is the mental model the agent applies and the one every Documentum developer should carry.

Tables and views, per type

For every type X, the Content Server maintains:

Object Contents
X_s One row per object. All single-valued attributes declared by X.
X_r Many rows per object. All repeating attributes declared by X, keyed by r_object_id plus a position index.
X_sv View joining X_s up the whole type hierarchy to dm_sysobject_s.
X_rv The repeating-attribute equivalent.
X_sp / X_rp The same, plus row-level security.

Attributes live in the table of the type that declares them. object_name lives in dm_sysobject_s no matter which subtype your DQL names. This is the single most common reason a proposed index gets created on the wrong table.

What a query actually costs

SELECT object_name, contract_status
  FROM my_contract
 WHERE ANY keywords = 'urgent'

with my_contract → my_base_doc → dm_document → dm_sysobject, executed by a normal user:

   my_contract_sp                    ← security-applying view
        │
        ├── my_contract_s            contract_status
        │      ⋈ r_object_id
        ├── my_base_doc_s
        │      ⋈ r_object_id
        ├── dm_document_s
        │      ⋈ r_object_id
        └── dm_sysobject_s           object_name, i_latest_flag, owner_name, ...
                 ⋈ r_object_id
             dm_sysobject_r          keywords  ← N rows per object
                 │
                 └── DISTINCT        added by the server to collapse the multiplication
                 +
             ACL evaluation          per row, every time

Five joins, a DISTINCT and a security predicate — from three lines of DQL, none of which mention a join.

The four cost drivers

Driver Cost What you control
Type hierarchy depth One _s join per level, on every query Type design (long-term); querying the narrowest type (immediate)
Repeating attributes One _r join per attribute referenced, plus row multiplication and DISTINCT Whether the predicate genuinely needs a repeating attribute (DQL-004, DQL-008)
Security filtering ACL evaluation per row for non-superusers Little, directly — but it makes result-set bounding far more valuable, and you control whether you add a second evaluation on top (DQL-036, and Where an ACL comes from)
Projection width Each projected attribute may add a table to the join set Everything (DQL-001)

Security views — the benchmarking trap

Non-superuser sessions query _sp/_rp; superuser sessions query _sv/_rv. The plans can differ completely.

A query benchmarked as a superuser and reported as representative is a misleading measurement. This is the most common methodological error in Documentum performance work, and it is why dql-benchmark-runner defaults to a non-superuser identity and logs the identity used on every result.

Where an ACL comes from

The section above says ACLs are evaluated per row. That is only half the model, and the other half is the part developers have never seen: an object's ACL is usually not set on the object.

Every dm_sysobject carries acl_name + acl_domain, which point at a dm_acl object. On save, the Content Server resolves that pointer in this order — the first rule that applies wins:

Order Source Set by
1 Explicitly assigned ACL The application called setACL() or IDfSysObject.setACLName()
2 The primary folder's default ACL dm_folder.acl_name on the folder the object was first linked into
3 The type's default ACL Registered against the type
4 The owner's default ACL dm_user.acl_name
5 The docbase default dm_docbase_config

Three consequences that decide whether a query is correct, not merely fast:

  1. acl_name is a snapshot of resolution, not a rule. It records what the ACL was when the object was saved. Change a folder's default and existing objects keep the old ACL; re-file an object and its ACL does not follow. So WHERE acl_name = 'contract_acl' is a query about history, and it quietly means different things for objects created down different paths. This is the reasoning behind DQL-036.
  2. Two objects in the same folder can hold different ACLs, if one was created before a default changed or one was assigned explicitly. "Everything in /Legal has the Legal ACL" is an assumption, not a guarantee — and it is not one the type model enforces.
  3. A "permission" is not a column. Permissions live in dm_acl_r — one row per accessor per ACL, where an accessor is a user or a group. Answering "can this user see it" means resolving group membership transitively, which is what i_all_users_names materializes.

Why the physical cost is worse than it reads

dm_sysobject_sp                        ← the view you queried
     │
     ├── dm_sysobject_s                acl_name, acl_domain
     │        ⋈ (acl_name, acl_domain)
     ├── dm_acl_s                      the ACL object
     │        ⋈ r_object_id
     └── dm_acl_r                      N rows: one per accessor per ACL
              ⋈ r_accessor_name
          group membership             resolved TRANSITIVELY through nested groups

The join is on (acl_name, acl_domain) — a two-column string pair, not r_object_id. That is the only place in the physical model where the hot join key is not the 16-character primary key, and it is why ACL-heavy predicates behave unlike anything else you tune.

Deeply nested group structures are the compounding factor: membership resolution is transitive, so a group tree ten levels deep multiplies the work at every row, and nothing in the DQL hints at it.

What to do about it

Situation Action
"Objects this user can see" Nothing. The _sp/_rp view already applies it. Restating it is DQL-036.
Filtering by business state that correlates with an ACL Filter on the business attribute (contract_status), not on acl_name. It is indexable, it means what it says, and it survives a folder-default change.
Genuinely administrative — auditing which objects carry an ACL Legitimate. Bound it hard, run it out of hours, suppress DQL-036 with a reason.
"Is this user in this group" One bounded query against dm_group, cached. Not a join inside a hot query.
Permissions feel like the bottleneck Confirm it with a plan before believing it — and confirm it as a non-superuser, or you will not see the ACL work at all.

That last row is the trap the section below describes, and ACL predicates are where it bites hardest: a superuser session reads _sv/_rv and skips exactly the work you were trying to measure.

r_object_id

A 16-character hex identifier. The first two characters are the type tag (09 for dm_sysobject/dm_document, 0b for dm_folder, and so on). It is the primary key of every _s table and the join key throughout — which is why WHERE r_object_id = '...' is always fast and why every index recommendation should be evaluated against it.

Related identity attributes worth knowing:

Attribute Meaning
i_chronicle_id Stable across all versions of an object
i_latest_flag Single-valued, indexed marker for the latest version on the current tree
i_folder_id Repeating — an object can be linked into several folders
i_is_deleted Soft-delete marker
i_vstamp Optimistic-locking version stamp

i_folder_id being repeating is why folder predicates touch dm_sysobject_r, and why FOLDER(..., DESCEND) over a broad subtree is expensive (DQL-007).

Where full text fits

xPlore maintains an index outside the RDBMS. A statement routes to it only if it meets the Content Server's FTDQL qualification rules — version-specific and strict. A statement that narrowly fails falls back to the RDBMS silently.

Two consequences the agent must always state:

  1. Asynchronous consistency. A just-saved document may not be findable yet. This decides the routing question in any read-your-own-writes use case, regardless of performance.
  2. Linguistic, not literal, matching. Stemming and tokenization mean full-text results are not a drop-in replacement for LIKE.

Seeing it for yourself

Question How
What SQL did that DQL generate? EXECUTE get_last_sql (superuser) — MCP dql_generated_sql
What is this type's structure? DESCRIBE <type> — MCP dctm_type_schema
How deep is the hierarchy? SELECT name, super_name FROM dm_type WHERE name = '<type>', walked up
Which attributes are repeating? dm_type / dmi_dd_attr_info, or DESCRIBE
What indexes exist? MCP dctm_indexes; also ALL_INDEXES / sys.indexes
Are statistics current? MCP dctm_statistics
Where did this object's ACL come from? SELECT acl_name, acl_domain FROM <type> WHERE r_object_id = '...', then compare against the primary folder's acl_name — if they match, it was almost certainly inherited rather than assigned
Who does an ACL actually grant to? SELECT r_accessor_name, r_accessor_permit FROM dm_acl WHERE object_name = '<acl>' — remembering that an accessor may be a group

EXECUTE get_last_sql is the one to remember. Most Documentum developers have never run it, and it answers the question their entire performance problem depends on.

Design implications (for new development)

The cheapest DQL optimization is a type model that does not need one:

  • Keep hierarchies shallow. Every level is a join on every query, forever.
  • Do not make an attribute repeating unless it genuinely is. A repeating attribute with one value per object costs an _r join and buys nothing.
  • Put frequently-filtered attributes high enough in the hierarchy that queries can reach them without descending — or on the type actually queried, so the predicate applies before the joins.
  • Design folder structures for breadth, not depth, if DESCEND queries are expected.
  • Decide early which searches are full-text, so the type model and the index configuration agree.

These are architecture decisions with a ten-year half-life, which is why they are worth raising even when the immediate ask is "make this query faster".