White Papers

10.02.18

Validation of Data-only Migration from DB2/400 to Oracle

Many companies purchased IBM Midrange systems to run their business in the 1990s using one of many ERP systems offered on the AS/400 platform – JD Edwards, Mapics, BPCS, Silverlake, and others. As the business grew and more advanced products came on the market, those companies made a change from those systems to SAP on Unix or Linux using an Oracle database.

Introduction

Many companies purchased IBM Midrange systems to run their business in the 1990s using one of many ERP systems offered on the AS/400 platform – JD Edwards, Mapics, BPCS, Silverlake, and others.  As the business grew and more advanced products came on the market, those companies made a change from those systems to SAP on Unix or Linux using an Oracle database.

The move from JDE to SAP, however, typically did not include a data migration primarily because data maps were not available and there were no efficient processes offered by either the new or old software vendors.  Nor was Oracle able to provide cost-effective help.  Thus, companies had to bear with the direct and indirect costs of maintaining the orphaned system to have operational or regulatory access to the historical data.

DB2/400

TheAS/400 isamature andhighlyproprietaryenvironmentdesignedtodeploy commercial applicationsto support bothinteractiveandbatchprocessing.  Thesystem uses both RPGandCOBOL400 programminglanguages,a Control Language (CL)for systemleveltasksandDDS language for sourcedescriptionsfordata, display, prinfiles.  This architecture is fundamentally different from that of the Linux/x86 systems to which AS/400 applications would be rehosted. 

The AS/400 system has a relational database built into the operating system level.  DB2/400utilizes a physical/logical file structure.   Oracle’s architecture is entirely different; it uses tables and views. AS/400 data is stored as operating system objects, and the operating system controls how you access your data.  Oracle is a much more flexible independent database program that stores data in user-defined, user-accessible tablespaces. 

Infinite Solution

Infinite has developed a solution which reconciles all these architectural differences so that programs written for the AS/400 can execute on Linux or Windows.  As part of the application migration, the Toolset also provides the optional capability for end users to modernize the database capabilities of the system to circumvent the constraints of the DB2/400 database.  We did this by building a complete replication of DB2/400 into our Toolset which makes us uniquely able to rehost from this highly proprietary environment independent of the original IBM host.  

It is this capability within Infinite which provides an automated process to take a standard DB2/400 database and do a one-time migration to Oracle or MS SQL.  Infinite migrates the physical and logical data structure into a standard SQL data structure as tables and views.

Automated Migration

Infinite worked with Oracle and Microsoft to develop APIs to manage the interoperability within the Infinite Toolset.  Infinite writes directly to Oracle or MS SQL Server without having to modify databasecalls. The Toolset does not use scripts to convert physical and logical files to Oracle. It’s a more intrinsic process in which the CNVSQLTBL command reads the PF or LF DDS and uses OCI calls to submit the appropriate CREATE instructions to generate the Oracle components.

Infinite readstheexternal definitionofeachfile and createsthe correspondingtablein Oracle usinthemost appropriatedatatypes. Wepopulatethetables anmaintainthem asthefiles areupdated Wealso perform all procedural andadministration. The implementation of Oracle on Infinite is entirely transparent to the application programs. Infinite manages the entire process within the Toolset.

Migration Methodology

The structure of DB2/400 and standard third-party databases like Oracle and MS SQL are different in fundamental ways.  However, the Infinite Toolset can bridge virtually all these differences.  Occasionally a situation will arise where rules are broken and need to be corrected. For example, the AS/400 may have accommodated alpha characters in a numeric field where Oracle will not. Issues like these will not in any way compromise the integrity of the migration and remediation of this type can be handled manually by Infinite as part of our services

Clients typically want to take the opportunity to review the data first to identify the data which is required, discard duplicate or obsolete data and correct any corrupted tables or indices. 

 

