Marc:
Myschema can generate external table unload and load scripts for your tables (note that I'm using the latest November 4th release of myschema, the October 28th release first included changing the table to RAW before the load and back to STANDARD after). Here's an example:
art@Elbereth:~/GoogleDrive/MyConsulting/test$ myschema -d art -t extents --myexport-express --myexport-delimiter >/dev/null
art@Elbereth:~/GoogleDrive/MyConsulting/test$ ls -laF
total 28
drwxrwxr-x 3 art art 4096 Nov 10 07:15 ./
drwxrwxr-x 75 art art 12288 Oct 29 06:27 ../
-rw-rw-r-- 1 art art 431 Nov 10 07:15 art.extents.myexport.sql
-rw-rw-r-- 1 art art 502 Nov 10 07:15 art.extents.myimport.sql
art@Elbereth:~/GoogleDrive/MyConsulting/test$ cat art.extents.myimport.sql
CREATE EXTERNAL TABLE extents_myimport (
dbsname "informix".lvarchar(10000),
tabname "informix".lvarchar(10000),
chunk INTEGER,
offset INTEGER,
size INTEGER,
truth "informix".boolean
) USING ( FORMAT "DELIMITED",
DATAFILES ( "disk:/home/art/GoogleDrive/MyConsulting/test/exten01930.unl" ),
DELIMITER ",", EXPRESS, ESCAPE ON );
ALTER TABLE extents TYPE( RAW );
INSERT INTO extents
SELECT * FROM extents_myimport;
DROP TABLE extents_myimport;
ALTER TABLE extents TYPE( STANDARD );
You would just have to change the DATAFILES option to list the actual path of your CSV file.
If you are not familiar with myschema, you can always download the latest release of my utils2_ak package which includes myschema, from my web site at: My Utilities
.
BTW, you can speed up the dbload run somewhat by altering the table to RAW before the load. It won't be as fast as external tables, but may be fast enough for you.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Wed November 10, 2021 05:47 AM
From: Marc Demhartner
Subject: 14.1 WE - fastest way to load 61Mio lines CSV file
Thanks for your feedback - yes, we´re loading it to a fresh created "loading table" tl_marc e.g. without index - CSV file is 100% valid, we´re converting it on OS before loading it into the DB.
Yes, its a single-node instance.
@khaled Bentebal WE does not allow parallelize.
Can you explain that "external table" thing?
------------------------------
Marc Demhartner
Original Message:
Sent: Wed November 10, 2021 05:37 AM
From: Alexandre Marini
Subject: 14.1 WE - fastest way to load 61Mio lines CSV file
Hi Marc.
Csv loading is never a "simple trick".
Dbload is a very good utility that has several validations to avoid loading incorrect data.
But... you can also try bewer methods eg: external tables (supposing your data is delimited). That might be a good option.
I am just curious when you say "several hours". How is your dbload storing that? Directly into a permanent table? Do you have any cluster or only single-node instance?
Alexandre Marini
Original Message:
Sent: 11/10/2021 5:25:00 AM
From: Marc Demhartner
Subject: 14.1 WE - fastest way to load 61Mio lines CSV file
Hi all,
i have a question about loading CSV files into informix. Currently we´re getting really huge CSV files from ERP systems (61Mio lines in 1 file) - and it takes hours to load it via dbload.
How you would to it ? Base is Informix 14.10 Workgroup Edition with RHEL8 64bit
------------------------------
Marc Demhartner
------------------------------
#Informix