Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    57

    Unanswered: Dbe writes that deletes row but it is not true??

    Hello all!
    During the load I noticed that some rows has been deleted. I created a problem on a small table and tried to see what is going on. I have a table with two columns, the first is PK, inserted 4 rows in it. Then I exported all rows, droped the table and created it again, filled with 3 rows, third one different than it was before. I loaded 4 rows and received a message that 3 rows were DELETED. According to this I expected '3', 'c' in the third row. But DB2 didn't delete rows.

    create table db2inst1.a( kol1 char(1) not null, kol2 char(2), primary key (kol1));
    insert into db2inst1.a(kol1, kol2 ) values( '1', 'a');
    insert into db2inst1.a(kol1, kol2 ) values( '2', 'b');
    insert into db2inst1.a(kol1, kol2 ) values( '3', 'c');
    insert into db2inst1.a(kol1, kol2 ) values( '4', 'd');
    export to db2inst1.a of ixf select * from db2inst1.a;
    drop table db2inst1.a;

    create table db2inst1.a( kol1 char(1) not null, kol2 char(2), primary key (kol1))
    insert into db2inst1.a(kol1, kol2 ) values( '1', 'a')
    insert into db2inst1.a(kol1, kol2 ) values( '2', 'b')
    insert into db2inst1.a(kol1, kol2 ) values( '3', 'X')
    db2 "load from db2inst1.a of ixf insert into db2inst1.a"

    Number of rows read = 4
    Number of rows skipped = 0
    Number of rows loaded = 4
    Number of rows rejected = 0
    Number of rows deleted = 3
    Number of rows committed = 4

    $ db2 "select * from db2inst1.a"

    KOL1 KOL2
    ---- ----
    1 a
    2 b
    3 X
    4 d

    4 record(s) selected.


    DB2 should write that 3 rows are rejected, not deleted?
    Do you share my opinion?

    Thank you in advance,
    NNicole

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    No.

    DB2 LOAD adds the rows to the table, whether they violate the constraints or not.

    It then goes through a "delete phase" where it removes duplicates (in the case of a primary key).

    Rejected means the row was never added, ie. there was something wrong with the format or a mismatch with the target column definition.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    With the load command, the indexes are not created until the end, so the duplicates cannot be checked until the rows are already added.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •