![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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.
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
| Table | Purpose |
|---|---|
| SIBOWNER | Ensures exclusive access to the data store by an active messaging engine. |
| SIBCLASSMAP | Catalogs the different object types in the data store. |
| SIBLISTING | Catalogs the SIBnnn tables. |
| SIBXACTS | Maintains the status of active two-phase commit transactions. |
| SIBKEYS | Assigns unique identifiers to objects in the messaging engine. |
| SIBnnn, where nnn is numeric | Contains 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:
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.
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 ME_UUID INC_UUID VERSION MIGRATION_VERSION ---------------- ---------------- ----------- ----------------- 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.
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:
>DB2 REORGCHK UPDATE STATISTICS ON TABLE SIB000 |
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!
-
WebSphere
Application Server Information Center: Diagnosing problems with data store exclusive access locks
-
WebSphere
Application Server Information Center: Data store exclusive access locking
Tom Alcott is consulting IT specialist 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.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
