This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).
Shape the future of IBM!
We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:
Search existing ideas
Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updateson them if they matter to you. If you can't find what you are looking for,
Post your ideas
Post an idea.
Get feedback from the IBM team and other customers to refine your idea.
Follow the idea through the IBM Ideas process.
Specific links you will want to bookmark for future use
The majority of new tables we create contain a record identity column defined as GENERATED BY DEFAULT, starting at 1, increment by 1. This column is also the primary key for the tables.
Most inserts into tables do not specify a value for the record identity column. This causes DB2 to use the stored next identity value as the record identity of the inserted record. After the insert DB2 increments the next identity value based on the rules defined for the column.
A simple example. New table is empty and next identity value is 1. Ten records are inserted into the table. Next identity value is now 11. Perfect. The next record inserted will have a primary key of 11. The next identity value is incremented to 12.
A special situation arises that requires records to be inserted and the record identity value to be provided. For example, records with primary keys of 13 and 14 are inserted into the table. The record identity of each record inserted is greater than the DB2 next identity value.
After the records 13 and 14 are inserted the next identity value is still 12, not the expected 15. (14 + 1).
All subsequent inserts which do not specify a record identity value will fail. DB2 uses 12 for the insert which is used in an existing record.
At this point an ALTER TABLE ALTER COLUMN statement must be used to correct DB2's next identity value. This can be problematic if the table in question is highly used since an exclusive lock is required. Compounding this problem the alter table statement requires a numeric literal as the next identity value. It's possible to miskey the new value which can lead to more problems.
DB2 should correctly update the next identity value when an insert provides a record identity value that is greater than the current next identity value.
DB2 should not allow a RESTART WITH value that conflicts with existing record values.
Normal record inserts work as expected.
Special record inserts with record identity values specified require off hours processing: 1. Allocate the table(s) 2. Insert records with specified record identity values 3. Retrieve list of DB2's next identity value for the table(s) 4. Execute ALTER TABLE table_name ALTER COLUMN record_id RESTART WITH correct_next_identity_value for each table 5. Deallocate the table(s)
Do not place IBM confidential, company confidential, or personal information into any field.