LOAD vs. IMPORT

[usenet]

Hi.

Can any of you explain the major differences in LOAD and IMPORT in
laymen terms?

I've read the DB2 docs: "[IMPORT] Inserts data from an external file
with a supported file format into a table, hierarchy, or view. A faster
alternative is LOAD; however, the load utility does not support loading
data at the hierarchy level."

What does "loading data at the hierarchy level" imply? I have 5 tables,
no (enforced) referential constraints. The biggest table has 500.000
rows and if possible I would like to avoid locking the table for long
(rather return an empty result set that have user wait).

Given my situation, what are the pros and cons of the two?

Thanks.

Morten

[kenfar]

us****@kikobu.c om wrote:

"... IMPORT (by default)
takes an exclusive lock on the table it is writing to" - if I do an
IMPORT with REPLACE option, and someone tries to SELECT from the table
while I'm importing, their SELECT will wait until..? Next commit from
the IMPORT or until the job is done?

Not positive on this one - but I don't think this will work for you: I
assume that the data previously in the table is completely wiped out by
the time of the first commit. So, you would likely want to have this
operation be all or nothing via load or by only commiting once.

An alternative in this scenario might be the insert_update option -
which will only create row locks if you use allow write access (thanks
Ian for the reminder). Of course, that won't delete data.

ken

[usenet]

Thanks for the input. If the REPLACE option is used, IMPORT will
truncate the table when it starts (as far as I can read the docs). The
question is now, whether IMPORT locks the table during the entire
process, or it allows pending read processes to run in between commits.
I need to delete the existing data (thus the REPLACE option), so I'm
afraid an "all or nothing" scenario is very hard to make using
LOAD/IMPORT.

[usenet]

Welp.. Import does not allow me to read in between commits. But it
suprises me that it is so extremely slow. I have written a client in
Java which using JDBC is very much faster (20-50 times) and that really
surprises me. The Java client uses batching (100 inserts sent over the
wire at a time, then commit) and stored procedures, the IMPORT job
commits after each 500 rows. I guess the IMPORT job sends one insert at
the time across the wire.

[Ian]

us****@kikobu.c om wrote:

Welp.. Import does not allow me to read in between commits. But it
suprises me that it is so extremely slow. I have written a client in
Java which using JDBC is very much faster (20-50 times) and that really
surprises me. The Java client uses batching (100 inserts sent over the
wire at a time, then commit) and stored procedures, the IMPORT job
commits after each 500 rows. I guess the IMPORT job sends one insert at
the time across the wire.

Yes, by default IMPORT does 1 row per insert statement. You can say,

import ... of del MODIFIED BY COMPOUND=x ...

(I think 0 < x <= 100)

This allows multiple rows to be written in each INSERT statement and can
often result in better performance.