Re: deleting 300,000 records with 50000 locks

Sukumar Konduru wrote: > 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 You need additional locks for indexes being updated. As others point out you can "LOCK TABLE ... IN EXCLUSIVE MODE" to avoid the locking problem, but, as you point out you may still run out of log space. One solution is to use dbload rather than the dbaccess load command. Dbload allows you to specify that the should or should not be locked dring the load and whether or not to commit every N rows to prevent the long transaction problem. Another option is to use an ALTER FRAGMENT statement moving all of the unwanted (or, better, the wanted rows) into a new fragment in another dbspace, a second ALTER FRAGMENT detaching the historical fragment into a separate table and then you can just drop the unwanted history table. This will probably be the fastest method. Art S. Kagel