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'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.

Answer 2

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".