A hub for sharing relational database knowledge and experimentation
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 availability and/or disaster recovery needs can easily be met by a 4-server HADR implementation.… Read the rest
Continue reading »
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 backup duration mystery.… Read the rest
Continue reading »
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 I’m constantly working on enhancements.… Read the rest
Continue reading »
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 large number of columns.… Read the rest
Continue reading »
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 what if you just want to find applications could become a problem, before the problem happens?… Read the rest
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 some version changes, the structure of tables that event monitors write to are changed.… Read the rest
Continue reading »
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 things I look for and work on as part of an implementation.… Read the rest
Continue reading »
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 storage attribute.… Read the rest
Continue reading »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 to filter out only the data I wanted to see.… Read the rest
Continue reading »
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 as of the writing of this article.… Read the rest
Continue reading »
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 all columns are referenced by queries.… Read the rest
Continue reading »
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 BLU.… Read the rest
Continue reading »