The Sevice Integration Bus (SIBus) which is the default messaging provider for WebSphere Application Server uses a message store to save messages and the operating information needed by the messaging engine. There are 2 ways the message store is implemented in SIBus -
FileStore – The messages are stored in a flat file
DataStore – A relational database is used to store messages using a JDBC interface.
Apart from the messages, the message store is used to store a lot of other critical information. To name one - it stores the status of 2 phase transactions the messaging engine is involved in.
The scope of this blog entry only involve the use of DataStore (the FileStore can be perhaps covered some other day).
When a messaging engine is configured with a data store, a number of tables are created. I would not be getting into details of each of them.
One of the primary reasons one should not be playing around with the tables is because the SIBus developers/architects never designed it to be played around with. However there are some who do. The primary reason I have noticed people doing it is to delete messages on the queue (because they are not able to consume it and the stuck messages are stopping other messages to be consumed). The SIB001 table contains the persistent objects (messages). This is the table that many truncate to delete messages without understanding what can go wrong. Lets discuss a few things that can go wrong -
The SIB000 table contains the information about the data in SIB001 (for persistent data) and SIB002 (for non persistent data). Now if SIB001 is truncated, the messaging engine perceives the data to be still there because SIB000 has a reference to it. This will confuse the messaging engine and will lead to unexpected results. This does not mean that one should be deleting SIB000 along with SIB001 table. :-)
Consider a situation where SIB001 is truncated. It contained messages that were put under a transaction and still have not been committed or rolled back. What this means is that table SIBXACTS still holds information of the status of the transactions. These transactions can never be resolved once the SIB001 is truncated. This has left the messaging engine broken permanently. A restart of the messaging engine would also not resolve this issue.
The above 2 examples gives a good idea of how badly the messaging engine can be affected when the tables are directly fiddled with. There are few things that can be considered when deleting of messages is necessary.
(If possible), a consumer can be attached to the queue and troublesome messages consumed.
The better option is to use the Destination Handler utility to delete messages or move them to a separate queue where the messages can be dealt with later on.
Contacting IBM support. After a good understanding of the exact issue, the support team may suggest deleting certain entries in certain tables.
Note – SIB001 (permanent table) and SIB002 (temporary table) by default contain persistent and non persistent objects. However the number of permanent and temporary tables can be increased. Consider they are increased to 2, in that case SIB001 and SIB002 would be permanent tables containing persistent data and SIB003 and SIB004 would be temporary tables
Disclaimer - The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.