gTA cannot be killed

Hallo, IBM Informix Dynamic Server Version 12.10.UC12W1 SUSE Linux Enterprise Server 11 (x86_64) VERSION = 11 PATCHLEVEL = 4 My problem: onstat -x shows this row:548c29c 8 -TB-G 0 12 152835:0x1581128 152835:0x1581768 DIRTY 00:00:00 0 onstat - k shows: 4729d818 0 (548c29c8 ) 8254df98 IX 400001 0 0 47544ad8 0 (548c29c8) 826384d8 HDR+IX 2005f6 0 0 47b74418 0 (548c29c8) 47eb4cd8 HDR+X 2005f6 11d03 0 I 47ca4bb8 0 (548c29c8) 47b74418 HDR+X 2005f7 11d03 K- 1 I 47eb4cd8 0 (548c29c8) 47544ad8 HDR+X 2005f6 3705 0 47ec7ed8 0 (548c29c8) 0 HDR+IX 200522 0 0 825410f8 0 (548c29c8) 4729d818 HDR+X 400001 112800 0 8254df98 0 (548c29c8) 47ca4bb8 HDR+X 2005f8 11d03 K- 1 I 826384d8 0 (548c29c8) 826bb098 HDR+B 200522 500 33 826bb098 0 (548c29c8) 82835458 HDR+X 200522 510 0 D 82835458 0 (548c29c8) 828abbf8 HDR+X 200522 100a 0 U 828abbf8 0 (548c29c8) 47ec7ed8 HDR+X 200522 511 0 onstat - G shows Global Transaction Identifiers address flags isol timeout fID gtl bql data 4e8e43d0 -L--G COMMIT 15 131075 30 28 312D613134313436343A393762323A35653 763383636613A316338333062613134313436343A393762323A35653763383636613A316338333133 548c29c8 -T--G DIRTY 30000 1096044365 30 13 3132372E302E312E312E746D31353839333 73434303432323130303030373132372E302E312E312E746D31 57950318 -T--G DIRTY 30000 1096044365 30 13 3132372E302E312E312E746D31353839333 73434313234333330303030383132372E302E312E312E746D32 3 active, 640 total I tried to kill this transaction with: onmode -H 0x548c29c8 Output: onmode -H may only be used to kill heuristically completed transactions onmode -Z 0x548c29c8onmode: Cannot kill transaction 0x548c29c8. Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd may be heuristically completed. How can i end this transaction. No user thread attached to the transaction.The flag code for position 1 is not set. (See 548c29c 8 -TB-G 0) Thanks for any help in advance! Stay healthy!! Heinz ------------------------------ Heinz Weitkamp ------------------------------ #Informix

Hi Heinz, If the global transaction is an orphaned transaction, if the logical log switch occurs at the rate (or number) set in LTXHWM, it will be rolled back naturally as shown below. -- Example of online.log messages when global transaction rollback 23:42:09 Aborting Long Transaction: tx: 0x56656bd8 no user info due to XA or distributed (2-phase commit) transaction 23:42:09 Aborting Long Transaction: tx: 0x56656e28 no user info due to XA or distributed (2-phase commit) transaction 23:42:13 Checkpoint Completed: duration was 4 seconds. 23:42:13 Thu Jul 1 - loguniq 306, logpos 0x3fff33b4, timestamp: 0xdedfaa19 Interval: 5616 23:42:13 Maximum server connections 15 23:42:13 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 83432, Llog used 182271 23:42:13 Session completed abnormally. Rolling back tx id 24, flags 0x108463b 23:42:13 Session completed abnormally. Rolling back tx id 32, flags 0x108463b 23:42:13 Long Transaction 0x56656e28 Aborted. Rollback Duration: 0 Seconds 23:42:14 Long Transaction 0x56656bd8 Aborted. Rollback Duration: 0 Seconds​Alternatively, you can also force the log switch to fire with the onmode -l command directly. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Hi SangGyu Jeong, thanks for the help and quick answer. Yes, the database then cleaned up the transaction. But I already had similar problems on development and test computers, whereby the database did not automatically abort the long transaction, even though LTXHWM was exceeded. Only a restart then fixed the problem. This solution is not optimal for the production server. So my question is whether there is a way to end such TA without restarting. Greeting Heinz ------------------------------ Heinz Weitkamp ------------------------------

Get your hands on a copy of "IBM Informix Developer's Handbook" on IBM red books site ( http://www.redbooks.ibm.com/abstracts/sg247884.html?Open ) . Search the pdf for xa_tool and you will find an example esqlc program that can terminate orphaned XA transactions. You can also use java to call the functions used on the program. And there is/was an RFE asking for adding an administrative command / SQL API for removing orphaned XA connections. ------------------------------ Luis Marques ------------------------------

Thank you very much for the good hint, Luis Marques. Will use the program at the next opportunity. greeting Heinz ------------------------------ Heinz Weitkamp ------------------------------

Hi, You can vote for: https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-231 This is an "old problem" with onmode -Z. Best regards, ------------------------------ Boycho Velkov ------------------------------

If I'm getting this right, onmode -Z is meant for I-Star 2-phase-commit distributed transactions, whereas onmode -H is for XA global transactions. The later (XA transactions) typically are not owned by Informix and should be taken care of by the XA transaction manager that initiated and oversees them; in this sense onmode -H only serves as a workaround for cases where such XA transaction remainders keep lingering, potentially blocking the Informix server. ------------------------------ Andreas Legner ------------------------------