Re: Large table with daily batch data load

Samuel >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? What?, delete any index, load your data and then re-create your indexes. You can of course when during large loading of data, increase the CheckPoint Interval so that the check point process doesn't decrease performance. >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;> NO, it is not. You can use SQL with SHELL commands , check the http://www.iiug.org archive for examples. > 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. You can still use dbload command from Shell scripts and specify it when to commit the work for a specific number of records. (Check your SQL documentation. >Thank you! Regards, Mario Estrada