Systems Engineering and RDBMS

Re-set identity values in DB2 LUW

Posted by decipherinfosys on August 8, 2007

In some of our previous posts, we had looked at how to re-set the sequence values in Oracle and how to re-seed the identity values for a column in SQL Server. DB2 LUW has the best of both the worlds – it has the flexibility of using either the identity property and a sequence to auto generate column IDs. It is the DBA/Developer’s choice what he/she wants to use. Here we will talk about resetting the identity value when identity property is used to auto generate column IDs. First create table and populate it with some data.

CREATE TABLE Test
(
Test_ID INT NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH +1, INCREMENT BY +1, CACHE 1000),
Test_DATE TIMESTAMP,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
— TABLESPACE Clause
;

INSERT INTO Test(Test_Date) VALUES(CURRENT TIMESTAMP);
INSERT INTO Test(Test_Date) VALUES(CURRENT TIMESTAMP);

SELECT TEST_ID FROM TEST will produce following output.
TEST_ID
——-
1
2

In DB2 truncating table will not reset the identity value of the table back to its initial value like it did in SQL Server. Following command will truncate the table in DB2. It is followed by an insert and select statements to check what will be the outcome of the select after inserting new records.

ALTER TABLE TEST ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
INSERT INTO Test(Test_Date) VALUES(CURRENT TIMESTAMP);
INSERT INTO Test(Test_Date) VALUES(CURRENT TIMESTAMP);

SELECT TEST_ID FROM Test;

You will observe that new identity values are 3 and 4 instead of 1 and 2 even after truncating the table. In order to reset the identity value we have to alter the column after deleting the data.

DELETE FROM TEST;
ALTER TABLE TEST ALTER TEST_ID RESTART WITH 1;

Now if you insert new records, identity value will start with 1.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: