Legacy Field Guide: Oracle and Db2
Updated
IntermediateWhat you'll learn
~55 min- Build a dependents inventory from catalog snapshots (the database's own list of what refers to what), cite every row to its source line, and send what the catalog cannot see to the unknowns file
- State what an inventory proves (coverage of the query's output) and what it does not (completeness), and name the gaps it leaves
- Map a COBOL copybook (the shared file that declares a record's field layout) into offsets and byte lengths, and slice golden file-in records into raw fields
- Plan an Oracle schema move with Data Pump (Oracle's bulk export and import utility) and rehearse the way back before the way forward
Lessons 22 to 25 worked on an intake office whose logic sat in classic ASP pages and SQL Server stored procedures. Plenty of offices have the same problem on other stacks. The rules live in PL/SQL (Oracle’s procedural language, stored in the database) packages and triggers, or in COBOL batch programs that read and write Db2 tables and fixed-width files. The method does not change: read and record before you touch anything (lesson 22), prove parity side by side (lessons 23 and 25), keep the database as the landing place (lesson 24). What changes is where you look, and which questions the database’s own catalog can answer.
This lesson gives you both halves of that, runnable without an Oracle or Db2 server. An inventory builder reads committed snapshots of the two catalogs and writes lesson 24’s dependents table. A copybook reader turns a COBOL record layout into a field map that lesson 30 uses to decode records. Both run on a laptop from fixtures; the optional local runtimes come near the end.
Before the detail — Artifact: modernization/db-dependents.md built from catalog snapshots with every row cited, the unknowns it could not resolve (U-30 to U-34), and a copybook field map with golden file-in records sliced into raw fields. Acceptance test: every row the builder read is cited, skipped with a reason, or sent to unknowns; a row deleted from the snapshot changes the committed table and fails the build by name; a copybook whose PIC clause is one digit too long fails every record on length. Status: reusable guidance. The catalog views are vendor documentation; the builder, the fixtures and the rules R-30 and R-31 are this module’s.
Catalog rows name your tables, packages and jobs, and job actions can carry paths and credentials in their text. Treat a snapshot taken from a real database as you would the restore in lesson 24: inside the authorization boundary, under the CUI (controlled unclassified information; in a commercial shop, think customer PII under contract) determination from lesson 01, and read for secrets before it goes in a prompt. Golden records for the COBOL half hold case data. Record them from synthetic or masked input, as the fixtures here do.
Where the logic hides
Start from the question lesson 22 asked of an ASP page: where do the rules live? On these stacks the answer is a list of places, and the first job is to visit each one. The lists below are “where to look”, drawn from how these systems are commonly built; nothing here is a cited claim about your estate, and your own catalog is the authority.
| Stack | Look in | Why it matters for parity |
|---|---|---|
| Oracle | package specifications and bodies | the main rule code; a body can change without its specification changing |
| Oracle | triggers on the core tables | fire for every writer, including your new one |
| Oracle | views | a view that selects * freezes its table’s column order (lesson 24) |
| Oracle | scheduler jobs | nightly exports and purges that nothing else mentions |
| Oracle | Forms triggers and program units inside .fmb files | rules in the screen layer, outside the database |
| Db2 and COBOL | COBOL programs and copybooks | the rules, and the record layouts shared between programs |
| Db2 and COBOL | JCL (the job control language that runs a mainframe batch job) steps and procedure overrides | sort steps, utility steps and file chains that change data between programs |
| Db2 and COBOL | CICS (IBM’s online transaction monitor) maps and transaction definitions | the screen and the input checks the user sees |
| Db2 and COBOL | stored procedures and triggers | rules kept inside the database |
Each row is a module in lesson 22’s sense, and each gets its own reader. The order matters less than finishing the list: a rule that lives in a place nobody opened is an unknown you did not write down.
Ask the catalog first
Plan the change with your agent
Open your agent in the project root and switch to Plan mode (Shift+Tab, /plan, or the mode picker; the other tool spellings are in lesson 02’s dated table). Ask it to read the Oracle and Db2 catalog snapshots and plan the dependents inventory before writing any files:
Read the catalog snapshots in fixtures/field-guide/data-tier/ and plan modernization/db-dependents.md for INTAKE, INTAKE_AUDIT, and RPT_DAILY without editing anything yet. Ask me anything unclear before writing code.What to check in the plan before you approve
- Every row in
modernization/db-dependents.mdcites the exactfile:lineof the CSV snapshot row it came from (oracle-all-dependencies.csv,oracle-view-text.csv,oracle-scheduler-jobs.csv,db2-syspackdep.csv). - Transitive dependents (such as a package or scheduler job that references a view or package over
INTAKE) are included, while self-references (package body -> package spec) and out-of-scope rows are recorded as skipped with a reason. - Anything the catalog snapshot cannot resolve locally (database links, calls to
UTL_FILEorDBMS_SQL, OS shell-script scheduler jobs, or unrecognized Db2 object types likeBTYPE Aaliases) is routed tounknowns.md(U-30..U-34), never guessed into the dependents table. - The builder prints a coverage line accounting for every snapshot row read and explicitly lists what catalog queries cannot see by construction (dynamic SQL, external schedulers, and direct external client connections).
If the agent’s plan treats an Oracle dependency row with a REFERENCED_LINK_NAME as a dependent on the local INTAKE table, correct it before approving:
Do not list a row with REFERENCED_LINK_NAME as a local dependent. Send database-link references to unknowns with the DBA as owner.Oracle documents ALL_DEPENDENCIES as the view that “describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user”, with columns for owner, name, type, the referenced owner, name and type, an optional link name and a dependency type of REF or HARD (ALL_DEPENDENCIES). Its siblings USER_DEPENDENCIES and DBA_DEPENDENCIES have the same shape. IBM documents SYSIBM.SYSPACKDEP for Db2 for z/OS as the table that “records the dependencies of packages on local tables, views, synonyms, table spaces”, keyed by the object (BNAME, BTYPE) and the package (DCOLLID, DNAME) (SYSPACKDEP). The page also says that some dependencies “are recorded in the SYSPACKDEP catalog table, but they are not shown in EXPLAIN output”, a reminder that the catalog and the access plan are different views of the same package. Plans, routines, triggers and the Db2 LUW equivalents live in sibling catalog tables and views; look for them by name in your release’s documentation.
Both views are built from names resolved when code is compiled. That is why they miss dynamic SQL (SQL assembled and run as a string, such as Oracle’s EXECUTE IMMEDIATE) and everything outside the database. That limit is inferred from how the catalog works, as it was for SQL Server’s catalog in lesson 24, not quoted from a page.
The builder
The snapshots are CSV files in the columns the views have. Here is the reference CSV reader excerpt from gov-dev-reference-app, which cites by position so every table row can be traced to a line (compare with your agent’s implementation; you do not type it by hand):
// src/modernization/field-guide/data-tier/inventory.ts (excerpt)/** Minimal RFC 4180 reader: quoted fields may hold commas and newlines. The cite is file:line of the record's first line. */export function parseCsv(file: string, text: string): Cited[] {// ... const [header, ...body] = records; return body.map((r) => ({ cite: `${file}:${r.line}`, row: Object.fromEntries(header.fields.map((h, i) => [h, r.fields[i] ?? ''])), }));}Three decisions in the Oracle half carry most of the weight. A reference through a database link is not a dependent on the local table, even when the remote object has the same name. A dependent of a dependent is a dependent (a package that reads a view over INTAKE is affected by changes to INTAKE). And a call to a package that opens a door the catalog cannot see through goes to unknowns, so the table does not look more complete than it is:
REFERENCE CODE src/modernization/field-guide/data-tier/inventory.ts (excerpt): transitive dependent resolution and unknown routing open reference hide reference Reference implementation from gov-dev-reference-app; open to inspect or compare with what your agent builds.
// src/modernization/field-guide/data-tier/inventory.ts (excerpt) for (let grew = true; grew;) { // a dependent of a dependent is a dependent grew = false; for (const { row } of o.dependencies) { if (live(row) && watched.has(ref(row)) && !watched.has(name(row))) { watched.add(name(row)); sourced.add(name(row)); grew = true; } } }// ... if (row.REFERENCED_LINK_NAME) { unknown(`${name(row)} reaches ${ref(row)} across database link ${row.REFERENCED_LINK_NAME}; the remote object is not in this catalog`, [cite], 'the DBA who owns the link'); } else if (name(row) === ref(row)) skip(cite, 'a body referring to its own specification'); else if (!watched.has(name(row))) skip(cite, 'outside scope'); else if (row.REFERENCED_OWNER === 'SYS' && DOORS[row.REFERENCED_NAME]) { unknown(`${name(row)} calls ${row.REFERENCED_NAME}, which ${DOORS[row.REFERENCED_NAME]}`, [cite], 'the package owner'); } else if (row.REFERENCED_OWNER === 'SYS') skip(cite, 'system package'); else if (watched.has(ref(row))) {The scheduler jobs are a separate snapshot, one row per job, because the dependency view does not list them. A job whose action names a package that is already a dependent joins the table; a job that runs an operating-system command is an unknown, because the database cannot say what the command touches.
What the catalog returned
Run the builder over the fixtures:
node src/modernization/field-guide/data-tier/cli.ts fixtures/field-guide/data-tier outThe dependents it wrote, in lesson 24’s columns:
| Dependent | Kind | Touches | R/W | Shape risk | Evidence |
|---|---|---|---|---|---|
INTBATCH.INTLOAD | Db2 package | INTAKE, INTAKE_AUDIT | ? | bound SQL not read here | db2-syspackdep.csv:2, db2-syspackdep.csv:3 |
INTBATCH.INTRPT | Db2 package | RPT_DAILY | ? | bound SQL not read here | db2-syspackdep.csv:7 |
INTCICS.INTQ01 | Db2 package | INTAKE | ? | bound SQL not read here | db2-syspackdep.csv:6 |
INTAKE.PKG_EXPORT | Oracle package body | INTAKE | ? | body not read here | oracle-all-dependencies.csv:8 |
INTAKE.PKG_INTAKE | Oracle package body | INTAKE, INTAKE_AUDIT | ? | body not read here | oracle-all-dependencies.csv:2, oracle-all-dependencies.csv:3 |
INTAKE.PKG_REPORTS | Oracle package body | via INTAKE.RPT_DAILYINTAKE | ? | body not read here | oracle-all-dependencies.csv:11 |
INTAKE.INTAKE_NIGHTLY_EXPORT | Oracle scheduler job | via INTAKE.PKG_EXPORT | ? | runs INTAKE.PKG_EXPORT.RUN_NIGHTLY | oracle-scheduler-jobs.csv:2 |
INTAKE.PURGE_REPORT_TEMP | Oracle scheduler job | via INTAKE.PKG_REPORTS | ? | runs BEGIN INTAKE.PKG_REPORTS.PURGE_TEMP; END; | oracle-scheduler-jobs.csv:3 |
INTAKE.TRG_INTAKE_TOUCH | Oracle trigger | INTAKE | ? | fires for every writer | oracle-all-dependencies.csv:6 |
INTAKE.RPT_DAILYINTAKE | Oracle view | INTAKE | R | SELECT * | oracle-all-dependencies.csv:7, oracle-view-text.csv:2 |
The R/W column is ? for everything except the view. Neither catalog records whether a dependent reads or writes; a package body has to be read for that, which is the next step and a job for a subagent per package. And the five rows in the unknowns output that the catalog could not place:
- U-30: INTAKE.PKG_EXPORT calls UTL_FILE, which writes or reads files outside the database. Evidence: oracle-all-dependencies.csv:10. Who can say: the package owner.- U-31: INTAKE.PKG_REPORTS calls DBMS_SQL, which builds SQL at run time, so the tables it touches are not in the catalog. Evidence: oracle-all-dependencies.csv:13. Who can say: the package owner.- U-32: INTAKE.PKG_REPORTS reaches INTAKE.INTAKE across database link HQ_LINK; the remote object is not in this catalog. Evidence: oracle-all-dependencies.csv:14. Who can say: the DBA who owns the link.- U-33: scheduler job INTAKE.PUSH_TO_SFTP runs /opt/intake/push_export.sh, which the database cannot describe. Evidence: oracle-scheduler-jobs.csv:4. Who can say: whoever owns the schedule.- U-34: package INTBATCH.INTXTRA depends on INTAKE.INTAKE_ALIAS, a BTYPE A object the snapshot cannot resolve to a table. Evidence: db2-syspackdep.csv:8. Who can say: the DBA who owns the alias.Two of these deserve a second look. U-30 and U-33 together describe a file leaving the building: the package writes it, a shell script pushes it. Neither is visible from the other, and lesson 24’s Intake_Nightly_Export (U-08) is the SQL Server version of the same shape. And PKG_REPORTS appears in the table because it reads a view, and in the unknowns because it also builds SQL at run time. Both are true, and the table row alone would have understated it.
The builder output ends by saying what it covered:
Coverage of the query output: 28 rows read, 10 dependents, 5 unknowns, 10 rows skipped with a reason.Not covered, by construction:- dynamic SQL (EXECUTE IMMEDIATE, PREPARE) leaves no catalog row- external schedulers and shell scripts are not in either catalog- application code, reports and spreadsheets that connect directly are not in either catalogThat is the honest claim. The inventory proves it accounted for every row the queries returned. It does not prove the queries returned every dependency, and a dependent that never appears in a catalog row, such as a spreadsheet with a saved connection, is exactly the kind lesson 24 found through session capture and still left open as U-09. The reference check in gov-dev-reference-app that enforces the first claim is short (it counts each row once, so a row cited and also listed as an unknown cannot stand in for a row nobody accounted for):
// src/modernization/field-guide/data-tier/inventory.ts (excerpt)/** Every row read is cited by a dependent, skipped with a reason, or named in an unknown. Counted by row, once: a row in two sets must not hide a row in none. */export function accounted(inv: Inventory): number { return new Set([...inv.dependents.flatMap((d) => d.evidence), ...inv.unknowns.flatMap((u) => u.evidence), ...inv.skipped.map((k) => k.cite)]).size;Where the Oracle data goes: Data Pump first
The dependents table says what must survive a move. The move itself, for an Oracle schema, has a baseline. AWS’s guidance for its managed Oracle service calls Data Pump “the recommended way to move large amounts of data from an Oracle database” to the service, and the rules that follow from that page are the ones to write into your runbook (Importing using Oracle Data Pump):
- Import in schema or table mode. Do not import in full mode: the managed service gives you no
SYSorSYSDBAaccess. - Dump files made with
TRANSPORT_TABLESPACESor the transportable option are not supported. - Exclude the Scheduler objects owned by
SYS,SYSTEMandRDSADMINwith aPROCOBJfilter. Scheduler jobs owned by your application schema travel; the system ones do not. Read the page’s exact filter before you copy one, because a bareEXCLUDE=PROCOBJis wider than that sentence. - Take a snapshot before a large import.
- Get the dump file there by one of two routes: from an S3 bucket with
rdsadmin.rdsadmin_s3_tasks.download_from_s3intoDATA_PUMP_DIR, or over a database link withDBMS_FILE_TRANSFER.
An export of the intake schema looks like this (illustrative; check the parameter names against your database’s version):
# illustrative export parameter file, not part of the referenceSCHEMAS=INTAKEDIRECTORY=DATA_PUMP_DIRDUMPFILE=intake_%U.dmpLOGFILE=intake_export.logWhatever the target, the lesson 24 proof applies unchanged: row counts and content checks on both sides, from the same backup, before anyone writes to the target. The dependents table then tells you what else to bring: the scheduler jobs that travel, the system ones that do not, and U-33’s shell script that was never in the dump.
The way back, rehearsed
A migration plan has a forward path and usually an untested return. This module’s practice is to run the return before the cutover and time it:
- Freeze the source: leave it intact and read-only for the cutover window, and take the snapshot.
- Cut over, and record the time.
- Rehearse the cutback on a copy: flip the route back to the legacy system and confirm it takes writes.
- Decide the fate of what the target accepted in the window. Either export it back (the same Data Pump run in the other direction, for a schema the legacy side can still take) or accept it as lost. Both are decisions with an owner, and neither is made during an incident.
The cutback is rehearsed when someone has timed it, and the number is in the runbook. This is this module’s guidance rather than a published standard.
Where the batch data goes: the copybook
On the COBOL side the file layout is the interface. A copybook (a shared source file declaring a record’s fields) tells every program which bytes are which field. The reference legacy copybook in the sample fixtures describes the nightly intake export (for your agent to inspect; you do not type it by hand):
* INTAKE-MASTER: one fixed-width record per case, nightly export. 01 INTAKE-REC. 05 IR-CASE-ID PIC 9(8). 05 IR-CASE-TYPE PIC X(10). 05 IR-STATUS PIC X. 05 IR-SUBMITTED PIC 9(8). 05 IR-HOUSEHOLD PIC 99. 05 IR-DAYS-WAITING PIC 9(3). 05 IR-SCORE PIC S9(5)V99 COMP-3. 05 IR-DISCOUNT PIC S9(3)V99 USAGE COMP-3. 05 IR-FILLER PIC X(5).PIC X(10) is ten characters of text. PIC 9(8) is eight digit characters. PIC S9(5)V99 COMP-3 is a signed packed decimal (two digits per byte, the sign in the last half-byte) with five digits before the implied decimal point and two after; the V takes no space in the record. A packed field of seven digits occupies four bytes, which is why the lengths have to be computed rather than read off the picture. This lesson maps the layout and slices records into raw fields. Decoding the packed bytes and the EBCDIC text is lesson 30, because that is where a wrong decode becomes a parity mismatch. Here is the reference copybook parser excerpt in src/modernization/field-guide/data-tier/copybook.ts from gov-dev-reference-app (compare with what your agent builds):
// src/modernization/field-guide/data-tier/copybook.ts (excerpt) if (level === '88') return; // condition names occupy no bytes if (/\b(OCCURS|REDEFINES|RENAMES|DEPENDING)\b/i.test(rest)) throw new CopybookError(`line ${line}: ${name} uses OCCURS or REDEFINES, which this reader does not map; list it as an unknown`); const pic = rest.match(/\bPIC(?:TURE)?\s+(?:IS\s+)?(\S+)/i); if (!pic) return; // a group item occupies the bytes of its children// ... if (/\b(COMP(?:-[1245])?|COMPUTATIONAL\S*|BINARY|POINTER)\b/i.test(usage) && !USAGE.test(usage)) throw new CopybookError(`line ${line}: ${name} uses a binary usage this reader does not map`); const packed = USAGE.test(usage); const length = packed ? Math.floor(p.digits / 2) + 1 : p.alnum || p.digits; fields.push({ name, line, pic: pic[1], kind: packed ? 'packed' : p.alnum ? 'alnum' : 'display', digits: p.digits, scale: p.scale, signed: p.signed, offset, length }); offset += length; }); return { fields, recordLength: offset };The reader refuses what it cannot map: OCCURS, REDEFINES, and binary usages stop it with the line number, because a guessed offset corrupts every field after it. A refusal is an unknown for the next reader, not a gap to fill. The field map:
| Field | PIC | Kind | Offset | Bytes | Decimals |
|---|---|---|---|---|---|
| IR-CASE-ID | 9(8) | display | 0 | 8 | 0 |
| IR-CASE-TYPE | X(10) | alnum | 8 | 10 | 0 |
| IR-STATUS | X | alnum | 18 | 1 | 0 |
| IR-SUBMITTED | 9(8) | display | 19 | 8 | 0 |
| IR-HOUSEHOLD | 99 | display | 27 | 2 | 0 |
| IR-DAYS-WAITING | 9(3) | display | 29 | 3 | 0 |
| IR-SCORE | S9(5)V99 | packed | 32 | 4 | 2 |
| IR-DISCOUNT | S9(3)V99 | packed | 36 | 3 | 2 |
| IR-FILLER | X(5) | alnum | 39 | 5 | 0 |
| record | 44 |
Golden cases for a batch job are files: the input file the job read and the output file it wrote, recorded from the legacy run. The input records live in the fixtures as hex, one per line, so a diff shows them and packed bytes that look like line breaks do not break the file. The reference slicer in gov-dev-reference-app refuses a record of the wrong length before it reads any field:
// src/modernization/field-guide/data-tier/copybook.ts (excerpt)export function sliceRecord(fields: Field[], recordLength: number, record: Buffer, index: number): RawRecord { if (record.length !== recordLength) throw new CopybookError(`record ${index}: ${record.length} bytes, copybook says ${recordLength}`);Here is a reference output record sliced into raw fields, with the packed field left as hex for lesson 30 to decode:
{ "IR-CASE-ID": "00004212", "IR-CASE-TYPE": "EMERGENCY ", "IR-STATUS": "N", "IR-SUBMITTED": "20261101", "IR-HOUSEHOLD": "12", "IR-DAYS-WAITING": "053", "IR-SCORE": "0001250C", "IR-DISCOUNT": "00500D", "IR-FILLER": " "}The text fields keep their padding ("EMERGENCY ", with its trailing space), because trimming is a rule the legacy program applies and the golden record must not apply it silently. 0001250C and 00500D are the packed bytes; the final character is the sign half-byte.
One reader per module
One agent reading both stacks at once mixes PL/SQL with COBOL and the citations drift. Apply lesson 22’s split: approve the plan, then give each legacy module its own subagent (a helper session with its own context window) writing only modernization/inventory/<module>.md under citation-or-unknown, and run one merge pass that may not add a rule. For this estate the modules are the package bodies (one subagent per package, reading the body for reads and writes, which fills the ? column), the triggers and views, the scheduler jobs, the COBOL programs, the JCL, and the copybooks. Module 19 lesson 5 (subagent, then background, then worktree ladder) teaches the orchestration; lesson 22 carries the rule that a subagent’s output is a draft until the checker passes, and the per-tool subagent file locations.
The merge pass is also where the catalog and the code meet. A package body that the subagent reads as touching a table the catalog did not list is a finding about the catalog’s blind spot (U-31 is the example), and it goes back into the inventory with the body’s line as its citation.
The pattern
| Problem | The pattern | Where your platform lesson lands it | You decide |
|---|---|---|---|
| Logic hides in places an app-code search never opens | read the catalog, then every place in the stack’s list, one reader per module | 34, 35, 36: the same inventory runs against whatever managed or self-run database you land on | which places on the list your estate actually has |
| The catalog is incomplete by construction | inventory = coverage of the query output; dynamic SQL, external schedulers and unseen clients are named gaps | 37: the pipeline fails a build whose committed table differs from the snapshot | who owns each U-nn and by when |
| Moving an Oracle schema | Data Pump in schema mode as the baseline, with the target’s rules | 35: the S3 route into the managed database’s Data Pump directory · 34, 36: see the status box before planning an Oracle target | the target engine and who holds its licence |
| A mainframe record is the interface | copybook field map, golden file-in records sliced raw, lengths checked first | 35: the AWS mainframe path in the status box · 34, 36: no first-party mainframe service confirmed | which programs to keep running as they are |
| Nobody has run the way back | a timed, rehearsed cutback before cutover | 34, 35, 36: the route switch is the same flag in each | what happens to writes the target took in the window |
Rules and unknowns
Two decisions here are rules, so they go in rules.md next to R-08 to R-11, each with a decision file under modernization/decisions/ whose line 4 holds the decision:
| ID | Rule | Citation | Side effects |
|---|---|---|---|
| R-30 | a dependents-table row exists only with a cited snapshot row; anything the catalog cannot resolve is an unknown, never a row | decisions/D-30-inventory-coverage.md:4 | none |
| R-31 | a copybook field map is accepted only when its record length equals the length of every golden record | decisions/D-31-record-length.md:4 | none |
Their tests of record are named in the reference tested-elsewhere.json from gov-dev-reference-app, as lesson 24 did for R-08 to R-10:
{ "R-30": { "file": "tests/unit/field-guide-data-tier.test.ts", "name": "accounts for every row it read" }, "R-31": { "file": "tests/unit/field-guide-data-tier.test.ts", "name": "refuses a record whose length is not the copybook length" }}The unknowns are U-30 to U-34 above. U-32, the database link, is the one most likely to hide a second system: a remote table nobody listed.
Watch it work
- Typed in the terminal:
claude - Mode:Plan mode (Shift+Tab)
- You type in the session: Read the catalog snapshots in fixtures/field-guide/data-tier/ and plan the dependents table for INTAKE, INTAKE_AUDIT and RPT_DAILY. Write nothing yet.
- The tool replies: Plan: 1. Parse oracle-all-dependencies.csv, oracle-view-text.csv, oracle-scheduler-jobs.csv, db2-syspackdep.csv; cite every row as file:line. 2. Account for every row at least once: a dependent, a skip with a reason, or an unknown (a row can be both a dependent and an unknown; it is counted once). 3. Send database-link rows, calls to UTL_FILE and DBMS_SQL, shell-script jobs and unrecognized Db2 object types to unknowns from U-30. 4. State the gaps the catalog cannot cover. One subagent per package body for the R/W column, after this step. Approve?
- You type in the session: Approved. Build it and show me the coverage line.
- The tool replies: node src/modernization/field-guide/data-tier/cli.ts fixtures/field-guide/data-tier out Coverage of the query output: 28 rows read, 10 dependents, 5 unknowns, 10 rows skipped with a reason. Not covered, by construction: - dynamic SQL (EXECUTE IMMEDIATE, PREPARE) leaves no catalog row - external schedulers and shell scripts are not in either catalog - application code, reports and spreadsheets that connect directly are not in either catalog Field map: 9 fields, 44 bytes per record. npx vitest run tests/unit/field-guide-data-tier.test.ts Tests 15 passed (15)
- You type in the session: Delete the trigger's row (line 7) from oracle-all-dependencies.csv and re-run the tests. Then restore it.
- The tool replies: npx vitest run tests/unit/field-guide-data-tier.test.ts x writes the dependents table in lesson 24 shape, every row cited x sends what the catalog cannot see to unknowns, numbered from U-30 x does not treat a row through a database link as a dependent on the local table Tests 3 failed | 12 passed (15) The first failure names the missing row: the committed table has INTAKE.TRG_INTAKE_TOUCH, the rebuilt one does not. Every later citation also moved up a line. The row-accounting test stayed green. Restored the file; Tests 15 passed (15).
- You type in the session: Now change IR-SUBMITTED in the copybook from PIC 9(8) to PIC 9(9) and run the copybook tests. Restore it after.
- The tool replies: npx vitest run tests/unit/field-guide-data-tier.test.ts x maps offsets and lengths, with packed fields sized from their digits x slices every golden record into raw fields, packed fields as hex x refuses a record whose length is not the copybook length x one digit more in a PIC clause moves every later offset and fails every record Error: record 1: 44 bytes, copybook says 45 Tests 4 failed | 11 passed (15) One digit moved every later offset by one. The length check fails before any field is read, which is the point of R-31. Restored the copybook.
Illustrative session; exact output varies by tool and agency configuration.
The test output and the builder’s output in this lesson come from running this code, type-checked, in gov-dev-reference-app; the planning and approval turns are condensed. No Oracle or Db2 server was involved: the snapshots are files we wrote in the shape of the catalogs’ columns, so they show the method and not the exact output of your release. Your own snapshot may carry more columns and a different spelling of the object types.
Prove it
From gov-dev-reference-app, with the pinned Node from lesson 02.
1. The suite passes. npx vitest run tests/unit/field-guide-data-tier.test.ts reports Tests 15 passed (15). Ten tests cover the inventory (quoted CSV fields and their line numbers, the committed table, the unknowns, the row accounting, a row cited twice, the database-link row, dependents of dependents and scheduler jobs, the SELECT * flag, the Db2 rows, and a row dropped from the snapshot) and five cover the copybook (the field map, the sliced golden records, the length check, the refusals, and the wrong-digit plant).
2. The inventory plant. Delete line 7 of fixtures/field-guide/data-tier/oracle-all-dependencies.csv (the trigger’s row). Expected: three tests fail, the first showing the committed table’s INTAKE.TRG_INTAKE_TOUCH row missing and the citations after it shifted by one. Notice what did not fail: the coverage accounting counts each row once, reads the rows it was given and still balances. A missing row is invisible to the inventory itself and visible only against the committed table, which is why the table is committed and reviewed.
3. The copybook plant. In intake-master.cpy, change IR-SUBMITTED PIC 9(8) to PIC 9(9). Expected: four tests fail, with record 1: 44 bytes, copybook says 45. Restore it.
4. Optional local runtimes. Neither is needed for the steps above.
- Oracle’s Free edition runs in a container. Oracle’s own image repository README lists its current Free build and the local tag
oracle/database:23.26.0-free; the registry path and current tag for the prebuilt image were not confirmed, so check Oracle’s registry page in a browser before you copy a pull command, and note that a pull may need an account. - GnuCOBOL 3.2 is the newest release in the GNU file listing as of the status date below. It compiles the copybook-and-program pairs on a laptop. Whether its embedded-SQL support covers your Db2 precompiler was not checked. A z/OS system cannot be reproduced locally.
The inventory builder reports 'N rows read, N accounted for' and lists no unknown for the nightly export. Does that prove nothing outside the database depends on the intake tables?
A row in the Oracle dependency snapshot says PKG_REPORTS references INTAKE with a database link name filled in. What does the inventory do with it, and why?
Availability, authorization, agency approval, region support, and price are five separate facts. Recheck each cited source and your agency’s authorization boundary before you rely on any of it.
- Oracle on AWS RDS. Supported releases are 19c, 21c and 26ai; 26ai is Enterprise Edition only and multitenant only (database versions). RDS is offered in both AWS GovCloud (US) regions; the GovCloud page says “Instance types and engine versions might vary in the AWS GovCloud (US) Regions” and does not list the Oracle engine’s versions there, so check the GovCloud RDS console (RDS in GovCloud).
- Db2 on AWS RDS. The same page says “Amazon RDS for Db2 is available with Bring Your Own License (BYOL) only.”
- Oracle Database@Azure. Microsoft’s region page lists commercial regions and no Azure Government region; the Azure Government audit-scope page (tables dated February 2026) has no Oracle Database@Azure row. Read that as “not listed”, not “unavailable” (regions, audit scope).
- Oracle Database@Google Cloud. The region page lists 17 live commercial regions and says nothing about Assured Workloads or government use (regions).
- Oracle Database@AWS. The documentation history through 2026-10-15 adds commercial regions only; a GovCloud region was not seen, which is unverified either way (doc history).
- Mainframe modernization. AWS Mainframe Modernization: “self-managed experience is no longer open to new customers”, and the managed runtime “has stopped accepting new customers” (availability change). AWS Transform lists “Modernize and migrate IBM z/OS and Fujitsu GS21 to AWS” (what is AWS Transform); its GovCloud status is unverified. Azure’s mainframe refactor reference describes partner tooling, not a first-party migration service (Architecture Center). A Google Cloud mainframe page could not be fetched, so none is cited.
- Oracle Forms and Reports. Oracle’s lifecycle pages could not be fetched. Read the dates in Oracle’s Lifetime Support Policy before promising a timeline for a Forms estate; the Forms Statement of Direction says dates live there.
- Local runtimes. Oracle’s repository README names the Free build as 26ai (23.26.0) (docker-images README); the registry tag is unverified. GnuCOBOL 3.2, released 2023-07-28, is the newest in the GNU listing (ftp.gnu.org). The Db2 Community Edition container’s image tag and resource limits were not retrieved.
Practice status — reading Oracle and Db2 estates before moving them
| Practice | Status | Also called |
|---|---|---|
| dependents table built from catalog views, every row cited | common baseline | dependency analysis; data lineage |
| inventory stated as coverage of the query output, with named gaps | strong optional | known unknowns register; discovery gaps |
| one reader per legacy module, merged by a pass that adds no rules | emerging | divide-and-merge discovery |
| Data Pump schema-mode export and import as the Oracle baseline | common baseline | logical export and import |
| copybook field map with record-length check before golden capture | strong optional | record layout contract; schema-on-read check |
| cutback rehearsed and timed before cutover | strong optional | rollback drill; reverse migration test |
Scale: required | common baseline | strong optional | reference-shop (seen only at organizations that publish their own practice) | emerging
Key takeaway
Oracle and Db2 estates hide their logic in packages, triggers, views, scheduler jobs, copybooks and JCL, and their catalogs list what they can resolve by name. Build the dependents table from snapshots of those catalogs with every row cited, send what they cannot resolve to unknowns, and say plainly that the table covers the query output and not the estate. Move an Oracle schema with Data Pump in schema mode under the target’s rules, and time the way back before you need it. Map each COBOL record from its copybook, check its length before reading a field, and slice golden records raw. Lesson 30 decodes them.
- LEADERSHIP DECISION
- name an owner and a date for every U-nn the catalog could not place; no cutover until the cutback has been run once and its time is in the runbook
- PRACTITIONER ACTION
- snapshot the dependency catalogs, run the builder, read each package body for the R/W column, and map every copybook before recording a single golden file
- SUCCESS MEASURE
- audit finding avoided: no dependent discovered after cutover that the inventory's gap list did not already name
Legend: LEADERSHIP DECISION = what a leader decides · PRACTITIONER ACTION = what the engineer does next · SUCCESS MEASURE = how you know, in calendar time, assessor-hours or cost, contract or revenue risk, or audit finding avoided