Hello,
I have an application that persist data into DBMS through Hibernate Framework.
I used it on MySql, on Oracle and now on DB2.

Only with DB2 something strange happens:
- when i execute my application, HIBERNATE isn't able to complete persist data on DB2;
- any error or exception is generated;
- the application is in a "wait state" until the warning below is printed on log file:

2008-02-14 16:43:37,519 WARN [org.jboss.tm.TransactionImpl] Transaction TransactionImpl:XidImpl[FormatId=257, GlobalId=oracolo-coll/24, BranchQual=, localId=24] timed out. status=STATUS_ACTIVE

- DB2 is completely LOCKED! All the tables concerning the Hibernate process ara locked. If i run a simple query like "SELECT * FROM <a table name>", it goes in lock! Only if i stop and start DB2 from his control centre I recovery the functionality of my DB2.


Since my log, it seems that Hibernate locks on a sequence of DB2 but I tried to insert data and it's fine! Finally the same tables and the same sequences on Oracle worked fine! So the problem is only DB2.

Someone could help me?
Thanx.

I've found the error!

Hibernate (in my application) uses a datasource to connect with database.
Datasource is defined on my application server (jboss).

If i set a datasource for local transaction (class com.ibm.db2.jcc.DB2Driver in db-ds.xml) everything goes well.
If i set a datasource for distributed transaction (class com.ibm.db2.jcc.DB2XADataSource in db2-xa-ds.xml) every table goes in lock state !!!

Someone could tell me how I can configure my datasource for distributed transaction? I need the transaction be DISTRIBUTED

You may have some in-doubt transactions. Have a look at the LIST INDOUBT TRANSACTIONS command, which DB2 provides. This may resolve your DB2 being "locked" issue.

In the end, you will have to figure out which of the transaction participants cannot do an xa_prepare or xa_commit and for what reason. Since you are using Hibernate as the TP monitor/coordinator, you have to investigate at this level, I'd say.

Thank you Stolze.

The command LIST INDOUBT TRANSACTIONS doesn't show any in-doubt transactions but my 22 table are in lock state

So who/which program is holding those locks? And what type of locks are we talking about?

I used the command "db2 get snapshot for all" to see the lock status of entire DB.

For all my table I see the follow informations:

Lock Name = 0x04000E00000000000000000054
Lock Attributes = 0x00000000
Delivery Indicators = 0x40000000
Number of lock = 2
Number of lock mantained = 0
Lock object name = 14
Object Type = Table
Tablespace Name = DATI
Table Schema = ORACOLO
Table Name = COUNTRY
Type = IX

or

Lock Name = 0x04000301040000030000000052
Lock Attributes = 0x00000008
Indicators of Delivery = 0x40000000
Number of lock = 2
Number of lock mantained = 0
Lock object name = 50331652
Object type = Row
Tablespace Name = DATI
Table Schema = ORACOLO
Table Name = POSTEPAY_SUBSCRIPTION
Type = X

(PS. i translated for you the parameters of the snapshot italian-->english)

In front of the "List of locks" you see which application is holding the locks in the list. So you have to figure out which application is holding the lock and then go from there.

Also, you said "or" - what does that mean?

When I say "or" I mean that each table is in lock state with:

"Object Type = Table
...
Type = IX"

AND

"Object type = Row
...
Type = X"


Finally, before "List of locks" I have:

Database Lock Snapshot

Database name = TEST
Database path = D:\DB2_01\NODE0000\SQL00002\
Input database alias = TEST
Locks held = 46
Applications currently connected = 6
Agents currently waiting on locks = 0
Snapshot timestamp = 26/02/2008 20:10:00.134520

Application handle = 176
Application ID = *LOCAL.DB2_01.080226190128
Sequence number = 00017
Application name = javaw.exe
CONNECT Authorization ID = FABIANA
Application status = UOW in wait
Status change time = 26/02/2008 20:06:26.515990
Application code page = 1208
Locks held = 0
Total wait time (ms) = 0

Application handle = 162
Application ID = *LOCAL.DB2_01.080226185517
Sequence number = 00002
Application name = java.exe
CONNECT Authorization ID = ILOG
Application status = UOW Executing
Status change time = 26/02/2008 19:58:12.967576
Application code page = 1208
Locks held = 22
Total wait time (ms) = 0


Thank you a lot STOLZE!

In case this is the complete output, have a look at application handle 162 (some sort of Java application that connects as user ILOG) and try to figure out why this application is not releasing its locks.

The applications:
- "CONNECT Authorization ID = FABIANA"
and
- "CONNECT Authorization ID = ILOG"
are in a unique Transaction and both uses Hibernate in the same way to persist data.

Finally, when we integrated the system (so, the same code!) with Oracle DBMS there wasn't this kind of error.

I think is a wrong setting of the driver JDBC on JBoss... is it possible?

At this point anything is possible. Oracle uses entirely different concurrency control mechanism to that in DB2, so I suspect the problem was still there when you were running Oracle but it was not so obvious.

My feeling is that in your code you don't complete database (Hibernate) transactions and/or sessions properly. DB2 will hold locks acquired during a transaction until the transaction is either committed or rolled back.

My colleagues thinks the problem is in the management of "Nested Transaction" by XA DB2 Driver.

The application with "CONNECT Authorization ID = ILOG" is NESTED in the transaction opened by that with "CONNECT Authorization ID = FABIANA" !!!

There are no "nested" transactions in XA. If your program has two open XA connections, you must xa_prepare/xa_commit both of them individually.

So you would have to describe us how connections are handled in your application. As we repeatedly told you, your application is most likely the culprit - and not DB2. (I won't rule out DB2, but I don't consider it to be very likely...)

I can't find the problem in my application!

Hibernate should manage xa_prepare/xa_commit in transparent way... i use the methods SAVE and LOAD of the framework for persist and load data from DB2.

Yestarday a collegue suggest me to set the following parameter of my db:

MAXAGENTS -> 2000
MAX_connections -> 2000
NUM_INITAGENTS -> 20
NUM_POOLAGENTS -> 1000

and

IBMDEFAULTBP --> 19968

But never change!

Hibernate is just an application - from the DB2 perspective. I suggest that you ask in some forum dedicated to Hibernate.