Blogs

Practical DBA – Comparing databases

By Eddy Coppens posted Jan 15, 2018 07:21 AM

  

As a DBA you are rather frequently asked whether the database is the same structure-wise all over the different environments. Whenever a decent release management is in place you can be very relaxed answer that this is the fact … when setting up a new environment missing such a release management, you’re not so sure. We’ve all come across the fact that we took it for granted that a release was already deployed to database A in environment B, and notice later on it didn’t make it.

You can always use db2look as a tool of opportunity and try to come up with the differences between both sets of DDL, but it doesn’t always provide you the clear view you need.

Recently I had to compare four of such databases and thought to try it via another angle: SQL. I first enabled FEDERATED on all databases and cataloged them in one single Db2 instance. In the Dummy database I have running within that instance, I nicknamed a few of the tables of the four participating databases.

The first query that came to mind was to count the number of schemas there were on each database. I wasn’t interested in those schemas standard to any IBM tool so I ruled those out:

Query 1 - showSchema_count.sql

 query1.png

resultQuery1.png

 

 

 

 

 

 

This is nice to have a quick peek on which database might have an extra schema or is missing one, but this does not really cut it. You need to know which schema does exist on a particular database but is missing in another. The idea I came up with will probably not win a price in a beauty contest, but it does the job just fine:

 

Query 2 - showSchema_Differences.sql

query2.png

resultQuery2.png

Basically, the start of the second query is based on the first one, but this time I pivot the data of the four databases into a column, to keep only those columns-representing-a-database containing a NULL-value. The NULL-value indicates a schema name which exists in another database but is missing for this particular database.

The same method could be used to find any other database object.

What I found the most daring, was to come up with a plan to show the differences found in the Database (Manager) Configuration. The approach I took was kind of like the second query but I had the idea that there should be a distinction between the configuration settings – some values should be different between databases (think of instance names, …), some could be different (memory, heaps, …) and some just have to be the same. It is up to you to shift the configuration parameters in ‘Query 3’ around as you like

The two queries that follow, do compare the Database (Manager) Configurations and might look scary, but are truly a next step building on the previous query:

  • “gatherData” assembles all possible values
  • “singleDb(m)Values” lists all parameters which are specific on their own and don’t need to have the same value for all environments or databases
  • “pivotTables” pivots the gathered data (“gatherData”).
  • the final where-clause does some decision taking:
  1. when the configuration parameter is found in “singleDb(m)Values” then check whether the deferred value has a different value from the current available value … indicating e.g. a stop/start is needed
  2. the configuration parameter is not found in “singleDb(m)Values” and thus has to be equal for all four environments / databases

Query 3 - showDBM_differences.sql

query3.png

resultQuery3.png

 (*) Note that the value of the column “DB2_DBMCFG_DEFERRED_VALUE” for the database manager configuration parameter svcename is different from the value of column “DB2_DBMCFG_VALUE”. The parameter is not yet activated as the instance needs to be recycled first.

 

  1. for the database differences only: some values I do wish to always see as they are too important for my specific case (e.g. blocknonlogged, …) and I will let them pop-up by specifying them in the WHERE-clause

Query 4 - showDB_differences.sql

 query4.png

resultQuery4.png

Querying for differences only works when the number of database to compare is rather small. The more databases you need to compare the lesser readable your queries will become. Sure enough, that content checking of functions, stored procedures and views are completely not covered by this method, this is where db2look still has to do its thing.

I read that the administrative views are deprecated, but I used them anyway as I’m still on a search on how to “nickname” the new table functions replacing the administrative views.

Eddy Coppens

BiteStrike

 

 

3 comments
9 views

Comments

Robert Wright
Jan 30, 2018 07:05 PM

IBM Data Studio alternative?

Hi Eddy

IBM Data Studio has a compare database capability. Have you tried that?

Cheers

Rob

 

Luiz da Silva
Jan 16, 2018 08:01 AM

.

 TGT_NAME TGT_VALUE TGT_DEFERRED_VALUE SRC_NAME SRC_VALUE SRC_DEFERRED_VALUE
 ------------------- -------------------------------------------------------- -------------------------------------------------------- ------------------- ---------------------------------------- --------------------------------------
 alt_collate NULL NULL alt_collate <> 
 app_ctl_heap_sz 256 256 app_ctl_heap_sz <256> 256
 appgroup_mem_sz 20000 20000 appgroup_mem_sz <30000> 30000
 appl_memory 40000 40000 appl_memory <40016> 35088
 applheapsz 256 256 applheapsz <4096> 4096
 archretrydelay 20 20 archretrydelay <20> 20
 ...

Luiz da Silva
Jan 16, 2018 07:58 AM

Alternative

Hi Eddy, 

I think you can simplify the "Union then Pivot" solution to a "Join" approach.

update dbm cfg using FEDERATED yes;
-- db2stop && db2start 
connect to

 

select 
 t.name as tgt_name, t.value as tgt_value, t.deferred_value as tgt_deferred_value,
 s.name as src_name, s.value as src_value, s.deferred_value as src_deferred_value
from 
 sysibmadm.dbcfg t join sourcedb_sysibmadm.dbcfg s on s.name = t.name
order by 
 1,2,4,5;