Re: deleting 300,000 records with 50000 locks

If you have indexes on these tables, the number of locks acquired will exceed the number of rows being loaded. You do not state what version of Informix you are running. With Informix ODS 7.2x you do several things to reduce the number of locks held during the load process. 1. Change to page level locking. ALTER TABLE tabname LOCK MODE(PAGE);2. Disable constraints and indexes. SET CONSTRAINTS, INDEXES FOR TABLE tabname DISABLED; 3. Use the dbload utility to load the data. It will issues COMMITs at requested intervals. After the data is loaded, you need to reverse actions 1 and 2: SET CONSTRAINTS, INDEXES FOR TABLLE tabname ENABLED; ALTER TABLE tabname LOCK MODE(ROW);Since I am somewhat new to Informix, I do not know which of these features are available with earlier releases. Rick At 11:27 AM 04/29/1998 -0400, you wrote: >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 > > 4.1 Begin work; > 4.2 LOCK TABLE tabname IN EXCLUSIVE MODE > >> 5. load from file. >> i.e load from "file.dat" insert into tablename > > 6. COMMIT WORK > >> >> 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 >> > >Hope this helps > >Tino > >> sukumar@gte.net >> >> Thank you >> sukumar konduru > >