Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9

    Unanswered: cannot insert after loading with load utility

    After loading a table with LOAD utility I can't insert new rows - I get duplicate key errors. I have tried with only a 10 row test case, so I know the duplicate key error is false, and also that it's not a size issue. Queries work fine.

    Using DB2 8.1. Have same problem on windows and linux.

    I suspect it has to do with the table being left in BACKUP PENDING or something like that, but I can't find an indication of that, and haven't found anything to do to make the table usable again.

    I want to load with LOAD rather than IMPORT because it's very fast and also allows me to override the generated identity column.

    The table def:
    ID integer not null primary key generated always as identity,
    POSTAL_CODE varchar(20) not null,
    COUNTRY_CODE varchar(5) not null,
    unique(POSTAL_CODE, COUNTRY_CODE)

    The load command:
    LOAD FROM "C:\NLS\data\normalized.postal_codes"
    OF DEL MODIFIED BY CHARDEL% COLDEL|
    IDENTITYOVERRIDE METHOD P (1, 2, 3) MESSAGES "C:\NLS\logs\LoadPostalTables.log"
    REPLACE
    INTO NLS4.NLS_POSTAL_CODES (ID, POSTAL_CODE, COUNTRY_CODE)
    STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL COPY NO INDEXING MODE AUTOSELECT

    Thanks for any advice.

    Bill

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you override the indentity key definition in the load, don't you need to skip those numbers when you insert data? Try finding the highest indentity column value after the load, and then alter the table to start new inserted indentity values after that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9
    Quote Originally Posted by Marcus_A
    If you override the indentity key definition in the load, don't you need to skip those numbers when you insert data? Try finding the highest indentity column value after the load, and then alter the table to start new inserted indentity values after that.
    It's a good idea, though I would have thought with all the clever functions in LOAD, it would have updated the generated column counters. Anyway, I don't see right off where to change the next ID number. I opened ALTER for the table, then CHANGE for the ID column, but all fields except COMMENT are grayed out. GENERATE is checked, IDENTITY is selected, and initial value and increment are both set to 0.

    Bill

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't believe that the LOAD will update the identity column if you tell it to override the identity column assignment. You will need to alter the table to alter the column with a "RESTART WITH numeric-constant" that specifies a number higher than already exists in the table.

    I don't know if the Control Center will handle this. If not, you will have to code the SQL by hand to do this.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    Run a COPY utility to remove the BACKUP pending.

  6. #6
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9
    Quote Originally Posted by Marcus_A
    I don't believe that the LOAD will update the identity column if you tell it to override the identity column assignment. You will need to alter the table to alter the column with a "RESTART WITH numeric-constant" that specifies a number higher than already exists in the table.

    I don't know if the Control Center will handle this. If not, you will have to code the SQL by hand to do this.
    urquel: It's not that. I thought so at first, it seems the one people most often have stumbled on. But the db is not in backup pending, and backing it up does not solve the problem. But thanks anyway.

    Marcus: I guess youre idea was right. I changed the column to "generate by default" (it's not really what I wanted tho...) and an insert that provides no key gets a duplicate key exception, but one that provides a key not in the existing range works. So I guess it's just that. I will see if I can reset the counter as you suggest above for "generated always", if not, I'll use "generate by default" and move on.

    thanks again
    Bill

  7. #7
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9
    end of story: the "restart with" is a total solution, even works with generate always. So thanks Marcus, you fixed it.

  8. #8
    Join Date
    Nov 2004
    Posts
    374
    if the tablespace is in a different status : execute :
    db2 list tablespaces show detail : this should indicate the status code and message for each tablespace.
    If backup pending : maybe execute a load with nonrecoverable parameter
    Hope this will help
    Best Regards, Guy Przytula

Posting Permissions

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