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

I would be interested to learn how you came to that conclusion.

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

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?

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

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

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.

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.