High Speed Loading

I have a requirement to load bulk data at high speed into one specific table with two columns. (The data file can be of any size between 1meg to 100megs). I have to load this file from an application which is using ESQL/C and it is connected to the database and it has access the ascii file which is residing on local machine. What options do I have? What is the correct format? And any implications such that I dont run out of locks .. Any help or insights is much appreciated!

In article <373B8679.59D743BE@mci.com>, DK <devendra.kumar@mci.com> wrote: > I have a requirement to load bulk data at high speed > into one specific table with two columns. > (The data file can be of any size between 1meg to 100megs). > I have to load this file from an application which is using > ESQL/C and it is connected to the database and it has > access the ascii file which is residing on local machine. > > What options do I have? > What is the correct format? > And any implications such that I dont run out of locks .. > > Any help or insights is much appreciated! > > You have following options in the order of increasing speed. 1. load sql command Syntax : load from <file_name> delimiter <delimiting_char> insert into <table_name> 2. dbload utility Syntax : dbload options Options: -c command file -d database -n number of rows after which to commit -e number errors to ignore before aborting and so on Typical command file: FILE "product.unl" DELIMITER "|" 10; INSERT INTO product; FILE "Customer.unl" (Customer_id 1-6, name 7-35, city 37-51, zip 52-56 NULL="?????"); INSERT INTO Customer VALUES (customer_id, name, city, zip); 3. insert cursors dbload above is a generalized insert cursor. You can buffer records and load them into the table when the buffer is full 4. High Performance Loader This is the most advanced type of loader capable of doing parellel loads and taking input as a stream, data conversion while loading etc. It can also perform raw load in Informix internal storage format. HPL has many more useful features. However, you may have to buy this separately. Hope that helps. --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---

DK wrote: > I have a requirement to load bulk data at high speed > into one specific table with two columns. > (The data file can be of any size between 1meg to 100megs). > I have to load this file from an application which is using > ESQL/C and it is connected to the database and it has > access the ascii file which is residing on local machine. > > What options do I have? > What is the correct format? > And any implications such that I dont run out of locks .. > > Any help or insights is much appreciated! Do you have access to HPL (High Performance Loader)? You didn't specify which platform you are running on. HPL may be slower than a good esql/c program. Also it depends on how well you wrote the esql/c program. Are you using the sqlvar struct? Did you consider making your esql/c program forked() or multi-threaded? (Simple librarian / Single Writer,Multiple reader problem) -Uncle Mikey

Srinivasulun@hotmail.com wrote: > In article <373B8679.59D743BE@mci.com>, > DK <devendra.kumar@mci.com> wrote: > > I have a requirement to load bulk data at high speed > > into one specific table with two columns. > > (The data file can be of any size between 1meg to 100megs). > > I have to load this file from an application which is using > > ESQL/C and it is connected to the database and it has > > access the ascii file which is residing on local machine. > > > > What options do I have? > > What is the correct format? > > And any implications such that I dont run out of locks .. > > > > Any help or insights is much appreciated! > > > > > > You have following options in the order of increasing speed. > > 1. load sql command > Syntax : load from <file_name> delimiter <delimiting_char> > insert into <table_name> > 2. dbload utility > Syntax : dbload options > Options: > -c command file > -d database > -n number of rows after which to commit > -e number errors to ignore before aborting > and so on > Typical command file: > FILE "product.unl" DELIMITER "|" 10; > INSERT INTO product;> FILE "Customer.unl" (Customer_id 1-6, name 7-35, city 37-51, > zip 52-56 NULL="?????"); > INSERT INTO Customer VALUES (customer_id, name, city, zip);> > 3. insert cursors > > dbload above is a generalized insert cursor. You can buffer > records and load them into the table when the buffer is full > > 4. High Performance Loader > > This is the most advanced type of loader capable of doing parellel > loads and taking input as a stream, data conversion while loading > etc. It can also perform raw load in Informix internal storage > format. HPL has many more useful features. However, you may have to > buy this separately. You do not have to buy the HPL separately, it comes as part of the product. HPL will do parallel loads in parallel streams. Split your input data up into multiple files and build an HPL job for each file. Start them all at the same time. It will help tremendously if your data is partitioned in the schema and the input files match that partioning scheme. > > > Hope that helps. > > --== Sent via Deja.com http://www.deja.com/ ==-- > ---Share what you know. Learn what you don't.---