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=%22identity
+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
[mailto:db2udbdba-ezmlmshield-x24034576.[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
******************************************************
DB2 & UDB email list listserv db2-l LazyDBA home page