Databases
StackOverflow2013
Postsdbo
POAS400 DB2 Duplicate Key Error during Insert in Table with PK Identity Column
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAS400 DB2 Duplicate Key Error during Insert in Table with PK Identity Column
    primarykey
    data
    text
    <p>I got a Table with an Auto Increment Column which looks like: </p> <pre class="lang-sql prettyprint-override"><code>ALTER TABLE SOMESCHEMA.SOMETABLE ALTER COLUMN ID SET DATA TYPE INTEGER GENERATED BY DEFAULT SET INCREMENT BY 1 SET NO ORDER SET NO CYCLE SET MINVALUE 1 SET MAXVALUE 2147483647 SET NO CACHE; </code></pre> <p>As long as i let the DBMS generate the Ids everything works fine and I can get the generated Id via: </p> <pre class="lang-sql prettyprint-override"><code>SELECT IDENTITY_VAL_LOCAL() FROM sysibm.sysdummy1 </code></pre> <p>But sometimes i need to insert a row with an ID of my choice and there i get into trouble.</p> <p>Lets say we got a single row in the table with ID 1.<br>Now i insert a new row with a manually assigned id of 2.<br> The next time i try to insert a new row without a preset ID i get an error SQL0803 "DUPLICATE KEY".</p> <p>I assume the internal "NextId" field for that Auto-Increment Column doesnt update itself if the Id of a row is manually set.</p> <p>So I tried reseting this field with:</p> <pre class="lang-sql prettyprint-override"><code>ALTER TABLE SOMESCHEMA.SOMETABLE ALTER COLUMN ID RESTART WITH 3 </code></pre> <p>But this causes a permanent Table lock, which i dont know how to unlock.</p> <p>How can i get this "Mixed-Mode" ID-Column working? Is it possible to get it to work like MySQL where the DBMS automatically updates the "NextID" upon a manually-Id Insert? If not, how can I release that {insert swear-word here} lock that pops up if i try to reset the NextId?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USM C
    1. USM C
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAS400 DB2 Duplicate Key Error during Insert in Table with PK Identity Column
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't believe there is such thing as a "permanent table lock". Locks are released upon the transaction commit.
      singulars
      1. POAS400 DB2 Duplicate Key Error during Insert in Table with PK Identity Column
      1. USmustaccio
    2. COTill I stumbled upon this issue I thought so, too. I still get the following error after the ID reset: SQL0913 Zeile oder Objekt SOMETABLE der Art *FILE in Bibliothek SOMESCHEMA wird verwendet.
      singulars
      1. POAS400 DB2 Duplicate Key Error during Insert in Table with PK Identity Column
      1. USM C
    3. COUntil i manually kill the "Qzdasoinit" job via iSeries Navigator or CLI the table keeps locked indefinetly
      singulars
      1. POAS400 DB2 Duplicate Key Error during Insert in Table with PK Identity Column
      1. USM C
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload