IBM Power Ideas Portal


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 updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas

  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Add a new idea Subscribe
Status Future consideration
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Nov 3, 2016

RELATED IDEAS

DB2 Next Identity Value has incorrect value

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.


Use Case:

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)


Idea priority Medium
  • Guest
    Reply
    |     Apr 28, 2021

    IBM will use this request as input to planning but no commitment is made or implied. This request will be updated in the future if IBM implements it.

  • Guest
    Reply
    |     Nov 16, 2018

    This RFE is fine, but there could be a tiny change to get more out:
    Change the ALTER TABLE mySchema.myTable ALTER COLUMN myIdColumn RESTART WITH instead of using a numeric value;

    We can code around this issue with a RPG-Command and something like that, but its more efficient, if IBM deliver the correct way. And this "max-Thing" works, but it is not correct.

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.