Jim Youmans
asked on
DB2 Schema Comparison Tool
DB2 10.5 on Windows
I am looking for a good tool (free is better) that will compare one DB2 database to another. I want to be able to compare our production to our staging to make sure they are in sync before we do testing. I have one named SQL Power Architect what works pretty well but it only allows you to compare one schema at a time. I have over 30 schemas so that wouldn't be ideal.
There is one called DTM Schema Comparer that will do what I want and I can schedule it, but it has a cost associated with it and before I go begging for money I just wanted to be sure there is not a similar free tool.
So I need it to compare all schemas, not one at a time, generate a readable report and SQL change file and be run from command line.
Tall order but if you don't ask, you don't know.
Thank you!!
Jim
I am looking for a good tool (free is better) that will compare one DB2 database to another. I want to be able to compare our production to our staging to make sure they are in sync before we do testing. I have one named SQL Power Architect what works pretty well but it only allows you to compare one schema at a time. I have over 30 schemas so that wouldn't be ideal.
There is one called DTM Schema Comparer that will do what I want and I can schedule it, but it has a cost associated with it and before I go begging for money I just wanted to be sure there is not a similar free tool.
So I need it to compare all schemas, not one at a time, generate a readable report and SQL change file and be run from command line.
Tall order but if you don't ask, you don't know.
Thank you!!
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Jim,
If you want a quick and dirty way to do this, it's pretty easy to generate a compare/non-compare status by comparing the data in the system (SYSIBM) tables.
That will generate 1 line for every table-column in the database and will have the basic description of the column. Table name, column name, column number in the table, date type, and length-precision.
Generate it on both databases and a simple compare tool (do you have unix access?) will confirm or deny equality.
Or you could dump both into data tables and use standard SQL to identify missing schemas, table differences, etc.
It should set up pretty quickly and after writing a few key queries it should give you the answers you want a lot faster than using a tool to process one schema at a time.
Kent
If you want a quick and dirty way to do this, it's pretty easy to generate a compare/non-compare status by comparing the data in the system (SYSIBM) tables.
select table_schema, table_name, column_name, ordinal_position, data_type, numeric_scale, numeric_precision from sysibm.columns where table_schema not like 'SYS%' order by 1, 2, 4
That will generate 1 line for every table-column in the database and will have the basic description of the column. Table name, column name, column number in the table, date type, and length-precision.
Generate it on both databases and a simple compare tool (do you have unix access?) will confirm or deny equality.
Or you could dump both into data tables and use standard SQL to identify missing schemas, table differences, etc.
It should set up pretty quickly and after writing a few key queries it should give you the answers you want a lot faster than using a tool to process one schema at a time.
Kent
ASKER
Jim