Table 1. Identity Columns Versus Sequence Objects.
Identity Columns
Sequence Objects
Internal objects generated and maintained by DB2
Standalone database objects created by a DBA
Associated with a single table
Not associated with a specific table; usable across tables
Use IDENTITY_VAL_LOCAL() to get last value assigned
Use PREVIOUS VALUE FOR seq-expr to get last value assigned
N/A
Use NEXT VALUE FOR seq-expr to get next value to be assigned
Add/change using ALTER TABLE ...ALTER COLUMN (DB2 V8 only) ...ALTER COLUMN (DB2 V8 only)
Administer using ALTER SEQUENCE, DROP, COMMENT, GRANT, and REVOKE
Version 6 refresh; Version 7
Version 8
-----Original Message-----
From: Cox George
[mailto:db2udbdba-ezmlmshield-x65059728.[Email address protected]
Sent: Tuesday, January 24, 2006 3:16 PM
To: LazyDBA Discussion
Subject: Altering an Identity column
Hi All,
I'm a newbie in DB2 Z/OS Version 7 looking for info about altering an
identity column. We created a table in our prod environment using
GENERATED BY DEFAULT and having a unique index on the identity column.
We then loaded rows into the table from our QA environment. When the
client attempted to insert a row, the insert failed on a duplicate key
error. To get around this we dropped the table and recreated it with
the start field set to start at 1000 and incremented by 1. We then
loaded the rows back into the table (254 rows). When the client
inserted the row it was created with an identity id of 1000, as
expected. This obviously is not something we want to do going forward,
which leads me to my question.
We just went to Version 7 two weeks ago and migration to Version 8 is
not even on the radar yet. My question is: Is the ALTER TABLE
<table> ALTER COLUMN <id col> RESTART WITH <nextval> available in
Version 7 if we apply maintenance ?
TIA,
George
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Please take a few minutes to provide feedback on the quality of service you received. The Department of Education values your feedback as a customer. Commissioner John L. Winn is committed to continuously assessing and improving the level and quality of services provided to you by Department staff. Simply use the link below. Thank you in advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
DB2 & UDB email list listserv db2-l LazyDBA home page