Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: IDENTITY funtionality

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: IDENTITY funtionality

    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Re: IDENTITY funtionality

    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.

  5. #5
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: IDENTITY funtionality

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •