How to AUTO_INCREMENT in db2?

Question

I thought this would be simple, but I can't seem to use AUTO_INCREMENT in my db2 database. I did some searching and people seem to be using "Generated by Default", but this doesn't work for me.

If it helps, here's the table I want to create with the sid being auto incremented.

  create table student(
      sid integer NOT NULL <auto increment?>
      sname varchar(30),
      PRIMARY KEY (sid)
      );

Any pointers are appreciated.

Answer 1

You will have to create an auto-increment field with the sequence object (this object generates a number sequence).

Use the following CREATE SEQUENCE syntax:

  CREATE SEQUENCE seq_person
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 10

The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

  INSERT INTO Persons (P_Id,FirstName,LastName)
  VALUES (seq_person.nextval,'Lars','Monsen')

The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

Answer 2

hi If you are still not able to make column as AUTO_INCREMENT while creating table. As a work around first create table that is:

create table student( sid integer NOT NULL sname varchar(30), PRIMARY KEY (sid) );

and then explicitly try to alter column bu using the following

alter table student alter column sid set GENERATED BY DEFAULT AS IDENTITY

Or

alter table student alter column sid set GENERATED BY DEFAULT AS IDENTITY (start with 100)

Answer 3

Added a few optional parameters for creating "future safe" sequences.

CREATE SEQUENCE <NAME>
  START WITH 1
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 10;

Answer 4

You're looking for is called an IDENTITY column:

create table student (
   sid integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
  ,sname varchar(30)
  ,PRIMARY KEY (sid)
);

A sequence is another option for doing this, but you need to determine which one is proper for your particular situation. Read this for more information comparing sequences to identity columns.