identity-alteration RESTART option

[shorti]

Can anyone explain in greater (and more comprehensive) detail what the
RESTART option does in the ALTER TABLE table ALTER COLUMN statement.

This is the description in Info Center:

RESTART or RESTART WITH numeric-constant
Resets the state of the sequence associated with the identity column.
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.
The column must exist in the specified table (SQLSTATE 42703), and must
already be defined with the IDENTITY attribute (SQLSTATE 42837).
RESTART does not change the original START WITH value.

The numeric-constant is an exact numeric constant that can be any
positive or negative value that could be assigned to this column
(SQLSTATE 42815), without non-zero digits existing to the right of the
decimal point (SQLSTATE 428FA). The numeric-constant will be used as
the next value for the column.

Really the only statement in this write up that explains what it is for
is the 'Resets the state of the sequence'.

I am curious if it has something to do with recalculating (reindexing)
the sequence numbers everytime??? the database is restarted?? or
something like that.

The reason I ask is I was looking around for an easy (automatic) way to
do this task without having to add code to update the column every so
often in order to avoid hitting the MAXVALUE on a column that is set to
NO CYCLE. Since my settings are such that I wont really have to worry
about the MAXVALUE for this table column for years...it would be nice
to not have to worry about it at all...and without having to kick
something off once a month to clean up.

So could setting the RESTART do something for this?

[Serge Rielau]

shorti wrote:

Can anyone explain in greater (and more comprehensive) detail what the
RESTART option does in the ALTER TABLE table ALTER COLUMN statement.

This is the description in Info Center:

RESTART or RESTART WITH numeric-constant
Resets the state of the sequence associated with the identity column.
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.
The column must exist in the specified table (SQLSTATE 42703), and must
already be defined with the IDENTITY attribute (SQLSTATE 42837).
RESTART does not change the original START WITH value.

The numeric-constant is an exact numeric constant that can be any
positive or negative value that could be assigned to this column
(SQLSTATE 42815), without non-zero digits existing to the right of the
decimal point (SQLSTATE 428FA). The numeric-constant will be used as
the next value for the column.

Really the only statement in this write up that explains what it is for
is the 'Resets the state of the sequence'.

I am curious if it has something to do with recalculating (reindexing)
the sequence numbers everytime??? the database is restarted?? or
something like that.

The reason I ask is I was looking around for an easy (automatic) way to
do this task without having to add code to update the column every so
often in order to avoid hitting the MAXVALUE on a column that is set to
NO CYCLE. Since my settings are such that I wont really have to worry
about the MAXVALUE for this table column for years...it would be nice
to not have to worry about it at all...and without having to kick
something off once a month to clean up.

So could setting the RESTART do something for this?

Err. If all you want to do is start at the beginning when you hit max
value then that is exactly what CYCLE does....

Anyway RESTART is a big rest button for the number generator.
DB2 at any given time, knows the next value it will generate for a
sequence/identity column. If you say RESTART DB2 will rest itself and
next time generated the START WITH values. If you say RESTART WITH it
will reset to generate the specified value next time you do NEXT VALUE
FOR <sequence> or you INSERT INTO the table.
DB2 does NOT update all existing rows in your table.
To force re-issuance of an identity value simply do:
UPDATE T SET id = DEFAULT
(possibly with a where clause).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[shorti]

I dont think I can use CYCLE. Lets say I set MAXVALUE to 10 for column
s_id. Over time I insert and delete records. By the way, s_id is also
a unique index because I can never have two of the same sequence
numbers. So now my s_id is fragmented and now the table looks like
this:

Name s_id
Tom 1
Sue 3
Jim 7
James 10

Now I cycle back to 1 but the record already exists with an s_id of 1
so I would think it would fail the insert. Do I have this wrong? will
it skip 1 and set the next record to s_id = 2 because 1 exists?

Back to RESTART. Is the 'reset' only triggered when you do something
like the UPDATE t SET id = DEFAULT ?

For instance, as I was testing the code I did two UPDATE t SET =
DEFAULT stmts...the first one started the sequence at 1 (START WITH 1)
and the second time it renumbered everything starting where it left off
with the sequence. So if I had the RESTART option included in the
ALTER COLUMN it would have renumbered everything starting with
1....correct ?

If so then, If I were to use 'UPDATE t SET id = DEFAULT' to
periodically reset the number then the RESTART option would have to be
implemented otherwise I would be defeating the purpose of doing the
UPDATE as I would be hitting my maxvalue sooner than I want to.

[Serge Rielau]

shorti wrote:

I dont think I can use CYCLE. Lets say I set MAXVALUE to 10 for column
s_id. Over time I insert and delete records. By the way, s_id is also
a unique index because I can never have two of the same sequence
numbers. So now my s_id is fragmented and now the table looks like
this:

Name s_id
Tom 1
Sue 3
Jim 7
James 10

Now I cycle back to 1 but the record already exists with an s_id of 1
so I would think it would fail the insert. Do I have this wrong? will
it skip 1 and set the next record to s_id = 2 because 1 exists?

Back to RESTART. Is the 'reset' only triggered when you do something
like the UPDATE t SET id = DEFAULT ?

For instance, as I was testing the code I did two UPDATE t SET =
DEFAULT stmts...the first one started the sequence at 1 (START WITH 1)
and the second time it renumbered everything starting where it left off
with the sequence. So if I had the RESTART option included in the
ALTER COLUMN it would have renumbered everything starting with
1....correct ?

If so then, If I were to use 'UPDATE t SET id = DEFAULT' to
periodically reset the number then the RESTART option would have to be
implemented otherwise I would be defeating the purpose of doing the
UPDATE as I would be hitting my maxvalue sooner than I want to.

If you wanted to "renumber" your identity values in regular intervals,
then yes, I would ALTER TABLE ALTER COLUMN RESTART followed by
UPDATE SET id = DEFAULT

Note btw, that DB2 Viper has a SET INTEGRITY option to perform identity
column generation. It's intended for roll-in of data though.

Anyway, the real interesting question is: What the heck ar eyou doing
that you need to do such a thing.
Either make you ID so big you'll never have to worry about it BIGINT or
DEC(31,0) if you are afraid of liability of your descendants ;-)
Or don't use identity to begin with and use a natural key.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[shorti]

hehe...a very good question...one I asked the requestor myself! Long
story but we have to support some old code that requires this info and
it cannot accept a value larger than the int32.

I was trying to convince them to just use a nice, simple
counter...storing the value in a table...but I guess there are other
reasons to keep the sequencing.

Besides, where's the challenge in a counter =)

Thanks for the help again!