Re: deleting 300,000 records with 50000 locks

Sukumar Konduru wrote: > > Hi > > I need to delete 300,000 transactions for every 3 months. We have more > than 1 million transactions in this table. > > What I do is as follows. > > 1. Prepare dbschema for the table > 2. unload to a file required transactions. i.e recent 3 months > transactions. > 3. Drop the table > 4. create table with dbschema > 5. load from file. > i.e load from "file.dat" insert into tablename > > We have only 50,000 locks. Only way I am able to load records in to > database is by splitting > into lesss than 10,000 records and inserting records from these small > files one by one . > > Why I am not able to even insert 40,000 records at a time , inspite of > having 50,000 locks. > When I try to insert even 10,000 records, onstat shows that 40000 > locks are in use. > > Is every inserting transactions needs 4 locks?. It is surprising me. > > I can not use "BEGIN WORK", because we do not have more log files. > All these problems are due to lack of RAM > > Could you please mail me > > sukumar@gte.net > > Thank you > sukumar konduru Are you using row or page locking? If you are using row locking, why do not you use page locking during the load operation. You can use alter table lock mode (page); before you lock the table. This may require a lesser number of locks. Hope this helps! Amgad Al-Sisi