title: How to obtain a DB2 Sequence Value in a Multithreaded Application
body: <p>I am working on a multithreaded application that uses DB2 for its primary database.  In the past we've mostly used Identity columns for tables where we needed an auto-generated unique identifier.  To do that we would run the below 2 queries in the same transaction:</p>
  
  <pre><code>INSERT INTO tbname (IDENTITY_COL, ...) VALUES (DEFAULT, ...);
  SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;
  </code></pre>
  
  <p>We are now being pressured to switch to Sequence instead.  I know you can use "NEXT VALUE FOR <em>colname</em>" in both INSERT and SELECT statements, but I can't figure out how to both INSERT and SELECT with the same value without risking a race condition in a multithreaded application.  For example, if I use:</p>
  
  <pre><code>INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR SEQUENCE_COL, ...);
  SELECT PREVIOUS VALUE FOR SEQUENCE_COL;
  </code></pre>
  
  <p>Then there's a possibility another INSERT was run between the above INSERT and SELECT, hence providing me the incorrect value.  If I try:</p>
  
  <pre><code>SELECT NEXT VALUE FOR SEQUENCE_COL;
  </code></pre>
  
  <p>store the value in a variable and pass that in to the INSERT:</p>
  
  <pre><code>INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (variable_value, ...);
  </code></pre>
  
  <p>Then there's a possibility another thread got the same NEXT VALUE and tries to insert the same value, resulting in a DB2 -803 error.  Is it possible to use SEQUENCE columns in a multithreaded environment, or do I need to fight to keep my IDENTITY columns?</p>