Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    16

    Unanswered: Max Value Reached for Integer Column

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2009
    Posts
    14
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Here is the documentation from the CREATE TABLE statement, which states that DB2 will cycle:
    START WITH numeric-constant
    Specifies the first value for the identity column. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA). The default is MINVALUE for ascending sequences, and MAXVALUE for descending sequences. This value is not necessarily the value that would be cycled to after reaching the maximum or minimum value for the identity column. The START WITH clause can be used to start the generation of values outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •