Open Framework, Information Management Strategy & Collaborative Governance | Data & Social Methodology - MIKE2.0 Methodology
Wiki Home
Collapse Expand Close

Members
Collapse Expand Close

To join, please contact us.

Improve MIKE 2.0
Collapse Expand Close
Need somewhere to start? How about the most wanted pages; or the pages we know need more work; or even the stub that somebody else has started, but hasn't been able to finish. Or create a ticket for any issues you have found.

Implementation Considerations for PL/SQL

From MIKE2.0 Methodology

Share/Save/Bookmark
Jump to: navigation, search
Under construction.png
This article is currently Under Construction. It is undergoing major changes as it is in the early stages of development. Users should help contribute to this article to get it to the point where is ready for a Peer Review.

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.

Contents

Script Packaging

PL/SQL migration procedures should be grouped according to the logical entity (or group of entities) they relate to, and contained within packages.

Migration Control

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:

Column Name Type Null Description
RUN_NUMBER NUMBER(10) NOT NULL Run number - for One-Time this will be 0
PROG_NAME VARCHAR2(100) NOT NULL The name of the program step in the migration sequence
DAILY_FLG VARCHAR2(1) NOT NULL Daily/One-Time indicator
STATUS VARCHAR2(1) NOT NULL Status of the program step (‘Y’, ‘N’ or ‘E’)
START_DTTM DATE NOT NULL Start time of the program step
END_DTTM DATE NULL Completion time of the program step

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.

Common Package

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

Error Logging

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:

Column Name Type Null Description
ID NUMBER(20) NOT NULL Unique sequence number to identify errors captured during migration
PROG_NAME VARCHAR2(100) NOT NULL The migration program in which the error occurred
PROG_STEP VARCHAR2(100) NULL The step within the migration program in which the error occurred
SRC_TBL VARCHAR2(30) NULL Source table of erroneous record
SRC_ID VARCHAR2(32) NULL Source primary key of erroneous record
DEST_TBL VARCHAR2(30) NULL Target table of erroneous record
DEST_ID VARCHAR2(20) NULL Target primary key of erroneous record
ERR_DESCR VARCHAR2(1000) NOT NULL High level description of error. This would contain an Oracle error message for unexpected errors
PROG_TEXT VARCHAR2(4000) NULL Detailed description for the error. This may include key values within the erroneous record
CREATED_BY VARCHAR2(10) NOT NULL User creating the record
CREATED_DATE DATE NOT NULL Creation date of the record
RUN_NUMBER NUMBER(10) NOT NULL Run number - for One-Time this will be 0

Key Mapping

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:

Column Name Type Null Description
TGT_TABLE_NAME VARCHAR2(30) NOT NULL Target table name
TGT_PK_ONE NUMBER(20) NULL Primary key of the target table
TGT_PK_TWO NUMBER(20) NULL Secondary key of the target table (composite primary key)
SRCA_PK_ONE VARCHAR2(32) NULL Primary key of the first source table
SRCA_PK_TWO NUMBER(20) NULL Secondary key of the first source table (composite primary key)
SRCB_PK_ONE VARCHAR2(32) NULL Primary key of the second source table
SRCB_PK_TWO VARCHAR2(20) NULL Secondary key of the second source table (composite primary key)
CREATED_BY VARCHAR2(10) NOT NULL User creating the record
CREATED_DT DATE NOT NULL Creation date of the record

For reconciliation purpose it may make sense to include also specific value fields or dates in this audit trail.

Wiki Contributors
Collapse Expand Close