Help with debugging Queries

Hello all, I am having an SPL that on occassion (once every other day) take 10 to 20 min to complete at seemingly random times. I am wondering if there is any way I can see what kind of locks exist in the db for a particular table--even better is there a history kept on the type of locks that have existed in the past and their related queries? Any other suggestion will be much appreciated as well! You can safely assume I am a fairly new Informix user :) TIA, George

You will get a better response - but for starters anyways... I go into dbaccess and lock a table in exclusive mode to get started. Now I look at onstat -k : Locks address wtlist owner lklist type tblsnum rowid key#/bsiz ac16080 0 4f351484 0 HDR+S 100002 209 0 ac160d8 0 4f351484 ac16080 HDR+X b00002 0 0 The first entry tells me that there is a shared lock on a database - not that big of a deal. Line 2 is of interest - it shows a HDR+X lock - in english, this is an exclusive lock - on tblsnum (table or fragment) 0xb00002 (it is a hex value - b right?) Since the rowid = 0 I know this is a table lock and not a row lock or page lock. If it were a row lock then rowid would be a value like the rowid in line 1, (0x209) i.e greater than 2 digits and not ending in 0. If the value of rowid is something other than 0 but ENDS in 0 it is a page lock. If key#/bsiz has a value then it's an index or byte lock. To find what the table name is that is locked run: oncheck -pt 0xb00002 (the tblsnum value) and bammmmmo! TBLspace Report for magie:informix.rules The Database Magie has a table called rules and it is locked in exclusive mode. werd MM

Thank you Mike! This will definitely help. Now is there any way to find the SQL query responsible for the exclusive lock, or any other lock for that matter? TIA, George On Fri, Dec 4, 2009 at 12:20 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > You will get a better response - but for starters anyways... > > I go into dbaccess and lock a table in exclusive mode to get started. Now I > look at onstat -k : > > Locks > address wtlist owner lklist type tblsnum rowid key#/bsiz > ac16080 0 4f351484 0 HDR+S 100002 209 0 > ac160d8 0 4f351484 ac16080 HDR+X b00002 0 0 > > The first entry tells me that there is a shared lock on a database - not that > big of a deal. Line 2 is of interest - it shows a HDR+X lock - in english, > this is an exclusive lock - on tblsnum (table or fragment) 0xb00002 (it is a > hex value - b right?) Since the rowid = 0 I know this is a table lock and not > a row lock or page lock. If it were a row lock then rowid would be a value > like the rowid in line 1, (0x209) i.e greater than 2 digits and not ending in > 0. If the value of rowid is something other than 0 but ENDS in 0 it is a page > lock. If key#/bsiz has a value then it's an index or byte lock. > > To find what the table name is that is locked run: > > oncheck -pt 0xb00002 (the tblsnum value) and bammmmmo!> > TBLspace Report for magie:informix.rules > > The Database Magie has a table called rules and it is locked in exclusive > mode. > > werd > > MM > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Yes, in the output from onstat -k you see a column called owner. You can grep this value from an onstat -u output. The output from onstat -u will include session ids (I think in column 1 but I can't run the cmd right now) that can be used with onstat -g sql to determine what sql is running, (onstat -g sql <session_id>). MM