Informix 9.21/HP.UX 11 : no more locks.

Online Log has following message. 19:27:47 Lock table overflow - user id 415, session id 3300 19:27:48 Lock table overflow - user id 415, session id 3301 19:27:48 Lock table overflow - user id 415, session id 3302 When I tried to insert records into the database it fails with Error Code 271 that is 'Could not insert new row into the table'. As result of the above issues I increased the following parameters on onConfig file. But, still the problem persists. LOCKS 20,000 -> 400,000 SHMVIRTSIZE 16,000 -> 320,000 SHMADD 8,192 -> 32,000 Please advise me. Should I have to change HP-US Parameters? Your help is much appreciated. Regards Jeya

Chances are that you have a badly behaved application, which is applying too many individual locks instead of doing something like a table lock, instead. It's time to look at the actual application code and see if you can spot anything. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco From: "JEYAKANTHAN...." <jeyakanthan.kulasekaram@hp.com> > >Online Log has following message. > >19:27:47 Lock table overflow - user id 415, session id 3300 >19:27:48 Lock table overflow - user id 415, session id 3301 >19:27:48 Lock table overflow - user id 415, session id 3302 > > >When I tried to insert records into the database it fails with Error Code >271 that is 'Could not insert new row into the table'. > >As result of the above issues I increased the following parameters on >onConfig file. But, still the problem persists. > >LOCKS 20,000 -> 400,000 >SHMVIRTSIZE 16,000 -> 320,000 >SHMADD 8,192 -> 32,000> >Please advise me. Should I have to change HP-US Parameters? > >Your help is much appreciated. > >Regards >Jeya > > _________________________________________________________________ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo

----LNX_Mon_Jan_05_2004_15:25:03_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2004.01.05 14:36:54 >Sender: JEYAKANTHAN.... <jeyakanthan.kulasekaram@hp.com> > >When I tried to insert records into the database it fails with ErrorCode= 271 >that is 'Could not insert new row into the table'. = As the text displays when calling finderr -271 that problem can have many causes - you should look for the accompanying = ISAM error code.If you insert a lot of rows in one statement/transaction you may run into= a locking problem. = >Online Log has following message. > >19:27:47 Lock table overflow - user id 415, session id 3300 >19:27:48 Lock table overflow - user id 415, session id 3301 >19:27:48 Lock table overflow - user id 415, session id 3302 > >As result of the above issues I increased the following parameters on >onConfig file. But, still the problem persists. > >LOCKS 20,000 -> 400,000 = Did you restart the database engine? The parameter change takes effect only after a database restart. = Unless you lock an entire table explicitly with 'lock table .. in exclus= ive mode' the engine uses row or page level locking (depends on the table definitio= n and can be checked with dbschema -ss -d <database> -t <table> ). = If the table is created with row level locking you will need a lock for e= very row affected (maybe during a sequential scan to find a row for update) or= inserted and for every index entry too. = Possible causes: bulk inserts , updates requiring sequential scan on larg= e tables, updates/deletes on whole tables , .... = It is possible that some other session uses most of the locks (onstat-u = shows how many locks a session uses) and you get the error because too few locks are available for your transaction. = Regards, Andreas Kutsche = ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Mon_Jan_05_2004_15:25:03_V3.33----

What is the ISAM error code ?? do a finderr on ISAM error code to check whether lock overflow is because of LOCKS param or system level semaphores number. How many records are you trying to insert in one go? if ur table has row level locking ( check from systables ), then approximate the locks by no of rows. How r u trying to insert records , isql ? dbaccess?? dbload ?? 4gl/ec app ?? try to use dbload with commit after certain no of records. how did u change the LOCKS param etc ? in onconfig directly or thru onmonitor?? did u restart after that? Rgds Preetinder JEYAKANTHAN.... wrote: >Online Log has following message. > >19:27:47 Lock table overflow - user id 415, session id 3300 >19:27:48 Lock table overflow - user id 415, session id 3301 >19:27:48 Lock table overflow - user id 415, session id 3302 > > >When I tried to insert records into the database it fails with Error Code 271 that is 'Could not insert new row into the table'. > >As result of the above issues I increased the following parameters on onConfig file. But, still the problem persists. > >LOCKS 20,000 -> 400,000 >SHMVIRTSIZE 16,000 -> 320,000 >SHMADD 8,192 -> 32,000> >Please advise me. Should I have to change HP-US Parameters? > >Your help is much appreciated. > >Regards >Jeya > > > > >

Thanks so much for the responses. It helped me a lot. The accompanying ISAM error code is 134. (no more locks). I changed the paramemters(locks ...) in onconfig and re-started the Informix Server. It didn't work. The data is being inserted/updated into the database by more than one ways. Stored Procedures, Informatica(ETL), Perl Scripts etc. Finally, I narrowed down the issue to a particular program which does the house keeping. This is program deletes the records which are more than 90 days old. I have suspended that program. I am still not clear on how I am going to tune/trouble shoot this program. This program is holding too many locks. I ran the update statistics to ensure the this program uses the index to search. Thanks and regards Jeya

