Many times while working to migrate legacy client data we find that they have become creative in the way data is stored. This usually happens because database changes can be hard to accommodate in a timely fashion on legacy mainframes. The most common issue involves putting data into a column for which it was not specifically designed. If we are lucky this just involves a column name that does not match the data it contains.
At times we have had to deal with client data that has COMP data defined in an ALPHA field. Once this situation is unearthed the research begins. The program updating this information must be found so the proper record definition is available.
You may have made use of what we refer to as a miscellaneous table. This is basically a table of tables. You define a key or keys and a large ALPHA area so that developers can store whatever makes them happy. Most times this data is a mix of ALPHA and NUMERIC. When extracting these tables for migration you need to provide each redefinition of this generic area.
Some instances where the user is allowed to enter free form data, for instance in the form of notes or comments, we have encountered screen control characters have made their way into database fields. One painful problem is when you encounter an inadvertent end of text marker when extracting a record. The processing of the data record stops when the end of text marker is encountered and partial data is migrated. Truncated records can be missed if simple record count comparisons are being used to verify migrated data. Record data comparison must be done during or after migration to capture these truncated data records.
Heck in at least one instance we have run into a client that stored an ALPHA character representation in a COMP field. It took them some time to find the program doing this and provide us with a definition of how it needed to be migrated.
In each of these situations it required additional time and manpower to resolve the issue so data migration could be completed. Researching and resolving these challenges adds to the timeline and budget of any conversion or migration.
If your company is considering a migration in the future it is important a data cleansing analysis be done. If any of these concerns are discovered it will be beneficial if data cleanup of any non-compliant data storage methods is completed. The effort will save you time and money as well as make your data migration go more smoothly.