Does DB2 have an equivalent to MS SQL SERVER's IDENTITY data type ?

For example in SEQUEL you can do this :

create table x
(
a int IDENTITY (1,1) NOT NULL,
b nvarchar(10)
)

This means that when inserting into table 'x' you do not specify a value for x.a - it is automatically generated by sequel - you can then retreive this value using 'SELECT @@IDENTITY'

thanks for any help on this.

Andy.

It is called by the same name ... It can be defined as GENERATED BY DEFAULT or GENERATED ALWAYS ...

There is another object called Sequences in DB2 which is helpful in generating unique values ...

This article should explain
http://www-106.ibm.com/developerwork...2fielding.html

Believe it or not, DB2 has another feature like MS SQL Server--manuals! You can down load the manuals in PDF format from the IBM site. Links are available in Useful DB2 Stuff thread.

Sorry for not replying sooner - other projects...

Yeah, I know about DB2's IDENTITY data type.
The thing I need to know about though is retreival.

SQL SERVER populates its @@IDENTITY value with the last identity value generated in any table in the current session. This guarantees that you will only get back the IDENTITY value that "you" created and nobody else's.

My question seems to be a very popular one as it appears on various forums but as yet I have not found any answers.

Thanks for any help on this.

Andy.

Hi

DB2 has funtion IDENTITY_VAL_LOCAL() and it's doing exatly the same thing.

(However, as I'm a OS/390 man - I cannot be 100% sure if it will on UDB)

"...SQL SERVER populates its @@IDENTITY value with the last identity value generated in any table in the current session. This guarantees that you will only get back the IDENTITY value that "you" created and nobody else's..."

Cheers, Bill