paulasilvatech DB2 to PostgreSQL migration
Turn inspected DB2 and COBOL record structure into a relational schema that preserves precision, occurrence semantics, and access paths, and prove the result with reconciliation numbers instead of assertions.
When to invoke
- "Map this DB2 DDL to a PostgreSQL schema."
- "How should this OCCURS DEPENDING ON group be stored?"
- "This COMP-3 amount is losing precision after migration."
- "Prove the migrated table matches the legacy DB2 table."
- "Review this DB2 to PostgreSQL mapping before implementation."
Type mapping
| DB2 or COBOL source | PostgreSQL | Rule |
|---|---|---|
CHAR(n) |
char(n) or varchar(n) |
DB2 CHAR is blank-padded. Decide whether trailing blanks are significant before choosing. |
VARCHAR(n) |
varchar(n) or text |
Confirm the declared maximum against real data before shortening it. |
GRAPHIC, VARGRAPHIC |
text |
Confirm the source CCSID; do not assume UTF-8. |
DECIMAL(p,s), COBOL COMP-3 |
numeric(p,s) |
Precision and scale come from the DDL or the PIC clause, never from a sample value. |
NUMERIC, zoned PIC 9 display |
numeric(p,s) |
Check sign handling: SIGN LEADING, SIGN TRAILING, and SEPARATE change the stored bytes. |
SMALLINT, INTEGER, BIGINT, COMP |
smallint, integer, bigint |
Match the binary width; COBOL COMP width depends on the PIC digits and compiler options. |
REAL, DOUBLE, FLOAT |
real, double precision |
Never use these for money, quantities, or anything summed and compared. |
DATE, TIME, TIMESTAMP |
date, time, timestamp |
DB2 TIMESTAMP can carry more fractional digits than the target default; state the precision. |
Date as PIC 9(8) |
date |
Confirm the stored pattern, usually YYYYMMDD, and reject impossible values instead of coercing them. |
ROWID, IDENTITY |
Surrogate key plus a retained legacy column | Never expose a physical identifier as business identity. |
BLOB, CLOB |
bytea, text |
Confirm size limits and whether the value is ever compared or indexed. |
Monetary and quantity fields keep exact decimal types end to end: numeric in PostgreSQL, BigDecimal
in Java, and a string in JSON. A single conversion through a binary floating type is enough to break a
reconciliation.
Structure mapping
| Legacy structure | Default PostgreSQL shape | Use an alternative only when |
|---|---|---|
| DB2 table | Table | Never merge two tables into one without a recorded decision. |
| Column | Column | The column is a documented composite that programs always split. |
OCCURS fixed group |
Child table with (parent_id, occurrence) |
Order and cardinality are bounded, values are never queried individually, and an array is a recorded decision. |
OCCURS DEPENDING ON |
Child table with (parent_id, occurrence) |
Never. The controlling count is data and must survive the migration. |
REDEFINES |
One column per real interpretation, plus a discriminator | Never collapse to a single column; the active reading is decided by program logic. |
Nested OCCURS |
Grandchild table with both ordinals | Never. Both occurrence dimensions are meaningful. |
| Primary and unique index | Primary key and unique constraint | Uniqueness was never enforced in the legacy data. |
| Secondary index | Index on the mapped columns | The index is unused by every inspected program and query. |
| VSAM KSDS | Table with a unique key on the mapped record key | The dataset is a pure sequential extract with no key semantics. |
| Sequential dataset | Staging table or file-backed load | The data is transient and never queried. |
Store the occurrence ordinal explicitly. Occurrence position is often load-bearing in reports, control breaks, and "first entry wins" logic.
Identity, integrity, and access paths
- A DB2 foreign key is declarative evidence; a relationship enforced only in COBOL is a hypothesis. Cite the program and line that establishes it.
WITH DEFAULTandNOT NULL WITH DEFAULTchange what an absent value means. Map each column explicitly.- A cursor with
FOR UPDATEimplies locking behavior; a cursor withoutORDER BYhas no guaranteed order even when the legacy report appears sorted. SQLCODE +100is a normal empty result. The migrated query must produce the same empty-result behavior, not an error.- Add a constraint only when the legacy behavior actually rejects the value. A constraint the legacy system never enforced turns a load into a silent data-loss event.
Semantic traps
- Null is not blank and not zero. DB2 nullable columns, COBOL blank fields, and low-values are three distinct states. Decide the mapping per column and record it.
- Blank padding.
CHARcomparisons include trailing blanks. Moving tovarcharchanges equality results. - Sign representation. Zoned and packed values carry the sign in the last nibble or byte. A wrong read silently flips a sign on a subset of rows.
- Shortened values on load. Widening a column hides a legacy length rule that programs relied on. Preserve the rule explicitly or record it as an accepted deviation.
- Variable-length records. An
OCCURS DEPENDING ONrecord read with the maximum length reads past the real data. Migrate the actual count, not the maximum. - Low-values and high-values. These sentinels often mean "unset" or "end marker"; mapping them to a literal byte string changes comparisons.
- Denormalized redundancy. A value duplicated across tables is often intentionally stale. Normalizing it changes reported history.
Reconciliation procedure
Equivalence is a measurement, not a claim. Run these against the same input and record actual numbers.
- Row counts. Legacy row count per table or dataset versus target row count per table.
- Occurrence counts. Total
OCCURSentries versus child-table row counts. - Aggregates per numeric column.
count,countof non-null,sum,min, andmax, compared at full precision. - Null and default distribution. Null counts per nullable column, compared with the legacy state.
- Distribution checks. Distinct-value counts for every indexed or key column.
- Sampled record diff. Deterministic ordering, a fixed sample, and a field-by-field comparison including trailing blanks and occurrence order.
- Edge-case set. Maximum and zero occurrences, negative and zero amounts, boundary precision, nulls, low-values, and the longest character values.
Report every number that was produced and every check that could not run. A reconciliation reported without numbers is not evidence.
Safety
- Fixtures and examples use synthetic data. Never copy production records, personal identifiers, or real monetary values into the repository, tests, logs, or issue text.
- Migration scripts are re-runnable against an empty target and never mutate the legacy source.
- Credentials come from the environment or a managed identity, never from a script, migration file, or connection string in version control.
- A destructive load step requires an explicit flag and a recorded approval.
Limits
- This skill maps structure and proves data equivalence. It does not extract business meaning; use a business-rule extraction capability for that.
- It does not tune the resulting schema; use a PostgreSQL optimization capability after the mapping is correct.
- It does not decide scope, priority, or which tables migrate first.
- Reconciliation proves that data matches. It does not prove that behavior matches; that needs characterization tests against the legacy outputs.
Output template
## DB2 to PostgreSQL mapping
**Status:** proposed | reviewed | reconciled | blocked
**Scope:** <DB2 table, copybook, or dataset>
### Column mapping
| Source field | Type | Target column | Type | Rule or risk | Evidence |
| --- | --- | --- | --- | --- | --- |
### Structure mapping
| Structure | Target shape | Rationale | Evidence |
| --- | --- | --- | --- |
### Access paths
| Index or cursor | Target index | Uniqueness proven | Evidence |
| --- | --- | --- | --- |
### Reconciliation
| Check | Legacy value | Target value | Match | Evidence |
| --- | --- | --- | --- | --- |
### Open questions and accepted deviations
- <question or deviation, owner, decision reference>
Quality gate
- Every mapped field cites the DDL, DCLGEN, or
PICclause rather than a sampled value. - Monetary and quantity fields use exact decimal types across every layer.
-
OCCURS,OCCURS DEPENDING ON, andREDEFINESstructures preserve occurrence identity, order, and count. - Indexes and cursors map to access paths that match the inspected queries.
- Every foreign key and unique constraint cites the DDL, program behavior, or data proof that justifies it.
- Null, blank, default, sign, and length decisions are explicit per column.
- Empty-result behavior matches the legacy
SQLCODE +100paths. - Reconciliation reports actual counts, aggregates, occurrence totals, null counts, and sampled diffs.
- Checks that could not run are reported as not run, with the blocking reason.
- No production, personal, or real monetary data entered fixtures, examples, or logs.