How to remove a "zombie" transaction
On Solaris with IDS 9.40.UC7, a long transaction survived an engine restart with no user thread or session attached (flags --H-G in onstat -x), so onmode -z couldn't kill it; it kept re-triggering LONGTX as logical logs filled. Suggestions were onmode -Z/-H with the transaction address (as a decimal, not hex) or calling IBM support. The onmode -Z attempt failed with "Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd may be heuristically completed", so the poster planned to rebuild the instance via dbexport/dbimport, with Art Kagel advising a level-0 archive first. No command-level fix was found.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi guys,
Platform : Solaris 9 Sparc, IDS 9.40 UC7
We have an issue with a transaction that has no userthread anymore,
so no corresponding session that could be killed with "onmode -z".
Fact is there was a LONGTX, not enough logical logs were available and the
engine has been restarted. (so no corresponding session anymore)
The situation has been "unblocked" by adding a number of logical logs after
the current log.
So users can work again but the transaction is still there and at a certain
point, when logical logs are filled up again the status LONGTX appears again.
Any idea how to solve this without using "dbimport" ?
Thanks for any feedback
Jacques Lapeire
Extract of "onstat -x" : see transaction 11829b58
rootb#/>onstat -x
IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4 days
16:22:10 -- 188416 Kbytes
Transactions
address flags userthread locks beginlg curlog logposit isol retrys coord
11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
Call IBM and let their down systems guys abort the TX.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACQUES
LAPEIRE
Sent: Monday, January 31, 2011 5:08 AM
To: ids@iiug.org
Subject: How to remove a "zombie" transaction [22632]
Hi guys,
Platform : Solaris 9 Sparc, IDS 9.40 UC7
We have an issue with a transaction that has no userthread anymore, so no
corresponding session that could be killed with "onmode -z".
Fact is there was a LONGTX, not enough logical logs were available and the
engine has been restarted. (so no corresponding session anymore)
The situation has been "unblocked" by adding a number of logical logs after
the current log.
So users can work again but the transaction is still there and at a certain
point, when logical logs are filled up again the status LONGTX appears again.
Any idea how to solve this without using "dbimport" ?
Thanks for any feedback
Jacques Lapeire
Extract of "onstat -x" : see transaction 11829b58
rootb#/>onstat -x
IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4 days
16:22:10 -- 188416 Kbytes
Transactions
address flags userthread locks beginlg curlog logposit isol retrys coord
11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
try with onmode -Z 0x11829b58 or (if it was a XA transaction, onmode -H
0x11829b58).
Good luck,
Marcus Haarmann
-----Original Message-----
From: JACQUES LAPEIRE [mailto:jacques.lapeire@ts.fujitsu.com]
Sent: Monday, January 31, 2011 11:08 AM
To: ids@iiug.org
Subject: How to remove a "zombie" transaction [22632]
Hi guys,
Platform : Solaris 9 Sparc, IDS 9.40 UC7
We have an issue with a transaction that has no userthread anymore, so
no corresponding session that could be killed with "onmode -z".
Fact is there was a LONGTX, not enough logical logs were available and
the engine has been restarted. (so no corresponding session anymore)
The situation has been "unblocked" by adding a number of logical logs
after the current log.
So users can work again but the transaction is still there and at a
certain point, when logical logs are filled up again the status LONGTX
appears again.
Any idea how to solve this without using "dbimport" ?
Thanks for any feedback
Jacques Lapeire
Extract of "onstat -x" : see transaction 11829b58
rootb#/>onstat -x
IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4
days 16:22:10 -- 188416 Kbytes
Transactions
address flags userthread locks beginlg curlog logposit isol retrys coord
11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Marcus,
we already tried with onmode -Z <transaction-address> , but this command seems
to expect an integer not a hex-value.
By the way onmode -z <sessionid> doesn't help either because there's no
session anymore.
Thx,
Jacques
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Marcus
Haarmann
Sent: Monday, January 31, 2011 2:02 PM
To: ids@iiug.org
Subject: RE: How to remove a "zombie" transaction [22634]
Hi,
try with onmode -Z 0x11829b58 or (if it was a XA transaction, onmode -H
0x11829b58).
Good luck,
Marcus Haarmann
-----Original Message-----
From: JACQUES LAPEIRE [mailto:jacques.lapeire@ts.fujitsu.com]
Sent: Monday, January 31, 2011 11:08 AM
To: ids@iiug.org
Subject: How to remove a "zombie" transaction [22632]
Hi guys,
Platform : Solaris 9 Sparc, IDS 9.40 UC7
We have an issue with a transaction that has no userthread anymore, so
no corresponding session that could be killed with "onmode -z".
Fact is there was a LONGTX, not enough logical logs were available and
the engine has been restarted. (so no corresponding session anymore)
The situation has been "unblocked" by adding a number of logical logs
after the current log.
So users can work again but the transaction is still there and at a
certain point, when logical logs are filled up again the status LONGTX
appears again.
Any idea how to solve this without using "dbimport" ?
Thanks for any feedback
Jacques Lapeire
Extract of "onstat -x" : see transaction 11829b58
rootb#/>onstat -x
IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4
days 16:22:10 -- 188416 Kbytes
Transactions
address flags userthread locks beginlg curlog logposit isol retrys coord
11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
HEX 11829b58 == DEC 293772120
onmode -Z 293772120
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Jan 31, 2011 at 8:08 AM, Lapeire, Jacques <
jacques.lapeire@ts.fujitsu.com> wrote:
> Hi Marcus,
>
> we already tried with onmode -Z <transaction-address> , but this command
> seems
> to expect an integer not a hex-value.
> By the way onmode -z <sessionid> doesn't help either because there's no
> session anymore.
>
> Thx,
> Jacques
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Marcus
> Haarmann
> Sent: Monday, January 31, 2011 2:02 PM
> To: ids@iiug.org
> Subject: RE: How to remove a "zombie" transaction [22634]
>
> Hi,
>
> try with onmode -Z 0x11829b58 or (if it was a XA transaction, onmode -H
> 0x11829b58).
>
> Good luck,
>
> Marcus Haarmann
>
> -----Original Message-----
> From: JACQUES LAPEIRE [mailto:jacques.lapeire@ts.fujitsu.com]
> Sent: Monday, January 31, 2011 11:08 AM
> To: ids@iiug.org
> Subject: How to remove a "zombie" transaction [22632]
>
> Hi guys,
>
> Platform : Solaris 9 Sparc, IDS 9.40 UC7
>
> We have an issue with a transaction that has no userthread anymore, so
> no corresponding session that could be killed with "onmode -z".
>
> Fact is there was a LONGTX, not enough logical logs were available and
> the engine has been restarted. (so no corresponding session anymore)
>
> The situation has been "unblocked" by adding a number of logical logs
> after the current log.
>
> So users can work again but the transaction is still there and at a
> certain point, when logical logs are filled up again the status LONGTX
> appears again.
>
> Any idea how to solve this without using "dbimport" ?
>
> Thanks for any feedback
> Jacques Lapeire
>
> Extract of "onstat -x" : see transaction 11829b58
>
> rootb#/>onstat -x
>
> IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4
> days 16:22:10 -- 188416 Kbytes>
> Transactions
> address flags userthread locks beginlg curlog logposit isol retrys coord
> 11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
> 118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
> 118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
> 118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
> 11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
> 11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
> 11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
> 1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
> 1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517503b081cc253049b246ec7
Thanks Art,
there was no syntax error anymore, but anyway it didn't work.
Message :
onmode: Cannot kill transaction 0x11829b58.
Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd
may be heuristically completed
We have a dbexport and I will initialize the instance , reconfigure the
dbspaces and do a dbimport tomorrow.
Best regards,
Jacques
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, January 31, 2011 2:32 PM
To: ids@iiug.org
Subject: Re: How to remove a "zombie" transaction [22636]
HEX 11829b58 == DEC 293772120
onmode -Z 293772120
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Jan 31, 2011 at 8:08 AM, Lapeire, Jacques <
jacques.lapeire@ts.fujitsu.com> wrote:
> Hi Marcus,
>
> we already tried with onmode -Z <transaction-address> , but this command
> seems
> to expect an integer not a hex-value.
> By the way onmode -z <sessionid> doesn't help either because there's no
> session anymore.
>
> Thx,
> Jacques
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Marcus
> Haarmann
> Sent: Monday, January 31, 2011 2:02 PM
> To: ids@iiug.org
> Subject: RE: How to remove a "zombie" transaction [22634]
>
> Hi,
>
> try with onmode -Z 0x11829b58 or (if it was a XA transaction, onmode -H
> 0x11829b58).
>
> Good luck,
>
> Marcus Haarmann
>
> -----Original Message-----
> From: JACQUES LAPEIRE [mailto:jacques.lapeire@ts.fujitsu.com]
> Sent: Monday, January 31, 2011 11:08 AM
> To: ids@iiug.org
> Subject: How to remove a "zombie" transaction [22632]
>
> Hi guys,
>
> Platform : Solaris 9 Sparc, IDS 9.40 UC7
>
> We have an issue with a transaction that has no userthread anymore, so
> no corresponding session that could be killed with "onmode -z".
>
> Fact is there was a LONGTX, not enough logical logs were available and
> the engine has been restarted. (so no corresponding session anymore)
>
> The situation has been "unblocked" by adding a number of logical logs
> after the current log.
>
> So users can work again but the transaction is still there and at a
> certain point, when logical logs are filled up again the status LONGTX
> appears again.
>
> Any idea how to solve this without using "dbimport" ?
>
> Thanks for any feedback
> Jacques Lapeire
>
> Extract of "onstat -x" : see transaction 11829b58
>
> rootb#/>onstat -x
>
> IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4
> days 16:22:10 -- 188416 Kbytes>
> Transactions
> address flags userthread locks beginlg curlog logposit isol retrys coord
> 11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
> 118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
> 118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
> 118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
> 11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
> 11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
> 11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
> 1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
> 1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517503b081cc253049b246ec7
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Yuck! Good luck. Just for paranoia's sake, and because paranoid DBAs are
good DBAs, I would take a level 0 archive before trashing the instance.
Worst case, you can use archecker to extract data from the archive if
something turns up missing from the dbexport files.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Feb 1, 2011 at 10:30 AM, Lapeire, Jacques <
jacques.lapeire@ts.fujitsu.com> wrote:
> Thanks Art,
>
> there was no syntax error anymore, but anyway it didn't work.
>
> Message :
> onmode: Cannot kill transaction 0x11829b58.
> Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd
> may be heuristically completed
>
> We have a dbexport and I will initialize the instance , reconfigure the
> dbspaces and do a dbimport tomorrow.
>
> Best regards,
> Jacques
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, January 31, 2011 2:32 PM
> To: ids@iiug.org
> Subject: Re: How to remove a "zombie" transaction [22636]
>
> HEX 11829b58 == DEC 293772120
>
> onmode -Z 293772120>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Mon, Jan 31, 2011 at 8:08 AM, Lapeire, Jacques <
> jacques.lapeire@ts.fujitsu.com> wrote:
>
> > Hi Marcus,
> >
> > we already tried with onmode -Z <transaction-address> , but this command
> > seems
> > to expect an integer not a hex-value.
> > By the way onmode -z <sessionid> doesn't help either because there's no
> > session anymore.
> >
> > Thx,
> > Jacques
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Marcus
> > Haarmann
> > Sent: Monday, January 31, 2011 2:02 PM
> > To: ids@iiug.org
> > Subject: RE: How to remove a "zombie" transaction [22634]
> >
> > Hi,
> >
> > try with onmode -Z 0x11829b58 or (if it was a XA transaction, onmode -H
> > 0x11829b58).
> >
> > Good luck,
> >
> > Marcus Haarmann
> >
> > -----Original Message-----
> > From: JACQUES LAPEIRE [mailto:jacques.lapeire@ts.fujitsu.com]
> > Sent: Monday, January 31, 2011 11:08 AM
> > To: ids@iiug.org
> > Subject: How to remove a "zombie" transaction [22632]
> >
> > Hi guys,
> >
> > Platform : Solaris 9 Sparc, IDS 9.40 UC7
> >
> > We have an issue with a transaction that has no userthread anymore, so
> > no corresponding session that could be killed with "onmode -z".
> >
> > Fact is there was a LONGTX, not enough logical logs were available and
> > the engine has been restarted. (so no corresponding session anymore)
> >
> > The situation has been "unblocked" by adding a number of logical logs
> > after the current log.
> >
> > So users can work again but the transaction is still there and at a
> > certain point, when logical logs are filled up again the status LONGTX
> > appears again.
> >
> > Any idea how to solve this without using "dbimport" ?
> >
> > Thanks for any feedback
> > Jacques Lapeire
> >
> > Extract of "onstat -x" : see transaction 11829b58
> >
> > rootb#/>onstat -x
> >
> > IBM Informix Dynamic Server Version 9.40.UC7 -- On-Line (LONGTX) -- Up 4
> > days 16:22:10 -- 188416 Kbytes> >
> > Transactions
> > address flags userthread locks beginlg curlog logposit isol retrys coord
> > 11829018 A---- 117f8018 0 0 1115 0x22f8700 COMMIT 0
> > 118291f8 A---- 117f8630 0 0 0 0x0 COMMIT 0
> > 118293d8 A---- 117f8c48 0 0 0 0x0 COMMIT 0
> > 118295b8 A---- 117f9260 0 0 0 0x0 COMMIT 0
> > 11829798 A---- 117f9878 0 0 0 0x0 COMMIT 0
> > 11829b58 --H-G 0 0 1096 1107 0x103c COMMIT 0
> > 11829d38 A---- 117faac0 0 0 0 0x0 COMMIT 0
> > 1182a0f8 A---- 117fedc8 1 0 0 0x0 DIRTY 0
> > 1182a2d8 A---- 117fa4a8 0 0 0 0x0 COMMIT 0
> >
> > ************************************************************************
> > *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001517503b081cc253049b246ec7
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015175ce020bc3a60049b3a4e63