How can i refresh my primary key column?

Question

Till recent time i was using hibernate @Entity annotation to map to database tables.All the primary keys are annotated with @GeneratedValue(strategy = GenerationType.IDENTITY)

I got a scenario where i need to create new schema + migrate data from old schema into new schema.(with few column changes like drop, length and type)

After successful migration of data to new schema tables when i try to insert data using Application its throwing an exception

[ERROR] util.JDBCExceptionReporter DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1; _NewSchema_._TableName_ , DRIVER=3.51.90

I believe that application is trying to insert rows again with Primary key value starting from 1 because same application is working fine with empty tables.

I want data rows to be inserted with its primary key value as highest value of existing rows primary key .

Any help will be thank full :)

Answer 1

Your guess is correct, here is my solution, execute the following SQL to give the ID column a specified start position, then your application will work fine.

alter table TABLE_NAME alter column ID set GENERATED BY DEFAULT RESTART WITH 10000;

Hope to help you :)