deleting 300,000 records with 50000 locks -Reply

Sukumar, The reason you are using multiple locks per rows may be due to indexes you have on the table (probably 3 if you have 4 locks per record). Have you tried locking the table in exclusive mode prior to your insert/delete operation? Of course, this would require logging but only 1 lock for the table would be used. Hope that helps, John ---------------------------- John F. Coyle, DBA Yankee Candle Co. South Deerfield, MA 01373-0110 jfc@yankeecandle.com >>> Sukumar Konduru <sukumar@gte.net> 04/29/98 09:57am >>> 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