We bandy about the term “online” reorg. But you’d be surprised how often there are issues with that. IBM officially calls it an “INPLACE” reorg. Read on to understand exactly when it is fully online and when it is not.
When we first got our hands on online reorgs, it seemed like the “INPLACE” keyword was all we needed, and we had availability during reorg – that any they required less disk space at one time. Yet, if you don’t specify the “NOTRUNCATE” keyword on the reorg command, then at the end of the reorg, DB2 does a lock-drain on the table, and acquires an S lock. This may be more accepatable in a DW/DSS environment than it is in an OLTP/e-commerce environment. I still find that my smaller clients can handle truncation just fine, so I implement reorgs allowing truncation (usually at a very low volume time), and then wait for issues before adding in that “NOTRUCATE” keyword.
What does NOTRUNCATE really do? Well, in the course of a reorg, the pages are properly populated with data – fitting as many rows as possible on to a page. If there has been more data deleted than added or if the pattern of deletions has caused pages to become sparsely populated, then after the reorg, DB2 is left with empty pages at the end of the table. If you do NOT specify NOTRUNCATE, an S lock is acquired on the table and these pages at the end of the table are freed back to the tablespace to be used however makes sense for the tablespace.
If NOTRUNCATE is specified, there is no lock, and those pages remain a part of the table to be filled or left empty as data growth for that table only allows.
Think about the growth patterns in your database when deciding whether or not to allow truncation. Generally my e-commerce databases are steadily (and slowly if I’m pruning right) growing. Unless there’s a large delete, I don’t expect to have a lot of pages to truncate, and even if there are some, they’ll generally get filled in with data pretty quickly.
Thus if you’ve been doing a lot of deletion, you’ll want to let your reorgs truncate so you can get that space back.
Online Reorg of Indexes
We call it online index reorgs. And, heck, it is more online than the old classic offline reorgs. But, unless you’re specifying ‘CLEANUP ONLY’ or ‘CLEANUP ONLY PAGES’, DB2 will re-build the indexes and acquire a Z lock on the table while switching indexes. Yes, a Z lock – also known as a super-exclusive lock. This causes troubles, especially for my larger clients.
To be clear, the syntax that triggers this behavior is:
REORG INDEXES ALL FOR schema.table ALLOW WRITE ACCESS
The good part is that you should only be doing this type of reorg if formulas F5 or F6 are flagged in REORGCHK. If formula F7 is flagged, you should add ‘CLEANUP ONLY’ and if formula F8 is flagged, you should add ‘CLEANUP ONLY PAGES’. If formula F4 is flagged, you should be reorging the table on the index only if you desire the table to be clustered on that index, otherwise you should ignore flags on F4.
I’ve asked a number of people in a number of situations if I can either improve reorg performance or throttle the reorgs. Reorgs cannot be throttled, and several people recently have made it clear to me that IBM’s strategic direction is not to improve REORG, but to reduce the need for reorgs.
There are two things you should know about REORG and perceived performance. The first is that an inplace REORG will wait forever for each and every lock it needs. REORG does not respect LOCKTIMEOUT. If you have users that are not committing frequently, or worse, a table where an application holds a lock on the table continuously, you can run into problems. Maybe the answer to speeding up the reorg is to get your applications to commit more frequently.
I’ve actually had an issue with an event monitor that writes to an unformatted table – I have to specifically exclue it from reorgs, or my reorgs hang up on it.
The other thing you need to know about reorg performance is that clustering reorgs are slower than non-clustering reorgs. A clustering reorg (REORG table on INDEX) does a forward scan through the table and generates a RID list using the specified index. It clears out a few pages, and then moves in the RIDs that need to be there. A non-clustering reorg starts at the end of the table and tries to move rows from the end of the table up among the other rows – if it vacates pages, they are already at the end of the table.
That means that a clustering reorg often moves every row in a table, and reads the index too, while a non-clustering reorg only moves the rows it needs to.
Between that and some other details I’ve learned recently, I am reconsidering my strategy of many years to reorg every table that doesn’t have a clustering index on it’s primary key. I think I’ll still aim for some clustering, but based on things I’ve learned about index efficiency, I think it’ll be more selective, and focused on lower cardinality columns.
I have a number of clients who see HADR go into a “CONGESTED” state during reorgs. And indeed, online reorgs can generate an astounding number of log files. Every one of those log entries must be copied over the HADR link. This can slow down your reorg performance if you’re using NEARSYNC (or SYNC). At the IDUG NA 2013 conference, I actually got two ideas on how to deal with this problem.
First, consider making the LOGBUFSZ larger on the Standby than on the Primary. I’ve always tried to keep my database settings the same between primary and standby, but this actually makes sense to me – more memory available for logs on the standby with NEARSYNC doesn’t affect recoverablity unless both servers fail at exactly the same time, and it does give a bit more room.
Second, there is a new parameter available in DB2 10.1 called HADR_SPOOL_LIMIT. It is in the DB config, and if set to a non-zero value, it allows logs on the standby to spool to disk – so they are still exeternalized, and your recoverablility is not impaired. The one drawback is that your actual failover time might be longer, because if there were a lot of logs spooled to disk, the standby would have to rollforward through all of them to come up. I wouldn’t like to be spooling all the time, but it sounds like a great solution for spikes like online reorgs can cause. I have not actually used this feature, since I’ve been unable to get my hands on 10.1 yet – hopefully WebSphere Commerce will certify 10.1 this year so I can play with it.
Offline Reorg Trick
Did you know you can do an offline reorg using ADMIN_MOVE_TABLE? And with 10.1.2, DB2 will support ADMIN_MOVE_TABLE for tables with RI. I’d have to investigate how online ADMIN_MOVE_TABLE really is(seems like it would make sense to have a table lock for the switch), but it is intriguing.