![]() ![]() If WITH numeric-constant is not specified, the sequence for the identity column is restarted at the value that was specified, either implicitly or explicitly, as the starting value when the identity column was originally created. Resets the state of the sequence associated with the identity column. (I am unable to try as I dont hav access to any db2 database anymore). I don't remember if I tried this - but you can write an sp where max(id) is set in a variable and assign the variable in the alter.restart stmt. Hence I had to do a select max(id), get the value and replace it in the alter.restart stmt manually. As per DB2 documentation the value should be a 'numeric constant'. Unfortunately, there is no way to specify the next value automatically. Tim, I had faced the same issue where I needed to restart the identity to the next value. Isn't this whole thing as complicated as it seems to me? Could I somehow import data, preserving the IDs and have the auto-increment column still working as expected? Is there a way to find the next value for an auto-increment column, so I could write Statements that would check, if this value is less then the SELECT MAX and needs to be set? If I forget this, an Insert-Statement will fail with an duplicate PK error, since the auto-increment column is the primary key. ![]() When I now import data, the IDs are inserted correctly, but everytime I do this, I have to remember to set a new start for the auto-increment column by getting the highest Id+1 and alter the column like this: SELECT MAX(mycolumn)+ 1 FROM mytable ĪLTER TABLE mytable ALTER COLUMN mycolumn RESTART WITH This is necessary, sinceI need the IDs to be consistent, because other tables reference them. " command, I changed them to GENERATED BY DEFAULT. At first, all my columns were defined as GENERATED ALWAYS, but since I had trouble with this when using the "db2 import. I face some trouble with IBM DB2's auto-increment columns. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |