Posted by: Serge Rielau

What seems like eons ago we introduced IDENTITY columns into DB2. This was in DB2 UDB V7.1 to be precise.
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.

For example:

CREATE TABLE emp
(empid INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(20),
  salary DECIMAL(10, 2));

You can now insert employees and let DB2 take care of producing employee ids

INSERT INTO emp(name, salary) VALUES('Jones', 15043.21);

To retrieve the generated id you can either use the IDENTITY_VAL_LOCAL() function

VALUES IDENTITY_VAL_LOCAL();
 
1
---------------------------------
                               1.
 
  1 record(s) selected.

or, even better retrieve the value as you insert:

SELECT empid FROM NEW TABLE(INSERT INTO emp(name, salary) VALUES ('Smith', 21345.00));
 
EMPID
-----------
          2
 
  1 record(s) selected.
 

So far so good. DB2 will always remember what value it has produced last and generate the next value in the sequence.
There is a catch however. In some circumstances you may get collisions and that is when you either:

• LOADed data into the table and inherited the identity columns from the data being loaded
• You defined or altered the IDENTITY column to be GENERATED BY DEFAULT and then choose to INSERT values directly.

Most times IDENTITY columns are ever increasing and users have an expectation that DB2 should just figure out that the next value to be inserted is the next value after what they have inserted or loaded themselves.
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.

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE SYNCIDENTITY
(IN schemaname VARCHAR(128),
 IN tablename VARCHAR(128))

BEGIN
  DECLARE sqlcode INTEGER;
  DECLARE maxid BIGINT;
  DECLARE idcolname VARCHAR(128);
  DECLARE stmttxt VARCHAR(1000);
  DECLARE s STATEMENT;
  DECLARE cur CURSOR FOR s;
 
  SELECT colname INTO idcolname
    FROM SYSCAT.COLUMNS
   WHERE tabname = tablename
     AND tabschema = schemaname
     AND identity = 'Y';
  IF SQLCODE = 100 THEN
    SIGNAL SQLSTATE '78000'
       SET MESSAGE_TEXT = 'can''t find identity column';
  END IF;
  SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
                schemaname || '"."' || tablename || '"';
  PREPARE s FROM stmttxt;
  SET maxid = 0;
  OPEN cur;
  FETCH cur INTO maxid;
  CLOSE cur;
  SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename ||
                '" ALTER COLUMN "' || idcolname ||
                '" RESTART WITH ' || CHAR(maxid + 1);
 
  EXECUTE IMMEDIATE stmttxt;
END
@
--#SET TERMINATOR ;

Let's try it out by resetting the identity and allowing to override it, too:

ALTER TABLE emp ALTER COLUMN empid DROP IDENTITY SET GENERATED BY DEFAULT AS IDENTITY;

We can add a big empid of our choosing:

SELECT empid FROM NEW TABLE(INSERT INTO emp(empid, name, salary) VALUES(1000, 'Fuller', 12300.00));

EMPID
-----------
       1000

  1 record(s) selected.

But DB2 cannot generate be cause the empid 1 is already taken:

SELECT empid FROM NEW TABLE(INSERT INTO emp VALUES('Shoemaker', 31015.97));
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign 
key update caused by a DELETE statement are not valid because the primary key, 
unique constraint or unique index identified by "1" constrains table "SERGE.EMP" 
from having duplicate values for the index key.
SQLSTATE=23505

Synch the table up with the high water mark (1000):

CALL SYNCIDENTITY(CURRENT SCHEMA, 'EMP');
Return Status = 0
 
SELECT empid FROM NEW TABLE(INSERT INTO emp VALUES('Shoemaker', 31015.97));
 
EMPID
-----------
       1001
 
  1 record(s) selected.

#Db2