Corrected on 9/8/2017 to reflect correct syntax to eliminate just the one table.
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.
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.
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).
There are some things to be aware of with ongoing support of a HADR system. I thought I’d group them together to provide a primer of do’s and don’ts for support of HADR.
Not long ago, I posted some of the details of using PowerShell as your command line for DB2 on Windows. I am definitely addicted to PowerShell as my command line when I have to work on Windows servers.
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.
Current statistics are vital to performance. If I’m encountering a database I haven’t regularly been supporting, and don’t know the maintenance plans and schedules, I frequently query to get an idea of how current they are.
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.
(Edited 8/12/2014 to add links to the old tutorials from IBM)
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.