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
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
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:
- 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
- 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
(*) 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.
- 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
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.