Issue with Online Reorgs on Unformatted Event Monitor Tables

Posted by

This is an issue I’m currently seeing on about 2 out of 3 9.7 databases. First, a little background. My reorg script determines if reorgs are needed using reorgchk formulas, and the last couple of weeks, it has picked up the unformatted event monitor tables and done inplace reorgs on them. The servers where I’m seeing this are running 9.7 Fix Pack 4.

The issue is that the online reorg on the unformatted event monitor table never completes. The first time it happened, it had been running for 5 days before I realized it. I’ve got a PMR open with support to figure it out. The first thing they asked (and one of the first things I checked) was that LOCKTIMEOUT was set – and it is at 45. If you don’t have LOCKTIMEOUT set and have a hanging reorg, it could be related to that.

To find any active inplace (online) reorgs that you have running, use:

> db2 "select snapshot_timestamp, reorg_start, substr(table_name,1,18) as table_name, REORG_CURRENT_COUNTER, REORG_MAX_COUNTER, REORG_TYPE, REORG_STATUS from table(SNAPSHOT_TBREORG('$db_name', -1)) as tab_reorg where reorg_status=1"

-------------------------- -------------------------- ------------------ --------------------- -------------------- ----------- ------------
2012-02-21- 2012-02-18- MY_LOCKS                              0                    9   268500992            1

  1 record(s) selected.

You can also get the same information from a table snapshot:

> db2 get snapshot for tables on dbname
 Table Schema        = DBA
 Table Name          = MY_LOCKS
 Table Type          = User
 Data Object Pages   = 10
 LOB Object pages    = 1
 Rows Read           = 492
 Rows Written        = 8
 Overflows           = 0
 Page Reorgs         = 0
 Table Reorg Information:
   Reorg Type        =
        Inplace Table Reorg
        Allow Write Access
   Reorg Index       = 0
   Reorg Tablespace  = 9
   Start Time        = 02/18/2012 00:04:23.274071
   Reorg Phase       =
   Max Phase         =
   Phase Start Time  =
   Status            = Started
   Current Counter   = 0
   Max Counter       = 9
   Completion        = 0
   End Time          =

Notice that the reorg type from SNAPSHOT_TBREORG is a bit odd there. But both sources clearly show the reorg is in progress. The table here has about 500 rows in it. Am I particularly concerned about this one unformatted event monitor table getting reorged? No. But I am concerned about making sure that this doesn’t gum up my reorgs over time. I’m hoping to hear from support either that there is a known issue here, or that I simply shouldn’t attempt to reorg this type of table. Either I can script around rather easily, but wanted to make sure the issue was out there so that others didn’t end up with silent reorg failures or aren’t surprised to find a days-long reorg in their own environment.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.