| View previous topic :: View next topic |
| Author |
Message |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Mon Mar 21, 2005 2:49 pm Post subject: Alter Table with an IDENTITY column |
 |
|
I did this:
| Code: |
ALTER TABLE HOURS.PREMLETTER
ADD IDENTCOL1 INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 0 ,
INCREMENT BY 1 ,
NO CYCLE ,
MINVALUE 0 ,
MAXVALUE 2147483647) ;
|
Now I'm getting this:
| Code: |
CREATE UNIQUE INDEX HOURS.XPRMLT5
ON HOURS.PREMLETTER
(IDENTCOL1)
USING STOGROUP FSGALLT
PRIQTY 12000
SECQTY 400
BUFFERPOOL BP5
CLOSE NO;
|
| Code: |
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT HOURS.XPRMLT5 has PLACED OBJECT IN REBUILD ENDING
DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 65 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000041' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
|
I have changed my Index from it's original UNIQUE index to the Identitiy column. Still get the same error/warning. Could this be due to the fact the column(IDENTITY) is null and the table needs a reorg to supply the needed value?
Does the Identity column need to be the UNIQUE Index?
Thanks! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 21, 2005 3:03 pm Post subject: |
|
|
Nascar,
When you add an identity column to a table that is not empty, DB2 places the table space that contains the table in the REORG pending state. When the REORG utility is subsequently run, DB2 generates the values for the identity column in all existing rows and then removes the REORG pending status. These values are guaranteed to be unique, and their order is system-determined.
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Mon Mar 21, 2005 3:49 pm Post subject: |
|
|
Thank You Kolusu! This what I suspected. Does the Identity column need to be the UNIQUE Index?
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 21, 2005 4:01 pm Post subject: |
|
|
| Quote: |
Does the Identity column need to be the UNIQUE Index?
|
Nascar,
It depends . For an identity column, DB2 inserts a specified value but does not verify that it is a unique value for the column unless the identity column has a unique, single-column index; without a unique index, DB2 can guarantee unique values only among the set of system-generated values.
Also a lot depends on the CYCLE/NOCYCLE parameters.
When CYCLE is in effect, duplicate values can be generated by DB2 for an identity column. However, if a unique index exists on the identity column, and a non-unique value is generated for it, an error occurs.
when NO CYCLE is specified then values will not be generated for the identity column once the maximum or minimum value for the sequence has been reached. This is the default.
So it is up to you to have an unique index on identity column
Hope this helps...
Cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
