Purpose and usage of ROW-ID and SEQUENCE in a DB2

Problem: How can you implement a logic to automatically generate a unique value in a DB2 column for every new row inserted?

Solution

We can implement a logic in a DB2 table through which we can have one column which will have an automatically generated value for every new row inserted. This column can serve as a primary key and hence it is very useful for the random access of the DB2 table. This logic can be implemented via ROW-ID and SEQUENCE.

Any one column of the DB2 table can be defined as type ROW-ID following which DB2 will automatically assign a new ID to an inserted row. The assigned ID remains unique throughout the table. A SEQUENCE can also be used to achieve this. In case of SEQUENCE the ID assigned by DB2 to a row is UNIQUE throughout the database and not just the single table.

Examples

ROW-ID

If we declare the ORDER_ID column of the ORDERS DB2 table as ROW-ID then DB2 will automatically generate a unique value for the ORDER_ID column based on the defined criteria for each new row inserted in this table. The ROW-ID defined column will unique value throughout the table.

SEQUENCE

If we have a DB2 database which contains 4 tables. Each table stores the order details from the North, East, West, South region respectively. The ORDER_ID column of each table can be marked as SEQUENCE. The DB2 will assign a unique value for the ORDER_ID column for every new row inserted in these tables and the assigned value will remain unique throughout the database.