Starting a DB2 Instance in a Quiesced State

Some applications are really good at continually trying to re-establish connections to a database. This can be useful when I want to quickly bounce the database and have the app reconnect without also having to bounce the app. It is problematic when I need DB2 to be down and stay down, but still allow me to work with it. This can be needed to kick off a backup or to keep things down during an upgrade. When upgrading to DB2 8.1, there was actually a point in time where, if an application connected, the entire upgrade was hosed.

Continue reading »

Ember’s Best Practices for Runstats and Reorgs

A number of new clients I’ve worked with lately have had minor or major runstats or reorg issues. I have tons of advice on runstats and reorgs across many blog entries, but I don’t have one place where all that advice is brought together. Here is that article. The truth is that reorgs and runstats are complicated, and most DBAs get them wrong or disagree on them in some way. There is no one right way, but there are a vast variety of ways to get it wrong.

Continue reading »

Using DB2’s ADMIN_MOVE_TABLE to Move Tables from One Tablespace to Another

What is ADMIN_MOVE_TABLE

ADMIN_MOVE_TABLE is an administrative stored procedure introduced in DB2 9.7. The intent is to provide a tool that can be used to perform an online move of a table, while transactions are still occurring against the table. Moves can be used to change what tablespace a table is in, convert a table to MDC, change the table name, perform several changes that would normally require reorgs, reduce the size of a column, and perform other changes. In the early fixpacks, it was not much more than any DB2 DBA could have written themselves. It uses triggers against the source table to track changes in a staging table while moving the data to a new table. Once the new table is populated, then the data tracked by the triggers is replayed against the target table and a table-level exclusive lock is obtained on the source table for a short period to make the switch (rename).

Continue reading »

Giving Automatic Maintenance a Fair Try

I’m a control freak. I think that control freaks tend to make good DBAs as long as they don’t take it too far. My position for years has been that I would rather control my runstats, reorgs, and backups directly than trust DB2’s automatic facilities. But I also try to keep an open mind. That means that every so often I have to give the new stuff a chance. This blog entry is about me giving automatic maintenance a try. I am NOT recommending it yet, but here’s how I approached it and what I saw.

Continue reading »

Clustering a Table on an Index

I have been playing a fair amount lately with clustering indexes and have been rehashing my views on reorging tables on an index. This is still a work in progress, but thought I’d share some details and see if others out there have any thoughts to share with me and others on it.

Continue reading »

How to Tell When a Table Reorg is Needed

Reorgs are one critical piece of both performance maintenance and maintaining how much disk space is used. In my first blog entry in this series, DB2 Basics: What is a Reorg, I talked about some purposes of reorgs. In this entry, I’m going to talk about how to determine what kind of reorg is needed on what tables. I will have a future blog entry to discuss index reorgs in detail. The advice I’m giving here is directed at single-partition databases with non-partitioned tables.

Continue reading »