Odp: [DB2-L] IDENTITY COLUMN ISSUE

Mariusz .

Odp: [DB2-L] IDENTITY COLUMN ISSUE
Helllo,

You have not defined the identity column value, so DB2 does it.
However the record (identified by x'00000c6f10') with that value already
exists in your table. You have to try to modify the definition of your
column by changing the START WITH value, or get rid of the existing
record.

Greetings

Mariusz Stakowski

Asseco Poland





Od:
SUBSCRIB DB2-L sumanthdb2 <[login to unmask email]>
Do:
[login to unmask email]
Data:
2009/11/30 17:16
Temat:
[DB2-L] IDENTITY COLUMN ISSUE



Hi ,

In our environment DB2 V8 CM z/os,
we have a table having identity column defined as GENERATED BY DEFAULT.
Also, it is the primary key and
Unique index is defined on this identity column alone..whenever i am
trying
into insert a row without specifying the value for the identity coulmn..

error:an inserted or updated value is invalid because index in index space
task1 constrains columns of the table so no two rows can contain duplicate
values in those columns. rid of existing row is x'00000c6f10'

I am unable to insert it. Please kindly can someone help me in
understanding
why this happens..and help me to over come this..

Is changing it to GENERATED ALWAYS, will keep this away..Please let me
know
your thoughts, suggestions and advices.

Thanks,
Sumanth

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation
of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Francis Leblanc

Re: IDENTITY COLUMN ISSUE
(in response to Mariusz .)
Hi Sumanth.

Is the table in production or test/development? Has all previous data been inserted, or was some of the data loaded via a utility? If some of the data may have been loaded, I would check the values in SYSIBM.SYSSEQUENCES for columns start, increment, and possible minvalue and maxvalue. You may need to determine the maximum current value in the table containing the identity column and alter the identity column with a new "start with" value that is higher than the highest existing value (assuming that the increment value is greater than zero).

That should get you past the problem.

Good luck.

Fritz

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SUBSCRIB DB2-L sumanthdb2
Sent: Monday, November 30, 2009 10:50 AM
To: [login to unmask email]
Subject: [DB2-L] IDENTITY COLUMN ISSUE

Hi ,

In our environment DB2 V8 CM z/os,
we have a table having identity column defined as GENERATED BY DEFAULT.
Also, it is the primary key and
Unique index is defined on this identity column alone..whenever i am trying
into insert a row without specifying the value for the identity coulmn..

error:an inserted or updated value is invalid because index in index space
task1 constrains columns of the table so no two rows can contain duplicate
values in those columns. rid of existing row is x'00000c6f10'

I am unable to insert it. Please kindly can someone help me in understanding
why this happens..and help me to over come this..

Is changing it to GENERATED ALWAYS, will keep this away..Please let me know
your thoughts, suggestions and advices.

Thanks,
Sumanth

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

This E-Mail has been scanned for viruses.

Peter Vanroose

Re: IDENTITY COLUMN ISSUE
(in response to Francis Leblanc)
Sumanth,

My impression is that the start value of the identity column is wrong, or
better said, its default (1) is not what you want when you add the
"identity, generated by default" property to an existing column (with
existing data).
Be sure to set the START value to (at least) 1 more than the highest
occurring value.

Still, the problem could pop up later again, if some applications have their
own logic for inserting new values into this PK column (typically:
MAX(col)+1); this will conflict with other applications using the (new) "by
default" mechanism.
If that's what is happening, set the START value to a "very high" value, or
maybe to a very small negative value, or set it to -1 and also set INCREMENT
BY to -1, and nomore conflicts will occur between the two kinds of access.

If you change the column to GENERATED ALWAYS, the "old" applications will
start failing.

-- Peter Vanroose
ABIS Training &Consulting


On Mon, 30 Nov 2009 15:49:59 +0000, <[login to unmask email]> wrote:
>In our environment DB2 V8 CM z/os, we have a table having identity column
> defined as GENERATED BY DEFAULT.
>Also, it is the primary key and Unique index is defined on this identity column
> alone..whenever i am trying into insert a row without specifying the value
> for the identity coulmn..
>error:an inserted or updated value is invalid because index in index space
>task1 constrains columns of the table so no two rows can contain duplicate
>values in those columns. rid of existing row is x'00000c6f10'
>
>I am unable to insert it. Please kindly can someone help me in understanding
>why this happens..and help me to overcome this..
>
>Is changing it to GENERATED ALWAYS, will keep this away..Please let me know
>your thoughts, suggestions and advices.
>
>Thanks,
>Sumanth

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Seibert

Re: IDENTITY COLUMN ISSUE
(in response to Peter Vanroose)
Hello Sumanth,

Do you know why the column has the GENERATED BY DEFAULT attribute?
Do you know how the key value is handled?
e.g., does the application ever insert a value for this column or is it always generated?
Do you run the LOAD utility against the table?
Are you or your DBAs in the practice of dropping/recreating/LOADing the table - to accomplish table structure changes?

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Vanroose
Sent: Monday, November 30, 2009 12:55 PM
To: [login to unmask email]
Subject: Re: [DB2-L] IDENTITY COLUMN ISSUE

Sumanth,

My impression is that the start value of the identity column is wrong, or
better said, its default (1) is not what you want when you add the
"identity, generated by default" property to an existing column (with
existing data).
Be sure to set the START value to (at least) 1 more than the highest
occurring value.

Still, the problem could pop up later again, if some applications have their
own logic for inserting new values into this PK column (typically:
MAX(col)+1); this will conflict with other applications using the (new) "by
default" mechanism.
If that's what is happening, set the START value to a "very high" value, or
maybe to a very small negative value, or set it to -1 and also set INCREMENT
BY to -1, and nomore conflicts will occur between the two kinds of access.

If you change the column to GENERATED ALWAYS, the "old" applications will
start failing.

-- Peter Vanroose
ABIS Training &Consulting


On Mon, 30 Nov 2009 15:49:59 +0000, <[login to unmask email]> wrote:
>In our environment DB2 V8 CM z/os, we have a table having identity column
> defined as GENERATED BY DEFAULT.
>Also, it is the primary key and Unique index is defined on this identity column
> alone..whenever i am trying into insert a row without specifying the value
> for the identity coulmn..
>error:an inserted or updated value is invalid because index in index space
>task1 constrains columns of the table so no two rows can contain duplicate
>values in those columns. rid of existing row is x'00000c6f10'
>
>I am unable to insert it. Please kindly can someone help me in understanding
>why this happens..and help me to overcome this..
>
>Is changing it to GENERATED ALWAYS, will keep this away..Please let me know
>your thoughts, suggestions and advices.
>
>Thanks,
>Sumanth

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L