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 YoumansSr Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Jim,

Pretty good question.... Half way through I was thinking of pointing you to Data Studio, but what you're looking for goes deeper than that.

I don't know of any tool that does everything that you want.  There are a number of tools that do most of what you want.  I kinda like the UDB Workbench.    It seems to do everything you want, but only works on 1 schema at a time.  It will apply the changes to sync the databases, or write them as a script.

SQLEdit has a command line feature.  The last version I used was in 2007 and I liked it, but it is a lot different today.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim YoumansSr Database AdministratorAuthor Commented:
Yeah, I really like SQL Power Architect but it has the same drawback.  You have to do one schema at a time.  The DTM Schema Comparer from SQLEdit seems like a good candidate but I am having some detail issues with it, like it is telling me identical indexes are missing from both prod and stage.  Kind of weird.  And it costs $200 (my company is a bit on the "let's go with the free version" bandwagon).

Kent OlsenDBACommented:
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.

  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

Open in new window

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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.