LOAD and LOAD with AMDIN_CMD

[Gregor =?UTF-8?B?S292YcSN?=]

Hi!

I've tried LOAD .... INSERT INTO TABLE_NAME. The docs say about INSERT
option:
"One of four modes under which the load utility can execute. Adds the loaded
data to the table without changing the existing table data."

But when I do LOAD I get this in my message file:
Number of rows read = 671
Number of rows skipped = 0
Number of rows loaded = 671
Number of rows rejected = 0
Number of rows deleted = 669
Number of rows committed = 671

Why there are 669 rows being deleted? If rows are being deleted, can I
assume that this is similar to INSERT_UPDATE option with IMPORT?

I've tried to use LOAD with ADMIN_CMD command, but find it not very useful,
because the result set returned by this procedures gives me a MSG_RETRIEVAL
SQL. With this SQL I can get SQLCODE and MSG to get messages. The problem
is that my message file has:
--------------------------------------------------------------------------
SQL3501W The table space(s) in which the table resides will not be placed
in
backup pending state since forward recovery is disabled for the database.

SQL3109N The utility is beginning to load data from file
"/home/gregor/Apps/BLAGAJNA/IMPORT/10.07.2007_15.29.29.887/PODROCJE.IXF".

SQL2036N The path for the file or device
"/home/gregor/Apps/BLAGAJNA/IMPORT/10.07.2007_15.29.29.887/PODROCJE.IX" is
not
valid.

SQL3107W There is at least one warning message in the message file.
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0
--------------------------------------------------------------------------

but MSG_RETRIEVAL SQL only gives me:
SQL3107W There is at least one warning message in the message file.

Why? where is the problem?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Gregor Kova� wrote:

But when I do LOAD I get this in my message file:
Number of rows read = 671
Number of rows skipped = 0
Number of rows loaded = 671
Number of rows rejected = 0
Number of rows deleted = 669
Number of rows committed = 671

Duplicates? Lots of duplicates...
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[The Boss]

Serge Rielau wrote:

Gregor Kovac wrote:

>But when I do LOAD I get this in my message file:
Number of rows read = 671
Number of rows skipped = 0
Number of rows loaded = 671
Number of rows rejected = 0
Number of rows deleted = 669
Number of rows committed = 671

Duplicates? Lots of duplicates...

I was thinking that as well, but wouldn't/shouldn't these just be rejected
i.s.o. loaded+committed+deleted?

--
Jeroen

[Arun Srinivasan]

On Jul 11, 4:07 pm, "The Boss" <[e-mail]:

Serge Rielau wrote:

Gregor Kovac wrote:

But when I do LOAD I get this in my message file:
Number of rows read = 671
Number of rows skipped = 0
Number of rows loaded = 671
Number of rows rejected = 0
Number of rows deleted = 669
Number of rows committed = 671

Duplicates? Lots of duplicates...

I was thinking that as well, but wouldn't/shouldn't these just be rejected
i.s.o. loaded+committed+deleted?

--
Jeroen

Only 2 useful rows loaded. In this case, if you had used exception
table opton on the
load command the deleted rows will be in the exception table.If you
hadn't then either again
load(use exception), take rows /import. BUt since the number of rows
is really low(I even use
import for 50000 rows) you might as well do an import on the whole
file again...
Hope this helps

Arun

[Serge Rielau]

The Boss wrote:

Serge Rielau wrote:

>Gregor Kovac wrote:

>>But when I do LOAD I get this in my message file:
Number of rows read = 671
Number of rows skipped = 0
Number of rows loaded = 671
Number of rows rejected = 0
Number of rows deleted = 669
Number of rows committed = 671

Duplicates? Lots of duplicates...

I was thinking that as well, but wouldn't/shouldn't these just be rejected
i.s.o. loaded+committed+deleted?

I think duplicate elimination is done in a post phase.
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gregor =?UTF-8?B?S292YcSN?=]

Serge Rielau wrote:

The Boss wrote:

>Serge Rielau wrote:

>>Gregor Kovac wrote:
But when I do LOAD I get this in my message file:
Number of rows read = 671
Number of rows skipped = 0
Number of rows loaded = 671
Number of rows rejected = 0
Number of rows deleted = 669
Number of rows committed = 671
Duplicates? Lots of duplicates...

I was thinking that as well, but wouldn't/shouldn't these just be
rejected i.s.o. loaded+committed+deleted?

I think duplicate elimination is done in a post phase.
Cheers
Serge

I was thinkging something else.
Thedocs says that INSERT option adds the loaded
data to the table without changing the existing table data. but you can see
deletes there, so it does change existing table data, right ?
Does this also mean that rows that are in the table AND in the file being
LOADed, so duplicates, are first deleted and the new rows are inserted?

Why am I asking this? IMPORT on big tables is slow, so I thought of using
LOAD, but LOAD does not support INSERT_UPDATE option only INSERT. But from
the samples I did INSERT actually deletes duplicates and then inserts new
rows, so it basically does INSERT_UPDATE.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Gregor Kova� wrote:

Why am I asking this? IMPORT on big tables is slow, so I thought of using
LOAD, but LOAD does not support INSERT_UPDATE option only INSERT. But from
the samples I did INSERT actually deletes duplicates and then inserts new
rows, so it basically does INSERT_UPDATE.

Kovi,

I checked with teh load team and they confirmed my belief:

"For load, the "deletes" being performed are based on "new" data. For
duplicates, load does not delete existing rows and replace them with new
data. Instead, load first inserts data (including rows that violates
unique key constraints), and then deletes new data that violates unique
key constraints."

So whatever you saw it wasn't an upsert.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gregor =?UTF-8?B?S292YcSN?=]

Aha, I see now. :))
I have just one final thing to clear up.
Let's say I have only one table in database, meanin there are no foreign
keys, no check constraint, no nothing. Just one database with it's primary
key and indexes (unique and non-unique). If I now do a LOAD into this table
is it ever going to be put into SET INTEGRITY pending state or any other
state that will prevent me from working with this table (doing
SELECTs, ...)?
How about if I have dropped table recovery feature enabled on the tablespace
that this table is in?

Best regards,
Kovi

Serge Rielau wrote:

Gregor Kova� wrote:

>Why am I asking this? IMPORT on big tables is slow, so I thought of using
LOAD, but LOAD does not support INSERT_UPDATE option only INSERT. But
from the samples I did INSERT actually deletes duplicates and then
inserts new rows, so it basically does INSERT_UPDATE.

Kovi,

I checked with teh load team and they confirmed my belief:

"For load, the "deletes" being performed are based on "new" data. For
duplicates, load does not delete existing rows and replace them with new
data. Instead, load first inserts data (including rows that violates
unique key constraints), and then deletes new data that violates unique
key constraints."

So whatever you saw it wasn't an upsert.

Cheers
Serge

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-