Hi All,


IBM DB2 UDB specification says that for an INTEGER data type the range of integer values is −2147483648 to +2147483647.
In my application I have a CREATE TABLE statement with its primary key as "GENERATED ALWAYS AS IDENTITY START WITH 1 .....".
Can someone please tell me what is typically done from a DBA perspective if the max value of the INTEGER data type is reached (as it will not allow further inserts and the application connected to this table might fail) ?

Thanks in Advance,
Pravas

The value in the identity column will flip over (from +2 147 483 647 to −2 147 483 648) and it will increment to 0, after that there is a possibility of PK constraint violation, as the identity column started from 1.

You could also use another data type like BIGINT with a range of -9 223 372 036 854 775 808 to +9 223 372 036 854 775 807.

I'm not working on a system that will generate more than 4.3 billion records in one table, so it's an academical question for me. You could use BIGINT to postpone this event from happening.

Thank you for the information. One more thing after re add the identity it seems to start with 1 again. But I still have data in it. do you know how to restart the identity to continue from the max value in the table

Here is the documentation from the CREATE TABLE statement, which states that DB2 will cycle: