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,

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'));

Thanks for the solution

I have to create table with IDENTITY


but how to add IDENTITY to existing table field using UPDATE?

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
Please provide command for that

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