Simple Configuration & Health script that reports high-level database metrics

The purpose of a simple Configuration & Health script is to provide DBAs with a high-level view of macro database metrics that could suggest specific areas worthy of further investigation.

The report would include these components as follows:

- General data (number CPUs, Memory, Db2 level, OS level)

- Database Uptime

- Database Sizes (GB)

- Current number of connections and highwater mark of Connections

- Swap Used

- Instance Metrics (Sizes: Limit, Current, Highwater Mark, Headroom=Limit – Highwater mark)

- Bufferpool Hit Rates (Data, Temp, Index by bufferpool)

- Bufferpool Service Times (ms)

- Prefetching (% Read, % Unread)

- Prefetch Service Time (ms)

- Shared Sort/Work Memory (Current Size, Highwater Mark Size, Highwater Mark Size for single largest sort consumer, highwater mark for concurrent sort consumers)

- Sort Metrics (% Overflows, % Throttled)

- Catalog Cache Metrics (Number of Overflows, % Hit Rate)

- Package Cache Metrics (Number of Overflows, % Hit Rate)

- Locking Metrics (# lock timouts, avg lock wait time (sec), # lock escalations, # deadlocks)

- Transaction Log Metrics (# Log Buffer full, % Log Reads (reads/write), Log Read Hit Ratio (log read I/O / Log Reads), Avg Log Read Time (ms), Avg Log Write Time (ms), % Current Commit Reads)

- BLU Processing (% of Columnar processing time)

Where applicable, a metric of comparison should be included. For example, 0 log buffer full is the target, 0 catalog cache overflows is the target, 0 package cache overflows is the target, log read time +/- 1-2 ms, log write time +-1 2-3 ms, bufferpool hit rates 98-99+%, instance headroom > 0, etc.

While one might put this type of report in the Data Management Console, not all Db2 customers have adopted DMC so a script based report is required. In addition to a GUI performance portal, Netezza offers a collection of DBA scripts that are widely adopted because of their ease of use and the value provided. This type of script would be akin to that.