Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15

    Unanswered: How to work with IDENTITY like MSSQL Server in DB2

    Hi All,

    When we work with MSSQL Server we have a concept IDENTITY that we assign to a field and then we will get unique value for that field using command SELECT @@IDENTITY.
    How we can do the same thing in DB2?

    Regards,

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are doing an insert, just use the term default or leave out the column value for the indentity column.

    Assume that table employee has a PK of empno which is an identity column:

    insert into employee (empno, name, birthdate) values (default, 'Bob Smith', '1979-08-14');

    or

    insert into employee (name, birthdate) values ('Bob Smith', '1979-08-14');

    If you want to know the value of the indentity value you just inserted, do this:

    select empno from final table (insert into employee (empno, name, birthdate) values (default, 'Bob Smith', '1979-08-14'));
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15
    Thanks for the solution

    I have to create table with IDENTITY
    create table testIdentity (
    userid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 100,INCREMENT BY 1 CACHE 24),
    username char(20));


    but how to add IDENTITY to existing table field using UPDATE?
    Last edited by nitingautam; 11-28-08 at 04:56.

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    You can try the following for an existing table:

    ALTER TABLE table-name ADD COLUMN xxx GENERATED ALWAYS AS IDENTITY START WITH .....

    Thanks,
    Jayanta

  5. #5
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15
    Quote Originally Posted by JAYANTA_DATTA
    You can try the following for an existing table:

    ALTER TABLE table-name ADD COLUMN xxx GENERATED ALWAYS AS IDENTITY START WITH .....

    Thanks,
    Jayanta
    Thanks for the command Jayanta..but I want to Alter an existing column.

    I tried following but not worked
    ALTER TABLE testIdentity ALTER COLUMN userid GENERATED BY DEFAULT AS IDENTITY START WITH 100 INCREMENT BY 1 ;
    Please provide command for that

  6. #6
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I see. You want to alter the existing field.

    You can try this:

    ALTER TABLE TEST.TB_DROPME ALTER COLUMN EMPID
    SET GENERATED ALWAYS AS IDENTITY ( START WITH 1, INCREMENT BY 1, NO CYCLE, CACHE 20 ) ;

    You need to ensure that in the existing table the field EMPID is NOT NULL and BIGINT.

    Thanks,
    Jayanta

Posting Permissions

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