MVSFORUMS.com

pradeepg1 · Posted: Tue Jan 21, 2003 2:52 pm Post subject: Auto Genereate key in a DB2 table

Hi,
Can one of you suggest me a good reference manual on the topic
'Auto generated key' in DB2.
Thanks,
Pradeep

kolusu · Posted: Tue Jan 21, 2003 3:12 pm Post subject:

Pradeep,

Add a column with the data type ROWID or an identity column. ROWID columns and identity columns contain a unique value for each row in the table. You can define the column as GENERATED ALWAYS, which means that you cannot insert values into the column, or GENERATED BY DEFAULT, which means that DB2 generates a value if you do not specify one. If you define the ROWID or identity column as GENERATED BY DEFAULT, you need to define a unique index that includes only that column to guarantee uniqueness.

Inserting data into an identity column using AUTONUM

Inserting data into a ROWID column using AUTONUM

Hope this helps...

cheers

kolusu

pradeepg1 · Posted: Tue Jan 21, 2003 4:45 pm Post subject:

Thanks Kolusu. For now the links provided by you have gave me an idea of ROWID and Identity column. But if have a primary key and index built on that primary key then we are going to get the same feature of fast retreival of records. Is there any specific use of this ROWID and Identity column and in what scenario this is more helpful. More over it is an additional burden on application programmer if we define as GENERATE DEFAULT, because programmer needs to insert the values into Identity column when ever he inserts a new row into table(ofcourse system defaulats if we don't insert any value).

kolusu · Posted: Tue Jan 21, 2003 6:34 pm Post subject:

Pradeep,

Rowid was once upon a time a feature of ORACLE.with DB2v6 onwards IBM also introduced the ROWID feature.ROWID specifies where the row lives in a table. It is allocated once for each row and remains in effect for the lifetime of the row. Hence one can use the ROWID in a query to reference a row (until the row is deleted). The ROWID is unique for each row, like a primary key. However, the ROWID remains constant over the lifetime of the row. One may update the value of a primary key attribute, but the same ROWID will apply to that row in the table.

Using the ROWID, you can access a particular row without needing to access an index or scan the table space. Usually a ROWID identifies a row in the table that has the ROWID defined.

Hope this helps...

cheers

kolusu

pradeepg1 · Posted: Wed Jan 22, 2003 9:40 am Post subject:

Thanks a lot for throwing light on this topic. May be once I start using this concept in real time environment then I will experiene the actual benefit of this feature against Primary key feature.