- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Articles
- Purpose and usage of SAVEPOINT in COBOL-DB2 program
- What is the purpose and usage of “WHERE CURRENT OF” clause in a COBOL-DB2 program?
- What is the purpose and usage of “FOR UPDATE OF” clause in a 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
- What is the purpose and usage of ATOMIC and NON-ATOMIC clause in multi row insert?
- Example and usage of JOINS in DB2
- 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?
