DB2 migrating data to new schema

Question

We have a schema in DB2 database with around 700 tables - requirement is to migrate data from these tables to tables in a new schema (in same DB), tables in both the schema have almost the same names.

The requirement is bit tricky due to following two factors:

• Fields/Columns are less in new tables.
• Old tables have all VARCHAR fields, whereas new tables have specific data types (DATE, DECIMAL, CLOB, etc.)

We are looking for solutions to cast data between loading and unloading flow, and mapping of columns between tables.

Thanks!

Answer 1

This is a very difficult task, because source and target differ a lot. It is not easy for a tool to understand the lack of columns in new tables, even if they are empty or nulls. Casting is also difficult, because it is necessary to analyse the data from source column, and then check if all those values correspond to a type (but it is necessary to test many types before) and then perform the matching.

Probably, if the columns names in source and target tables are the same, the task could be easier. You just have to match names, and then just inject the data.

After analysing this situation, I conclude that metadata is not very useful in this case, because the properties of the tables will change a lot in the migration. Most of the time, migrations are based on metadata, but because you are changing almost all of that, the catalog database is almost useless. Also, many tools base their knowledge in metadata, not the data itself.

You are taking from a varchar, the most generic data type where you can put the values of any other type, and you want to optimise storage by assigning the appropriate data type according to the data, however, it should also be done for relation integrity; I mean, the datatype of a column that is primary key in a relation, should be match the foreign keys in other tables, and not just the datatype but the precision. This last is an extra problem, and I hope you have relational integrity there.

I do not know a tool as intelligent as you are looking for. However, I advise you to do a manual work. First, try to detect a policy in columns names, I mean if a column is called address you will know that in all tables is a varchar of 64, a column called price could be in all your tables a column of double. Just by doing this lexicographic analyses of the columns names, you could standardise the model. However, you have to check if values in all columns with that name, "enter" in that column precision. This last part is a very time consuming process, because you have to read the values from all tables, and look for the longest value per date type.