Problems with Informix and Cascading Deletes --- Help :)

Hi all, I have been having a couple of problems with my Informix server's cascade deletion capability. In my database, I have two tables (say, table "Tab1" and table "Tab2"). Row entries in "Tab2" are triggered such that when its related row in "Tab1" is deleted, the entry in "Tab2" is deleted. Visually (before deleting a row in Tab1): Tab1 Tab2 ---- ---- 1) bob smith employee 1 2) john doe employee 2 3) amanda doe employee 3 Unfortunately, in our large database configuration (18+ gigabytes), I have found that, sometimes, entries in "Tab1" are deleted but the entries in "Tab2" are not deleted. Even more bizarre -- the function that performs the deletion of entries in "Tab1" is not an ESQL/C program at all, but a stored procedure in the database (executed by an ESQL/C program). The result is that the tables in my database are growing unwieldly large, and the number of extents is increasing to unmanageable levels, due to the orphaned entries in "Tab2". Visually (after deleting an entry): Tab1 Tab2 ---- ---- 1) bob smith employee 1 2) john doe employee 2 employee 3 Admittedly, the problem may reside in my code, but at this point in time, I am trying to explore all posibilities. (Incidentally, the database has logging turned on.) Unfortunately, it has been very, very difficult to reproduce this error (it does not seem to appear in controlled lab conditions, but in customers' configurations :). I am using "INFORMIX-OnLine Version 7.14.UD1XH". Has anyone encountered similar problems with this version of Informix? If so, has a patch has been released (or has the problem been fixed in more recent versions)? Does there exist a webpage that provides a list of patches and releases, as well as their corresponding fixes? I apologize beforehand for not relating too much specific information on this newsgroup, but I would prefer to discuss this matter through e-mail, if possible. Thanks for your time and help, Kris Vorwerk mailto:kvorwerk@crosskeys.com

Kristofer Vorwerk wrote: > > Hi all, > > I have been having a couple of problems with my Informix server's > cascade deletion capability. In my database, I have two tables (say, > table "Tab1" and table "Tab2"). Row entries in "Tab2" are triggered > such that when its related row in "Tab1" is deleted, the entry in "Tab2" > is deleted. > > Visually (before deleting a row in Tab1): > > Tab1 Tab2 > ---- ---- > 1) bob smith employee 1 > 2) john doe employee 2 > 3) amanda doe employee 3 > > Unfortunately, in our large database configuration (18+ gigabytes), I > have found that, sometimes, entries in "Tab1" are deleted but the > entries in "Tab2" are not deleted. Even more bizarre -- the function > that performs the deletion of entries in "Tab1" is not an ESQL/C program > at all, but a stored procedure in the database (executed by an ESQL/C > program). The result is that the tables in my database are growing > unwieldly large, and the number of extents is increasing to unmanageable > levels, due to the orphaned entries in "Tab2". > > Visually (after deleting an entry): > > Tab1 Tab2 > ---- ---- > 1) bob smith employee 1 > 2) john doe employee 2 > employee 3 > > Admittedly, the problem may reside in my code, but at this point in > time, I am trying to explore all posibilities. (Incidentally, the > database has logging turned on.) Unfortunately, it has been very, very > difficult to reproduce this error (it does not seem to appear in > controlled lab conditions, but in customers' configurations :). > > I am using "INFORMIX-OnLine Version 7.14.UD1XH". Has anyone encountered > similar problems with this version of Informix? If so, has a patch has > been released (or has the problem been fixed in more recent versions)? > Does there exist a webpage that provides a list of patches and releases, > as well as their corresponding fixes? > > I apologize beforehand for not relating too much specific information on > this newsgroup, but I would prefer to discuss this matter through > e-mail, if possible. Let me just suggest that the stored procedure has not SET LOCK MODE TO WAIT and so it is occassionally being locked out from deleting the tab2 row. Also you are apparently not trapping and returning the error to the delete trigger on tab1 or the transaction would rollback in its entirety. Art S. Kagel