Auto-incrementing columns are important when you need serial numbers or key fields for joins. In DB2 for versions 7 and later you can add the IDENTITY clause to the CREATE TABLE statement to create such a column.
To use the IDENTITY clause, enter the following:
CREATE TABLE tablename (colname SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH startnumber, INCREMENT BY incrementnumber)
This command generates one and only one identity column in a table. For version 8 you could use the IDENTITY clause with a database spanning more than one partition. Version 7 only allowed a single partition database to use this feature.
Starting with version 7.2 DB2 allows you to create a sequence database object. A sequence automatically generates values, but is not necessarily assigned to one particular column. Sequences are not bound to a table column or accessible through that column. As with identities, sequences are allowed in version 8 in multiple partition databases, and restricted to single partition database in version 6.
The CREATE SEQUENCE statement is exemplified by the following:
CREATE SEQUENCE sequencename START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24
About the Author
Barrie Sosinsky (barries@sosinsky-group) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.