LOAD and LOAD with AMDIN_CMD
in [DB2]
|
Prev: Run DB2CMD commands through PowerShell
Next: SQL0332N There is no available conversion for the source code page
From: Gregor Kovač on 12 Jul 2007 09:57 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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Serge Rielau on 12 Jul 2007 13:19 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
From: Gregor Kovač on 18 Jul 2007 01:04 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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Serge Rielau on 18 Jul 2007 08:13 Gregor Kovač wrote: > 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, ...)? To the best of my knwoledge: No. You didn't mention IDENTITY and expression generated columns, but LOAD will maintain these without need for SET INTEGRITY. > How about if I have dropped table recovery feature enabled on the tablespace > that this table is in? To the best of my knowledge that is orthogonal. In fact I'm unclear how you arrived at the question. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Gregor Kovač on 7 Aug 2007 08:05
Serge Rielau wrote: > Gregor Kovač wrote: >> 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, ...)? > To the best of my knwoledge: No. > You didn't mention IDENTITY and expression generated columns, but LOAD > will maintain these without need for SET INTEGRITY. > >> How about if I have dropped table recovery feature enabled on the >> tablespace that this table is in? > To the best of my knowledge that is orthogonal. In fact I'm unclear how > you arrived at the question. > > Cheers > Serge I was just exploring all options. :)) -- -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- | In A World Without Fences Who Needs Gates? | | Experience Linux. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- |