Raghu On Tech

Pro tips on managing data at scale

How To Deep Compare Two Db2 Environments?

by | Dec 19, 2017 | 11 comments

What is DB2 Deep Compare ?

Have you ever tried to compare DDL between two schemas or databases and had to deal with the tedious task of text processing to figure out the differences ?

Also did you ever run into a situation where you need to compare two tables and its depending objects i.e. its columns, column order, data types, primary keys, foreign keys, tablespaces and bufferpools etc. If so have you tried to solve this problem with SQL and someone had to bring a fire extinguisher to stop your hair from catching fire ? If so, please read on…

A picture is worth more than a 1000 words, so take a look at the below data tree and how would you go about comparing two similar data trees one from source and another from target ?

In the interest of readers time, before discussing the hoops that I had to jump through to arrive at a solution, I am going to show how to run Db2Diff and compare two DB2 environments. Remember you can even compare two different schemas with in the same database.

EXPAND/COLLAPSE → What does Db2Diff compare anyway ?

It compares a whole lot! This initial developed version compares the following

Operating System:

  • OS NAME
  • KERNEL VERSION
  • TOTAL CPU’s
  • CONFIGURED CPU’s
  • TOTAL MEMORY
  • OS VERSION
  • ARCHITECTURE TYPE

Registry Variables:

  • MISSING/UNSET Registry Variables between the two environments
  • Registry Variables set to different values

Instance Environment:

  • INSTANCE NAME
  • RELEASE NUMBER
  • SERVICE LEVEL
  • BUILD LEVEL
  • PROGRAM TEMPORARY FIX (PTF)
  • FIXPACK NUMBER
  • DB2 VERSION

DBM Configuration (DBM CFG):

  • COMPARE DBM CFG Parameters when they were not set to AUTOMATIC and reports mismatches
  • When either one of the DBM CFG is set to AUTOMATIC, reports only when the other one is not set to AUTOMATIC

DB Configuration (DB CFG):

  • COMPARE DB CFG Parameters when they were not set to AUTOMATIC and reports mismatches
  • When either one of the DB CFG is set to AUTOMATIC, reports only when the other one is not set to AUTOMATIC

Database Objects:

  • As of now only tables and all its dependent objects such as PK’s, FK’s, Indexes, Tablespaces, bufferpools and their properties are being compared. You can view high level things that are being compared in the image shown above.

How do you run Db2Diff ?

  1. Determine the version of your java run time environment, remember you can run this utility from linux, unix and windows anywhere as long as you have java installed. This utility comes prepackaged with all the libraries you need. You can run the following command to determine the version of your JRE.

2. Download the respective db2utils.jar and db2diff.prop files depending on the version of your JRE from below link.

Download db2utils.jar

3. Update the properties file (db2diff.prop) with proper connection information for your source and target databases. If you want to compare the whole databases between the two environments then simply leave srcschema and trgschema properties empty. If you just want to compare just single schema (schemas can be different e.g. srcschema=”PROD” and trgschema=”TEST”) populate srcschema and trgschema respectively.

4. Run the downloaded jar file as follows, remember to keep db2diff.prop in the same directory as the db2utils.jar file. It will display a clean and formatted output of all related database objects for the tables as shown below.

5. It is important to remember in the command “java -cp db2utils-1.x.jar Db2Diff”, Db2Diff is case sensitive. It is the name of the  java class that gets called up on running the command. This utility should work on all DB2 versions above V10.1.

6. If you run this utility for comparing just one schema, environment comparison will be bypassed i.e. Db2Diff will not compare    Hardware Info, Registry Variables, DBM CFG, DB CFG.

 

My Journey Towards The Solution:

I tried multiple things before getting to the point of building an all in one jar file. Please read on if you would like to know a little bit more about the journey…

Method 1 (SQL):

Like any normal DBA I tried to solve this problem using export, import, custom SQL with multiple joins etc. and ended up with a horribly performing SQL. I can not run a nasty SQL against a database in good conscience. On top of poor performing SQL, for using native SQL capabilities I had to do some data movement from one database to another to compare the data between two databases. At this point I simply came to an understanding that using SQL to solve this problem is a bad idea. Even using federation is not flexible.

Method 2 (Perl):

