75 ways to demystify DB2: #5: Tech Tip:Have you encountered a lock wait during online reorg TRUNCATE phase ?

Body

The following explains the "Truncated" state of a reorg and why  applications might have a LOCK WAIT on reorg :

Online reorg holds an S lock on the table during the process of table truncation. While online reorg holds an S lock on the table, no insert/update/delete activity can occur on the table. During the online reorg process rows are moved to pages closest to the start of the table. While this is going on, rows inserted into the table may end up close to the end of the table and online reorg may not see these rows depending on its scan position.

Once online reorg is completed, if the table is to be truncated, it acquires an S table lock and does a backward table scan to move any rows it missed that are at the end of the table to the logical end of the table. Once this is completed, it then truncates the table. The physical truncation of the table is a very short duration. However, if there are many empty pages past the logical end of the table, online reorg has to scan each page to make sure there are no rows beyond the logical end of table need to be moved.  The duration of the S lock on the table will depend on the number of  pages past the logical end of table that has  to be checked for rows. If rows need to be moved during the  truncation process, the progress of  online reorg will be dependant on concurrent scans (as it is while it is moving rows during reclustering/space reclamation). So, to minimize the amount of time the S  lock is held,one must ensure frequent commits are done.

Also might make sure while online reorg is running there are not many  IUD (Insert/Update/Delete) activity. If no rows need to be moved during the truncation process, the duration of the S table lock should be very short.

If the amount of time the S lock is held is a concern and space reclamation is not an issue, you can specify that the table not be truncated (truncated is the default) using NOTRUNCATE option.


Downside of using NOTRUNCATION :
NOTRUNCATION might cause empty spaces to exist in the table after the reorg. If a reorganized  table has gone through a truncate phase ,these empty spaces can be freed up for  use of other tables in the same tablespace


Reference  :

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024854.html