DB2 and automated PK genearation..

[Saniya Ansari]

Hi..
I'm using DB2 for a project.. I want to be able to genearate an automated PK which can just be a serial number like 1, 2,3.....
So I want to know that how can i do that with DB2
Secondly, When i insert I want to know what was the PK that was genearted.. so how can i do that.. like i'll execute the insert statement for example with a persons details.. a record is inserted and a pk is generated.. so how do i get that pk value..

Thanks..

[Jeanne Boyarsky]

You can use an identity column.

  CREATE TABLE NAME
(KEY            INTEGER GENERATED ALWAYS AS IDENTITY,
 OTHER_FIELDS  INTEGER)

Candle has a short example. (search for "identity" in the list of features.)

[Dana Hanna]

Note that this feature became available with DB2 version 7. DB2 5.2 and 6.1 have no method of automated key generation.

[Saniya Ansari]

Thanks you guys.. one more part that i'm trying to figure out is that how do i get that key that was generated back.. bcoz usually in select we'll give the primary key and select a record.. i can i suppose make a query and put the rest of the fields for example name, address, phone, email to match with the record i just inserted and get its id but this sounds like a less efficient method.. are there any other ways someone can suggest...
the question is agand if i'm using an automated pk generated by db, after insert how do i get this pk back right after i have inserted the record...
Thankyou..