473,890 Members | 1,554 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Jan 30 '06 #1
8 2562
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
Jan 30 '06 #2
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. :-)


Jan 30 '06 #3
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.

Jan 30 '06 #4
mjf
Hello Ian, thanks for the info! I'll check them out.

Jan 30 '06 #5
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
Jan 30 '06 #6
mjf
Hello Philip, can you please email me the script (if it's too long to
post it here)? Thank you very much.

Jan 30 '06 #7
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.

Jan 31 '06 #8
su***********@g mail.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 "transparen t to
the users".

Phil
Jan 31 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2125
by: jyoti | last post by:
I am new in asp. I have two html files. I like to write code to compare those two files. I like to show the difference as highlighted area. Example: File 1 contains: <html><body> Compare Test</body></html> File 2 contains: <html><body> Compare Test modyfied</body></html> I like to make either the word "modyfied" highlighted or
2
3348
by: Nathan Rosaaen | last post by:
I want to be able to easily see what changes were made from original to updated sentences (words added/removed/changed). I'm not too concerned about speed, just looking for something. The compared text will be anywhere from a word or two to multiple paragraphs. An example: Original sentence: The brown dog ran outside at night. During the day, the brown dog slept. Updated sentence: The big brown dog ran outside when dark. During...
4
2103
by: roni | last post by:
hi. is there a tool that compare 2 vs.net projects and show the differences in code ? (in text..) such tool will help me alot to see changes i made between backups for example. have a nice day.
6
5321
by: rabii | last post by:
Hello, I'm using Entreprise Manager (for Sql Server 2000) to generate my database's script. By mistake, i've changer FillFactor one time. And, now I can't remove this data from generated sql script. How to remove that ? Thank's a lot.
1
1731
by: serge | last post by:
Can someone recommend what SQL Database compare tool to use and why? Thank you
26
25640
by: webrod | last post by:
Hi, I have some php pages with a lot of HTML code. I am looking for a HTML validator tool (like TIDY). TIDY is not good enough with PHP tags (it removes a lot of php code). Do you have any idea? Thanks you very much
0
1516
by: TimKnoll | last post by:
I want to compare to large (4+ MB) XML files from a C# program. I'm currently using The XML Diff and Path tool from Microsoft's site. The performance on the larger files isn't acceptable. Has anyone used another tool which is much faster? I just started looking at running Beyond Compare from a script. Any ideas would greatly be appreciated. Thank you. Tim.
0
1228
by: dingo | last post by:
DBC (Database Compare) is a fast, comprehensive database structure and data comparison tool.Sort sync scipts with database dependencies. www.d-softs.com
5
3838
by: teddysnips | last post by:
I have upsized a client's application from Access BE/FE to Access FE/ SQL Server. All is fine with initial testing, and they want to run the systems in parallel prior to going live. The MD, who is not particularly technical, is more or less insisting that every day a comparison is made of the two data repositories to see if they are out of sync. There are about four users who will be expected to key the data into the two systems...
0
10819
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10923
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10462
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9633
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8016
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5851
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4676
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4271
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3277
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.