DB2 LUW Performance: Are there any pending changes?

Recently a customer wrote me and asked if there was a way to run a query that would report on any pending (deferred) DB CFG or DBM CFG changes. More specifically, he wanted to create an alert in DBI's Brother-Hawk™ that would alert him if any changes would occur upon the next database restart. With V9+ SQL Snapshots, this is easy...

---

You can check to see if there are any pending (deferred) DBM and DB CFG changes with this SQL Snapshot query:

select dbcfg.name, dbcfg.value as current_value, dbcfg.deferred_value as pending_value
from sysibmadm.dbcfg dbcfg
where dbcfg.value != dbcfg.deferred_value
union all
select dbmcfg.name, dbmcfg.value as current_value, dbmcfg.deferred_value as pending_value
from sysibmadm.dbmcfg dbmcfg
where dbmcfg.value != dbmcfg.deferred_value
order by 1 asc;

It would also be easy to simply count the number of pending changes, if any.

A quick shameless marketing moment

Unlike the IBM DB2 Health Monitor, Brother-Hawk™ is flexible and extensible - you can add your own alert criteria (if you can test a condition with a SQL statement then you can turn it into an alert), define the hours of the day and days of the week to which the alert applies, have the alert rule automatically apply to one or more servers, instances, and databases, customize the alert emails, send SNMP trap messages to consoles, run DB2 commands, and/or execute OS commands and scripts. The DB2 Health Monitor ships with about 12 health indicators but Brother-Hawk provides dozens out of the box - and you can add your own.

• Learn more about Brother-Hawk™
• If being proactive about performance is important, if you require alert capability, or if you loathe the IBM DB2 Health Monitor like 95% of the DB2 LUW Community, please visit our Brother-Hawk versus DB2 Health Monitor comparison chart

DBI Brother-Eagle® for DB2 LUW Enterprise Edition customers can also add key performance and health indicators to Brother-Eagle plus create their own custom drill down reports. Using the SQL Snapshot example above, it would be easy to create an alert and report for pending DB CFG changes:

Step 1: Create the Custom Metric "DBCFG-Chgs", set the alert high threshold to 1, and enter the supporting query for the "stock ticker" display: select count(*) from sysibmadm.dbcfg where dbcfg.value != dbcfg.deferred_value and DBPARTITIONNUM = %PARTNUM%

Step 2: For the Custom Metric Parameter Drill Down statement, simply enter the query: select * from sysibmadm.dbcfg where dbcfg.value != dbcfg.deferred_value and DBPARTITIONNUM = %PARTNUM%

Step 3: Try it and have fun!

Cheers,
Scott