Codename: DQL Copilot Optimizer (DCO) Version: 1.0 Date: 13 August 2026 Status: For review Audience: Layered — Sections 1–3 are for sponsors, architects and presales; Sections 4–12 are the engineering build specification.
Part A — Executive & solution view
Part B — Technical specification 4. Architecture 5. Copilot customization surfaces — design 6. Component specification: instructions 7. Component specification: prompt files 8. Component specification: custom agents 9. Component specification: DQL MCP server 10. Component specification: deterministic linter and CI gate 11. The optimization method the agent executes 12. Non-functional requirements, security and governance
Part C — Delivery 13. Rollout plan 14. Metrics and success criteria 15. Risks, assumptions, dependencies 16. Appendix — repository manifest
Documentum query performance is one of the most persistent and most expensive operational problems in large ECM estates. The cause is structural, not accidental: DQL is not SQL. Every DQL statement is translated by the Content Server into vendor SQL against a normalized, view-layered, security-filtered physical schema that the query author never sees. A developer can write DQL that reads as three lines and executes as an eleven-table join with a correlated security predicate and a full scan of dm_sysobject_s.
The people who write DQL (application developers, D2 configurators, report authors, integration engineers) generally cannot see the generated SQL, do not have DBA access to the execution plan, and are not present when the query is blamed six months later. The people who can see the plan (DBAs) cannot read the DQL intent. That gap is where this solution operates.
This specification defines a GitHub Copilot–native capability that closes that gap by encoding Documentum query expertise directly into the developer's editor and the pull request. It does this with five layers, all of which are standard, supported GitHub Copilot customization surfaces — no bespoke IDE plugin, no forked tooling:
| Layer | Copilot surface | What it delivers |
|---|---|---|
| Knowledge | .github/copilot-instructions.md + .github/instructions/*.instructions.md |
Every Copilot completion and chat turn in the repo is grounded in Documentum schema behaviour and a versioned catalogue of 40+ DQL anti-patterns. Ambient — no user action required. |
| Workflow | .github/prompts/*.prompt.md |
Repeatable, parameterized operations: /dql-optimize, /dql-review, /dql-explain-plan, /dql-index-advisor, /dql-to-ftdql, /dql-regression-harness. |
| Autonomy | .github/agents/*.agent.md |
Named specialist agents (dql-optimizer, dql-reviewer, dql-index-advisor, dql-benchmark-runner) usable in VS Code, Copilot CLI, and assignable to GitHub issues via the Copilot cloud agent. |
| Grounding | MCP server (dctm-dql-mcp) |
Gives the agent real facts instead of plausible ones: the actual generated SQL (EXECUTE get_last_sql), the live type/index/statistics metadata, row counts, and RDBMS execution plans. This is what turns advice into evidence. |
| Enforcement | tools/dql_lint.py + .github/workflows/dql-lint.yml + AGENTS.md |
A deterministic rule engine that fails the build on high-severity DQL defects, so the standard is enforced by CI rather than by reviewer diligence. Copilot Code Review reads the same rule catalogue. |
The essential design decision in this specification is the separation of deterministic from generative work. Pattern detection, rule identification and CI gating are done by a rule engine — reproducible, auditable, no model variance. Rewriting, explaining, trade-off reasoning and plan interpretation are done by the model. The agent is explicitly instructed never to assert a performance improvement it has not measured through the MCP server. This is what makes the output safe to act on.
Expected outcome: a 40–70% reduction in mean execution time across the remediated query population, elimination of the highest-severity anti-patterns from new code at PR time, and — the durable benefit — conversion of scarce, tribal Documentum tuning knowledge into a versioned repository asset that every developer inherits automatically.
Five compounding factors:
1. The abstraction is opaque and lossy. SELECT r_object_id FROM dm_document WHERE ANY r_version_label = 'CURRENT' is idiomatic, appears in every tutorial, and produces a join to the repeating-attribute table dm_sysobject_r plus a DISTINCT — where WHERE i_latest_flag = TRUE reads a single-valued indexed column on dm_sysobject_s. The two are semantically near-equivalent for most use cases and differ by orders of magnitude in cost. Nothing in the DQL surfaces this.
2. Security filtering is invisible and expensive. For a non-superuser session the Content Server does not query dm_sysobject_s; it queries the security-applying views (dm_sysobject_sp / dm_sysobject_rp), which carry ACL evaluation into every query. A query benchmarked as a superuser by a developer can behave completely differently for a real user. Developers routinely benchmark under the wrong identity.
3. Type hierarchy joins are implicit. Querying a custom type five levels below dm_sysobject joins five _s tables on r_object_id before any predicate is applied. Deep, wide custom type hierarchies — common in regulated industries — are a silent tax on every query.
4. Knowledge does not scale. DQL tuning skill sits with a handful of long-tenured engineers. It is transmitted verbally, inconsistently, and it leaves when they leave. Documentum estates are typically 10–20 years old; the original architects are usually gone.
5. Modernization multiplies the query surface. D2 configuration, xPlore full-text, REST integrations, migration tooling and reporting each generate DQL through different paths, with different conventions and no shared standard.
| Remedy | Why it falls short |
|---|---|
| DBA-led SQL tuning | Fixes the symptom in the database. The DQL that generated it stays in the codebase and regenerates the problem on the next deployment. Also cannot see intent — a DBA cannot know that ANY r_version_label was meant as "current version". |
| Coding standards documents | Read once at onboarding. Not enforced. Not present at the moment of authorship. Decay immediately. |
| Periodic performance audits | Point-in-time, expensive, consultant-dependent, and the findings age out before they are all remediated. |
| Generic AI coding assistants | Have no Documentum-specific grounding. Will confidently produce syntactically valid DQL that is semantically wrong (e.g. inventing LIMIT, or JOIN syntax that DQL does not support in the way SQL does) and performance-naive. Ungrounded AI actively makes this problem worse. |
Costs currently absorbed by the organization:
- Infrastructure over-provisioning. Database and Content Server tiers are sized for the worst query, not the average one. Poorly-formed DQL is a direct, recurring hardware and licence cost.
- Engineering time. Query performance incidents are among the most time-expensive to diagnose because they cross the DQL/SQL boundary and require two scarce skill sets in the same room.
- User productivity and SLA exposure. Slow search and slow document lists are the number one user complaint in most Documentum estates; in regulated workflows they translate into missed processing SLAs.
- Modernization drag. Migration and re-platforming projects are repeatedly delayed by query performance discovered late in UAT.
The investment is deliberately modest: this is a configuration-and-content solution built on Copilot licences the organization is likely to hold already. There is no new runtime, no new licence tier, and no change to the Documentum platform itself. The one build item of substance is the MCP server (Section 9), and even that is optional for Phase 1 — the knowledge and workflow layers deliver value with zero repository connectivity.
| # | Principle | Consequence in the design |
|---|---|---|
| P1 | Ground, never guess. | The agent may not claim a performance improvement it has not measured. Every claim is tagged [MEASURED], [PLAN-DERIVED] or [HEURISTIC]. |
| P2 | Deterministic where possible, generative where necessary. | Rule detection and CI gating are a Python rule engine. Rewriting and reasoning are the model. |
| P3 | Semantics before speed. | A rewrite that changes the result set is a defect, not an optimization. Every rewrite carries an explicit semantic-equivalence statement and, where the change is not strictly equivalent, a blocking flag. |
| P4 | Meet developers where they are. | Ambient instructions apply with no user action. Prompt files cover deliberate operations. Agents cover delegated work. CI covers everything else. |
| P5 | Read-only by default. | The MCP server refuses any non-SELECT DQL and connects with a dedicated least-privilege account. Optimization must never mutate a repository. |
| P6 | Version-honest. | Documentum behaviour varies by Content Server version and RDBMS. Repository-specific facts live in one configuration file, not scattered through prompts. |
| P7 | Auditable. | Every optimization produces a structured record (rules fired, before/after, measurements, reviewer) suitable for change control in a regulated environment. |
DQL COPILOT OPTIMIZER — CAPABILITY MAP
AUTHORING TIME REVIEW TIME OPERATIONS TIME
───────────── ─────────── ───────────────
Inline grounding PR anti-pattern gate Slow-query triage
Anti-pattern avoidance Copilot Code Review Index/statistics advice
Hint guidance Semantic-equivalence check Regression benchmarking
Schema-aware completion Plan-backed justification Backlog remediation
│ │ │
▼ ▼ ▼
┌─────────────────────────────────────────────────────────────────────┐
│ L1 KNOWLEDGE copilot-instructions.md + *.instructions.md │
│ L2 WORKFLOW *.prompt.md (/dql-optimize, /dql-review, ...) │
│ L3 AUTONOMY *.agent.md (dql-optimizer, dql-reviewer, ...) │
│ L4 GROUNDING dctm-dql-mcp (SQL, plans, schema, stats, timing) │
│ L5 ENFORCEMENT dql_lint.py + GitHub Actions + AGENTS.md │
└─────────────────────────────────────────────────────────────────────┘
In scope
- DQL authored in application source (Java/DFC, .NET, REST clients, scripts), in D2 configuration exports, in reports, and in migration/utility tooling.
- Oracle-backed and SQL Server–backed Documentum Content Server deployments, plus vendor-neutral guidance.
- Full-text query routing (xPlore / FTDQL) and the DQL↔full-text decision.
- DFC/DFS client-side query execution patterns (collection handling, batch size, session reuse).
- Index and statistics advice — recommendations produced for the DBA, with the evidence attached.
Out of scope
- Applying DDL to production databases. The solution recommends; a DBA disposes.
- Content Server, JMS or xPlore infrastructure sizing and tuning.
- Rewriting Documentum-internal or OpenText product queries.
- Any write-path DQL optimization that changes repository data or object model semantics.
| Persona | Primary surface | Value |
|---|---|---|
| Application developer | Ambient instructions + /dql-optimize in VS Code |
Writes performant DQL first time; gets a rewrite with rationale in seconds. |
| D2 configurator | /dql-review on exported config |
Catches expensive property-page and search queries before they reach users. |
| Technical lead / reviewer | Copilot Code Review + CI gate | Consistent enforcement without personally knowing every rule. |
| Documentum architect | dql-index-advisor agent |
Evidence-backed index and statistics recommendations to take to the DBA. |
| DBA | MCP plan output + advisor report | Receives DQL intent alongside the SQL plan for the first time. |
| Support / SRE | dql-optimizer agent on a slow-query ticket |
Triage from a query string to a candidate fix without waiting for a specialist. |
┌──────────────────────────────────────────────────────────────────────────────┐
│ DEVELOPER PLANE │
│ │
│ VS Code + Copilot Chat Copilot CLI GitHub.com │
│ ├ ambient instructions ├ custom agents ├ Copilot Code Review │
│ ├ /dql-* prompt files └ --agent dql-optimizer├ cloud agent on issue │
│ └ agent picker └ PR checks │
└───────────────┬──────────────────────────────────────────────────────────────┘
│ reads repository customization files
▼
┌──────────────────────────────────────────────────────────────────────────────┐
│ REPOSITORY PLANE (this repo — version controlled, reviewed, released) │
│ │
│ AGENTS.md .github/copilot-instructions.md │
│ .github/instructions/*.instructions.md (applyTo-scoped knowledge) │
│ .github/prompts/*.prompt.md (parameterized workflows) │
│ .github/agents/*.agent.md (named specialist agents) │
│ docs/dql-antipatterns.md (rule catalogue — SSOT) │
│ docs/dql-hints.md docs/documentum-schema-model.md │
│ tools/dql_lint.py tools/rules.yaml (deterministic engine) │
│ .vscode/mcp.json (MCP wiring) │
└───────────────┬──────────────────────────────────────────────────────────────┘
│ agent tool calls (MCP / stdio)
▼
┌──────────────────────────────────────────────────────────────────────────────┐
│ GROUNDING PLANE dctm-dql-mcp (read-only, least privilege) │
│ │
│ dql_validate dql_generated_sql dql_explain_plan │
│ dctm_type_schema dctm_indexes dctm_statistics │
│ dql_measure dctm_registered_tables dctm_repo_profile │
└───────────────┬──────────────────────────────────────────────────────────────┘
│ Documentum REST Services / DFC bridge · read-only DB user
▼
┌──────────────────────────────────────────────────────────────────────────────┐
│ DOCUMENTUM PLANE (NON-PRODUCTION by default) │
│ Content Server ── RDBMS (Oracle | SQL Server) ── xPlore ── D2 │
└──────────────────────────────────────────────────────────────────────────────┘
Three deployment tiers, adopted in order:
| Tier | Components | Documentum connectivity | Value delivered |
|---|---|---|---|
| T1 — Knowledge only | L1, L2, L5 | None | Ambient grounding, prompt workflows, CI gate. Static analysis only. Deployable in a day. |
| T2 — Grounded (non-prod) | + L3, L4 against DEV/TEST repository | Read-only to a non-production repository | Real generated SQL, real plans, real schema. This is the target steady state. |
| T3 — Production-observed | + read-only production metadata and AWR/Query Store feed | Read-only, metadata and plan cache only; no query execution against production | Prioritizes remediation by real production cost. |
The dql_measure tool is hard-disabled against any repository flagged production: true in dctm-mcp.config.json. Production grounding is limited to metadata, statistics and plan-cache reads.
| Option | Description | When to use |
|---|---|---|
| A — In-repo | Copy .github/, AGENTS.md, docs/, tools/ into each Documentum application repository. |
Small number of repositories; teams want to fork the rules. |
| B — Org-level defaults | Place agents in /agents/ of the org's .github repository and organization-wide custom instructions in GitHub settings; keep repo-specific facts in-repo. |
Many repositories; central governance. Recommended. |
| C — Hybrid | Org-level baseline (Option B) + per-repo .github/instructions/repo-facts.instructions.md carrying that repository's type model, Content Server version and RDBMS. |
Large estates with heterogeneous repositories. |
Note that path-specific (applyTo-scoped) instruction files are honoured differently by different clients — in VS Code they apply in chat generally, while on GitHub.com they are documented as applying to Copilot code review and the cloud agent. Design accordingly: anything that must always apply belongs in .github/copilot-instructions.md or AGENTS.md, not in a path-scoped file.
| Requirement | Surface chosen | Rationale |
|---|---|---|
| Always-on Documentum grounding | .github/copilot-instructions.md |
Loaded into every chat request in the repo, in every client. No user action. |
Language/context-specific rules (DFC Java vs D2 XML vs .dql files) |
.github/instructions/*.instructions.md with applyTo globs |
Keeps the always-on file small; loads detail only when relevant. |
| Cross-tool agent contract | AGENTS.md |
Honoured by Copilot and by other agentic tools; the correct home for build/test/lint commands. |
| Repeatable parameterized operation | .github/prompts/*.prompt.md |
Invoked as /name, supports ${input:...} variables and tool pinning. |
| Deep knowledge that must load only when relevant | .github/skills/*/SKILL.md |
Progressive disclosure: only name and description sit in context; the body loads when the model judges the task relevant, and referenced files load only when read. Model-invoked, so a developer who asks "why is this query slow?" gets the method without knowing a /command exists. |
| Delegated multi-step work | .github/agents/*.agent.md |
Own toolset, own model, invocable in VS Code, Copilot CLI (--agent) and assignable to issues via cloud agent. |
| Live repository facts | MCP server via .vscode/mcp.json and agent mcp-servers frontmatter |
The only way to replace guesswork with evidence. |
| Hard enforcement | GitHub Actions + tools/dql_lint.py |
Model output cannot be a merge gate; deterministic rules can. |
Instruction content competes with the user's actual code for context. The design therefore enforces:
copilot-instructions.md≤ ~150 lines. It contains the irreducible core: the mental model, the top-10 rules, the guardrails, and pointers to detail files.- Detail lives in
docs/and is pulled in on demand — by prompt files via markdown links and#file:references, and by skills, whose bodies and referenced files load only when the model selects them.docs/remains the single source for rule and model content; skills navigate to it and add procedure rather than restating it, so there is one home per fact. applyToglobs are narrow.applyTo: "**"is used exactly once, deliberately, indql-core.instructions.md.- The full rule catalogue — 33 rules, of which 24 are machine-detected (23 in
rules.yamlplusDQL-000, emitted by the linter itself) and 9 are review judgement — is never loaded ambiently. The linter carries the detected ones deterministically, and thedql-antipattern-catalogueskill loads the index only when the model judges a triage task relevant, withdocs/dql-antipatterns.mdread on demand from there.
| Task | Recommended model class | Reasoning |
|---|---|---|
| Inline completion / simple rewrites | Fast general model | Latency-sensitive, well-constrained by instructions. |
/dql-optimize on a single query |
Balanced reasoning model | Needs multi-step reasoning over schema and plan. |
dql-optimizer agent on a query backlog |
Strongest reasoning model available | Long-horizon, multi-tool, semantic-equivalence risk. |
dql-reviewer in CI |
Balanced model | Volume operation; deterministic linter carries the load. |
Model names change frequently; agent files therefore specify model only where it materially matters, and the specification records the class rather than pinning a name that will age out.
| File | applyTo |
Purpose | Budget |
|---|---|---|---|
.github/copilot-instructions.md |
(always) | Core mental model, top rules, guardrails, output contract. | ≤150 lines |
AGENTS.md |
(always) | Agent contract: how to lint, test, benchmark; what never to do. | ≤120 lines |
.github/instructions/dql-core.instructions.md |
** |
Full DQL syntax and semantics guardrails; the "DQL is not SQL" rules. | ≤200 lines |
.github/instructions/dql-oracle.instructions.md |
**/*.{dql,sql,java,xml,json,properties} |
Oracle-specific translation, hints, plan reading. | ≤120 lines |
.github/instructions/dql-sqlserver.instructions.md |
**/*.{dql,sql,java,xml,json,properties} |
SQL Server–specific translation, hints, plan reading. | ≤120 lines |
.github/instructions/dfc-java.instructions.md |
**/*.java |
DFC/DFS client patterns: collections, batch size, session policy. | ≤120 lines |
.github/instructions/d2-xplore.instructions.md |
**/*.{xml,json,properties} |
D2 config queries and xPlore/FTDQL routing. | ≤120 lines |
.github/instructions/repo-facts.instructions.md |
** |
Per-deployment. CS version, RDBMS, custom type hierarchy, known hot tables. Maintained by the platform team. | ≤80 lines |
- Imperative, testable statements. "Prefer
i_latest_flag = TRUEoverANY r_version_label = 'CURRENT'when the intent is 'current version'" — not "consider version predicates carefully". - Every rule carries its rule ID (
DQL-012) so linter output, chat output and PR comments all speak the same language. - Negative examples are mandatory for the high-frequency traps — the model needs to see the wrong form to recognize it.
- No vendor claims without a version qualifier. Behaviour that varies across Content Server versions is stated as "verify with
dctm_repo_profile". - The output contract is stated once, in the core file, and referenced everywhere else.
Every DQL optimization response must contain, in order:
1. VERDICT One line: optimize / already optimal / needs more information
2. FINDINGS Table: Rule ID | Severity | Evidence | Impact
3. OPTIMIZED DQL Fenced block, runnable, no placeholders
4. SEMANTIC DELTA Explicit: "identical result set" OR precise description of the difference
5. EVIDENCE [MEASURED] / [PLAN-DERIVED] / [HEURISTIC] tagged, with numbers where measured
6. DBA ACTIONS Index/statistics recommendations, as DDL, marked as recommendations only
7. RESIDUAL RISK What was not verified and why
Section 4 is non-negotiable. An optimization that silently changes the result set is the primary failure mode of this class of tooling, and the contract exists to make that failure impossible to hide.
Prompt files live in .github/prompts/ with the .prompt.md extension and are invoked as /name in Copilot Chat. Frontmatter supports name, description, argument-hint, agent, model and tools; the body supports ${input:...} variables, workspace variables and #file: / #tool: references.
| Command | Inputs | Behaviour | Requires MCP |
|---|---|---|---|
/dql-optimize |
dql, context, rdbms |
Full seven-step optimization loop (Section 11) on one statement. Produces the Section 6.3 output contract. | Optional (degrades to [HEURISTIC]) |
/dql-review |
path or selection |
Scans a file or diff for DQL, runs the rule catalogue, reports findings ranked by severity. No rewriting. | No |
/dql-explain-plan |
dql |
Retrieves generated SQL via EXECUTE get_last_sql, obtains the plan, translates the plan into DQL-level cause-and-effect. |
Yes |
/dql-index-advisor |
dql or type_name |
Analyses predicates and joins against live index and statistics metadata; emits candidate DDL with justification and a cost note. | Yes |
/dql-to-ftdql |
dql |
Assesses whether the query should be routed to xPlore; rewrites to SEARCH DOCUMENT CONTAINS form or explains why it cannot qualify. |
Optional |
/dql-regression-harness |
dql_before, dql_after |
Generates a repeatable comparison harness: result-set equality assertion first, then timing across warm/cold runs. | Optional |
/dql-batch-triage |
path to slow-query CSV |
Clusters a slow-query export by root-cause pattern, ranks by total cost, produces a prioritized remediation backlog. | Optional |
/dql-explain-to-dba |
dql |
Produces a DBA-facing brief: intent in business terms, generated SQL, plan, and the specific ask. | Yes |
- One responsibility per prompt.
/dql-reviewfinds;/dql-optimizefixes. Combining them produced worse results in both directions during design review. argument-hinton every prompt, so the chat input tells the developer what to supply.- Tool pinning. Prompts that require grounding declare their MCP tools in
tools:and state explicitly in the body: if these tools are unavailable, say so and tag all claims[HEURISTIC]— do not fabricate measurements. - Deterministic-first. Every prompt that can be preceded by the linter instructs the agent to run
python tools/dql_lint.pyfirst and treat its output as ground truth for rule detection.
Custom agents are Markdown files with YAML frontmatter in .github/agents/. They are available in VS Code's agent picker, in Copilot CLI (copilot --agent <name>), and — when placed in .github/agents/ of the repository, or /agents/ of the org .github repository — to the Copilot cloud agent for issue assignment. Frontmatter supports name, description, tools, model, mcp-servers and (in VS Code) workflow keys such as argument-hint, handoffs, agents and user-invocable.
| Agent | Mission | Tools | Autonomy |
|---|---|---|---|
dql-optimizer |
End-to-end optimization of one or many queries, with measurement. The flagship agent. | MCP (all read tools), read, edit, runCommands (linter + harness only) |
High — may edit source to apply an approved rewrite |
dql-reviewer |
Read-only review of a diff or file set. Produces findings, never rewrites. | read, search, runCommands (linter only) |
Low — read-only by design |
dql-index-advisor |
Schema/index/statistics analysis and DDL recommendation. | MCP (dctm_type_schema, dctm_indexes, dctm_statistics, dql_explain_plan), read |
Medium — emits DDL, never executes it |
dql-benchmark-runner |
Executes before/after harnesses against non-production, produces a signed measurement record. | MCP (dql_measure, dql_validate), runCommands, edit (results file only) |
Medium — hard-blocked from production |
dql-migration-scout |
Sweeps a legacy codebase for DQL, builds an inventory and a risk-ranked remediation backlog. | search, read, runCommands, edit (backlog file only) |
Medium |
The optimizer defines a handoff chain so a developer can complete the loop without re-prompting:
dql-reviewer ──▶ dql-optimizer ──▶ dql-benchmark-runner ──▶ dql-index-advisor
(find) (rewrite) (prove) (escalate to DBA)
Each handoff passes the rule IDs and the candidate rewrite forward. The chain deliberately places measurement before index recommendation — an index request that a rewrite would have made unnecessary is a cost the DBA should never be asked to bear.
For backlog remediation, dql-optimizer is assigned to a GitHub issue with a defined template (.github/ISSUE_TEMPLATE/dql-slow-query.yml). The cloud agent:
- Reads the issue (query text, observed timing, repository, user context).
- Runs the linter and the optimization loop.
- Opens a pull request containing: the rewrite, the Section 6.3 record as the PR body, and the regression harness as a test file.
- Requests review from the Documentum guild.
MCP servers for the cloud agent are declared in the agent's mcp-servers frontmatter, with credentials supplied by repository/organization secrets and the environment prepared by .github/workflows/copilot-setup-steps.yml.
dctm-dql-mcp is the grounding layer. Without it the solution is a very good static advisor; with it, it is an evidence-producing one.
| Tool | Input | Output | Notes |
|---|---|---|---|
dctm_repo_profile |
— | CS version, RDBMS + version, docbase name, production flag and the reason for it, session user, superuser status, unverified_fields |
Called first by every agent; determines which rules and hints are valid. Facts the server has not verified are returned as null and listed in unverified_fields — never as a plausible default, because the guardrails downstream depend on knowing what is unknown |
dql_validate |
dql |
{valid, error, statement_type} |
Rejects any non-SELECT statement before it reaches the server |
dql_generated_sql |
dql |
Vendor SQL string | Implemented via EXECUTE get_last_sql after a bounded-cost execution; requires superuser or equivalent privilege on the non-production repository |
dql_explain_plan |
dql | sql (exactly one) |
Normalized plan tree + cost/cardinality/access-path per node, plus the SQL actually explained | Oracle: EXPLAIN PLAN / DBMS_XPLAN. SQL Server: SET SHOWPLAN_XML. Normalized to one JSON shape so prompts are vendor-agnostic. The dql path executes the statement once to obtain its generated SQL, so it requires the dfc backend and is non-production only; without dfc it refuses rather than planning a statement it cannot translate |
dctm_type_schema |
type_name |
Full inheritance chain, attribute list with single/repeating flag, underlying _s/_r tables and views |
The single most valuable tool — the type hierarchy is what developers cannot see |
dctm_indexes |
type_name | table_name |
Documentum-managed and DBA-created indexes, columns, uniqueness, and (where available) usage statistics | |
dctm_statistics |
table_name |
Row count, last-analyzed timestamp, column cardinality/histogram summary | Stale statistics are a top-5 root cause and are trivially detectable here |
dql_measure |
dql, runs, mode (warm | cold) |
Elapsed ms per run, median/min/max, relative spread, rows returned; warm discards a warm-up run, cold reports the first execution |
Disabled unless the repository is positively classified production: false. Enforces a statement timeout. cold does not clear any database or OS cache, which the server cannot control, and says so in its result |
dctm_registered_tables |
— | Registered external tables available to DQL | |
dctm_slow_queries |
since, top_n |
Slow-query extract from Oracle AWR / SQL Server Query Store, mapped back to DQL where identifiable | Read-only; the input to /dql-batch-triage |
| Control | Implementation |
|---|---|
| Read-only enforcement | Statement-type allowlist (SELECT only) applied before transport. Comments are stripped and string literals are masked before inspection, so neither a comment nor a data value can smuggle a mutating keyword past the gate — or trip it. A query whose data contains the word UPDATE is a normal query |
| Least privilege | Dedicated repository account with read access to the target types and no superuser rights in production; separate elevated account for the non-production get_last_sql path only |
| Production interlock | Fails closed. dql_measure, dql_generated_sql, dql_result_checksum and DQL-sourced plans are enabled only when the config file positively classifies the connected docbase as production: false. A missing or unreadable config, an unset docbase, a docbase that is not listed, an ambiguous case-insensitive match, or a listed entry with no production key are all treated as production. The refusal carries the reason, so the agent states the limitation rather than silently downgrading its evidence |
| Cost ceiling | Server-side statement timeout (default 30 s) and ENABLE(RETURN_TOP n) injection on exploratory executions |
| Data minimization | Result values are never returned to the model — only row counts, timings and shapes. This is a firm boundary: the grounding layer returns metadata and metrics, never content |
| Audit | Every tool call logged with caller, timestamp, statement, and outcome |
| Secrets | Credentials from environment variables sourced from GitHub secrets or the developer's local secret store; never in mcp.json, which uses inputs prompts |
- Transport: stdio for local VS Code and CLI use; optional HTTP for the cloud agent.
- Backend adapter pattern: the server abstracts three possible connection paths — Documentum REST Services (preferred; no native libraries), a DFC bridge process (for
get_last_sqland dmAPI access), and a direct read-only JDBC/ODBC connection to the RDBMS (for plans and statistics only). Each is independently optional; the server degrades gracefully and reports which capabilities are live viadctm_repo_profile. - Caching: type schema and index metadata are cached with a configurable TTL (default 15 min). Schema lookups dominate call volume and are stable.
- Language: Python with the MCP SDK, packaged for
uvxexecution. Seemcp/dctm-dql-mcp/.
Model output must never be the merge gate — it is not reproducible and it is not auditable. The linter provides a stable, versioned floor; the model provides judgement on top of it.
tools/rules.yaml— the machine-readable rule catalogue. Each rule:id,title,severity(blocker/major/minor/info),detect(regex set withall_of/any_of/none_ofcomposition),applies_to(RDBMS/CS-version scoping),rationale,remediation,doc_anchor.tools/dql_lint.py— extracts DQL from.dqlfiles, Java string literals and concatenations, XML/JSON configuration values and SQL-adjacent files; normalizes whitespace and case; applies the rule set; emits SARIF (for GitHub code scanning annotations), JSON (for the agent) and human-readable text.- Suppression:
-- dql-lint:disable=DQL-012 reason="..."— reason is mandatory, and suppressions are reported in the CI summary so they cannot accumulate silently.
.github/workflows/dql-lint.yml:
| Severity | PR behaviour |
|---|---|
blocker |
Fails the check. Merge blocked. |
major |
Annotates the diff; requires a Documentum guild reviewer. |
minor / info |
Annotates only. |
The workflow runs on changed files only for speed, and uploads SARIF so findings appear inline in the Files Changed view. Copilot Code Review is configured with the same catalogue via instructions, so the human-facing and machine-facing standards cannot drift.
This is the core intellectual property of the solution — the seven-step loop encoded in dql-optimizer.agent.md and /dql-optimize.
Its operational home is .github/skills/dql-optimization-method/SKILL.md, which is what the model actually loads and follows; this section is the specification of that skill. Change them together.
Call dctm_repo_profile. Record CS version, RDBMS, and whether the session is superuser (which changes whether security views are in play). If grounding is unavailable, declare it now and tag every subsequent claim [HEURISTIC].
State, in one sentence, what the query is for. This is the anchor for the semantic-equivalence check in Step 6 and the single most-skipped step in manual tuning. If intent cannot be determined from context, ask — do not assume.
Run python tools/dql_lint.py --format json. Its findings are ground truth for which rules fired. The model's job is not to re-detect but to explain, prioritize and remediate.
Call dctm_type_schema for every type in the FROM clause. Compute the implied join set: type-hierarchy _s joins, _r joins for each repeating attribute referenced, folder/ACL joins, and the security view substitution. Make the hidden join graph explicit in the output — this is the highest-value single artefact for a developer, because it is the thing DQL conceals.
Call dql_generated_sql, then dql_explain_plan. Identify: full scans on large tables, missing/unused indexes, cardinality misestimates (compare estimated vs actual rows), sort and hash spills, and nested-loop joins driven by a bad row estimate. Cross-check dctm_statistics for staleness — a plan built on statistics six months old is a statistics problem, not a query problem, and the correct recommendation is different.
Apply remediations in the order below (cheapest and safest first), and for each one state the semantic effect explicitly:
- Predicate correction — replace repeating-attribute predicates with single-valued equivalents; make predicates sargable (no functions on indexed columns, no leading wildcards); push filters earlier.
- Projection reduction — never
SELECT *; each dropped attribute may drop a join to another_stable. - Type narrowing — query the most specific type that satisfies intent; the type hierarchy join is paid on every row.
- Folder/path strategy — replace broad
FOLDER(..., DESCEND)with bounded folder sets or an indexed metadata predicate where intent allows. - Full-text routing — move content and unstructured-text predicates to xPlore rather than forcing
LIKE '%...%'through the RDBMS. - Result bounding — apply
ENABLE(RETURN_TOP n, OPTIMIZE_TOP n)where the consumer is paginated; this is one of the highest-yield, lowest-risk changes available. - Hints —
FORCE_ORDER,SQL_DEF_RESULT_SET, vendor passthrough hints. Last resort, always. A hint freezes a plan against a schema that will change. Every hint applied must carry an expiry review date in a code comment. - Client-side — batch size, collection lifecycle, session reuse, caching (see
dfc-java.instructions.md).
Then: run the equivalence check. Compare result-set cardinality and a checksum of ordered r_object_id values between original and rewrite on non-production. If the sets differ, the rewrite is rejected unless the difference is the explicitly stated intent, approved by the developer.
Call dql_measure on both forms (default 5 runs, cold/warm reported separately). Produce the Section 6.3 output contract with real numbers. Where measurement was impossible, say so plainly — an honest [HEURISTIC] is worth more than a fabricated percentage, and the credibility of the whole capability depends on this discipline.
| ID | Requirement | Target |
|---|---|---|
| NFR-01 | /dql-optimize end-to-end latency, grounded |
< 60 s p50 |
| NFR-02 | /dql-review on a 50-file diff |
< 30 s |
| NFR-03 | CI lint gate on a typical PR | < 90 s |
| NFR-04 | MCP schema tool response (cached) | < 500 ms |
| NFR-05 | Ambient instruction context cost | < 4 000 tokens |
| NFR-06 | Linter false-positive rate on the calibration corpus | < 5% |
| NFR-07 | Semantic-equivalence failures reaching a PR | 0 |
| Concern | Control |
|---|---|
| Repository content exposure to the model | The MCP layer returns metadata, counts and timings only — never attribute values or content. Enforced server-side, not by prompt. |
| Credential handling | mcp.json inputs prompts and environment variables; no secrets in version control; org secrets for the cloud agent. |
| Privilege escalation | Read-only account; statement-type allowlist parsed rather than pattern-matched; no EXECUTE exec_sql, no apply mutations. |
| Production impact | production: true interlock disables execution tools. T3 grounding is metadata and plan-cache only. |
| Prompt injection via repository content | The agent treats file and tool content as data, never as instructions; AGENTS.md states this explicitly. Tool results cannot alter the safety rules. |
| Auditability | Structured optimization records committed to the repository; MCP call log retained per the organization's standard. |
| Regulated environments (GxP, SOX) | Every change reaches production through the normal PR, review and release process. The agent proposes; humans approve. No autonomous production change is possible by construction. |
Documentum Query Guild — a small standing group (2–4 people: platform architect, senior developer, DBA) that owns:
- The rule catalogue: additions, severity changes, and the quarterly review.
- The
repo-facts.instructions.mdfor each environment. - Adjudication of suppressions and of
majorfindings in review. - The calibration corpus (Section 12.4).
Change control. Rule catalogue changes are pull requests to this repository, reviewed by the guild, released with semantic versioning. A severity increase to blocker requires a two-week major-only grace period first, to avoid blocking unrelated work.
Feedback loop. Every rejected suggestion and every false positive is logged as an issue against the catalogue. This is the mechanism by which the asset improves; without it, the rules ossify and developers route around them.
A versioned set of ≥50 real DQL statements from the estate, each with: the original, the expert-approved optimization, the measured improvement, and the rule IDs that should fire. Used to (a) regression-test the linter, (b) evaluate instruction and prompt changes before release, and (c) quantify the capability's accuracy for stakeholders. This corpus is the difference between a solution that improves and one that merely exists. Building it is a Phase 1 deliverable, not an afterthought.
| Phase | Duration | Scope | Exit criteria |
|---|---|---|---|
| P0 — Discovery & calibration | 2 weeks | Inventory DQL across target repositories. Extract top 100 slow queries from AWR/Query Store. Build the initial calibration corpus with the guild. Confirm CS/RDBMS versions per environment. | Corpus ≥50 statements with expert answers; baseline performance recorded |
| P1 — Knowledge layer (T1) | 2 weeks | Deploy instructions, prompts, AGENTS.md, linter and CI gate to one pilot repository. major-only enforcement. |
Linter FP rate <5% on corpus; pilot team trained; /dql-optimize in daily use |
| P2 — Grounding layer (T2) | 3–4 weeks | Build and deploy dctm-dql-mcp against DEV/TEST. Enable dql-optimizer and dql-index-advisor agents. |
Grounded optimization demonstrated end-to-end on 10 real queries with measured improvement |
| P3 — Enforcement & scale | 3 weeks | Promote top rules to blocker. Roll to all Documentum repositories (Option B org-level). Enable Copilot Code Review with the catalogue. |
Zero blocker-severity findings merged; ≥80% developer adoption |
| P4 — Backlog remediation | 6–8 weeks | Cloud agent works the ranked slow-query backlog issue by issue. Guild reviews each PR. | Top 50 production queries remediated and measured |
| P5 — Sustain | Ongoing | Quarterly catalogue review, corpus growth, new-joiner onboarding via the same assets. | Quarterly metrics report to sponsor |
Total to steady state: approximately 4 months, with measurable value from week 4.
Deliberately, P1 delivers value with no Documentum connectivity at all — this de-risks the programme, because the hardest approval (read access from developer tooling to a repository) is not on the critical path to the first demonstrable win.
| Metric | Baseline | Target | Source |
|---|---|---|---|
| Mean execution time, remediated query population | P0 measurement | −40% to −70% | dql_measure / AWR / Query Store |
| p95 execution time, top-50 production queries | P0 measurement | −50% | AWR / Query Store |
| Blocker-severity anti-patterns merged per month | P0 measurement | 0 | CI lint reports |
| New DQL passing lint first time | — | >85% by P3 | CI lint reports |
| Mean time to diagnose a query performance incident | P0 measurement | −60% | Incident tickets |
/dql-optimize invocations per developer per week |
0 | ≥3 by P3 | Copilot usage telemetry |
| Suggestion acceptance rate | — | >70% | Guild feedback log |
| Semantic-equivalence failures reaching production | — | 0 | Incident and release records |
Reported monthly to the sponsor; reviewed quarterly by the guild.
| ID | Risk | Impact | Likelihood | Mitigation |
|---|---|---|---|---|
| R1 | Model proposes a rewrite that changes the result set | High | Medium | Mandatory semantic-delta section; automated equivalence check in the harness; human PR approval; blocker rule on unverified rewrites |
| R2 | Copilot customization file formats change | Medium | Medium | Formats are stable but evolving (e.g. .chatmode.md → .agent.md). Guild reviews against current docs quarterly; content is portable Markdown, so migration is mechanical |
| R3 | MCP connectivity to Documentum is refused by security | Medium | Medium | T1 delivers value with no connectivity; T2 is scoped to non-production, read-only, metadata-only returns — designed to be approvable |
| R4 | Developers ignore or route around the capability | High | Medium | Ambient layer requires no behaviour change; CI gate makes the floor non-optional; guild feedback loop keeps rules credible |
| R5 | Rule catalogue drifts from platform reality after a CS upgrade | Medium | Medium | dctm_repo_profile version-gates rules; upgrade checklist includes a corpus re-run |
| R6 | Hint over-application freezes plans and degrades over time | Medium | Medium | Hints are step 7 of 8 in the method, require a comment with an expiry review date, and are a major lint finding when unjustified |
| R7 | Copilot licence coverage insufficient | Low | Low | Confirm in P0; the linter and catalogue deliver value even without Copilot |
- GitHub Copilot Business or Enterprise licences are available to the target developers, with MCP permitted by organization policy.
- Source-controlled repositories exist for the Documentum application code. (Where DQL lives only in a database or a D2 configuration UI, an export step is required — identified in P0.)
- A non-production Documentum repository representative of production schema is available for T2.
- A DBA or platform engineer is available to act on index and statistics recommendations.
- Read-only Documentum account provisioning (P2).
- AWR or Query Store access for baseline and prioritization (P0).
- Guild members' time: ~4 hours/week during P0–P3, ~2 hours/month thereafter.
dql-copilot-optimizer/
├── README.md Quick start and 5-minute demo
├── SPEC.md This document
├── AGENTS.md Cross-tool agent contract
├── .github/
│ ├── copilot-instructions.md L1 — always-on core
│ ├── instructions/
│ │ ├── dql-core.instructions.md DQL semantics guardrails
│ │ ├── dql-oracle.instructions.md Oracle translation & plans
│ │ ├── dql-sqlserver.instructions.md SQL Server translation & plans
│ │ ├── dfc-java.instructions.md DFC/DFS client patterns
│ │ ├── d2-xplore.instructions.md D2 config & full-text routing
│ │ └── repo-facts.instructions.md Per-deployment facts (EDIT THIS)
│ ├── prompts/
│ │ ├── dql-optimize.prompt.md /dql-optimize
│ │ ├── dql-review.prompt.md /dql-review
│ │ ├── dql-explain-plan.prompt.md /dql-explain-plan
│ │ ├── dql-index-advisor.prompt.md /dql-index-advisor
│ │ ├── dql-to-ftdql.prompt.md /dql-to-ftdql
│ │ ├── dql-regression-harness.prompt.md /dql-regression-harness
│ │ ├── dql-batch-triage.prompt.md /dql-batch-triage
│ │ └── dql-explain-to-dba.prompt.md /dql-explain-to-dba
│ ├── skills/
│ │ ├── dql-optimization-method/ The seven-step method (§11), model-invoked
│ │ ├── dql-antipattern-catalogue/ Rule index & triage procedure
│ │ ├── documentum-schema-model/ Join-set computation procedure
│ │ └── dql-plan-reading/ Plan → DQL cause and effect
│ ├── agents/
│ │ ├── dql-optimizer.agent.md Flagship optimization agent
│ │ ├── dql-reviewer.agent.md Read-only reviewer
│ │ ├── dql-index-advisor.agent.md Index/statistics advisor
│ │ ├── dql-benchmark-runner.agent.md Measurement agent
│ │ └── dql-migration-scout.agent.md Codebase sweep & backlog
│ ├── ISSUE_TEMPLATE/dql-slow-query.yml Cloud-agent intake
│ └── workflows/
│ ├── dql-lint.yml CI gate (SARIF)
│ └── copilot-setup-steps.yml Cloud agent environment
├── .vscode/mcp.json MCP wiring
├── docs/
│ ├── dql-antipatterns.md Rule catalogue (human-readable SSOT)
│ ├── dql-hints.md ENABLE hint reference & policy
│ ├── documentum-schema-model.md The hidden physical model
│ ├── baseline-and-metrics.md Measurement method
│ └── governance.md Guild charter & change control
├── tools/
│ ├── dql_lint.py Deterministic rule engine
│ └── rules.yaml Machine-readable catalogue
├── mcp/dctm-dql-mcp/
│ ├── README.md Build & deploy
│ ├── pyproject.toml Package metadata & entry point
│ ├── dctm_dql_mcp/
│ │ ├── policy.py Config + safety layer (no MCP dependency)
│ │ └── server.py MCP tool surface
│ └── dctm-mcp.config.example.json Repository/environment config
├── samples/ Before/after calibration examples
└── tests/ Linter and MCP-policy unit tests
Prepared for Vivek. All Documentum behavioural statements should be re-verified against the OpenText Documentum Server DQL Reference and Performance Tuning guides for the specific Content Server version in your estate; docs/ marks version-sensitive claims explicitly.