Manually incremented keys

[Stan James]

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]

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]

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]

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]

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]

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]

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