Synching up IDENTITY columns
Comments (2) Visits (9743)
As you may know IDENTITY columns are special forms of SEQUENCEs which are tight to a specific column in a specific table.
The purpose of an IDENTITY column is to supply an abstract primary key for the table which generally is maintained by DB2.
You can now insert employees and let DB2 take care of producing employee idsCREATE TABLE emp
To retrieve the generated id you can either use the IDENINSERT INTO emp(name, salary) VALUES('Jones', 15043.21);
or, even better retrieve the value as you insert:VALUES IDEN
So far so good. DB2 will always remember what value it has produced last and generate the next value in the sequence.SELECT empid FROM NEW TABLE(INSERT INTO emp(name, salary) VALUES ('Smith', 21345.00));
There is a catch however. In some circumstances you may get collisions and that is when you either:
That however need not be true. For example you could could have a specific range for empids that you generated outside the database,
or perhaps you have a sequence that runs into the negative or cycles.
So DB2 cannot automatically synch identity values because, in essence it is not (yet) telepathic.
Nonetheless wouldn't it be nice if there at least would be a simple routine that would do the work for you and do what you expect?
The following procedure does just that.
When you call it after a LOAD or mass INSERT is concluded it will find the identity column, figure out what the maximum value is at this point and reset the identity to the next expected value.
Let's try it out by resetting the identity and allowing to override it, too:--#SET TERMINATOR @
We can add a big empid of our choosing:ALTER TABLE emp ALTER COLUMN empid DROP IDENTITY SET GENERATED BY DEFAULT AS IDENTITY;
But DB2 cannot generate be cause the empid 1 is already taken:SELECT empid FROM NEW TABLE(INSERT INTO emp(empid, name, salary) VALUES(1000, 'Fuller', 12300.00));
Synch the table up with the high water mark (1000):SELECT empid FROM NEW TABLE(INSERT INTO emp VALUES('Shoemaker', 31015.97));