473,890 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

identity-alteration RESTART option

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?

Apr 13 '06 #1
4 7306
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
Apr 13 '06 #2
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.

Apr 13 '06 #3
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
Apr 13 '06 #4
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...stori ng 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!

Apr 13 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4673
by: Edward | last post by:
SQL 7.0 I have a form in ASP.NET and I want to write the values to the SQL Server tables. The tables are Customer and Address tables. There will be an insert into the Customer table, and I need to use the Identity of this inserted record for the Foreign Key in the Address table insert.
5
8208
by: DBA | last post by:
I have an identity field on a table in SQL Server. The identity seed is 1 and the identity increment is 1. If I remove a record from this table, the identity sequence is broken. For example: Table contents prior to record delete: Fname(varchar), Lname (varchar), row_id (identity) -------------------------------------------------- Smith, Jane, 1 Smith, Tom, 2 Jones, Mark 3
5
9866
by: grzes | last post by:
MS SQL Server 2000. My case is: I have the table T with primary key calling __recid int without identity property. This table includes a lot of records (about 1000000). I need to convert __recid's data type to IDENTITY. As you know sql sentence: "alter table T alter column __recid int IDENTITY not null" does not work with not-empty tables. I use the SQL Enterprise Manager which can convert the field __recid into identity but I need...
5
4017
by: Eugene | last post by:
I have a table EugeneTest(id_num, fname, minit, lname) where field "id_num" is type IDENTITY and has UNIQUE constraint Let's say 2 user execute this code at the same time: DECLARE @return integer use EugeneTest INSERT employees ( fname, minit, lname) VALUES
4
41607
by: brent.ryan | last post by:
How do I get the next int value for a column before I do an insert in MY SQL Server 2000? I'm currently using Oracle sequence and doing something like: select seq.nextval from dual; Then I do my insert into 3 different table all using the same uniqueID. I can't use the @@identity function because my application uses a connection pool and it's not garanteed that a connection won't be used
8
9485
by: Razak | last post by:
Hi, I have a class which basically do Impersonation in my web application. From MS KB sample:- ++++++++++++++++++++code starts Dim impersonationContext As System.Security.Principal.WindowsImpersonationContext Dim currentWindowsIdentity As System.Security.Principal.WindowsIdentity
3
4518
by: Dan | last post by:
I'm writing a record from an asp.net page to SQL Server. After the insert I'm selecting @@identity to return the ID of the record that I just wrote. It worked fine until I typed a semicolon into one of the string fields to be inserted. The string fields are inside single quotes in the INSERT command. With the semicolon in the string, the record is written correctly including the semicolon, but the identity is not returned. Without a...
5
32063
by: Veeru71 | last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS IDENTITY), is there any way to get the last generated value by DB2 for the identity column? I can't use identity_val_local() as the INSERTS are happening in a different session. Eg, We have the following table....
3
2363
by: Rob | last post by:
Hi all, I have a bit of a complicated question, hope we have an SQL guru out there that can help us solve this killer problem. Due to the size of SQL Database we have (largest in the US), we try to pre-process large data files in IO until we are ready to insert directly into the database via BCP (quick, no constraints, no mess... well um that's the problem)
13
5815
by: PinkBishop | last post by:
I am using VS 2005 with a formview control trying to insert a record to my access db. The data is submitted to the main table no problem, but I need to carry the catID to the bridge table CatalogImage where imgID also needs to be placed. Below is my code behind to carry the catID using the Select @@Identity and insert imgID to the bridge table. No data is being entered into the bridge table.
0
9975
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9812
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11212
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10799
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8004
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7154
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5832
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4655
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3263
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.