Posted by 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.
Truncation
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.
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.
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.
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?
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.
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.
While I generally agree , I have databases where I don’t get any offline windows except for a full on upgrade every few years.
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.
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?
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: https://datageek.blog/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.
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.
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-13.12.18.443400-300 E35303168E576 LEVEL: Warning
PID : 3477 TID : 139779937134336 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DB1
APPHDL : 0-1496 APPID: *LOCAL.DB2.170525180642
AUTHID : DB2INST1 HOSTNAME: osprey
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-13.12.18.443785-300 E35303745E627 LEVEL: Error
PID : 3477 TID : 139779937134336 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DB1
APPHDL : 0-1496 APPID: *LOCAL.DB2.170525180642
AUTHID : DB2INST1 HOSTNAME: osprey
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
APPLICATION.
2017-05-25-13.12.18.483924-300 E35304373E590 LEVEL: Error
PID : 3477 TID : 139779937134336 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DB1
APPHDL : 0-1496 APPID: *LOCAL.DB2.170525180642
AUTHID : DB2INST1 HOSTNAME: osprey
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
/dev/mapper/vg_osprey-lv_root
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
TABLE_NAME REORG_STATUS REORG_COMPLETION REORG_START REORG_END
——————————————— ———— —————- ————————– ————————–
DB2INST1.COPPEL PAUSED FAIL 2017-05-25-13.06.42.151366 2017-05-25-13.12.18.524836
Thanks
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.
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.
Hi Ember,
Have you created any Page for using ADMIN_MOVE_TABLE for offline reorg?
Regards,
I don’t have that. Admin_move_table can be good, but I still find it risky.
Thanks Ember,
Your posts really very supportive for me as a new DB2 DBA
Could you please provide me the complete steps to setup db2 DPF environment?
I am waiting for response.
Email-id:- birendra11ram@gmail.com
Regards,
Birendra