By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select service_level from sysibmadm.env_inst_info;
| SERVICE_LEVEL |
|---|
| DB2 v11.1.4.4 |
CREATE TABLE test
( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,name VARCHAR(50)
);
INSERT INTO test(id, name)
VALUES
(1, 'Item 1'),
(2, 'Item 2'),
(3, 'Item 3');
select * from test;
| ID | NAME |
|---|---|
| 1 | Item 1 |
| 2 | Item 2 |
| 3 | Item 3 |
BEGIN
DECLARE max_id INT;
SET max_id = (SELECT NVL(MAX(id),0) FROM test); -- incase there is no row, NVL is added to return 0
EXECUTE IMMEDIATE 'ALTER TABLE test ALTER COLUMN id RESTART WITH ' || (max_id + 1);
END;
INSERT INTO test( name)
VALUES
( 'Item 4'),
( 'Item 5');
select * from test;
| ID | NAME |
|---|---|
| 1 | Item 1 |
| 2 | Item 2 |
| 3 | Item 3 |
| 4 | Item 4 |
| 5 | Item 5 |