‘Data migration’ – sounds easy doesn’t it? It’s just a matter of taking some data and moving it from one place to another, possibly changing its format along the way and sometimes moving it to a new application.
The reality is that data migration requires considered planning and a good technical knowledge of the database you are moving from and the one you are moving to.
This blog will focus on the migration of data from an IBM i to either another stand-alone database or to another application running on a different platform.
Planning
As with any successful project, it’s all in the planning.
Before starting the migration careful consideration should be given to the data that actually needs migrating, for example;
- Does all the transactional data need to be migrated?
- Is financial transaction data required in the new application or would opening balances suffice?
- When/how will the migration take place?
- Should you close the system down for trading and migrate over a weekend?
- How will the migration be tested?
- Should a parallel system on your new application be run? To run data through your migrations process.
- Will access to reporting over your legacy data be required? If you are not migrating it to your new application.
Common Problems when Migrating a DB2 database
DB2 on the IBM i dates back to the 1980s. A relational database, the database is created using DDS (data description specification) to specify the attributes and functions of fields within files.
DB2 restrictions mean that file names and field names are restricted to 10 characters. This means that without a data dictionary or schema of your application database, files and their field contents can appear meaningless – ABC001 is not obviously a file holding order transactions for example.
DB2, in its original form, did not have date fields so dates and times were held in 6 or 8-long fields sometimes as numeric and sometimes alpha.
DB2 data is held in EBCDIC format so issues may arise when converting to ASCII, ANSI or to UTF character sets.
Data Dictionary/Schema
If your DB2 application is bespoke and has been written over many years, then it is fairly unlikely that the database itself will be well documented.
Time spent determining which files hold what data and carefully documenting the database will pay dividends before you start any migration and will often reveal nuances of the source system that will not have been apparent in normal everyday usage.
It will also help to avoid migrating duplicated data and will help identify historical data that is not required by the new system.
Formatting Issues
When converting date, time, or datetime fields where information has been stored as either numeric, or string date, routines will need to be written to convert the data to standard date, time, or datetime formats. This will also allow your new system to work across multiple localities worldwide.
As DB2 is an EBCDIC database there will undoubtedly be characters within the database that will not transpose to the new database. In this instance, we would suggest a conversion/mapping table holding the character being mapped from DB2 and the corresponding character you would like to convert to.
Many applications and databases differentiate between a blank value, and no value at all, a null value.
Within IBM i applications it is common to simply use blank values in all cases so, part of any data migration involves making sure the appropriate pattern is followed.
Sometimes this will be a simple choice for every value in the database, however, each field should be analysed, and the decision of how to handle blank values made on a case-by-case basis.
Moving to a New Application
If you are moving to a new application, you may well need to move your existing data into that application. You will need to move customers, suppliers, sales data, stock positions etc. This will all need to be carefully planned with your new system implementers to ensure all data is mapped correctly.
You may be able to create opening balances in the new application and start trading from that position. You could then move your historical financial data to another database that you can keep for regulatory purposes and have the ability to run reports over that data if required.
By starting with an opening balance in the new application and running a month’s work of transactions through the new system you can prove the month-end closing balance matches your existing system. This is a good safety check to ensure the new system and your data migrations are working as planned.
Migrating for Archive
If you intend to shut down your existing system and do not need any of the data in your new applications then you could consider migrating the transactional and financial transaction data to a new stand-alone SQL database. A simple suite of reporting programs can be produced to give access to the data if required for auditing purposes in the future.
At KFA we have extensive knowledge of IBM DB2 and have experience of converting a DB2 database to SQL on the IBM i. We have written numerous functions to extract data from the IBM i into SQL databases for use in other applications.
We also have experience of extracting entire databases to other platforms as applications come to end-of-life and experience of migrating data to new applications to replace the IBM i including SAP and Oracle.
Richard Austin
KFA Connect New Business Director
5th January 2023
Want to know more? Get in touch with our team today.