Write rerunnable upgrades and verify schema changes

Development and modifications

26R1 reference · Documentation-based walkthrough · Sources reviewed 2026-09-28 · Not executed in IFS or Oracle. Match the database, build home, and tools to your installed release. Example names are fictional.

Deliver a schema/data change that can be checked before execution, reconciled afterwards, and recovered after interruption. Separate controlled upgrade transformations from normal IFS business operations.

Before you start

Use a representative restored test environment or disposable schema and the project’s supported build/install framework. Record the actual source/target release, data volume, dependencies, maintenance constraints, and recovery owner. Obtain a recoverable baseline before changing retained data.

An efficient upgrade is not permission to bypass business APIs in ordinary runtime code. Conversely, a controlled migration may need a reviewed set-based transformation that is appropriate to the upgrade framework.

1. Define preconditions and postconditions

For a fictional new handover_state value, specify:

which existing rows need a value;
the permitted values and how each old row maps to one;
whether new inserts should have a default;
whether the final column is required;
how interruption and rerun should behave;
what counts, totals, or key-level comparisons prove completion.

Do not choose a constant backfill merely because it makes a NOT NULL constraint succeed. The value must mean something correct for existing business records.

2. Rehearse a set-based backfill

This standalone exercise changes only a new disposable table. Confirm its name is unused. Setup runs once, and DDL commits implicitly:

CREATE TABLE c_demo_upgrade (
   id NUMBER PRIMARY KEY,
   is_closed NUMBER NOT NULL,
   handover_state VARCHAR2(10)
);
INSERT INTO c_demo_upgrade VALUES (1, 0, NULL);
INSERT INTO c_demo_upgrade VALUES (2, 1, NULL);
INSERT INTO c_demo_upgrade VALUES (3, 0, 'REVIEW');
COMMIT;

Preview the proposed population, then transform only missing states:

SELECT id, is_closed FROM c_demo_upgrade
WHERE handover_state IS NULL;

UPDATE c_demo_upgrade
SET handover_state = CASE WHEN is_closed = 1 THEN 'CLOSED' ELSE 'OPEN' END
WHERE handover_state IS NULL;

Expect two updated rows: 1 becomes OPEN, 2 becomes CLOSED, and 3 remains REVIEW. Rerun the UPDATE before committing: expect zero updated rows. Inspect all three rows, then commit the verified exercise change.

This demonstrates an idempotent data predicate. It does not make repeated CREATE TABLE statements valid, nor guarantee the whole real deployment is rerunnable.

3. Add constraints only after reconciling the data

Check for null and invalid values. For this fixture:

SELECT COUNT(*) AS missing_count
FROM c_demo_upgrade WHERE handover_state IS NULL;

SELECT COUNT(*) AS invalid_count
FROM c_demo_upgrade
WHERE handover_state NOT IN ('OPEN', 'CLOSED', 'REVIEW');

Both counts should be zero. Only then apply the exercise constraint:

ALTER TABLE c_demo_upgrade MODIFY (handover_state NOT NULL);

A staged backfill is useful when different records require different values. For a compatible IFS upgrade where one valid value applies to existing rows, the installation framework also provides a direct NOT NULL-column mechanism. Inspect the actual Database_SYS.Set_Table_Column/Alter_Table signatures and the keep_default_ => 'N' option when the backfill value must not become the permanent insert default. Generate/review this through the actual release’s framework; do not copy a generic helper call into a production upgrade.

4. Make the full migration restartable

Detect the actual schema state before each DDL phase. Compare datatype, length, nullability, and default, not just column existence.
Distinguish not started, partly completed, completed, and incompatible states. Fail visibly for an unexpected state rather than swallowing all exceptions.
Use deterministic data predicates and unique keys to prevent duplicate inserts on rerun.
Test restart after each planned phase on a restored copy. A marker written too early is not evidence that the work finished.
Validate constraints, invalid objects, indexes, and application startup after completion.

If large data volumes require batches, use stable key ranges and a recorded checkpoint designed with the transaction/recovery policy. Do not add COMMIT every arbitrary number of rows without considering partial completion and undo/recovery needs.

5. Compare a fresh install with an upgraded database

Build a fresh target installation and an upgraded copy using the same source revision. Compare the changed columns’ types, lengths, defaults, nullability, constraints, indexes, grants, and generated API metadata. An accidental default that appears only on upgrades creates a long-term support defect.

Repeat the affected page/service and security tests as normal users. Schema equality alone does not prove that existing records are usable.

6. Measure and prepare recovery

Measure the representative upgrade’s elapsed time, changed-row count, redo/undo pressure, lock duration, and available space with the environment owner. Prefer reviewed set-based operations where semantics allow. Preserve required validation and data relationships rather than assuming every loop is wrong.

Document which phases can be rolled back and which require restore or a forward corrective migration. Oracle DDL commits implicitly, so a final ROLLBACK is not a complete recovery plan. Test the recovery path and retain sanitized reconciliation evidence in the review worksheet.

Troubleshooting

Symptom	Check
Rerun overwrites valid values	Missing idempotent predicate or incorrect state detection
Fresh and upgraded schemas differ	Retained default, length/nullability mismatch, or generation order
NOT NULL fails	Unmapped existing rows or invalid migration assumptions
Long outage	Representative data volume, plan, indexes, undo/redo, and lock duration
“Completed” marker but data missing	Marker/commit ordering and postcondition checks

Exercise and solution

Exercise: why does the fixture preserve row 3’s REVIEW state instead of resetting every open record to OPEN?

Show the solution

The requirement is to fill missing values. WHERE handover_state IS NULL preserves existing intentional values and makes the UPDATE rerunnable. If the requirement were to recalculate every state, it would need a different contract and reconciliation plan.

Cleanup and delivery evidence

End the exercise transaction, verify the disposable table identity, and run DROP TABLE c_demo_upgrade. For a real IFS delivery, retain preconditions, build/install logs, rerun tests, reconciliation results, and the proven recovery plan. No IFS or Oracle execution is claimed here.