When is a Reorg Really Online?

You may also like...

13 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.

  8. 4APK.RU says:

    since that is really a waste to have some space allocated aside only for the online reorg .. When it does this, it builds the copy in the temp tablespace, drops the original table, and then copies the data from the temp tablespace back to the original data tablespace.

  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 *