Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008

    Unanswered: Threaded Merge Statements

    Hey Guys I was wondering if someone could help me with this. I have a table with a primary key (that auto increments) and two more columns as a unique index. Basically I merge data into it to insert new records and do nothing if they already exist (to preserve uniqueness).

    I run these queries in a multithreaded environment, and sometimes the merge statements are executed simultaneously, and both try to commit a unique row, where one of them fails to insert a row because of the uniqueness constraint on the second two columns of the table.

    If I try to set the table type to be "table" locked instead of "row" locked, the merges time out sometimes.

    Here is the query I run:

    MERGE INTO table AS p
    	   USING ( VALUES (:val1, :val2) ) AS v(val1, val2)
    	   ON ( p.val1= v.val1 AND p.val2= v.val2)
    		  INSERT (val1,val2) VALUES (v.val1, v.val2)
    If you know a way to properly execute this in a threaded environment please let me know.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    What's your DB2 version on which platform?
    Which error messages do you get exactly (for both cases, i.e. lock timeout vs. duplicates)?
    Do you use auto-commit or not?
    Which isolation level is used?
    Have you any other special settings (like DB2_SKIP_INSERTED) in the DB2 registry or elsewhere?

    Basically, with the highest isolation level you shouldn't have any problems running those statements concurrently over multiple DB2 connections. But given that we do not yet know the above requested details, we cannot give you a more detailed explanation why you see errors.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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