Infinite will provide detailed instructions to the Client’s technical team to create and transfer AS/400 backup files and upload them to the Infinite SFTP site.  Infinite takes AS/400 backup files including the source and physical and logical files. The system creates an identical structure within Infinite which looks and performs just like DB2/400.  The Toolset then creates a corresponding schema within a standard Oracle database and populates it with the data from the internal database.

Once Infinite has confirmed that it has the complete set of libraries as specified in the Scope of Work (SOW), a technician will process and recompile them. During this operation the system will take the build instructions from the file headers and build within Infinite db an exact twin of the AS/400 structure, displaying each physical file as a table and each field listed on the table.  The names of the files in Oracle will correspond precisely to the member name in DB2/400  

Once the Toolset has created the structure, we will configure the Infinite toolset to write the data to that new Oracle structure.  

During validation, the technician will compare the database on screen by clicking on a table to display the individual table and to match the number of records in each against the AS/400.

After reviewing each table, Infinite will prepare a Validation Report showing that all file members were successfully migrated and that record counts match.  

If the client has provided test scripts, Infinite will execute them and document the results. Alternately, Infinite and the client can conduct a shared session in which to view the results dynamically. 

 

Common Considerations

Migrating only legacy archive data along with queries simplifies the operation.  At the same time, the application is no longer available to run core processes for validation.  A review of industry and academic literature consistently focuses on five common issues that can arise during a data-only migration.  They are listed here along with Infinite’s solution to each:

Data changes during the test.  Infinite performs a side-by-side comparison between the source and target databases verifying that each data element has been correctly migrated.

Source data is partially corrupt.  Data corruption sometimes happens, particularly with unused tables while the legacy application was still in production.  Infinite will migrate the corrupt data to the new database in exactly the same state.  The client has the option to correct corruptions either on DB2/400 or in Oracle. 

Source/target mapping changes.  During a typical data migration, either a DBA or a tool is used to map data from one database to the other, and they are frequently just a bit different. This is a particularly thorny issue when migrating legacy AS/400 physical and logical data files to Oracle tables and views.  

Fortunately, it is not an issue when using Infinite because the system intelligently manages the migration in two steps.  It builds an exact copy of the DB2/400 structure within the Infinite environment and the technician validates the migration.  Once in Infinite, the system readstheexternal definitionofeachfile and createsthe correspondingtables and views in Oracle.   

Data migration fails, but only partially.  This is not an issue that will occur during the repopulation of data from Infinite to Oracle because the conversion from Infinite to Oracle is transactional. Should the execution of the command end with errors, the conversion of the physical file will be rolled back.  The table (with partial data up to the point of error) will be dropped from Oracle. If the execution of the command finishes without reporting errors, we know that the table was successfully created in Oracle based on the DDS definition of the physical file in Infinite and all the records were moved successfully with no errors during the conversion process. 

Data migration takes a long time to complete.  A major factor in data migration duration is the transfer rate.  However, it is not a consideration when archival, versus production, data is being migrated.

Testing and Validation

Here are the validation approaches based on the experience of a number of client implementation projects. These tests are used irrespective of whether we do a full application rehosting project or migrate data only.

Load Balancing Validations– Load Balancing among Source and Target systemsto ensure that the count of data librariesare in sync. It looks inside each library to confirm that the data objects as shown on the AS/400 match data files on Oracle or MS SQL.

Query ValidationClient will run queries on the AS/400 focusing on every significant data set and record results.  The queries accompanied by screen or report output will be provided to Infinite to compare the new database results against the original.  Alternately, these same results may be achieved in a shared session between the respective technical groups.

Validation Report– Thisreport summarizes the data elements provided, the original host structure and the migration results for theoperation and business teams. This document is used to document the migration results and summarize the post-migration results for future reference.   

Conclusion

 

The information above establishes how the Infinite toolset allows businesses to reduce costs and the need to support legacy hardware and databases.    Using the Infinite platform replaces the IBM I (AS/400) platform requirement for archive data and makes it more accessible for future needs by migrating the data to Oracle or MS SQL.  The solution is automated, secure, comprehensive and thoroughly tested.

Zurück