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. 8 2547
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
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. :-)
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.
Hello Ian, thanks for the info! I'll check them out.
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
Hello Philip, can you please email me the script (if it's too long to
post it here)? Thank you very much.
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. 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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...
|
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.
|
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.
|
by: serge |
last post by:
Can someone recommend what SQL Database compare tool to use
and why?
Thank you
| |
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
|
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.
|
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
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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...
|
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,...
|
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...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |