When is a Reorg Really Online?

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:


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.

Reorg Performance

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.

HADR Backpressure

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.

You may also like...

12 Responses

  1. Michael Krafick says:

    ADMIN_MOVE_TABLE is a lifesaver and makes things very easy to do. The only drawback is dropping/recreating RI. It really ham strings the tool. Can’t wait to try the new version of it in 10.1.2.

  2. Noel says:

    A good summary Ember. No answer to the Z lock unfortunately! Worth emphasizing that F4 requires a table rather than an index reorg, which you do allude to, and I don’t think WC uses any cluster indexes does it so F4 can often be ignored totally?

    • Ember Crooks says:

      WebSphere Commerce doesn’t have clustering indexes out of the box (though there used to be one on one of the likeminds tables – not sure if it’s there any more). But you can add clustering indexes, and you can also cluster on an index that is not explicitly defined as a clustering index. So I hate to say you can ignore it totally. While clustering reorgs are more expensive, if you’re doing a reorg anyway, it is sometimes worthwhile to cluster.

  3. Dean Betts says:

    It is great to read that experts are still “learning” and altering their ideas on basic database theories like REORG.

    In my shop, we only reorg tables, which rebuilds indexes anyway. Doing them offline is much faster but obviously requires the space. In a month worth of Saturday windows I can cluster-reorg the entire shop, approx 1.3Tb, with a 5th Saturday in the month for the small low-gain items.

    Another tip i can share is that any table with APPEND_MODE = Y will not INPLACE reorg; to do this, you need to disable it prior, which is an immediate ALTER TABLE statement and can be placed in your DDL.

    • Ember Crooks says:

      While I generally agree , I have databases where I don’t get any offline windows except for a full on upgrade every few years.

  4. Jeff Goss says:

    This is a pretty nice review of reorg – note that in 10.5 inplace table reorg gains a new option to only remove pointer/overflow pairs which greatly reduces the amount of work and logging needed to complete a reorg while addressing one of the biggest performance problems for tables – sync I/O required to fetch an overflow which can’t be pre-fetched. This along with the reduced need for clustering with the new prefetch modes under the hood of DB2 (prefetching based on rids in the index when sequential detection isn’t being effective) should make this a viable option for many users and reduce HADR congestion as well.

    Full disclosure: I was the solution owner for inplace reorg and owner of both inplace and classic reorg among other things in DB2.

  5. Naveed Shakur says:

    Hi, I have two questions for you, please help me to understand.

    If the tablespace is set to RECLAIMABLE_SPACE_ENABLED (0) and NOTRUNCATE is not used while online REORG will the tablespace size be reduced?
    I have a table with approx 15 Million records, with one CLOB column, will ADMIN_MOVE_TABLE be able to move table to new tablespace?

    • Ember Crooks says:

      To actually get the space released from a tablespace, it’s more than just a reorg. A reorg without the notruncate just releases space from the table back to the tablespace. To release space from the tablespace, you need to do more. It’s described here, assuming you’re using AST and tablespaces created on 9.7 or later: http://db2commerce.com/2012/07/12/how-to-delete-data-from-a-db2-table-and-release-disk-space/

      On your admin_move_table question, I don’t know off the top of my head without researching. I think it should move it as long as there is a primary key on the table.

  6. Uwe Thiel says:

    Thanks Ember,
    the simple detail about the S-Lock during truncate helped me a lot.
    The official IBM documentation is not showing this little detail. Or at least I have not found it in the manual pages 🙂
    Just for information purposes, in case anyone is needing it:
    Redwoods Cronacle Scheduler is “hanging” completely, during a reorg in phase truncate on the Job History Table (JCS_HISTORYJOB0) – so the truncate needs to be done during offline maintenance.

  7. miguel martin says:

    Hello, I have a problem with inplace reorg.
    db2 reorg table schema.table1 inplace allow write access

    After somo time I have an error in the diag path

    2017-05-25- E35303168E576 LEVEL: Warning
    PID : 3477 TID : 139779937134336 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : DB1
    APPHDL : 0-1496 APPID: *LOCAL.DB2.170525180642
    EDUID : 201 EDUNAME: db2reorg (DB1) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:1660
    MESSAGE : ADM1822W The active transaction log is being held by dirty pages.
    Database performance may be impacted.

    2017-05-25- E35303745E627 LEVEL: Error
    PID : 3477 TID : 139779937134336 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : DB1
    APPHDL : 0-1496 APPID: *LOCAL.DB2.170525180642
    EDUID : 201 EDUNAME: db2reorg (DB1) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860
    MESSAGE : ADM1823E The active log is full and is held by application handle
    “0-1496”. Terminate this application by COMMIT, ROLLBACK or FORCE

    2017-05-25- E35304373E590 LEVEL: Error
    PID : 3477 TID : 139779937134336 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : DB1
    APPHDL : 0-1496 APPID: *LOCAL.DB2.170525180642
    EDUID : 201 EDUNAME: db2reorg (DB1) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:6666
    MESSAGE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
    “Log File has reached its saturation point”
    DIA8309C Log file was full.

    But i also have space

    Filesystem Size Used Avail Use% Mounted on
    36G 31G 3.1G 91% /

    This is my log configuration

    Log buffer size (4KB) (LOGBUFSZ) = 2149
    Log file size (4KB) (LOGFILSIZ) = 1024
    Number of primary log files (LOGPRIMARY) = 9
    Number of secondary log files (LOGSECOND) = 200

    And when i monitor the reorg it get paused

    ——————————————— ———— —————- ————————– ————————–
    DB2INST1.COPPEL PAUSED FAIL 2017-05-25- 2017-05-25-


    • Ember Crooks says:

      You’re running out of transaction log space (or encountering log file saturation). 1024 is really small for LOGFILSIZ. I’d see what other activity is going on – while inplace reorgs use a lot of log files overall, they don’t often use a lot of active log space. I usually start with 10,000 for my transaction logs in many environments.

  1. October 7, 2014

    […] The Reorg itself will run longer if you’re doing inplace reorgs on a index as opposed to a reorg that does not cluster the table on any index. The reason for this is that a reorg without specifying an index will scan the table starting at the end, and move things around as it makes sense, and at the end may have a range of pages that can be released from the table. It never has to scan an index. An inplace reorg on an index, though, must first clear a range of pages at the beginning of the table, then scan an index to determine what rows need to be in what order and move those rows into the proper positions. This is a lot more data to move around, and can take significantly longer. Especially in larger databases, the amount of time a reorg takes may be a limiting factor in the kinds of reorgs you do, especially considering that inplace reorgs are not fully online (see When is a Reorg Really Online?). […]

Leave a Reply

Your email address will not be published. Required fields are marked *