Re: deleting 300,000 records with 50000 locks

You have multiple locks per insert because (probably) you have indexes on the table. Every insert (or update or delete) must lock the row in the table and the appropriate entries in each index, until commit time. Alternatives include: 1. Disable logging on the database, load the table, then re-enable logging. This will require a Level 0 archive, and means that no one else should be updating the tables. In fact, if the applications have BEGIN WORKs they will now fail because there is no logging. 2. Alter the table to page level locking instead of row level locking. This would allow multiple inserts to be handled by a single lock. Disadvantages include reduced concurrency with other transactions, but everything's a tradeoff. 3. Use the dbload utility, which has a parameter to force commits every "n" records. 4. If you're using ODS 7.1 or greater, fragment the table based on month. Then, ALTER FRAGMENT ... DETACH to separate the fragments for the dates you want to drop, drop the resulting tables, then add new fragments for future dates using the dbspaces the old fragments were in. 5. Use LOCK TABLE x IN EXCLUSIVE MODE. This will place a single lock on the table, regardless of how many updates are performed. Unfortunately, this requires you to be in a transaction and you've already said you don't have enough log space for that. If you have enough log space, obviously the next problem with this is that no one else can access the table while you're doing this. Mark Collins mcollins@us.dhl.com No matter how much Jell-O you put in a swimming pool you still can't walk on water. > 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 >