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. Sometimes even by a two-server implementation.
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.
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. I thought I’d blog on what I like to monitor and alert on without going too deeply into how these things can be monitored. There are excellent tools on the market, and excellent ways of scripting your own monitoring.
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.
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. This means that as you upgrade DB2, you must also run a command to upgrade your event monitors.
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. Sometimes the DBA has input on a solution chosen, and other times, A solution is dictated and a DBA must simply implement it.
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. Slowly, methods of using SQL to access snapshot data were introduced. First table functions for accessing snapshot data, then SYSIBMADM views, and finally we have the MON_GET family of table functions. I’m going to focus on the use of these MON_GET interfaces in this post.
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.
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.
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.