Let’s explore another of DB2’s advanced functionalities: the identity column. This is one of the most interesting and simultaneously least-used functionalities of DB2. Keep reading to find out how to use it.

Last time around, I talked about column-level encryption, a great DB2 functionality that allows you protect sensitive columns with a password by using just a couple of SQL statements.

The other functionality I’d like to talk about is so simple and has been around for so long that I consider it amazing that most shops don’t use it. One of the simplest pieces of business logic that can be embedded into your DB2 object definitions is automated key generation. Almost all applications have code that generates a key value for an item ID, an invoice, or a customer number and then inserts that value into a database table for storage. Why not just use DB2’s native functionality to generate that value as it inserts the row into the table?

Automated Key Generation Using Identity and Sequence

That is exactly what the Identity column attribute and sequence object can provide. Let DB2 handle the key generation and locking/serialization of that value, so you can concentrate on real business logic. Using native I/O, the relative record number (RRN) can be used to access exactly one selected record. SQL provides a scalar function RRN (file) to determine the relative record number; however, it is not possible to generate an index over the relative record number. To prevent SQL from reading the whole table to find the ID, you can add a column to hold the unique identifier and then build an index over this column. You have three possibilities at your disposal:

• Identity column attribute
• Sequence object
• ROWID data type

Identity Column

Let’s start with the Identity column attribute. This column’s type must be a numeric data type, like an integer or a decimal. Optionally, you can also specify values for the start, minimal, and maximal values, as well as an increment step. There are two ways to create an identity column:

• If you use the IBM i Navigator to create the table, select “Set as identity column” in the “New Column” window.
• If you use a script tool (such as STRSQL, Run SQL Scripts, or any of the other tools I mentioned in the “Tools of the Trade” subseries of “SQL 101,” which you can find here), just add a column with GENERATED ALWAYS AS IDENTITY after the data type. For instance, adding an identity column to the EMPLOYEE_MASTER table would be done something like this:

CREATE TABLE MYAPPDB/EMPLOYEE_MASTER

      (EMPLOYEE_ID INTEGER GENERATED ALWAYS AS IDENTITY

      , (some more columns here...))

The database engine will manage the value of each new record for you, so there’s no need to keep data areas with counters or any other solution. Cool, isn’t it? But wait, there’s more!

Sequence Objects

Here’s how the second item on the list, the sequence object, works: The sequence object allows automatic generation of values, just like the identity column. However, a sequence object is a global and standalone object that can be used by any tables in the same database, unlike an identity column attribute, which is bound to a specific table. Here’s how to create a sequence object named ORDER_SEQ:

CREATE SEQUENCE   ORDER_SEQ

      START WITH       10

      INCREMENT BY      10

When inserting a row, the sequence number must be determined through the NEXT VALUE FOR SEQUENCE instruction. For example, if I want to insert a row in the ORDER_HEADER table using a value from the sequence object, I just need to type this:

INSERT INTO       MYAPPDB/ORDER_HEADER (ORDNUM, CLINUM)

VALUES(NEXT VALUE FOR ORDER_SEQ, <client number goes here> )

Because the sequence is an independent object and not directly tied to a particular table or column, it can be used with multiple tables and columns (this can be both a blessing and a curse, so choose wisely which sequence you’re using for which table/column). Because of its independence from tables, a sequence can easily be changed through the SQL statement ALTER SEQUENCE. The ALTER SEQUENCE statement only generates or updates the sequence object, and it doesn’t change any data.

Neat, right?

Coming Up Next

Unfortunately, that’s all the time I have for now. Next time around, I’ll discuss ROWID, the third way to generate a unique key for a record. Until then, feel free to suggest other ways to automatically generate a record key or simply share your experience with the abovementioned methods.