Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: temp tables - all rows get removed on duplicate insert

    DB2 7.2

    Could anyone out there tell my how to stop DB2 removing all the rows from my temporary table when the unique constraint gets violated.

    table:
    DECLARE GLOBAL TEMPORARY table tt (a VARGRAPHIC(10),b VARGRAPHIC(20) ) ON COMMIT PRESERVE ROWS NOT LOGGED
    index:
    CREATE unique index SESSION.xtt on SESSION.tt(a)

    inserts:
    insert into SESSION.tt (a, b) values ('xxx', 'xxx')
    insert into SESSION.tt (a, b) values ('yyy', 'yyy')

    count:
    select count(*) from SESSION.tt
    - result is 2 - GOOD!

    another insert (get duplicate error as expected):
    insert into SESSION.tt (a, b) values ('xxx', 'xxx')
    - gets [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "SESSION.TT" from having duplicate rows for those columns. SQLSTATE=23505

    do count again:
    select count(*) from SESSION.tt
    - result is 0 - BAD!!!

    I would expect the count to still be 2.

    I tried tagging on "ON ROLLBACK PRESERVE ROWS" to the table declaration but it made no difference.

    thanks for any help on this.

    Andy

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have you considered upgrading to a supported version of DB2? (V7 is out of services for nearly 3 years now.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Well if I can resolve this issue I might...

  4. #4
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Actually its version 8.1.

    My diag tool was sort of hard coded...its a long story...

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This does look like a bug (or an error in the manual); I would open a PMR.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think I have seen this behavior also. I think it has to do with getting an error that clears the GTT. Maybe you should open a PMR with IBM.

    Andy

  7. #7
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Not bad - seeing as though I spotted this 'bug' in 2004!
    Re-addressing it due to performance problems. (my work around is to first select from the TT using the insert values to make sure that I don't get a duplicate before actually doing the insert).

    Ohh, well - if thats what you experts say then so be it.

    Andy

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A negative return code will perform a rollback. I am not sure it is a bug. You might be able to trap the error with a condition handler.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by andrewhallam
    Not bad - seeing as though I spotted this 'bug' in 2004!
    Re-addressing it due to performance problems. (my work around is to first select from the TT using the insert values to make sure that I don't get a duplicate before actually doing the insert).

    Ohh, well - if thats what you experts say then so be it.

    Andy
    Instead of doing a select then a conditional insert, use the MERGE statement.

    Andy

  10. #10
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Quote Originally Posted by Marcus_A
    A negative return code will perform a rollback. I am not sure it is a bug. You might be able to trap the error with a condition handler.
    If a rollback is performed then surely adding "ON ROLLBACK PRESERVE ROWS" to the table declaration would stop the previous rows from being removed? - as the wording suggests?

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    it will keep the rows that were comitted, but any work done between a prior commit and current rollback will be rolled back. This is not a bug it is well documented and the correct behavior.
    Dave

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The thing is that even the previously committed rows are gone after the error. Besides, I don't believe there is an implicit rollback upon the error.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Besides, the clause ON ROLLBACK PRESERVE ROWS implies that no rows are deleted upon rollback either. In that, temp tables deviate from regular base tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Quote Originally Posted by dav1mo
    it will keep the rows that were comitted, but any work done between a prior commit and current rollback will be rolled back. This is not a bug it is well documented and the correct behavior.
    Dave
    Just tested this and you are wrong - the previous comitted rows are also deleted.

    Looks like I'll have to raise a PMR with IBM.

    Thanks to all who contributed to my original question.

    Andy

Posting Permissions

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