Re: Informix DSA 7.23, Error 214 (Cannot remove file or table)

Felix Koshy Mathews wrote: } In <ZSYToDAgnInzEwuK@smooth1.demon.co.uk>, David Williams wrote: } <snip> } >>Have any of you ever encountered 214 error while dropping a table } >>( for re-creation ), from a stored procedure. On On-Line DSA 7.23 } UC1, } >>we have the following problem on one of the tables : } >> } >>-214 Cannot remove file for table table-name. } >> } >>On this error our program quits, on next try, the engine happly } drops } >>the table !!!. } >> } > } > What is the associated isam error? } > Someone probably has a row/page locked in the table or holds a table } } > level lock. } } David, } } Thanks for your feed-back. We can't re-create the problem, when the } user/tester got the error we couldn't catch the ISAM-error code. } } None of our program have any table/page level lock for the table } involved in this, transactions where either committed/rolled-back and } all cursors closed/freed, before executing the drop and re-create. } } For access permissions, Informix normally uses : } } SQL-CODE : -242 Could not open database table table-name } } With ISAM Code : -113 for file/table lock } -106 non exclusive access } . } } The problem was consistent for that session in which the user did } retry } the executable a few times (from error.log, information) and } re-produced } the problem. Only after re-starting the user-session we were informed } about the problem, but we can't re-produce it. } } -- } David, Thanks for your effort in helping us. Finally we were able to capture the ISAM error code and able analyse the reason behind it. It is not lock error as you suspected, but it is a random error caused by parallel processing threads ( that too when the machine is very busy ). We reached to the following conclusion : The program under investigation was reading data from the table for generating reports, after which it closes all cursors and all other things. This gets recoded by one thread ( for releasing table access or what ever internal flags of the engine ), before this job is physically completed ( logically it is), another thread executes remaining statements ( which inclues the drop table in SPL ). When the machine is really busy the second therad, reches before the earlier thread clears all of its table access flags. This ticks the engine !!!, and it gets wild raising : SQLCA.SQLERRD 214 and ISAM ERROR 106. We have implemented a work-around solution, which is tailor-made for the situation that we have. In engines before parallel threadding ( I am sure up to On-Line 5.0), Informix recommended users calling tech-support, if we get ISAM error 106 in any SQL-Products ( pgrogrammers were supposed to take care of this error, or make work arounds, for C-ISAM applications). Does it mean that now we are supposed have some work arounds even in case of On-Line/SQL-Products ??? } Have a nice day } } Felix K. Mathews } mailto:fmathews@systems.dhl.com -- Have a nice day Felix K. Mathews mailto:fmathews@systems.dhl.com