Re: Large table with daily batch data load

Samuel Tan wrote: > > Hi, > > I have a large table (approx. 500k records) with 3 indexes (incl. primary > key) which is used to store the monthly outstanding product costs. Everyday > there is data upload to the table. I have two questions in it. > > 1) Since the upload time is very slow, I decided to drop 2 indexes so as to > increase the efficiency. But the indexes are quite useful and I hope not to > remove them. How can I restore the indexes so that the data upload can be > done faster? You could drop all three indices and recreate them after the load. > > 2) Is it necessary to write 4GL programs for all the data loading process? > I have tried the simplest way, ie, in isql, > BEGIN WORK; > LOCK TABLE cost_cde IN EXCLUSIVE MODE; > LOAD FROM "infile.txt" DELIMITER "," > INSERT INTO cost_cde;> You could probably execute a SQL script that could include everything. > However most of the time I run the above SQL, Informix dies by saying > "Page cleaner timeout..." in the online log. So I write a program to > perform the data loading by committing the loading every 1000 records. > How is the data layout? It appears by the code that you posted that the data doesn't need to be manipulated, only loaded. You might try DBLOAD, which could consolidate the load and the COMMIT into one bundle. John Carlson Informix DBA WH Smith, Inc.