Truncate Table fails with SQL error -242 ISAM -106

We are Truncating tables in ESQL/C program, it's failing with SQL error -242 ISAM -106. We have tried setting IFX_DIRTY_WAIT 300 in oninit file but the truncate table SQL is not waiting for the 300 seconds before it actually attempts the TRUNCATE in case the table is locked by some other process. This is not the only truncate statement which this ESQL/C has, but all of them are executing successfully except one. Also, I'm not seeeing any lock wait or locks (syslocks) on the table , not sure how to find the SQL statement which might be holding locks and not allowing the TRUNCATE statement to run. Please help. Thanks in advance. pradeep

Hi, Error 106 implies that there might be some cursor open on that table. C= heck with onstat -g opn if your table partnum is opened by some other thread= . Regds, Uday. --------------------------------------------------- Uday Kale E-Mail : udayk@us.ibm.com Informix SQL Development IBM Information Management Group Phone: 913-599-8681 --------------------------------------------------- = "PRADEEP KUMAR" = <pkyadav1@hotmail = .com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Truncate Table fails with SQL er= ror 07/14/2009 11:28 -242 ISAM -106 [16393] = AM = = = Please respond to = ids@iiug.org = = = We are Truncating tables in ESQL/C program, it's failing with SQL error= -242 ISAM -106. We have tried setting IFX_DIRTY_WAIT 300 in oninit file but the truncat= e table SQL is not waiting for the 300 seconds before it actually attempts the TRUNCATE in case the table is locked by some other process. This is not the only truncate statement which this ESQL/C has, but all = of them are executing successfully except one. Also, I'm not seeeing any lock wait or locks (syslocks) on the table , = not sure how to find the SQL statement which might be holding locks and not= allowing the TRUNCATE statement to run. Please help. Thanks in advance. pradeep ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Thanks Uday. I didn't see any reference of the table in the onstat -g opn. Do we have any system table ( or query based on system tables ) which could display this information..? regards,

In your ESQL/C app, immediately after connecting to the data (so after DATABASE statement if you are using separate CONNECT and DATABASE) issue "SET LOCK MODE TO WAIT 300". 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 Tue, Jul 14, 2009 at 12:28 PM, PRADEEP KUMAR <pkyadav1@hotmail.com>wrote: > We are Truncating tables in ESQL/C program, it's failing with SQL error > -242 > ISAM -106. > > We have tried setting IFX_DIRTY_WAIT 300 in oninit file but the truncate > table > SQL is not waiting for the 300 seconds before it actually attempts the > TRUNCATE in case the table is locked by some other process. > > This is not the only truncate statement which this ESQL/C has, but all of > them > are executing successfully except one. > > Also, I'm not seeeing any lock wait or locks (syslocks) on the table , not > sure how to find the SQL statement which might be holding locks and not > allowing the TRUNCATE statement to run. > > Please help. > > Thanks in advance. > > pradeep > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5ad51178c5b046ead927e

Hello Pradeep, The situation you describe is one we've run into many times over the years. We have a select statement which does not show in syslcktab or onstat -g opn, yet prevents a different session from acquiring an exclusive table lock. And since nothing appears in syslcktab, lock mode wait has no effect. The best I can do for identifying the session is... select * from syssqlcurses where scs_sqlstatement matches "*<tablename>*" If your truncate is part of a program, I'd suggest trapping for the error and going through row deletes. The problem only effects table level activity and not row level deletes. Hope this helps, Dave Griffen PS. I can give instructions for reproducing this situation if anyone wants to investigate.