Next, I turned to my favorite scripting language perl to solve this problem and I must admit things were looking really good with perl. As perl DBI::DBD drivers let you handle connections to any number of databases and you can fetch the data from the SQL queries in the form of different data structures or fabricate the data structures from the data to suit your need. Although I made significant progress with perl towards reaching a solution, few things bothered me with this approach.

I have few people who will use this tool other than myself. These users may run this utility from different environments and for this utility to work with perl, the user needs to configure perl DB2 driver which can take a little bit of time and work.

What if I need to run this utility on a windows environment ? Not all windows boxes have active perl installed. Perl was initially developed with Unix flavors in mind.

Perl is outstanding to do something quick and dirty, but when the complexity of the programs grow and as the number of lines of code increases it can bring in some maintenance difficulties (especially when there are a lot of interdependent data structures). I learned it first hand by looking over what I wrote. You can take a look at the perl code that I wrote below to solve this problem and understand what I am trying to say.

So, I want a solution that I can use with out having to worry about causing adverse impact to the environment and I want to use it anywhere i.e. from my desktop, my laptop, my server etc. with out needing to do any extra setup.

I took a step back and clearly wrote down the problem definition. It is at this point I got enlightened that most people try to solve a problem with what ever tools they know well, even though other tools provide a more comprehensive solution. I learned the following things

  • I need a tool that can be executed from a variety of environments with minimal configuration.
  • I need a language with good support for data structures in an object oriented fashion (as objects may contain nested data structures).
  • Language should have easy garbage collection, as this tool should run with minimal memory foot print.

Method 3 (Java):

After doing some analysis, I determined that I could easily address all the aforementioned concerns raised during SQL and perl solutions using java. Using an object oriented language such as java, you can easily build things (tables, indexes etc.) as objects. In our case think of table as an object, PK as an object, Index as an object etc. You can put PK object, Indexes object etc. inside a table object. Once you have the nested object created, simply compare the objects between source and target.

java has been around for a while, has strong garbage collection capabilities, platform independent etc. so I decided to go ahead with java as programming language of choice.

 

Advantages of using Db2Diff:

  1. It can be run from any command line (Windows, Linux, OS X, AIX etc.) as long as it has java runtime environment installed.
  2. Minimal memory foot print as java is pretty good at garbage collection.
  3. Only meta data will be fetched from the database. Data structures along with their comparison is handled inside the java JVM (which could be on your desktop or mac), as a result this utility runs separately from database engine.

 

Whats next for Db2Diff:

  1. Initial version of db2utils.jar got shipped with only deep comparison of most fundamental RDBMS object i.e. table. Next versions will handle Routines, Stored Procedures, functions and other object comparisons.
  2. Would love to hear back from the community on what other functionality you would like to see as part of this utility ?
  3. Please leave comments or suggestions if you see any bugs while using this utility and I will try to fix them as soon as I can.

11 Comments

  1. Chandra Sekhar Nookarapu

    Very detailed written utility that can be useful for DBAs , developers and test analysts. Need to have deep understanding of DB2 internals and functionality to develop this utility. Also very well explained. Thank you for Sharing this.

    Reply
    • Raghu

      Thank you Sekhar! I am glad you find this utility useful. I hope to find more time to incorporate more features into this utility.

      Reply
  2. Ram

    Superb.Thanks Raghu

    Reply
    • Raghu

      Thank you Ram!

      Reply
  3. markus fraune

    hey raghu, sounds like a really helpful tool. will test next week and come back to you!

    Reply
    • Raghu

      Markus, thank you for taking time to test this out. Let me know how it goes.

      Reply
  4. Steven James

    I’m trying to run your tool against two databases stored on an iSeries. It’s asking for a DB Connect license. I’ve used jdbc connections in the past without this license… do you know why it would be asking for this?

    Reply
    • Raghu

      Hi Steven,

      Sorry I should have made it clear in the blog post. This tool is only designed to work with DB2 for LUW.

      Thanks!
      Raghu

      Reply
      • JW

        Are you going to develop this in the future? WHat about licence, it is open source? Are you piblishing source code?

        Reply
        • Raghu

          I have plans of developing this in future potentially with support from community. I am not spending as much time as I would love to on developing this utility.

          Plan is to get a github account and publish the source code/document. It will be open source.

          Reply
  5. Hung Tam Nguyen

    Looks like a great tool, been looking for this in the past. Take a look at lightweight commercial tools such as DBSolo if you need ideas for further improvements. 😉

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares
Share This