syslock on missing session

All, I have a table in which I can insert and select, but can't delete or update. I have run this statement, which I use to detected hung sessions (usually a transaction that gets stuck open for some reason): select ss.sid, ss.username, ss.hostname, ss.tty, trim(dbsname), trim(tabname), keynum, type, owner, sum(waiter) as waiters, count(*) as count from sysmaster:syslocks sl, sysmaster:syssessions ss where ss.sid = sl.owner and dbsname = "rmca_test" and type = "X" and keynum !=1 group by 1, 2, 3, 4, 5, 6, 7, 8, 9 Normally, I would just do an onmode -z for the session, but this time there is nothing shown. Upon researching this further, I do see a lock on this table, but the owner field lists a session that is not listed in syssessions. I attempted an onmode -z for that session id, and I got the response "cannot kill session 31". Is there a way I can just cancel this lock, or force the session cleanup to happen?

I should mention that this is in a cluster. If I run an onstat -u, I see the session - it is a low session number (31), so I think this might be a session between the secondary HDR and the primary HDR (that would also explain why onmode -z won't kill it). -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Justin Killen Sent: Thursday, September 19, 2013 10:15 AM To: ids@iiug.org Subject: syslock on missing session [31458] All, I have a table in which I can insert and select, but can't delete or update. I have run this statement, which I use to detected hung sessions (usually a transaction that gets stuck open for some reason): select ss.sid, ss.username, ss.hostname, ss.tty, trim(dbsname), trim(tabname), keynum, type, owner, sum(waiter) as waiters, count(*) as count from sysmaster:syslocks sl, sysmaster:syssessions ss where ss.sid = sl.owner and dbsname = "rmca_test" and type = "X" and keynum !=1 group by 1, 2, 3, 4, 5, 6, 7, 8, 9 Normally, I would just do an onmode -z for the session, but this time there is nothing shown. Upon researching this further, I do see a lock on this table, but the owner field lists a session that is not listed in syssessions. I attempted an onmode -z for that session id, and I got the response "cannot kill session 31". Is there a way I can just cancel this lock, or force the session cleanup to happen? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I have LOG_INDEX_BUILDS set to 1 on all the configs. This specific table though does not have any indexes defined. -Justin -----Original Message----- From: Paul Watson [mailto:paul@oninit.com] Sent: Thursday, September 19, 2013 10:48 AM To: Justin Killen Subject: RE: syslock on missing session [31459] Makes more sense you got an index build or similar on the far end ? Madison Rule of Thumb: In replication the problem is usually the target not the source Cheers Paul > I should mention that this is in a cluster. If I run an onstat -u, I see > the > session - it is a low session number (31), so I think this might be a > session > between the secondary HDR and the primary HDR (that would also explain why > onmode -z won't kill it).> > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Justin > Killen > Sent: Thursday, September 19, 2013 10:15 AM > To: ids@iiug.org > Subject: syslock on missing session [31458] > > All, > > I have a table in which I can insert and select, but can't delete or > update. I > have run this statement, which I use to detected hung sessions (usually a > transaction that gets stuck open for some reason): > > select ss.sid, ss.username, ss.hostname, ss.tty, trim(dbsname), > trim(tabname), > keynum, type, owner, sum(waiter) as waiters, count(*) as count > from sysmaster:syslocks sl, sysmaster:syssessions ss > where ss.sid = sl.owner and dbsname = "rmca_test" and type = "X" and> keynum != > 1 > group by 1, 2, 3, 4, 5, 6, 7, 8, 9 > > Normally, I would just do an onmode -z for the session, but this time > there is > nothing shown. Upon researching this further, I do see a lock on this > table, > but the owner field lists a session that is not listed in syssessions. I > attempted an onmode -z for that session id, and I got the response "cannot > kill session 31". > > Is there a way I can just cancel this lock, or force the session cleanup > to > happen? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

What does onstat -g ses 31 give? David. On 19 September 2013 at 18:45 Justin Killen <jkillen@allamericanasphalt.com> wrote: > I should mention that this is in a cluster. If I run an onstat -u, I see the > session - it is a low session number (31), so I think this might be a session > between the secondary HDR and the primary HDR (that would also explain why > onmode -z won't kill it).> > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Justin > Killen > Sent: Thursday, September 19, 2013 10:15 AM > To: ids@iiug.org > Subject: syslock on missing session [31458] > > All, > > I have a table in which I can insert and select, but can't delete or update. I > have run this statement, which I use to detected hung sessions (usually a > transaction that gets stuck open for some reason): > > select ss.sid, ss.username, ss.hostname, ss.tty, trim(dbsname), trim(tabname), > keynum, type, owner, sum(waiter) as waiters, count(*) as count > from sysmaster:syslocks sl, sysmaster:syssessions ss > where ss.sid = sl.owner and dbsname = "rmca_test" and type = "X" and keynum!= > 1 > group by 1, 2, 3, 4, 5, 6, 7, 8, 9 > > Normally, I would just do an onmode -z for the session, but this time there is > nothing shown. Upon researching this further, I do see a lock on this table, > but the owner field lists a session that is not listed in syssessions. I > attempted an onmode -z for that session id, and I got the response "cannot > kill session 31". > > Is there a way I can just cancel this lock, or force the session cleanup to > happen? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

We resolved the issue. onstat -g ses 31 -> came back with no results on any of the servers. onmode -z 31 -> came back with cannot kill session It appears to us that session 31 is the session from the secondary HDR/RSS to the primary HDR. Looking in the OAT report 'Lock List' we saw a different session pointing at the lock (session 1426028, Lock type HDR+IX on row id 0 and HDR+X on row id 7173). It looks like session 1426028 is the session that actually created the lock on the table, and session 31 held a lock on one of the system tables. Non of this appeared readily in onmode -k. After identifying that session from OAT, we tried the following: Onstat -g ses 1426028 -> came back with no results on any of the servers. Onmode -z 1426028 -> came back with cannot kill session We eventually opened a PMR with IBM (PMR # 44299 227 000). They're response was to reboot the servers and the problem would go away (Gee, thanks IBM). Since this is not an option for us during regular business hours, we tracked down the computer that had initiated the session that had the lock and had the user reboot. We were 95% sure there would be no difference, but we were wrong - 2 minutes after the phone call, the computer was shut down, and the session and the lock went away. So, I'm not sure why onmode -z refused to work for us, but at least the lock is gone now (nearly 24 hours later). If anybody has any insight as to why onmode -z couldn't close out the session, that would be helpful. Also, is there a way to limit a transaction time? For example, only allow a transaction to be open for at max an hour? -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of david@smooth1.co.uk Sent: Thursday, September 19, 2013 2:08 PM To: ids@iiug.org Subject: RE: syslock on missing session [31464] What does onstat -g ses 31 give? David. On 19 September 2013 at 18:45 Justin Killen <jkillen@allamericanasphalt.com> wrote: > I should mention that this is in a cluster. If I run an onstat -u, I see the > session - it is a low session number (31), so I think this might be a session > between the secondary HDR and the primary HDR (that would also explain why > onmode -z won't kill it).> > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Justin > Killen > Sent: Thursday, September 19, 2013 10:15 AM > To: ids@iiug.org > Subject: syslock on missing session [31458] > > All, > > I have a table in which I can insert and select, but can't delete or update. I > have run this statement, which I use to detected hung sessions (usually a > transaction that gets stuck open for some reason): > > select ss.sid, ss.username, ss.hostname, ss.tty, trim(dbsname), trim(tabname), > keynum, type, owner, sum(waiter) as waiters, count(*) as count > from sysmaster:syslocks sl, sysmaster:syssessions ss > where ss.sid = sl.owner and dbsname = "rmca_test" and type = "X" and keynum!= > 1 > group by 1, 2, 3, 4, 5, 6, 7, 8, 9 > > Normally, I would just do an onmode -z for the session, but this time there is > nothing shown. Upon researching this further, I do see a lock on this table, > but the owner field lists a session that is not listed in syssessions. I > attempted an onmode -z for that session id, and I got the response "cannot > kill session 31". > > Is there a way I can just cancel this lock, or force the session cleanup to > happen? > > > ******************************************************************************* > 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.