The following command does NOT work in db2 v7.1 for ZOS.
ALTER TABLE <table> ALTER COLUMN <id col> RESTART WITH <nextval>"
Thanks & Regards,
Divakar
-----Original Message-----
From: Day Daniel [mailto:Daniel.[Email address protected]
Sent: Friday, January 20, 2006 6:09 PM
To: Divakar Goel (WT01 - Technology,Media,Transportation&Services)
Subject: RE: Gap in IDENTITY Column values after LOAD
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Don't know a great deal about identity columns (aren't sequences a
better
solution?), but there's an article titled:
"Using the IDENTITY column following a load or import of data into the
table"
at:
http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=%22iden
tity
+column%22&uid=swg21007891&loc=en_US&cs=utf-8&lang=en
This article talks about using the following SQL to specify a new
starting
number:
ALTER TABLE <table> ALTER COLUMN <id col> RESTART WITH <nextval>
I believe this command works for DB2 for z/OS and LUW.
.............Dan
-----Original Message-----
From: divakar
[Email address protected]
Sent: 20 January 2006 12:03
To: LazyDBA Discussion
Subject: Gap in IDENTITY Column values after LOAD
This is the first time that I am using the IDENTITY Column in my table.
We have defined Column as GENERATED ALWAYS and also have a unique index
defined on it.
We are populating the table through LOAD utility. Of course while
LOADing we are NOT providing values for the IDENTITY Column. Till this
point its fine. We had given START WITH 1 and INCREMENT with 1 and
values were inserted fine. The problem started when I manually inserted
a row into the table. The value generated by DB2 was not the immediate
successor of the maximum value present in that column. There was a huge
gap in it. Say if the last value was 25, DB2 generated 1001 this time.
The successive inserts yielded 1002, 1003...
Can someone explain this behavior and what I can do if I want continuous
values.
Is there is a possibility of a gap even now when I am inserting
manually.
We are on db2 v7.1 for z/os
Thanks & Regards,
Divakar
The information contained in this electronic message and any attachments
to
this message are intended for the exclusive use of the addressee(s) and
may
contain proprietary, confidential or privileged information. If you are
not
the intended recipient, you should not disseminate, distribute or copy
this
e-mail. Please notify the sender immediately and destroy all copies of
this
message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient
should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus
transmitted
by this email.
www.wipro.com
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
******************************************************
The information in this E-mail and in any attachment is
confidential and is intended solely for the addressee.
Access, copying, disclosure or use of such information
by anyone else is unauthorised. If you are not the
intended recipient please contact [Email address protected]
While reasonable efforts are made to ensure these files are free
of virus infection and offensive materials, if something of this
nature is inadvertently sent to you, please destroy it, accept
our apologies and contact [Email address protected] with
details of the sender. We will ensure that action is taken
immediately to prevent any recurrence. Debenhams accept no
responsibility for any views expressed by the originator of this email.
Debenhams Retail plc (reg. no. 83395) Registered in England and Wales.
Registered office: 1 Welbeck Street, London W1G 0AA.
http://www.debenhams.com
******************************************************
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
DB2 & UDB email list listserv db2-l LazyDBA home page