find tables access user

Hello, i have a table call acc_xxx and i want to drop it but it says i has lock. so how can i find particular use who access table?? when i run onstat -g ses | grep table_name the output is like follows select * from acc_xxx ( table name) select * from acc_xxx ( table name) thanks

Find the hex partnum of the table 1st. (select tabname, hex(partnum) from systables where tabname = 'tablename' Then onstat -k|grep (the partnum you found above). That will have an owner/rstcb column. Then onstat -u|grep (owner rstcb from above) That should do it. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PUSHPA KUMARA Sent: Wednesday, November 12, 2014 6:49 AM To: ids@iiug.org Subject: find tables access user [34152] Hello, i have a table call acc_xxx and i want to drop it but it says i has lock. so how can i find particular use who access table?? when i run onstat -g ses | grep table_name the output is like follows select * from acc_xxx ( table name) select * from acc_xxx ( table name) thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Correlate onstat -k and onstat -u based on the table/index partnum and the userthread address Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 > On Nov 12, 2014, at 5:48, "PUSHPA KUMARA" <pushpa@cybersoft.lk> wrote: > > Hello, > > i have a table call acc_xxx and i want to drop it but it says i has lock. so > how can i find particular use who access table?? > > when i run onstat -g ses | grep table_name > > the output is like follows > select * from acc_xxx ( table name) > select * from acc_xxx ( table name)> > thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

Dan's nailed it. Just a note, if the table is fragmented, the partnum in systables will be zero and you will have to get the partnums of each of the partitions from sysfragments by tabid instead and look those up in the onstat -k output. An easier way would be to query sysmaster: select owner from syslocks where dbsname = 'mydatabase' and tabname = 'acc_xxx'; select * from syssessions where sid = <owner>; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Nov 12, 2014 at 7:11 AM, Mueller, Daniel D. <ddmueller@intercall.com > wrote: > Find the hex partnum of the table 1st. (select tabname, hex(partnum) from > systables where tabname = 'tablename' > > Then onstat -k|grep (the partnum you found above). That will have an > owner/rstcb column. > > Then onstat -u|grep (owner rstcb from above) > > That should do it. > > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > PUSHPA > KUMARA > Sent: Wednesday, November 12, 2014 6:49 AM > To: ids@iiug.org > Subject: find tables access user [34152] > > Hello, > > i have a table call acc_xxx and i want to drop it but it says i has lock. > so > how can i find particular use who access table?? > > when i run onstat -g ses | grep table_name > > the output is like follows > select * from acc_xxx ( table name) > select * from acc_xxx ( table name)> > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b61c3a35450507a8a3b9

Oops. Didn't think about a fragmented table. thanx for the clarification Art. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, November 12, 2014 7:36 AM To: ids@iiug.org Subject: Re: find tables access user [34155] Dan's nailed it. Just a note, if the table is fragmented, the partnum in systables will be zero and you will have to get the partnums of each of the partitions from sysfragments by tabid instead and look those up in the onstat -k output. An easier way would be to query sysmaster: select owner from syslocks where dbsname = 'mydatabase' and tabname = 'acc_xxx'; select * from syssessions where sid = <owner>; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Nov 12, 2014 at 7:11 AM, Mueller, Daniel D. <ddmueller@intercall.com > wrote: > Find the hex partnum of the table 1st. (select tabname, hex(partnum) > from systables where tabname = 'tablename' > > Then onstat -k|grep (the partnum you found above). That will have an > owner/rstcb column. > > Then onstat -u|grep (owner rstcb from above) > > That should do it. > > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > PUSHPA KUMARA > Sent: Wednesday, November 12, 2014 6:49 AM > To: ids@iiug.org > Subject: find tables access user [34152] > > Hello, > > i have a table call acc_xxx and i want to drop it but it says i has lock. > so > how can i find particular use who access table?? > > when i run onstat -g ses | grep table_name > > the output is like follows > select * from acc_xxx ( table name) > select * from acc_xxx ( table name)> > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b61c3a35450507a8a3b9 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Just to be sure, does it say that there's a lock or "non-exclusive access"...? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PUSHPA KUMARA Sent: Wednesday, November 12, 2014 4:49 AM To: ids@iiug.org Subject: find tables access user [34152] Hello, i have a table call acc_xxx and i want to drop it but it says i has lock. so how can i find particular use who access table?? when i run onstat -g ses | grep table_name the output is like follows select * from acc_xxx ( table name) select * from acc_xxx ( table name) thanks **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.