Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Manually incremented keys

 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I did a forum search on incrementing keys and found a lot of references to auto-increment columns. But I'm just given a database with an int key; I guess I have to increment it myself. As usual, I need to know the new key to update the next table. Surely there's a common way of doing this?
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmmm, just heard back from the database designer. They recommend random with dup detection. I did that in the 80s when the only DB2 doc we had was "the Date book" 1st ed.
 
Jesper de Jong
Java Cowboy
Saloon Keeper
Posts: 15482
43
Android IntelliJ IDE Java Scala Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What brand of database are you using?

Oracle has sequences. You perform an SQL statement like "SELECT MYSEQUENCE.NEXTVAL FROM DUAL" to get the next number in the sequence and increment the sequence in one atomic operation. MySQL has auto-increment columns. Other databases have other ways to do this. As far as I know there is not one common, standard mechanism to do this.

The problem with generating keys is that getting the value and incrementing it has to be done as an atomic operation (if there are multiple threads or processes doing updates at the same time), so that no duplicate keys are generated.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm stuck with what the DA will give me, which is an int field, period. As noted above he recommended random with dup detection, and sent me some code from another project that tries up to 100 times to generate a new unique key. They needed it for a 2 digit int key once.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In DB2 6.1 and above you should be able to use the generate_unique function.

INSERT INTO table VALUES (GENERATE_UNIQUE(),'000020','Update entry 1...');
INSERT INTO table VALUES (GENERATE_UNIQUE(),'000020','Update entry 2...');

Also, regarding the Oracle sequence value advice. Your code only is syntactically correct, but it is only valid, if a sequence is available to you in your schema.

A little extra FYI on sequences, all RDBMS systems I have been exposed to (Oracle, PostgreSQL, DB2, MySQL, etc.) all support sequences. I'm not sure why they have chosen not to include one in this database, but the function will resolve the issue for you and will always return a unique key.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for Generate_Unique(). I'll read up on it. If I can get the value back after the insert I'll be golden.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can find a wealth of information in IBM's online manuals and white papers. here is the link to generate_unique function: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000809.htm
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic