Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Unanswered: problem restoring db2 db with identity columns

    I've tried restoring a database created with a number of tables with identity columns, and I've discovered that inserting new records in the restored database can fail due to the database losing track of the expected value for the next insertion.
    Is there a setting for either backup, restore or table creation that I'm missing that would keep track of the identity value?

    I am running DB2 v9 on Linux.
    The table is created here:

    CREATE TABLE "DB2INST1"."TABLE1" (
    "TRANS_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
    START WITH +100
    INCREMENT BY +1
    MINVALUE +1
    MAXVALUE +2147483647
    NO CYCLE
    CACHE 300
    NO ORDER ) ,
    "DATA1" INTEGER NOT NULL ,
    "DATA2" INTEGER )
    IN "USERSPACE1" ;

    Thanks in advance
    -Andy Beaton

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by abeaton
    fail due to the database losing track of the expected value for the next insertion.
    I would be interested to learn how you came to that conclusion.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2007
    Posts
    12

    Re: problem restoring db2 db with identity columns

    The first instance occurred on a recently restored production database where insertions were failing until I reset the identity count with a "db2 alter table tabname alter column colname restart with 10000" command.
    I was able to reproduce the losing count problem on a small sample database, the ddl for which I posted above.
    -Andy

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Generation of identity values is a logged operation, and it is guaranteed to be logged _before_ any of the identity values are used by subsequent SQL statements. If you restore the database to a consistent state identity values should be correct.

    Would you mind posting the entire sequence of commands/statements that you use to reproduce the error?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2007
    Posts
    12
    1) check the table as it stands:
    db2inst1@perseus:~$ db2 "select * from table1"
    TRANS_ID DATA1 DATA2
    ----------- ----------- -----------
    1 1 1
    2 2 2

    2 record(s) selected.

    2) backup the database
    db2inst1@perseus:~$ db2 backup db test3
    3) drop the database
    db2inst1@perseus:~$ db2 drop db test3
    4) restore the database
    db2inst1@perseus:~$ db2 restore db test3
    5) insert 2 new records
    db2inst1@perseus:~$ db2 "insert into table1 values (default,3,3)"
    db2inst1@perseus:~$ db2 "insert into table1 values (default,4,4)"
    6) select the data
    TRANS_ID DATA1 DATA2
    ----------- ----------- -----------
    1 1 1
    2 2 2
    301 3 3
    302 4 4


    It's looking to me like the count is leaping ahead by ~ the cache value, but I would like to override that without losing the performance benefits.
    -Andy Beaton

  6. #6
    Join Date
    Jun 2007
    Posts
    12

    Re: problem restoring db2 db with identity columns

    One more bit of data; the count can be thrown off with a db2stop/start as well:


    db2inst1@perseus:~$ db2stop
    db2inst1@perseus:~$ db2start
    db2inst1@perseus:~$ db2 connect to test3
    db2inst1@perseus:~$ db2 "insert into table1 values (default,5,5)"
    db2inst1@perseus:~$ db2 "insert into table1 values (default,6,6)"
    db2inst1@perseus:~$ db2 "select * from table1"

    TRANS_ID DATA1 DATA2
    ----------- ----------- -----------
    1 1 1
    2 2 2
    301 3 3
    302 4 4
    601 5 5
    602 6 6

    6 record(s) selected.


    btw, I missed a step earlier:
    db2 alter table table1 alter column trans_id restart with 1
    right at the start to reset the counter to 1.
    -Andy Beaton

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This is exactly what should happen, given your identity cache value. And it has nothing to do with the backup - you will see the same behaviour if you deactivate and reactivate the database.

    Once you allocate a cacheful of identity values, the last allocated value (e.g. 300) will be logged, and when another connection requests an identity value it will start with the next higher value, 301 in this example. If you deactivate the database without using up all cached values they are lost, while the last logged value is not.

    You will have to choose what is more important to you: consecutive identity values or performance.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Just use BIGINT as data type and forget about the gaps. Unless you go to extreme measures, you will always have to deal with gaps, for example if you roll back a transaction that consumed an identity value.
    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
  •