A GitHub Copilot–native capability for optimizing OpenText Documentum DQL: instructions, prompt files, custom agents, an MCP grounding server, and a deterministic linter with a CI gate.
Full specification: SPEC.md
DQL is not SQL. Every DQL statement is translated by the Content Server into vendor SQL against a
hidden, normalized, security-filtered schema. Three lines of DQL routinely become a five-table join
with a DISTINCT and a per-row ACL evaluation — none of which appear in what the developer wrote.
The people who write DQL cannot see the generated SQL. The DBAs who can see the plan cannot see the intent. This repository closes that gap inside the editor and the pull request.
git clone <this repo> && cd dql-copilot-optimizer
pip install pyyaml
# 1. See the deterministic layer find real anti-patterns
python tools/dql_lint.py --format text samples/before/
# 2. See the optimized corpus come back clean
python tools/dql_lint.py --format text samples/after/
# 3. Lint a single statement from stdin
echo "SELECT * FROM dm_sysobject WHERE object_name LIKE '%invoice%'" \
| python tools/dql_lint.py --stdin
# 4. See a structural analyzer name the exact defect — this query can never return a row
echo "SELECT r_object_id FROM my_contract
WHERE contract_status = 'ACTIVE' AND contract_status = 'DRAFT'" \
| python tools/dql_lint.py --stdin
# MAJOR DQL-033
# why: ... Found here: contract_status cannot equal both 'ACTIVE' and 'DRAFT';
# the result set is always empty.
# 5. Compare a rewrite against its original — structural, no repository needed
python tools/dql_metrics.py --before samples/before/contract-search.dql \
--after samples/after/contract-search.dql
python tools/dql_metrics.py --explain # every weight, and why it has that value
# 6. Run the regression tests
pip install pytest && python -m pytest tests/ -qThen open the repository in VS Code with Copilot and try:
/dql-review samples/before/ContractService.java
/dql-optimize
| Layer | Files | Delivers |
|---|---|---|
| Knowledge | .github/copilot-instructions.md, .github/instructions/* |
Every Copilot completion and chat turn grounded in Documentum schema behaviour. No user action needed |
| Depth on demand | .github/skills/*/SKILL.md |
dql-optimization-method, dql-antipattern-catalogue, documentum-schema-model, dql-plan-reading — loaded by the model when relevant, so asking "why is this query slow?" gets the full method without knowing a /command exists |
| Workflow | .github/prompts/*.prompt.md |
/dql-optimize, /dql-review, /dql-explain-plan, /dql-index-advisor, /dql-to-ftdql, /dql-regression-harness, /dql-batch-triage, /dql-explain-to-dba |
| Autonomy | .github/agents/*.agent.md |
dql-optimizer, dql-reviewer, dql-index-advisor, dql-benchmark-runner, dql-migration-scout — in VS Code, Copilot CLI, and assignable to GitHub issues |
| Grounding | mcp/dctm-dql-mcp/, .vscode/mcp.json |
Real generated SQL, real plans, real schema, real timings. Turns advice into evidence |
| Enforcement | tools/dql_lint.py, tools/rules.yaml, .github/workflows/dql-lint.yml, AGENTS.md |
34 deterministically detected rules across DQL, DFC/DFS Java and D2 configuration. Blocker-severity defects fail the build; findings surface inline in Files Changed via SARIF |
| Before/after evidence | tools/dql_metrics.py |
Structural metrics and a rewrite comparison — subquery depth, OR breadth, correlation, sargability, complexity, filtering-early, and a rule-derived semantic-risk grade. Needs no repository connection, so it is the one before/after artefact available with no grounding at all |
DQL optimization decomposes into three kinds of analysis. This is what the capability covers in each, and — just as important — what it deliberately does not.
DQL Optimization
│
┌──────────────────────┼──────────────────────┐
▼ ▼ ▼
Syntax Semantics Execution
Analysis Analysis Analysis
│ │ │
▼ ▼ ▼
Redundant predicates Repeating attributes Query strategy
Nested IN ACL inheritance Filtering
OR chains Security Sorting
LIKE Object types Joins
EXISTS Subqueries
| Concern | Rules | Mechanism |
|---|---|---|
| Redundant predicates | DQL-033, DQL-018 | redundant_predicates analyzer — duplicated, subsumed and contradictory conjuncts |
Nested IN |
DQL-034, DQL-013 | in_subquery_depth analyzer — fires on nesting, not on a single level |
OR chains |
DQL-015 | or_chain analyzer — only when branches span more than one attribute |
LIKE |
DQL-002, DQL-003 | Regex — leading wildcards and function-wrapped columns, with an FTDQL routing path |
EXISTS |
DQL-035, DQL-013 | exists_correlation analyzer — uncorrelated, and correlated off r_object_id |
UNION |
DQL-038 | Regex — bare UNION de-duplicates and blocks per-branch bounding |
| Concern | Rules | Mechanism |
|---|---|---|
| Repeating attributes | DQL-004, DQL-008, DQL-011, DQL-012 | Regex, plus the _r join / row-multiplication / DISTINCT model in docs/documentum-schema-model.md |
| Result-set shape | DQL-037 | ENABLE(ROW_BASED) must acknowledge that it returns one row per repeating-attribute combination, via a -- semantic-delta: directive comment. It is usually added to silence DQL-008, trading a performance finding for a correctness defect |
| Versions and chronicles | DQL-039, DQL-004, DQL-012 | i_chronicle_id with no version discriminator matches the whole version tree — reported as "the UI shows this document 40 times", not as a slow query |
| Virtual documents | DQL-040 | IN DOCUMENT / IN ASSEMBLY traversal is recursive under DESCEND; cost tracks document depth, not result size |
| ACL inheritance | DQL-036 | Regex over the four ACL access forms, plus Where an ACL comes from — the folder → type → owner → docbase resolution chain, and why acl_name is a snapshot rather than a rule |
| Security | DQL-036, DQL-014 | _sp/_rp view cost, and the superuser benchmarking trap that dql-benchmark-runner guards against by defaulting to a non-superuser identity |
| Object types | DQL-005, DQL-031, DQL-016 | Regex, plus hierarchy depth as a cost driver and dctm_type_schema for a live DESCRIBE |
| Concern | Rules | Mechanism |
|---|---|---|
| Query strategy | — | dql-plan-reading: vendor gating first, a five-step read order, and a forced verdict of statistics / query / physical-schema rather than a description of the plan |
| Filtering | DQL-002, DQL-003, DQL-017 | Sargability rules, plus plan step 2 (scans on large tables) |
| Sorting | DQL-012, DQL-006 | Regex for repeating-attribute sorts; plan step 3 for sorts that spill |
| Joins | DQL-019 | Cartesian-product detection, join-set prediction from the type hierarchy, plan step 4 for nested loops from cardinality misestimates |
| Subqueries | DQL-032, DQL-034, DQL-013, DQL-035 | Analyzers for projection subqueries, nesting depth and EXISTS correlation |
Join order and join method are deliberately not static rules. They are properties of the plan the
optimizer chose, not of the statement — and the usual cause is a cardinality misestimate from stale
statistics, where the correct recommendation is a statistics refresh, not a rewrite. Guessing at them
from the DQL text would produce confident wrong advice, so they belong to dql-plan-reading.
Execution analysis is the one branch whose depth depends on grounding. Without a live MCP
connection to a non-production repository the agent cannot see a plan, and it says so — every claim
degrades to [HEURISTIC] rather than being asserted. docs/ToDo.md is the offline
artifact pipeline that recovers most of this from committed, expiry-stamped DESCRIBE and plan
captures; its phases are not yet started.
Five rules stay review judgement, because deciding them needs a fact the statement text does not carry: DQL-016 (which subtype the caller actually handles), DQL-025 (session management), DQL-026 (pagination strategy), DQL-027 (whether reference data could be cached) and DQL-030 (whether soft-deleted objects belong in the result). DQL-012 is detected for repeating system attributes but stays review judgement for unindexed custom attributes, which the linter cannot identify without the schema.
One construct family is not covered at all: literal INCLUDE / EXCLUDE version-scoping
keywords. They are not in this repository's documented DQL construct table
(dql-core.instructions.md) and the exact syntax is
version-specific, so no rule was written rather than guessing at syntax — the same never invent DQL
syntax rule the agent is held to. DQL-039 and DQL-040 cover the version-scope and
assembly-traversal problems those keywords sit next to. If your Content Server version supports them,
confirm the syntax against the DQL Reference and open a rule PR per
governance.md.
Each rule in docs/dql-antipatterns.md is labelled detected or
review judgement, and the label is load-bearing: for a review-judgement rule, no finding is not
evidence of no problem.
tools/dql_metrics.py closes the other half of the question — not "which
rules fired" but "did the rewrite actually simplify the statement, and what did that cost in
semantics".
Metric Before After
----------------------------------------------------------------
Nested subqueries (max depth) 3 1
OR predicates 5 2
Correlated subqueries 1 0
Non-sargable predicates 2 0
----------------------------------------------------------------
Estimated complexity 8.3/10 2.5/10
(raw score / ceiling) 10/12 3/12
Potential filtering early LOW HIGH
Semantic risk - HIGH
Three properties make this safe to put in a PR comment:
- Every number is a count of something in the statement text. Nothing is measured or inferred
from a plan, so the output is
[HEURISTIC]by construction — the JSON hard-codes the tag so a consumer cannot mislabel it. It needs no repository connection. - The formula is published.
--explainprints every weight with the reason it has that value, argued from generated-SQL cost. The ceiling is calibrated againstsamples/before/and a test asserts it stays in band, so the scale cannot quietly stop meaning anything. - Semantic risk is derived, not judged. The grade is the highest risk among the rules the rewrite resolved, read from the deltas already documented in the catalogue. It also reports rules the rewrite introduced — an optimization that adds a finding is a regression, and this is the cheapest place to catch it.
It is a comparison aid for one statement against its own rewrite, not a cross-statement cost
model: two 6.0s in different parts of the estate are not comparable, and neither predicts duration.
Ranking real work is still total cost = mean duration × executions. Full method in
docs/baseline-and-metrics.md.
Deterministic where possible, generative where necessary.
Pattern detection and CI gating are a Python rule engine — reproducible, auditable, no model variance. Rewriting, trade-off reasoning and plan interpretation are the model.
The engine matches regexes over whitespace-normalized text, and where an anti-pattern is structural
rather than textual it runs a named analyzer instead (tools/dql_lint.py,
ANALYZERS). Predicate redundancy, subquery nesting depth, OR-chain breadth, EXISTS correlation and
projection subqueries all need to know where the WHERE clause ends and what is inside which
parentheses — no regex over flattened text can decide them. The analyzers do a deliberately shallow
parse (mask string literals, split on AND/OR at parenthesis depth 0) and decline to report
anything they cannot decide with confidence — a valid half-open date range must never be reported as
a contradiction. A missed annotation costs one finding; a false positive costs trust in the whole gate.
And one hard rule on top: the agent may not claim a performance improvement it has not measured.
Every claim is tagged [MEASURED], [PLAN-DERIVED] or [HEURISTIC], and every rewrite states its
semantic delta. That is what makes the output safe to act on.
Every agent (dql-optimizer, dql-reviewer, dql-index-advisor, dql-benchmark-runner,
dql-migration-scout) runs on top of two evidence sources. MCP changes what evidence the agents
can produce, not whether they run.
Static tools (dql_lint.py, dql_metrics.py) |
MCP server (dctm-dql-mcp) |
|
|---|---|---|
| Needs a repository connection | No | Yes |
| Provides | Rule IDs / anti-pattern detection; structural metrics and before/after comparison | generated SQL, plans, schema, indexes, stats, timings, checksums |
| Evidence tag it unlocks | [HEURISTIC] |
[PLAN-DERIVED], [MEASURED] |
Without MCP (offline, the default). Everything that does not touch a live docbase works: the
linter runs, dql_metrics.py produces a real before/after comparison of the rewrite, the hidden
_s/_r join graph is rendered from static knowledge, rewrites are proposed with their semantic
delta, and DBA-recommendation DDL is emitted. The agent states that grounding is unavailable and tags
every performance claim [HEURISTIC].
With MCP (grounded, non-production only). dctm_repo_profile, dql_explain_plan, dql_measure
and friends become live, so the seven-step method runs fully — real SQL, real plans, real timings,
and dql_result_checksum for semantic-equivalence proof. Claims upgrade to [PLAN-DERIVED] and
[MEASURED]. Whatever backend is not wired is reported unavailable by dctm_repo_profile, so the
agent degrades gracefully rather than guessing.
Copy .github/, AGENTS.md, docs/ and tools/ into your Documentum application repository.
Then edit .github/instructions/repo-facts.instructions.md — it ships with placeholders, and
until it is completed the agent has no version-specific ground truth.
- Agents →
/agents/in your organization's.githubrepository - Organization-wide custom instructions → GitHub organization settings
- Repository-specific facts →
repo-facts.instructions.mdin each repository
See mcp/dctm-dql-mcp/README.md. The safety layer and tool contracts
ship complete; five backend functions need wiring to your environment. Start with
_dctm_describe (type schema) — the type hierarchy is the thing developers cannot see, and it alone
delivers most of the value.
Without grounding the capability still works — it degrades to static analysis and tags its claims
[HEURISTIC]. That is deliberate, so the first phase does not depend on getting repository access
approved.
| Phase | What | Duration |
|---|---|---|
| P0 | Inventory DQL, extract slow queries, build the calibration corpus | 2 weeks |
| P1 | Deploy knowledge layer + linter to a pilot repo, major-only |
2 weeks |
| P2 | Build and deploy the MCP server against DEV/TEST; enable agents | 3–4 weeks |
| P3 | Promote top rules to blocker; roll out org-wide |
3 weeks |
| P4 | Cloud agent works the ranked backlog | 6–8 weeks |
Details in SPEC.md §13.
Documentum behaviour varies by Content Server version and RDBMS. Every version-sensitive claim in
docs/ is marked as such. Verify against the OpenText Documentum Server DQL Reference and
Performance Tuning guides for the version in your estate, and record what you find in
repo-facts.instructions.md so the agent inherits it.
The rule catalogue is a starting point calibrated on common patterns — not a substitute for knowing
your own estate. docs/governance.md describes how the guild keeps it honest.
SPEC.md Full solution specification
AGENTS.md Cross-tool agent contract
.github/copilot-instructions.md Always-on core instructions
.github/instructions/ Scoped knowledge (core, Oracle, SQL Server, DFC, D2/xPlore, repo facts)
.github/skills/ Model-invoked skills (method, catalogue, schema model, plan reading)
.github/prompts/ /dql-* workflow commands
.github/agents/ Specialist agents
.github/workflows/ CI gate + cloud-agent environment setup
docs/ Rule catalogue, hints, schema model, metrics, governance
tools/dql_lint.py Rule engine: regex matcher + structural analyzers + CI gate
tools/dql_metrics.py Structural metrics and before/after rewrite comparison
tools/rules.yaml The rule catalogue, machine-readable single source of truth
mcp/dctm-dql-mcp/ MCP grounding server
samples/before/ Calibration corpus — every statement a deliberate anti-pattern
samples/after/ Optimized counterparts, each with its semantic delta. Must lint clean
tests/ Regression tests: per-rule, corpus, and catalogue/skill drift guards
Every dql-scope rule must be exercised by a statement in samples/before/ — tests/test_dql_lint.py
enforces it, so a rule cannot rot unnoticed. And samples/after/ must lint clean, which is what caught
an early version of DQL-012 that flagged every ANY + ORDER BY pairing, and an early DQL-033 that
would have reported a valid half-open date range.