Applied AI-Accelerated Government Development

Legacy Field Guide: Oracle and Db2

Updated

Intermediate

What 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.

⚠A catalog snapshot is metadata, and still handled

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.

StackLook inWhy it matters for parity
Oraclepackage specifications and bodiesthe main rule code; a body can change without its specification changing
Oracletriggers on the core tablesfire for every writer, including your new one
Oracleviewsa view that selects * freezes its table’s column order (lesson 24)
Oraclescheduler jobsnightly exports and purges that nothing else mentions
OracleForms triggers and program units inside .fmb filesrules in the screen layer, outside the database
Db2 and COBOLCOBOL programs and copybooksthe rules, and the record layouts shared between programs
Db2 and COBOLJCL (the job control language that runs a mainframe batch job) steps and procedure overridessort steps, utility steps and file chains that change data between programs
Db2 and COBOLCICS (IBM’s online transaction monitor) maps and transaction definitionsthe screen and the input checks the user sees
Db2 and COBOLstored procedures and triggersrules 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.md cites the exact file:line of 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_FILE or DBMS_SQL, OS shell-script scheduler jobs, or unrecognized Db2 object types like BTYPE A aliases) is routed to unknowns.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:

Terminal window
node src/modernization/field-guide/data-tier/cli.ts fixtures/field-guide/data-tier out

The dependents it wrote, in lesson 24’s columns:

DependentKindTouchesR/WShape riskEvidence
INTBATCH.INTLOADDb2 packageINTAKE, INTAKE_AUDIT?bound SQL not read heredb2-syspackdep.csv:2, db2-syspackdep.csv:3
INTBATCH.INTRPTDb2 packageRPT_DAILY?bound SQL not read heredb2-syspackdep.csv:7
INTCICS.INTQ01Db2 packageINTAKE?bound SQL not read heredb2-syspackdep.csv:6
INTAKE.PKG_EXPORTOracle package bodyINTAKE?body not read hereoracle-all-dependencies.csv:8
INTAKE.PKG_INTAKEOracle package bodyINTAKE, INTAKE_AUDIT?body not read hereoracle-all-dependencies.csv:2, oracle-all-dependencies.csv:3
INTAKE.PKG_REPORTSOracle package bodyvia INTAKE.RPT_DAILYINTAKE?body not read hereoracle-all-dependencies.csv:11
INTAKE.INTAKE_NIGHTLY_EXPORTOracle scheduler jobvia INTAKE.PKG_EXPORT?runs INTAKE.PKG_EXPORT.RUN_NIGHTLYoracle-scheduler-jobs.csv:2
INTAKE.PURGE_REPORT_TEMPOracle scheduler jobvia INTAKE.PKG_REPORTS?runs BEGIN INTAKE.PKG_REPORTS.PURGE_TEMP; END;oracle-scheduler-jobs.csv:3
INTAKE.TRG_INTAKE_TOUCHOracle triggerINTAKE?fires for every writeroracle-all-dependencies.csv:6
INTAKE.RPT_DAILYINTAKEOracle viewINTAKERSELECT *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 catalog

That 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 SYS or SYSDBA access.
  • Dump files made with TRANSPORT_TABLESPACES or the transportable option are not supported.
  • Exclude the Scheduler objects owned by SYS, SYSTEM and RDSADMIN with a PROCOBJ filter. 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 bare EXCLUDE=PROCOBJ is 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_s3 into DATA_PUMP_DIR, or over a database link with DBMS_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 reference
SCHEMAS=INTAKE
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=intake_%U.dmp
LOGFILE=intake_export.log

Whatever 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:

  1. Freeze the source: leave it intact and read-only for the cutover window, and take the snapshot.
  2. Cut over, and record the time.
  3. Rehearse the cutback on a copy: flip the route back to the legacy system and confirm it takes writes.
  4. 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:

FieldPICKindOffsetBytesDecimals
IR-CASE-ID9(8)display080
IR-CASE-TYPEX(10)alnum8100
IR-STATUSXalnum1810
IR-SUBMITTED9(8)display1980
IR-HOUSEHOLD99display2720
IR-DAYS-WAITING9(3)display2930
IR-SCORES9(5)V99packed3242
IR-DISCOUNTS9(3)V99packed3632
IR-FILLERX(5)alnum3950
record44

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

ProblemThe patternWhere your platform lesson lands itYou decide
Logic hides in places an app-code search never opensread the catalog, then every place in the stack’s list, one reader per module34, 35, 36: the same inventory runs against whatever managed or self-run database you land onwhich places on the list your estate actually has
The catalog is incomplete by constructioninventory = coverage of the query output; dynamic SQL, external schedulers and unseen clients are named gaps37: the pipeline fails a build whose committed table differs from the snapshotwho owns each U-nn and by when
Moving an Oracle schemaData Pump in schema mode as the baseline, with the target’s rules35: the S3 route into the managed database’s Data Pump directory · 34, 36: see the status box before planning an Oracle targetthe target engine and who holds its licence
A mainframe record is the interfacecopybook field map, golden file-in records sliced raw, lengths checked first35: the AWS mainframe path in the status box · 34, 36: no first-party mainframe service confirmedwhich programs to keep running as they are
Nobody has run the way backa timed, rehearsed cutback before cutover34, 35, 36: the route switch is the same flag in eachwhat 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:

IDRuleCitationSide effects
R-30a dependents-table row exists only with a cited snapshot row; anything the catalog cannot resolve is an unknown, never a rowdecisions/D-30-inventory-coverage.md:4none
R-31a copybook field map is accepted only when its record length equals the length of every golden recorddecisions/D-31-record-length.md:4none

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

SESSION Plan, build the inventory, then plant two faults
  1. Typed in the terminal: claude
  2. Mode:Plan mode (Shift+Tab)
  3. 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.
  4. 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?
  5. You type in the session: Approved. Build it and show me the coverage line.
  6. 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)
  7. 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.
  8. 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).
  9. 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.
  10. 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.
KNOWLEDGE CHECK

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?

KNOWLEDGE CHECK

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?

ℹStatus checked 2026-11-05

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

PracticeStatusAlso called
dependents table built from catalog views, every row citedcommon baselinedependency analysis; data lineage
inventory stated as coverage of the query output, with named gapsstrong optionalknown unknowns register; discovery gaps
one reader per legacy module, merged by a pass that adds no rulesemergingdivide-and-merge discovery
Data Pump schema-mode export and import as the Oracle baselinecommon baselinelogical export and import
copybook field map with record-length check before golden capturestrong optionalrecord layout contract; schema-on-read check
cutback rehearsed and timed before cutoverstrong optionalrollback 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

Search lessons