Deleting 100000 rows failed

Hi, I have a procedure which will delete 10 lac rows, now when i execute this procedure it fails after few min saying long transaction error. how can i autocommit this thing so that i doest not go into logical log files ?? Amit.

Increase you logs, or the size, or increase the percentages for long transactions in onconfig. In your procedure you could do a commit every n rows. You could alter temporarily your table to raw and after delete restore to the original size. Or well, don't use transaction at all, but you have to be certain what you're doing. Chucho! -----Original Message----- From: "AMIT DIXIT" <amdixit_x@hssworld.com> To: ids@iiug.org Date: Tue, 9 Nov 2004 10:29:07 -0500 (EST) Subject: Deleting 100000 rows failed [3654] Hi, I have a procedure which will delete 10 lac rows, now when i execute this procedure it fails after few min saying long transaction error. how can i autocommit this thing so that i doest not go into logical log files ?? Amit. Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com

If these are relatively simple deletes from a single table, get my dbdelete utility which is contained in the package utils2_ak which you can download from the IIUG Software Repository (www.iiug.org/software/index.html). If dbdelete can use RRN's to identify rows to delete (ie do not specify a -u option) it is just about the fastest way to delete records from a table. It deletes and commits blocks of 8192 rows by default but you can control that with the -b option (#rows deleted per commit = buffer size/4). These partial commits allow you to control how much logical log space is tied up in a single transaction and eliminate LONG TRANSACTION ROLLBACKs. The other thing you will need to do is make certain that the logical logs are archived to keep up with the logging requirements so that the logs do not fill during the delete operation. To eliminate the logging altogether, you can either mark the database as unlogged for this operation and then change it back or you can ALTER the table to mode RAW and change it back afterwards. Art S. Kagel ----- Original Message ----- From: Amit Dixit <amdixit_x@hssworld.com> At: 11/ 9 10:45 > Hi, > I have a procedure which will delete 10 lac rows, > now when i execute this procedure it fails after few min > saying long transaction error. > > how can i autocommit this thing so that i doest not go into > logical log files ?? > > Amit.

Amit, I don't think that you mean that you want to prevent it from going to the logical logs. There are ways to do that, but I think that what you really want to do is periodically commit the deletions, so that you don't fill the logs. So my first question is, how are you doing the deletes? Are you stepping through a cursor? If so, the problem becomes trivial to solve... If not, it gets more complicated. Basically, you need to find a way to break up the deletes into smaller bits, and then commit after each bit. Alternatively... you can always increase the size of your logical logs. It seems to me that 100,000 rows is sizeable, but not too large; your logs ought to be able to handle that in one transaction. I'd be curious about how much log space you have and what the LTXHWM settings are in the ONCONFIG file (assuming you're not using dynamic logging). If you're in a post 9.3 version (I think; I can't remember when dynamic logging started... been a while for me), you might want to turn that feature on, and see how big your logfiles grow (in development, of course, not in production). That way, you'll have a better sense of how large to make the production logs, or if this is really a problem. I hope that makes sense; if not reply either here, or off list, and I can probably help you out. Thanks. Dan Michaelis Senior Software Developer eOriginal 351 West Camden Street Suite 800 Baltimore, MD 21201 410.625.5187 (phone) 410.659.9799 (fax) -----Original Message----- From: AMIT DIXIT [mailto:amdixit_x@hssworld.com] Sent: Tuesday, November 09, 2004 10:29 AM To: ids@iiug.org Subject: Deleting 100000 rows failed [3654] Hi, I have a procedure which will delete 10 lac rows, now when i execute this procedure it fails after few min saying long transaction error. how can i autocommit this thing so that i doest not go into logical log files ?? Amit.