Try change the program in one of three ways: 1. LOCK TABLE before you start deleting. 2. Use a CURSOR WITH HOLD to delete a set of rows, COMMIT WORK and then continue deleting. 3. Find another way of deleting fewer rows at a time (like deleting rows older than 100 days, for instance) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco >From: "JEYAKANTHAN...." <jeyakanthan.kulasekaram@hp.com> >To: ids@iiug.org >Subject: Re: Informix 9.21/HP.UX 11 : no more locks. [2416] Date: Tue, 6 >Jan 2004 06:30:06 -0500 (EST) >Received: from ace.iiug.org ([216.177.38.212]) by mc11-f4.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6713); Tue, 6 Jan 2004 04:40:13 -0800 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i06Ba74S027089;Tue, 6 Jan 2004 07:23:53 >-0500 (EST) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i06BU66Q026957;Tue, 6 Jan 2004 06:30:06 -0500 (EST) >X-Message-Info: UZmYcfFpTCdJ5BGElTWioO4ZVxYFWObZ >Message-Id: <200401061130.i06BU66Q026957@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 06 Jan 2004 12:40:13.0845 (UTC) >FILETIME=[3AFB2450:01C3D452] > >Thanks so much for the responses. It helped me a lot. > >The accompanying ISAM error code is 134. (no more locks). > >I changed the paramemters(locks ...) in onconfig and re-started the >Informix Server. It didn't work. > >The data is being inserted/updated into the database by more than one ways. >Stored Procedures, Informatica(ETL), Perl Scripts etc. > >Finally, I narrowed down the issue to a particular program which does the >house keeping. This is program deletes the records which are more than 90 >days old. I have suspended that program. I am still not clear on how I am >going to tune/trouble shoot this program. This program is holding too many >locks. I ran the update statistics to ensure the this program uses the >index to search. > >Thanks and regards >Jeya > > _________________________________________________________________ Use MSN Messenger to send music and pics to your friends http://www.msn.co.uk/messenger

Jeya, check if the program is doing transactional work (begin/commit).... if so, count the records... after every 100 or 500 or... do a commit work... i think in informix 9.4 you can turn on 'set explain' for a process id. i have read of it, but not tried it, i'm only on 7.31. I'm not sure if the new feature is in 9.2. read your machine notes? good luck, Norma Jean -----Original Message----- From: jeyakanthan.kulasekaram@hp.com [mailto:jeyakanthan.kulasekaram@hp.com] Sent: Tuesday, January 06, 2004 5:30 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Re: Informix 9.21/HP.UX 11 : no more locks. [2416] Thanks so much for the responses. It helped me a lot. The accompanying ISAM error code is 134. (no more locks). I changed the paramemters(locks ...) in onconfig and re-started the Informix Server. It didn't work. The data is being inserted/updated into the database by more than one ways. Stored Procedures, Informatica(ETL), Perl Scripts etc. Finally, I narrowed down the issue to a particular program which does the house keeping. This is program deletes the records which are more than 90 days old. I have suspended that program. I am still not clear on how I am going to tune/trouble shoot this program. This program is holding too many locks. I ran the update statistics to ensure the this program uses the index to search. Thanks and regards Jeya ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Also, make sure you have a CLOSE cursor statement after your deletes are done to assure the cursor is closed because opened cursors do not release locks ! "Obnoxio The...." <obnoxio@hotmail.com> wrote:Try change the program in one of three ways: 1. LOCK TABLE before you start deleting. 2. Use a CURSOR WITH HOLD to delete a set of rows, COMMIT WORK and then continue deleting. 3. Find another way of deleting fewer rows at a time (like deleting rows older than 100 days, for instance) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco >From: "JEYAKANTHAN...." >To: ids@iiug.org >Subject: Re: Informix 9.21/HP.UX 11 : no more locks. [2416] Date: Tue, 6 >Jan 2004 06:30:06 -0500 (EST) >Received: from ace.iiug.org ([216.177.38.212]) by mc11-f4.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6713); Tue, 6 Jan 2004 04:40:13 -0800 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i06Ba74S027089;Tue, 6 Jan 2004 07:23:53 >-0500 (EST) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i06BU66Q026957;Tue, 6 Jan 2004 06:30:06 -0500 (EST) >X-Message-Info: UZmYcfFpTCdJ5BGElTWioO4ZVxYFWObZ >Message-Id: <200401061130.i06BU66Q026957@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 06 Jan 2004 12:40:13.0845 (UTC) >FILETIME=[3AFB2450:01C3D452] > >Thanks so much for the responses. It helped me a lot. > >The accompanying ISAM error code is 134. (no more locks). > >I changed the paramemters(locks ...) in onconfig and re-started the >Informix Server. It didn't work. > >The data is being inserted/updated into the database by more than one ways. >Stored Procedures, Informatica(ETL), Perl Scripts etc. > >Finally, I narrowed down the issue to a particular program which does the >house keeping. This is program deletes the records which are more than 90 >days old. I have suspended that program. I am still not clear on how I am >going to tune/trouble shoot this program. This program is holding too many >locks. I ran the update statistics to ensure the this program uses the >index to search. > >Thanks and regards >Jeya > > _________________________________________________________________ Use MSN Messenger to send music and pics to your friends http://www.msn.co.uk/messenger --------------------------------- Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes