By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

Strange or normal IDENTITY behavior?

P: n/a
Hi

UDB 8.2.2 Win

I am little confused about IDENTITY behavior.

create table aaa(
c1 integer not null generated by default as identity,
c2 integer,
primary key (c1)
)@

insert into aaa values(1,1)@

insert into aaa(c2) values (2)@

I recived an error message:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "AAA" from having duplicate rows for those columns.

It looks like DB2 tryed insert value "1" to "c1" field.

When I restarted instance and tryed execute "insert into aaa(c2) values (2)"
statement, I founded in table record with values(21, 2).
If I insert next record with "manually" (22) generated c1 value and after
this I use db mechanism I recive error again.
Works afted instance restart....

I am almost sure, we haven't this problem with 8.2.1 db version.
My question. This is correct or incorrect db behavior?

Thanks in advance.
Yaro
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Użytkownik "Yaro" <ya***************@op.pl> napisał w wiadomości
news:d5**********@83.238.170.160...
Hi
UDB 8.2.2 Win
I am little confused about IDENTITY behavior.
create table aaa(
c1 integer not null generated by default as identity,
c2 integer,
primary key (c1)
)@
insert into aaa values(1,1)@
insert into aaa(c2) values (2)@
I recived an error message:
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or foreign key update caused by a DELETE statement are not valid
because the primary key, unique constraint or unique index identified
by "1" constrains table "AAA" from having duplicate rows for those
columns. It looks like DB2 tryed insert value "1" to "c1" field.
When I restarted instance and tryed execute "insert into aaa(c2) values
(2)" statement, I founded in table record with values(21, 2).
If I insert next record with "manually" (22) generated c1 value and after
this I use db mechanism I recive error again.
Works afted instance restart....
I am almost sure, we haven't this problem with 8.2.1 db version.
My question. This is correct or incorrect db behavior?
Thanks in advance.
Yaro


I found
"if column is defined as generated by default (...) DB2 UDB guarantees
uniqueness only for the set of values that it generates"

ok, but after instance restart I haven't unique problem. Can I tell db
"change your sequence counter" after insert with manually generated value
but without instance restart?

Yaro
Nov 12 '05 #2

P: n/a
Yaro wrote:
Użytkownik "Yaro" <ya***************@op.pl> napisał w wiadomości
news:d5**********@83.238.170.160...
Hi
UDB 8.2.2 Win
I am little confused about IDENTITY behavior.
create table aaa(
c1 integer not null generated by default as identity,
c2 integer,
primary key (c1)
)@
insert into aaa values(1,1)@
insert into aaa(c2) values (2)@
I recived an error message:
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or foreign key update caused by a DELETE statement are not valid
because the primary key, unique constraint or unique index identified
by "1" constrains table "AAA" from having duplicate rows for those
columns. It looks like DB2 tryed insert value "1" to "c1" field.
When I restarted instance and tryed execute "insert into aaa(c2) values
(2)" statement, I founded in table record with values(21, 2).
If I insert next record with "manually" (22) generated c1 value and after
this I use db mechanism I recive error again.
Works afted instance restart....
I am almost sure, we haven't this problem with 8.2.1 db version.
My question. This is correct or incorrect db behavior?
Thanks in advance.
Yaro

I found
"if column is defined as generated by default (...) DB2 UDB guarantees
uniqueness only for the set of values that it generates"

ok, but after instance restart I haven't unique problem. Can I tell db
"change your sequence counter" after insert with manually generated value
but without instance restart?

Yaro

When you restart DB2 the current batch of numbers (see CACHE) gets
thrown away.
Take a look at RESTART WITH:
ALTER TABLE T ALTER COLUMN pk RESTART WITH <new _value>

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.