how to terminate global transactions?

This is a post about the workaround when Global Transaction is not terminated gracefully. We will introduce the case in detail.

On my Informix instance I have two global transactions holding some locks. See onstat -G and onstat -x output below.
How can I get rid of them?
Also after a server reboot they are still there.
onmode -Z <address> or onmode -H <address> do not work (see below).

IBM Informix Dynamic Server Version 11.50.UC7IE on Solaris SPARC 10.

Global Transaction Identifiers
address flags isol timeout fID gtl bql data
56656bd8 -LX-G LC 0 131075 30 28 312D2D35336530656466663A613139383.....
56656e28 -LX-G LC 0 131075 30 28 312D2D35336530656466663A613139383.....
2 active, 128 total

Transactions

                                                                          est.

address  flags userthread locks  begin_logpos      current logpos isol    rb_time  retrys coord

56655018 A---- 56623018   0      -                 -              COMMIT  -        0

56655268 A---- 56623638   0      -                 -              COMMIT  -        0

566554b8 A---- 56623c58   0      -                 -              COMMIT  -        0

56655708 A---- 56624278   0      -                 -              COMMIT  -        0

56655958 A---- 56624898   0      -                 -              COMMIT  -        0

56655ba8 A---- 56624eb8   0      -                 -              COMMIT  -        0

56655df8 A---- 566254d8   0      -                 -              COMMIT  -        0

56656048 A---- 56625af8   0      -                 -              COMMIT  -        0

56656298 A---- 56626118   0      -                 -              COMMIT  -        0

566564e8 A---- 56626738   0      -                 -              COMMIT  -        0

56656738 A---- 56626d58   0      -                 -              COMMIT  -        0

56656988 A---- 56627378   0      -                 -              COMMIT  -        0

56656bd8 -LX-G 0          2      logid 303         303:0x64b2622c COMMIT  2:58     0

56656e28 -LX-G 0          2      logid 303         303:0x64b2922c COMMIT  2:58     0

56657078 A---- 56627fb8   0      -                 -              COMMIT  -        0

566572c8 A---- 56627998   0      -                 -              COMMIT  -        0

56657e58 A---- 56629218   0      -                 -              COMMIT  -        0

Transaction with flags value of '-LX-G' does not have userthread information as in the example above. These transactions are called orphaned transactions, and the userthread has been dropped from the transaction. If these transactions stop using the Logical Log, they may enter the Long Transaction state.


> onmode -Z 1449487912
onmode: Cannot kill transaction 0x56656e28.
Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd
may be heuristically completed.

> onmode -H 1449487912
onmode -H may only be used to kill heuristically completed transactions.


This is the result of running onmode with address in decimal.
You can also run it in the same format as onmode -Z (or -H) 0x56656e28.

If you continue reading the post, you will find:

Try onmode -l until you have forced a long transaction, this usually
help us. We have requested a way to get rid of these, but it seems
that the view of  IBM that the TM should handle this i all cases.
Problem is that the TM fails sometimes, and then you are stuck.

Regards

Ulf

In the case of Frank, who asked the first question, log switching occurred naturally and caused a long transaction. This depends on the LTXHWM configuration variable setting, which defaults to 70. If there are 10 Logical Logs, if a transaction does not complete during 7 log switches, a long transaction is usually triggered.

Today, the transactions were rolled back by the server:

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

If onmode -l causes manual log switching or if the log accumulates and exceeds the LTXHWM limit, it will be processed as above. Generally, this does not affect instances or other transactions when processed as above.

It is difficult to find official documentation for onmode -H. It seems to apply only to heuristic transactions (onmode -H may only be used to kill heuristically completed transactions).

In my experience it is used when the value of flags in onstat -G is:

700000379440490  -TH-G 0                0      108002:0xaf7a018  108072:0xd068     DIRTY   26:56    0

70000038ad89b48  -TH-G 0                0      107898:0x4524018  107968:0x6a018    COMMIT  0:00     0

70000038c7ccfa0  -TH-G 0                0      107993:0x14781050 108063:0x9404     DIRTY   32:58    0

70000038c7e1160  -TH-G 0                0      108002:0x2dea018  108072:0x27678    DIRTY   27:10    0

70000038d69e178  -TH-G 0                0      108002:0x39e018   108072:0xe118     DIRTY   27:14    0

If the flags value is in the '-TH-G' state, the transaction will not be dropped beyond the LTXHWM limit. In this case, you can inadvertently remove it with the -H option, but you should be careful to use it because there is no description of the -H option on the IBM Knowledge Center or onmode command.

source:

http://www.iiug.org/forums/ids/index.cgi/noframes/read/2975

https://groups.google.com/forum/?hl=en&fromgroups=#!topic/comp.databases.informix/egoEcVkTmbY

https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.admin.doc/ids_admin_1066.htm