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.

Mandalika

Published on 30-Nov-2020 09:28:50

• Related Questions & Answers
• Purpose and usage of SAVEPOINT in COBOL-DB2 program
• What is the usage and purpose of DCLGEN and host variables used in COBOL-DB2 program
• What is the purpose and usage of SQLCODE within the SQLCA in a COBOL-DB2 program
• Example and usage of JOINS in DB2
• What is the purpose and usage of ATOMIC and NON-ATOMIC clause in multi row insert?
• What is the purpose and usage of “FOR UPDATE OF” clause in a COBOL-DB2 program
• What is the purpose and usage of “WHERE CURRENT OF” clause in a COBOL-DB2 program?
• Usage and syntax of INNER and OUTER JOIN in DB2
• What is the use and syntax of SEQUENCE in DB2?
• Usage and example of Multi-index and Index-only access path in DB2
• What is the definition and usage of alternate key in a DB2 table?
• What is the purpose and usage of SCROLLABLE CURSOR in COBOLDB2 program?
• Purpose and usage of subqueries in DB with the help of an example
• How to create a DB2 table TAB1 with 4 columns, Student ID, Enrollment ID, Name and Age?
• Get MAX and MIN values along with their row id in MySQL?