Does a DB2 identity column needs an extra index?

Question

I have a DB2 table with an id column that has an auto increment, like this:

CREATE TABLE Employee
  ( id        DECIMAL(19, 0) NOT NULL GENERATED BY DEFAULT AS IDENTITY
  , name      VARCHAR(50) NOT NULL
    ...
  )
;

Does it make sense to add a dedicated index on this column to ensure uniqueness in any case and/or to improve performance on lookups and joins? Or is an index implicitly added?

CREATE UNIQUE INDEX I1Employee
    ON Employee
     ( id
     )
;

I have always worked without an index in comparable scenarios (and never ran into troubles), but a colleague always creates one. What's the best practice here?

Answer 1

An identity column and an index are not the one or the other - they are two different functionalitites for different goals. This means yes a index will (most likely) be beneficial. If you define a primary key on the table the unique index will be implicitly created. Just having an identity column does not implicitly generate an index.