Re: Delete Cursor: What To Use In Select Clause?

Jonathan Leffler wrote: > >For a few reasons, actually: > > > >1. Like you mention, I am using a subquery that would have to refer to the > > table being deleted. > > That can make life tricky. Yes, it turned out that way! I am attempting to synchronize data between a proprietary "database" (actually indexed files in a Unix BASIC, ugh) and Informix, so I am doing the following: Exporting the data to a flat text file Loading the flat file into a temp table Deleting the "old" records Updating the changed records Inserting the "new" records Deleting the old records requires that I compare contents of the table on which I'm performing the DELETE to the temp table. > >2. I want to avoid long transaction errors and excessive locking. There are > > begin/commit work pairs during the processing of the cursor. > > So it is actually a WITH HOLD cursor. There are several issues. One, if > you are running into long transaction problems, then arguably your system > is under configured -- it needs more logical log space. I'm currently set at LOGFILES 6 and LOGSIZE 500. If I understand correctly, in theory, there is no perfectly safe setting for these parameters: I can always come up with a command that blows them out. >If locking is a > problem, then maybe you should consider locking the table as a whole; you'd > need to use an exclusive lock. If that raises problems, then you need to > reconsider the whole way in which the operations are structured. That's a good idea; I'll look into it. But both the locking and transaction issues have to be solved simultaneously. >Why are > you trying to do such extensive revisions to the database. As above, in the name of synchronization, without interrupting use of the tables. > If you preprocess the records to be deleted into a temp table, you can > report on how many records are to be deleted by analysing the temp table; > quicker than dealing with the main table. You can, however, run into > problems with the underlying table changing between the time your SELECT > starts and the time your DELETE starts. Some of these can be relieved by > using an appropriate isolation level (eg REPEATABLE READ), though that will > probably stress the locking system again. Actually, I want to have the interface provide progress indicators, and selecting into a temp table would prevent that. > Your discussion of sqlbreakcallback() means that you think the user should > be able to cancel the operation part way through. Using intermediate > transactions means that you cannot always recover to the state before the > DELETE operations started -- which makes me very worried about the > integrity of the database. Hmm. I will look into this. At this time our Informix software set is minimal (can you tell by my newbie questions? <grin>), so I haven't had users banging away at the delete key. Yet. But I was in general not thinking so much about cancelling the operation as letting the engine announce to the user "I'm still here!" >Have you thought of spawning off a DELETE > process -- you establish the criteria with the user, possibly counting the > number of rows to be deleted, etc. Then, with their approval, you kick off > a second process which actually does the DELETE using a new database > connection? They aren't waiting for the DELETE to finish, so that deals > with the waiting problem. Interesting. I'll keep it in mind. > I don't have cut and dried answers to the issues -- I don't think there are > any. But I'm very tempted to ask why interactive operations are doing such > extensive deletions? Again, this is synchronization without mass deletion of the table. But the same process could be applied to temp tables, I suppose, during intensive analysis applications. Certainly, where feasible, I'd rather EXECUTE a DELETE statement than monkey with a cursor. But I have to have reassurances that the scope of the deletions (or updates, for that matter) will not be resource-consuming (log, lock). BTW, thanks very much for all your feedback. You appear to be respected here as a veritable goldmine, and I haven't seen anything to the contrary. -- Alexander J. Oss Computer Systems Manager Films for the Humanities & Sciences