Lock table overflow

Can anybody let me know why we got this error below and their resolutions. We are using IDS 7.13 running on HP-UNIX 10.01. 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:09 Lock table overflow - user id 6257, session id 6712 16:04:09 Lock table overflow - user id 6148, session id 6734 16:04:09 Lock table overflow - user id 6204, session id 6739

As soon as it starts happening, check the session and SQL for the 1st session to have the problem; onstat -g ses 5214or onstat -g sql 5214 Whatever application is database session 5214 *probably* is the SQL that has used up all your locks allocated in your ONCONFIG file. (NUMLOCKS ?) Bob ----- Original Message ----- From: "DEEPAK JOSHI" <djoshih@hotmail.com> To: ids@iiug.org Sent: Wednesday, September 23, 2009 4:22:26 PM GMT -05:00 US/Canada Eastern Subject: Lock table overflow [17146] Can anybody let me know why we got this error below and their resolutions. We are using IDS 7.13 running on HP-UNIX 10.01. 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:09 Lock table overflow - user id 6257, session id 6712 16:04:09 Lock table overflow - user id 6148, session id 6734 16:04:09 Lock table overflow - user id 6204, session id 6739 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You basically have a session that has locked the table and/or necessary rows from other users. You need to determine that session onstat -u for high # of locks and determine why and if that offending session can be killed. IBM Informix Dynamic Server Version 10.00.UC5 -- On-Line -- Up 2 days 10:34:53 -- 165984 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com Page via Skytel: 2244650553@sprint.skytel.com Informix or MySQL Primary: 9110210@skytel.com Informix or MySQL Secondary: 7276872@skytel.com " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DEEPAK JOSHI Sent: Wednesday, September 23, 2009 3:22 PM To: ids@iiug.org Subject: Lock table overflow [17146] Can anybody let me know why we got this error below and their resolutions. We are using IDS 7.13 running on HP-UNIX 10.01. 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:09 Lock table overflow - user id 6257, session id 6712 16:04:09 Lock table overflow - user id 6148, session id 6734 16:04:09 Lock table overflow - user id 6204, session id 6739 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

A quick query to the sysmaster db will tell you the top offender .... who, where from, their SID and the query: select first 1s.sid,trim(s.username),trim(s.hostname),p.locksheld,t.sqs_statement from syssessions s, syssesprof p, syssqlstat t where s.sid = p.sid and s.sid = t.sqs_sessionid order by 4 desc; or modify to show all ... but the offender usually is right at the top. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Knox, Ernest Sent: Wednesday, September 23, 2009 3:51 PM To: ids@iiug.org Subject: RE: Lock table overflow [17148] You basically have a session that has locked the table and/or necessary rows from other users. You need to determine that session onstat -u for high # of locks and determine why and if that offending session can be killed. IBM Informix Dynamic Server Version 10.00.UC5 -- On-Line -- Up 2 days 10:34:53 -- 165984 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com Page via Skytel: 2244650553@sprint.skytel.com Informix or MySQL Primary: 9110210@skytel.com Informix or MySQL Secondary: 7276872@skytel.com " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DEEPAK JOSHI Sent: Wednesday, September 23, 2009 3:22 PM To: ids@iiug.org Subject: Lock table overflow [17146] Can anybody let me know why we got this error below and their resolutions. We are using IDS 7.13 running on HP-UNIX 10.01. 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:08 Lock table overflow - user id 6012, session id 5214 16:04:09 Lock table overflow - user id 6257, session id 6712 16:04:09 Lock table overflow - user id 6148, session id 6734 16:04:09 Lock table overflow - user id 6204, session id 6739 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You ran out of the configured number of locks. In later versions (7.30 and later) the lock table was made dynamic - ie self-growing - but in this VERY OLD release you have to increase the number of locks manually in the ONCONFIG file with the LOCKS parameter and bounce the IDS instance. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Sep 23, 2009 at 3:22 PM, DEEPAK JOSHI <djoshih@hotmail.com> wrote: > Can anybody let me know why we got this error below and their resolutions. > We are using IDS 7.13 running on HP-UNIX 10.01. > > 16:04:08 Lock table overflow - user id 6012, session id 5214 > 16:04:08 Lock table overflow - user id 6012, session id 5214 > 16:04:08 Lock table overflow - user id 6012, session id 5214 > 16:04:09 Lock table overflow - user id 6257, session id 6712 > 16:04:09 Lock table overflow - user id 6148, session id 6734 > 16:04:09 Lock table overflow - user id 6204, session id 6739 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517475f1c0bb1030474455ae3

Thx Art for all the support. Right now we are not in a position to increase the lock count in the onconfig file. We have a table having 2415540 records and this table accessed by every people in the company and everyday 1000+ records adding into this table. We were trying to delete older records (say before 2007) and we run the query below in normal hours and then we get the error related to locks. Query:- delete from <tablename> where created_data < "01/01/2007"; and then we get the error 244:Could not do physical order read to fetch next row 134:- ISAM error, No more locks We want to delete the older records from this table. Can you please let me know what is the best way & how to delete the older records from the table without locks error. Thx, in advance and I really appreciate your help all the times.

