The WebSphere Contrarian: Are you sure you want to reorg that messaging engine database?

The standard practice for database administration is to periodically check on the database and table organization to insure optimal performance -- but do these standard practices apply to a database used for JMS persistent message storage with IBM® WebSphere® Application Server? This content is part of the IBM WebSphere Developer Technical Journal.

Tom Alcott, Senior Technical Staff Member, IBM

Tom AlcottTom Alcott is Senior Technical Staff Member (STSM) for IBM in the United States. He has been a member of the Worldwide WebSphere Technical Sales Support team since its inception in 1998. In this role, he spends most of his time trying to stay one page ahead of customers in the manual. Before he started working with WebSphere, he was a systems engineer for IBM's Transarc Lab supporting TXSeries. His background includes over 20 years of application design and development on both mainframe-based and distributed systems. He has written and presented extensively on a number of WebSphere run time issues.

30 July 2008

In each column, The WebSphere Contrarian answers questions, provides guidance, and otherwise discusses fundamental topics related to the use of WebSphere products, often dispensing field-proven advice that contradicts prevailing wisdom.

Swamp things

I recently fielded an e-mail query, which resulted in series of several more e-mails over the course of a couple of days, that prompted me to recall a saying that a former manager of mine was fond of:

When you're up to your armpits in alligators, it's easy to forget why you wanted to drain the swamp.

What brought this to mind, you ask? Well, the query was for an "IBM recommendation to give to a customer on running DB2 reorgs and runstats on their messaging engine DB," and while I didn't presume to speak on behalf of all of IBM, my recommendation was not to worry about doing so. Not being fond of alligators, I usually try to avoid draining swamps, and while it seemed clear to me why the swamp didn't even need to be drained -- or, in this case, why database utilities on the messaging engine database didn't need to be run -- the recommendation didn't sit well with the customer. While I'm not sure that the customer was ultimately convinced, I thought that I'd share my rationale and methodology with you here so you can decide what might be most appropriate for your environment.

Surveying the swamp

Before draining a swamp, it's always a good idea to survey it. In the case of the messaging engine database, you can start your survey in the WebSphere Application Server Information Center, which provides this information about the database structure:

Table 1. Messaging database tables
SIBOWNEREnsures exclusive access to the data store by an active messaging engine.
SIBCLASSMAPCatalogs the different object types in the data store.
SIBLISTINGCatalogs the SIBnnn tables.
SIBXACTSMaintains the status of active two-phase commit transactions.
SIBKEYSAssigns unique identifiers to objects in the messaging engine.
SIBnnn, where nnn is numericContains persisted objects such as messages and subscription information; these tables hold both persistent and non-persistent objects, using separate tables for the different types of data.

One of the issues troubling the customer was the SIBOWNER table holding an exclusive lock as explained in the WebSphere Application Server Information Center, which states (in part):

The SIBOWNER table in the data store holds the lock as a pair of unique identifiers in a single row. When it starts, a messaging engine uses these two identifiers to acquire and maintain its exclusive lock:


    The unique identifier for a messaging engine, which remains the same whenever the messaging engine stops and restarts.


    The incarnation identifier for a messaging engine, which changes every time the messaging engine starts.

These identifiers determine which messaging engine is using a data store. These identifiers also determine whether a running instance of a messaging engine has maintained its exclusive lock for the time period during which it was running.

As a messaging engine starts, it obtains an exclusive table lock on the SIBOWNER table.

The result of the messaging engine holding an exclusive lock was that the database maintenance utilities could not be run in the preferred manner, which was to do so for all tables in the database.

Reclamation needed?

So now that you know what the swamp -- um, database -- looks like, do you really need to drain it? You can look at the content of this table by stopping the messaging engines that are using a messaging engine database:

>db2 select * from ibmme0.sibowner

---------------- ---------------- ----------- -----------------
E4C0B7CC5E3B76D3 4C224C2252CE2BDD           1                 0

1 record(s) selected.

Based on the description of this table, this is what I expected to see. Further, the documentation on the remainder of the tables (and my knowledge of how the messaging engine works) led me to expect that all of the remaining tables for the messaging engine would be comprised of a small number of rows. I validated this by running two tests, one synchronous and one asynchronous, where over 5,000,000 messages were persisted and processed from the database, and then ran DB2® REORGHK to determine if a database reorg is needed. Here is the output from REORGHK:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME                  CARD     OV     NP     FP ACTBLK    TSIZE  F1  F2  F3 REORG
Table: IBMME0.SIB000           48      0      3      3      -     9936   0 100 100 --- 
Table: IBMME0.SIB001            -      -      -      -      -        -   -   -   - --- 
Table: IBMME0.SIB002            -      -      -      -      -        -   -   -   - --- 
Table: IBMME0.SIBCLASSMAP      12      0      1      1      -      984   0   - 100 --- 
Table: IBMME0.SIBKEYS           3      0      1      1      -      111   0   - 100 --- 
Table: IBMME0.SIBLISTING        3      0      1      1      -       90   0   - 100 --- 
Table: IBMME0.SIBOWNER          1      0      1      1      -       62   0   - 100 --- 
Table: IBMME0.SIBXACTS          -      -      -      -      -        -   -   -   - ---

The key column of interest here is, not surprisingly, entitled REORG, which displays an "*" when a reorg of a table is warranted. You'll note that the tables in this database don't require any maintenance, even after processing over 5,000,000 messages.

If you stand back and think about this for a minute, this shouldn't come as a great surprise. Database reorgs are performed for a couple of reasons:

  • to organize related data close together to minimize disk access times.
  • to evenly distribute free space, again to minimize disk I/O.

In the case of the SIBOWNER table, there's only a single row, so there's no "related data" to try and place close together; it should be relatively apparent why no reorg is required. Each of the other tables contain a small number of rows as well, again negating the need for maintenance. Even the SIB000 table, which is where the messages were inserted into and deleted from, doesn't need maintenance. Since the messaging engine is processing messages as they arrive (or quickly thereafter), the database doesn't grow very large, and as a result doesn't require maintenance.

Dip your toe before you drain

While it's probably not prudent to suggest that the messaging engine database never needs maintenance, I think you can safely conclude that not all the tables need to be examined. Given the use and characteristics of the tables in the messaging engine database, only the SIBnnn tables are likely candidates for reclamation -- sorry, maintenance. In certain cases, perhaps during a surge in messages or an outage of the messaging engine (assuming your weren't using WebSphere Application Server High Availability for failover), enough records might be placed in the database to warrant maintenance. Another possible cause of message build-up in the data store are "poison messages," which can be managed using the methods introduced in this Fix Pack. Regardless of the cause, I'm not sure I would always go to the effort of draining the swamp. Perhaps I would monitor the water level periodically, in this case nightly or weekly, checking the SIBnnn tables with the command:


and then perform a reorg if warranted. You could also choose to check the remainder of the tables as well, with the exception of the SIBOWNER table, without stopping the WebSphere Application Server messaging engines -- though again these other tables contain only a small number of rows when compared to most relational databases, so the vast majority of the time a reorg will not be required

While I've focused on UDB (DB2) as the messaging engine data store in this discussion, this advice and rationale is the same for other database managers as well, though the tools used to monitor the database will differ by vendor -- unless of course you're fond of draining swamps!



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into WebSphere on developerWorks

ArticleTitle=The WebSphere Contrarian: Are you sure you want to reorg that messaging engine database?