Implementation Considerations for PL/SQL
From MIKE2.0 Methodology
-> You are here: Implementation Considerations for PL/SQL
The following are Implementation Considerations for PL/SQL that can be followed as part of a design process for data migration.
Some of the packaged ETL tools offer similar functionality. As there are sometimes PL/SQL steps embedded in other tools it is recommended to merge these logging approaches.
This can be used also a starting point of the reconciliation approach.
PL/SQL migration procedures should be grouped according to the logical entity (or group of entities) they relate to, and contained within packages.
The execution of all migration packages and procedures should be controlled by the main procedure of a control package.
When the decommissioning schedule for a legacy application requires concurrent operation of the legacy and replacement systems, a control table can be used to track each incremental run. Monitoring can be set up on the table to alert operations staff of migration problems.
A typical control table structure might be:
Each program step of an incremental run should be treated as a transaction, and as such should only be committed when all records in the cursor are successfully migrated.
If an exception occurs, all records for the program step should be rolled back and the overall process terminated. In this way, once the cause of an exception is located and fixed, the migration process can be continued from the failed program step.
Functions and procedures that are used in multiple packages, or that are not related to specific business logic should be contained in a common package (e.g. updating reference data, modifying trigger statuses or analysing schemas/tables).
Procedures should be sufficiently segmented into program blocks with individual exception handlers. In this way, deviations from expected data formats can be caught and logged. Where possible, exception handling blocks should catch named system exceptions, but should always include the standard ‘OTHERS’ clause to trap all remaining exceptions.
Where incremental migrations are necessary, the error log should include the run number.
A typical error table structure might be:
In order to maintain an audit trail of data migrated between systems, it is useful to store the relationships between the primary keys of source and target systems. This information is critical where the same entity from a source system can be delivered more than once.
A typical key mapping table structure might be:
For reconciliation purpose it may make sense to include also specific value fields or dates in this audit trail.
Wiki asset search