From MIKE2 Methodology
|
| 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.
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
|
BlogMarks
del.icio.us
digg
Furl
Simpy
smarking
