Problem solve Get help with specific problems with your technologies, process and projects.

Creating serial number columns

Auto-incrementing columns are important when you need serial numbers or key fields for joins. Here's how in DB2.

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.

Dig Deeper on iSeries programming commands

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close