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 13 17818
use...@kikobu.c om wrote: Hi.
Can any of you explain the major differences in LOAD and IMPORT in laymen terms?
The biggest difference is that import is a logged operation - similar
to doing your own inserts, while load bypasses the logs and adds the
data directly to the table. I think the issue about hierarchies is
regarding the implementation of sub-types through db2. Doesn't sound
like this is what you're doing.
Pros of each for your situation would probably look like:
import
- pros: best solution for concurrency & recoverability.
- cons: slower of the two solutions, and could result in quality errors
to queries while it is running - due to partial data caused by multiple
commits.
load
- pros: fastest solution
- cons: poor solution for recoverability in your situation, and
concurrency limitations (note though when used with "ALLOW READ ACCESS"
concurrency is usually fine).
Another question might be whether your concurrent queries will return
erroneous results if you load or import just a single table at a
time....
Ken us****@kikobu.c om wrote: 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
The "hierarchy level" refers to typed table hierarchies. Nothing you
have to worry about.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
"kenfar" <ke****@gmail.c om> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. . The biggest difference is that import is a logged operation - similar to doing your own inserts, while load bypasses the logs and adds the data directly to the table. I think the issue about hierarchies is regarding the implementation of sub-types through db2. Doesn't sound like this is what you're doing.
Pros of each for your situation would probably look like:
import - pros: best solution for concurrency & recoverability. - cons: slower of the two solutions, and could result in quality errors to queries while it is running - due to partial data caused by multiple commits.
load - pros: fastest solution - cons: poor solution for recoverability in your situation, and concurrency limitations (note though when used with "ALLOW READ ACCESS" concurrency is usually fine).
Another question might be whether your concurrent queries will return erroneous results if you load or import just a single table at a time....
Ken
Keep in mind that since import does inserts, it could fire triggers defined
on the tables. Load will not fire any triggers.
If doing an import, you should use the commitcount parm to do a commit every
1000 rows or so if you have a large amount of data to import. This will keep
the logs from filling up.
In article <11************ **********@g44g 2000cwa.googleg roups.com>, "kenfar" <ke****@gmail.c om> writes: use...@kikobu. com wrote: Hi.
Can any of you explain the major differences in LOAD and IMPORT in laymen terms?
The biggest difference is that import is a logged operation - similar to doing your own inserts, while load bypasses the logs and adds the data directly to the table. I think the issue about hierarchies is regarding the implementation of sub-types through db2. Doesn't sound like this is what you're doing.
Pros of each for your situation would probably look like:
import - pros: best solution for concurrency & recoverability. - cons: slower of the two solutions, and could result in quality errors to queries while it is running - due to partial data caused by multiple commits.
load - pros: fastest solution - cons: poor solution for recoverability in your situation, and concurrency limitations (note though when used with "ALLOW READ ACCESS" concurrency is usually fine).
Another question might be whether your concurrent queries will return erroneous results if you load or import just a single table at a time....
Ken
A big con for Load that we ran into was:
- load would leave the tablespace in 'backup pending' mode, thus requiring
a backup to continue to use the table
- you could use the NONRECOVERABLE option to avoid this, but then you
couldn't rollforward any journals after the load.
- load also needed to acquire a super exclusive lock on the table
Once we went production with our warehouse, we had to convert all of our
LOAD scripts to use IMPORT.
This is LUW, 8.1.6 I don't know if 8.2 has the same restrictions...
Doug
Doug Crowson wrote: A big con for Load that we ran into was: - load would leave the tablespace in 'backup pending' mode, thus requiring a backup to continue to use the table - you could use the NONRECOVERABLE option to avoid this, but then you couldn't rollforward any journals after the load.
You need to use COPY YES in order to perform a recoverable load and
avoid placing the tablespace into backup pending state.
- load also needed to acquire a super exclusive lock on the table
Yes, but if you are doing an online load, the Z-lock on the table is
only held for a short time.
>> A big con for Load that we ran into was: - load would leave the tablespace in 'backup pending' mode, thus requiring a backup to continue to use the table - you could use the NONRECOVERABLE option to avoid this, but then you couldn't rollforward any journals after the load. You need to use COPY YES in order to perform a recoverable load and avoid placing the tablespace into backup pending state.
Yeah, i try very hard to avoid using load on a transactional database.
In the case of my warehouse, it is non-transactional - and the load
files are the backups: if a recovery is required we move the compressed
files from archive to input, and the loader takes care of it.
Simplifies most things.
- load also needed to acquire a - load also needed to acquire a super exclusive lock on the table Yes, but if you are doing an online load, the Z-lock on the table is only held for a short time.
yeah, i've only really had problems with load allowing read access when
the server was getting hit by a massive barrage of like 60,000 queries
- to drive canned reports. Other than that, a long lockwaittime of 120
seconds or so (assuming average query duration of 5 seconds) has worked
fine.
One other thing I forgot to mention: the insert_update import option
is really handy, and i'm often now using it for smaller volume (<
100,000 row) table ETL operations. Especially when concurrency is
tricky.
kenfar wrote: One other thing I forgot to mention: the insert_update import option is really handy, and i'm often now using it for smaller volume (< 100,000 row) table ETL operations. Especially when concurrency is tricky.
Obviously you've been around the block a few times, but from a
concurrency perspective, you do realize that IMPORT (by default)
takes an exclusive lock on the table it is writing to, right?
This was a MAJOR concurrency issue; I had to write a replacement
for the import utility to avoid this in V7.2.
One of the V8 fixpacks (finally!) allowed you to work around this
requirement, and FP9 added the 'ALLOW WRITE ACCESS' option to IMPORT.
Thanks for all the input, it has been really insightful. I've decided
to go with LOAD first (COPY YES), and see how long the job takes. There
will be hardly any users on the system at the time I do the load, so if
I can do it below 1 minute, it's okay.
If this does not work, I'll wipe the table first (empty LOAD) and then
do an IMPORT. As mentioned, responsiveness is more of an issue than the
user seeing wrong/missing data for a few seconds.
This job is the only that ever writes to the tables.
Thanks.
Morten
"... 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?
Thanks.
Morten This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: davvid |
last post by:
hello,
please I would like to know if it's possible to load data in
hierarchical table structure with identity column used as pk (for all
the tables) :
I mean
table root (Id_root(pk,identity column generated always), att_r1,...)
table leaf (Id_leaf(pk,identity column generated always), id_root(fk
ref root), att_l1 ...)
|
by: jay |
last post by:
hi,
Question on Load/import command.
consider a sample table
create table table_name (
col1 timestamp not null default current timestamp,
col2 int,
col3 int,
col4 int,
primary key(col1) );
There is a file file.del containing data for col2,col3,col4. Data for
|
by: Ravi |
last post by:
Hi friends,
I have to lode/import the data from files, is there any command to
lode/import, in same time ( with singal command) iam using db2 8.2.
Thanks In advance,
Ravi.
|
by: Eric |
last post by:
Hi,
Any suggestion to Rapid Application Dev with postgreSQL ?
I have a small app to make.
I would prefer open-source solution if possible. I also would like to
developp on my linux box but the result will be used on a windows 2000
and XP machine. But If I have no choice, I will developp on winXP.
|
by: aj |
last post by:
DB2 WSE LUW 8.1.5
A few questions:
Why doesn't IMPORT have IDENTITYOVERRIDE support like LOAD does??
Why doesn't LOAD have CREATE INTO (ala IXF) like IMPORT DOES?
Why doesn't:
db2 -tvf blah.sql 2> blah.err
| |
by: Hemant Shah |
last post by:
Folks,
Today, I was exporting a table in one database and then importing it in
another database. The table in destination database was missing one column
(my mistake while creating the table), but import did not complain about it.
Source table:
Column Type Type
|
by: jrickard |
last post by:
Hi,
I'm getting an SQL3193N on DB2 v8 (fixpak 11)...
SQL3193N The specified view or materialized query table cannot be
updated. You cannot LOAD/IMPORT into this view or LOAD into this
materialized query table.
....when I try to IMPORT INSERT into my non-updateable view. The view
has an INSTEAD OF trigger defined on it, and inserts...
|
by: sai1001 |
last post by:
hi,
I am trying to load the data from flat file to temp table in different ways.
import from 'c:\OCT SC REPORT TAKE 2.csv' into session.kfs_file;
load from 'c:\OCT SC REPORT TAKE 2.csv' of ASC insert into session.kfs_file;
But i am getting the error like this.
SQL1325N The remote database environment does not support the command or...
|
by: rradhak |
last post by:
Hi,
We use udb version 8.1 fixpack 11 under AIX 5.3
Is there a way to use load/import against temporary tables?
DB2 does not recognize session tables. Is there any workaround?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |