How to put a csv file into a Db2 LUW table? IMPORT, LOAD and INGEST overview
Let’s assume you have the following data (it’s presented in the csv format) that you want to insert into a table called “ANIMALS”:
"MAMMALS","CATS"
"MAMMALS","COWS"
"MAMMALS","DOGS"
"MAMMALS","HORSES"
"MAMMALS","SHEEP"
The data is stored in a csv file called data_to_insert.csv. Table ANIMALS has two columns: CLASS and SPECIES.
What can you do? The most basic and obvious idea is to create proper INSERT query:
INSERT INTO ANIMALS (CLASS, SPECIES)VALUES ('MAMMALS', 'CATS'), ('MAMMALS', 'COWS'), ('MAMMALS', 'DOGS'), ('MAMMALS', 'HORSES'), ('MAMMALS', 'SHEEP');
IMPORT utility
In this case preparing such a query is acceptable, but what if I wanted to insert 100 new species? Should I write a super-long SQL query? Or is there a short-cut?
Yes, it is. There is a Db2 utility that creates such a query for you and runs it immediately. It’s called IMPORT. You just have to execute the following line:
db2 "IMPORT FROM data_to_insert.csv OF DEL INSERT INTO ANIMALS"
That’s it. All the SQL part is done under the cover. If you want to overwrite the data instead of appending it, change “insert” to “replace”:
db2 "IMPORT FROM data_to_insert.csv OF DEL REPLACE INTO ANIMALS"
That would be all you need to know regarding sending data from a file into a Db2 table, but…
IMPORT tool works fine for inserting thousands of records, but creating and executing an INSERT SQL query (even implicitly) for millions of records might take too long. So, if you need to insert really big amount of data, think about the LOAD utility.
LOAD utility
LOAD has similar syntax to IMPORT, but it is significantly faster, because instead of creating and executing an INSERT query, it puts the data directly into the table. In our case it would look like this:
db2 "LOAD FROM data_to_insert.csv OF DEL INSERT INTO ANIMALS"
So why did we bother discussing the IMPORT utility? Well… LOAD is not so safe as IMPORT. For example, if a load operation is interrupted or fails, the table may remain in Load Pending state, which means it cannot be used until you restart/kill the LOAD operation or until you restore the tablespace from a proper backup image; whereas IMPORT is just a list of INSERT operations — if it’s interrupted, you can rollback or restart or leave the table partially populated.
INGEST utility
OK, but what if I not only have a huge amount of data to be imported into an existing table, but also I want that table to be fully available for other users during the operation?
If you’re not using a Db2 version from the dinosaurs era (older than 10.1), then you can reach for yet another utility, namely INGEST. While IMPORT and LOAD require usually an exclusive lock on the table, the INGEST command locks only single rows, one by one. Thanks to that, other clients can use the table simultaneously with the INGEST process. What’s the syntax?
db2 "INGEST FROM data_to_insert.csv FORMAT DELIMITED INSERT INTO ANIMALS"
As you can see, usage is the same, but the naming convention changed a little. Instead of “OF DEL”, we use “FORMAT DELIMITED”. It’s longer, but more descriptive on the other hand.
Hmm…, so should we just use INGEST and forget about other tools?
No, because also this utility its drawbacks. For example it doesn’t support inline LOBs, inline XMLs or IXF format. Therefore you have to choose your weapon carefully each time you need to insert some data in the table.
Here are more detailed descriptions of each of the tools:
and here is a quick comparison of them in the form of a table:
So, again, choose your weapon carefully and good luck with your data!
