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

U¿ytkownik "Yaro" <yarok_delthisdel_@op.pl> napisa³ w wiadomo¶ci
news:d5sc8e$b9d$1@83.238.170.160...[color=blue]
> 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[/color]

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

Yaro wrote:[color=blue]
> U¿ytkownik "Yaro" <yarok_delthisdel_@op.pl> napisa³ w wiadomo¶ci
> news:d5sc8e$b9d$1@83.238.170.160...
>[color=green]
>>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[/color]
>
>
> 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[/color]
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