Try downloading Jon Leffler's sqlcmd and Art Kagel's utils2_ak packages from the iiug website. Somewhere in one of those is a utility that will let you delete large numbers of rows and automatically commit after every few thousand rows. --EEM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DEEPAK JOSHI Sent: Thursday, September 24, 2009 7:46 AM To: ids@iiug.org Subject: Re: Lock table overflow [17156] Thx Art for all the support. Right now we are not in a position to increase the lock count in the onconfig file. We have a table having 2415540 records and this table accessed by every people in the company and everyday 1000+ records adding into this table. We were trying to delete older records (say before 2007) and we run the query below in normal hours and then we get the error related to locks. Query:- delete from <tablename> where created_data < "01/01/2007"; and then we get the error 244:Could not do physical order read to fetch next row 134:- ISAM error, No more locks We want to delete the older records from this table. Can you please let me know what is the best way & how to delete the older records from the table without locks error. Thx, in advance and I really appreciate your help all the times. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Sure can. Go to the IIUG web site's Software Repository and download my utils2_ak package and build it if you have not done so already. Read the BUILDING file and modify the makefile according to its instructions for your platform, versions of make and of esql/c, and of IDS (ex: set NOINT8 in the EFLAGS variable). In that package is the utility dbdelete.ec. It is an ESQL/C program that will quickly delete anything you need it to and you can configure how many rows it will delete in a single transaction to limit the number of locks it will hold at any given time. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Sep 24, 2009 at 7:45 AM, DEEPAK JOSHI <djoshih@hotmail.com> wrote: > Thx Art for all the support. > > Right now we are not in a position to increase the lock count in the > onconfig > file. > > We have a table having 2415540 records and this table accessed by every > people > in the company and everyday 1000+ records adding into this table. > > We were trying to delete older records (say before 2007) and we run the > query > below in normal hours and then we get the error related to locks. > > Query:- > > delete from <tablename> where created_data < "01/01/2007"; > > and then we get the error > 244:Could not do physical order read to fetch next row > 134:- ISAM error, No more locks > > We want to delete the older records from this table. > > Can you please let me know what is the best way & how to delete the older > records from the table without locks error. > > Thx, in advance and I really appreciate your help all the times. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747965aa5236a047452dfa0

Then you will need to reduce the size of each transaction which is performing the delete. You won't be able to do this by issuing a simpl= e dbaccess statement which performs the delete. You'd need to use a hold cursor and then have a loop in which you delete the next row from the cursor and every 'x' number of deletes, issue a commit/begin work. M.P. = "DEEPAK JOSHI" = <djoshih@hotmail. = com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Lock table overflow [17156]= 09/24/2009 07:45 = AM = = = Please respond to = ids@iiug.org = = = Thx Art for all the support. Right now we are not in a position to increase the lock count in the onconfig file. We have a table having 2415540 records and this table accessed by every= people in the company and everyday 1000+ records adding into this table. We were trying to delete older records (say before 2007) and we run the= query below in normal hours and then we get the error related to locks. Query:- delete from <tablename> where created_data < "01/01/2007"; and then we get the error 244:Could not do physical order read to fetch next row 134:- ISAM error, No more locks We want to delete the older records from this table. Can you please let me know what is the best way & how to delete the old= er records from the table without locks error. Thx, in advance and I really appreciate your help all the times. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

You have several ways of doing this ... here are a couple ... First, delete less data at a time...by changing the date ... Second ... begin work; lock table xx in exclusive mode; delete ... commit work; Got to be careful with that one ... if you haven't enough logical logs .. you will end up hitting the transaction high water mark ... and rolling back ... or locking up the system depending on the values you have set for that ... Thirdly .. switch the table to page locking ... delete the data ... switch back to row ... alter table xx lock mode (page);delete .... alter table xxx lock mode (row)This is assuming the table is currently configured with row locking ... again .. you will need to make sure you have enough logical logs defined ... Hope this helps .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "DEEPAK JOSHI" <djoshih@hotmail.com> To: ids@iiug.org Date: 09/24/2009 08:46 AM Subject: Re: Lock table overflow [17156] Sent by: ids-bounces@iiug.org Thx Art for all the support. Right now we are not in a position to increase the lock count in the onconfig file. We have a table having 2415540 records and this table accessed by every people in the company and everyday 1000+ records adding into this table. We were trying to delete older records (say before 2007) and we run the query below in normal hours and then we get the error related to locks. Query:- delete from <tablename> where created_data < "01/01/2007"; and then we get the error 244:Could not do physical order read to fetch next row 134:- ISAM error, No more locks We want to delete the older records from this table. Can you please let me know what is the best way & how to delete the older records from the table without locks error. Thx, in advance and I really appreciate your help all the times. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.