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  lt auto increment  gt        sname varchar 30         PRIMARY KEY  sid             Any pointers are appreciated

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Added a few optional parameters for creating  future safe  sequences   CREATE SEQUENCE  lt NAME gt    START WITH 1   INCREMENT BY 1   NO MAXVALUE   NO CYCLE   CACHE 10

[db2] How to AUTO_INCREMENT in db2?

Examples related to db2

Examples related to auto-increment