HADR does an awesome job of replicating all logged operations to 1-3 standby databases. It is remarkably simple to use and pretty resilient. More than once I’ve started talking to a client about PureScale only to discover their actual high…
While I have tuned backup performance before, it has often been through physical database changes (spreading tables across more table spaces) that I achieve my greatest results. This post is not about backup performance tuning, but about solving a specific…
I have some very specific perspectives on monitoring DB2. In addition to regular consulting in my day job, we also provide full-service virtual DBA services, including monitoring. The monitoring we choose to do is very much under my control, and…
Purpose To generate a comma separated list of columns in a MON_GET table function, in the same order you should get the columns if you query the table. This SQL should handle larger tables like MON_GET_DATABASE that have a very…
Some time ago, I wrote a post that suggested using the NUM_LOG_SPAN database configuration parameter to prevent long-running transactions from filling your transaction log. Using NUM_LOG_SPAN will indeed prevent the problem of long running transcations causing log file saturation, but…
I am a huge fan of always running a locking event monitor, and also using other event monitors when appropriate. This means that most databases I support have at least one event monitor, whether it is active or not. With…
There are an astonishing number of vendor solutions available with specific interfaces for DB2. Working with a variety of clients, I see and help to evaluate and implement a variety of backup solutions. I thought I’d share some of the…
Since DB2 9.7, you have been able to reduce the size of tablespaces using the statement: ALTER TABLESPACE USERSPACE1 REDUCE MAX Using this functionality, however, requires that the tablespace uses automatic storage and that it was created with the reclaimable…
When I started working with DB2 (on versions 5 and 7), the only option for seeing what was going on in the database was snapshots. I spent a fair amount of time parsing the data in snapshots with Perl scripts…
Purpose These statements calculates the buffer pool hit ratio for both BLU and non-BLU activity. This post includes three SQLs – for calculating at the database, bufferpool, and tablespace level. The separate BLU calculations are not yet included in sysibadm.bp_hitratio…
Purpose This statement reports how selective at the column level queries are that run against a specific table. This does not look at overall selectivity or row selectivity. It will only work in DB2 10.5. BLU performs best when not…
Purpose This statement calculates the compression ratio for BLU tables. The compression ration can be used to help identify tables where compression is not optimal and you may need to look into why. Compression is critical to optimal performance on…