Memory problems starting JBossmq with DB2

[oaadland]

Hi

I have an application running on JBoss 3.2.7 using the JBossmq the comes with the installation of JBoss. We've been running this over half a year with almost no problems (At least not releated to JBoss code).

A short time ago however, we had trouble starting the JBoss instance again after a planned down period. After searching for the cause we found the problem to be related to loading for messages from a JMS destination. When trying to load the messages DB2 threw an exception complaining about out of memory (APP_CTL_HEAP_SZ set to low). In the JBossmq code the messages are loded with this sql:

SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?

The loading code is located in the loadFromStorage method in org.jboss.mq.pm.jdbc2.PersistenceManager

We checked out how many messages could be loaded this way with the default setting of DB2. The anwere where 1600 messages. So if there are more messages in a single destination then the mq service wouldn't start.

We have discussed this with IBM support and they are telling us that DB2 works as designed. According to them the heap is cleared when the transaction commits. In my opinion this is pretty stupid since you cannot navigate backwards in the recordset, and already read rows could be garbagecollected at once.

The interesting thing here is that there isn't any memory problems at the applicationserver, but only in DB2. Adjusting the APP_CTL_HEAP_SZ to higher levels cannot solve our problem since it doesn't scale well.

My questions are:

Have anyone else had similar problems either with DB2 or other databases?
Do anyone have a solution to how to get around this?

A rather simple solution (I think) for us would be to modify the loading code i JBossmq to load fewer messages in one transaction, and repeat until all messages are loaded. But we would prefere not to do this unless this is accepted into the JBossmq code.

Thank you.
Oystein Aadland

PS: I tried to search for similar posts in the forum and the bug database without finding anything. If I missed it I apologise for the inconvenience.

[adrian.brock]

"oaadland" wrote:

PS: I tried to search for similar posts in the forum and the bug database without finding anything. If I missed it I apologise for the inconvenience.

http://jira.jboss.com/jira/browse/JBAS-1891

[oaadland]

Thanks Adrian

I obviously searched the wrong categories. I'll do a followup in the subtask in Jira.

Oystein

[oaadland]

For those of you out there unfortunately enough to run DB2 I have the solution to this problem.

The solution to the problem as suggested in http://jira.jboss.com/jira/browse/JBAS-1891 (and parent) do not work with DB2. Setting fetch direction, concurrent read mode or fetch size will not let you load the JBoss messaging service with a large number of messages in a single queue.

The proposed solution http://jboss.org/index.html?module=bb&op=viewtopic&t=46186 wouldn't work either since you still try to load all messages in a destination in a single sql.

In DB2 memory for LOB records are freed only when the statement handle is dropped. This meens that you have to split the fetch sequence into several sqls where each sql only return a smaller amount of messages (In my case less than 1600 messages).

However it is an obscure workaround for this problem. Setting the patch2=50 in your db2cli.ini solves these issues. By setting this you tell DB2 to free memory after each fetch. This file really is an ODBC config file. Go figure.

Se http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0007882.htm for further explanation on this and other obscure settings. I hope no one else have to use as much time as me on this issue.

Oystein