Does DB2 UDB v8 come with a compare tool?

[mjf]

I need to come up with a tool to compare the table definition and/or
its data before and after some modification to the DDL or some tests
(that change the data). Does DB2 come with such a tool, or do I need to
write my own? Thanks.

[Serge Rielau]

mjf wrote:

I need to come up with a tool to compare the table definition and/or
its data before and after some modification to the DDL or some tests
(that change the data). Does DB2 come with such a tool, or do I need to
write my own? Thanks.

There is no pre-packaged tool. You can run db2look before and after and
get some text diff which may be helpful for the schema.
Now.. I see requests like this pop up in this group in regular interval.
Would you mind sharing the business problem and what you expect this
tool to do? Maybe I can whip something up if it's not too hard.

E.g. how should the reoprt of this tool look like? Which mismatches
should it flag and which should it ignore.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Ian]

Serge Rielau wrote:

mjf wrote:

I need to come up with a tool to compare the table definition and/or
its data before and after some modification to the DDL or some tests
(that change the data). Does DB2 come with such a tool, or do I need to
write my own? Thanks.

There is no pre-packaged tool. You can run db2look before and after and
get some text diff which may be helpful for the schema.
Now.. I see requests like this pop up in this group in regular interval.
Would you mind sharing the business problem and what you expect this
tool to do? Maybe I can whip something up if it's not too hard.

There are 3rd party tools that can handle DDL comparisons - BMC and
Quest Software both provide this functionality.

Comparing data in 2 tables is challenging; had to do this for a
migration once. It can be done with SQL, see the INTERSECT and EXCEPT
keywords. :-)

[mjf]

Hello Serge,

Thank you very much for your reply.

Here is a typical scenario for DDL change: the developers may add
(quite often) or drop (not often) columns into/from tables during
development of an application, according to the data they see that are
needed for the application and was not considered in the previous
database design. Then when the next release comes up, they will
implement an upgrade script to include all the changes that have been
done to the application and the related software (which includes the
modification to the database DDL), in order to upgrade all the other
machines that run this application. If we can see what has been changed
to tables and columns, then we can write a SQL script to update all of
them at one go for each machine.

And here is a typical scenario for data modification: let's say an
application drives a real-time system (hardware), meaning the
modification to the data in the database can change the behavior of the
system. The developers/testers would change some parameters (which are
saved into and retrieved from the database by the app) to fine tune how
the system moves, how much the system moves, etc. At the end of the day
or at certain point when they are satisfied with the system, they would
like to see which parameters have been modified, do some analysis, and
eventually establish a set of parameters as template for the other
systems.

I guess the simplest will be a tool similar to the "diff" on Linux (I
assume a tool with a GUI will take much longer to develop), where it
flags all the tables that have been modified, all the columns that have
been added or removed or modified (such as column size), and if
possible, all the constraints, sequence numbers, & triggers that have
been added or removed. Difference on whitespaces and new lines can be
omitted. Actually for now just the changes to a table (adding,
removing, and modification of columns) should be enough.

Thanks.

[mjf]

Hello Ian, thanks for the info! I'll check them out.

[Philip Nelson]

Serge Rielau wrote:

mjf wrote:

I need to come up with a tool to compare the table definition and/or
its data before and after some modification to the DDL or some tests
(that change the data). Does DB2 come with such a tool, or do I need to
write my own? Thanks.

There is no pre-packaged tool. You can run db2look before and after and
get some text diff which may be helpful for the schema.
Now.. I see requests like this pop up in this group in regular interval.
Would you mind sharing the business problem and what you expect this
tool to do? Maybe I can whip something up if it's not too hard.

E.g. how should the reoprt of this tool look like? Which mismatches
should it flag and which should it ignore.

Cheers
Serge

The problem I've found with db2look is that it outputs the DDL in strict
"last created / altered" order. The reason why is obvious : it makes it
easy to produce a DDL script which can run without any problems of "object
creation order" if you create it in an order which has run successfully
before.

However this format is virtually useless for doing a diff against.

Another problem is that of differences of white space, indentation and the
like.

What I wrote is a little utility which strips out the white space and sorts
the statements from db2look in alphabetic order. Not much use for
running, but the first step in identifying which objects have changed.

If anyone is interested I can make this script available.

Phil

[mjf]

Hello Philip, can you please email me the script (if it's too long to
post it here)? Thank you very much.

[sualeh.fatehi]

mjf,

From your requirements, it seems like the open-source SchemaCrawler

tool will do what you need. SchemaCrawler outputs details of your
schema (tables, views, procedures, and more) in a diff-able plain-text
format (text, CSV, or XHTML). SchemaCrawler can also output data
(including CLOBs and BLOBs) in the same plain-text formats. You can use
a standard diff program to diff the current output with a reference
version of the output. SchemaCrawler can be run either from the command
line, or as an ant task. A lot of examples are available with the
download to help you get started.

SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party jars are required.

Once you get familiar with SchemaCrawler's Java API, you can even
write plug-ins that will automatically generate the scripts that you
need.

Sualeh.

[Philip Nelson]

su***********@gmail.com wrote:

mjf,

From your requirements, it seems like the open-source SchemaCrawler

tool will do what you need. SchemaCrawler outputs details of your
schema (tables, views, procedures, and more) in a diff-able plain-text
format (text, CSV, or XHTML). SchemaCrawler can also output data
(including CLOBs and BLOBs) in the same plain-text formats. You can use
a standard diff program to diff the current output with a reference
version of the output. SchemaCrawler can be run either from the command
line, or as an ant task. A lot of examples are available with the
download to help you get started.

SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party jars are required.

Once you get familiar with SchemaCrawler's Java API, you can even
write plug-ins that will automatically generate the scripts that you
need.

Sualeh.

An interesting link, but will a generic tool like this produce all the DB2
specific DDL we are looking for ?

There are lots of tools on the market for looking at database schemas,
including some costing a considerable amount of money, but I've always
found that you have to treat what they produce with caution.

For example, at my "day job" we have Embarcadero DBArtisan. Not exactly
"free", but it still seems to struggle with the DB2 specifics. Issues
with the current release include incomplete or missing routine authorities,
which really messes things up when we want to do a change "transparent to
the users".

Phil