DB2 LUW Performance: Are there any pending changes?


Posted by Scott on January 18, 2010, 11:00 am
in DB2 Performance How-To ( DB2 Performance)

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...
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. icon_wink


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.


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

Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=161