Abort: Attempt to insert duplicate key row

[atan003]

the error log message:
Sybase Server message 3621 (severity 10) from stored procedure 'Input_1', line 1: Command has been aborted.
At row 36844, link "DSLink2"

Sybase Server warning 2601 (severity 14) from stored procedure 'Input_1', line 1: Attempt to insert duplicate key row in object 'cus_water_main' with unique index 'pk_cus_water_main'
At row 36844, link "DSLink2"

Sybase Server warning 1205 (severity 13) from stored procedure 'Input_0', line 1: Your server command (family id #0, process id #179) encountered a deadlock situation. Please re-run your command.

-----------------------------------------

the link "DSLink2" is an input link, and the settings are as follow:
"Update action" is "Update existing row or insert new rows"
"Transaction size" is "150"

According to the "Update action", it will check existing row first, then insert new rows.
So I don't know why it aborted.

In this job, there are 42108 rows readed and 42107 rows written, only one row aborted.

Thanks!

[clarcombe]

Can you give a bit more detail as to how you are updating the sybase table? i.e. are you doing a straight SF->XFM->Syb or something similar.

Are you running this job as a multiple invocation ?

What you might want to do is identify which row is causing the problem by creating a hashed file with all the existing keys in and then seeing which keys are already in your hashed file.

Of course, if you are updating existing keys then this might not help much.

[atan003]

In the job, source stages and target stage are all SYBASEOC stage.

There are three tables:
1.the source table, named "master_mstr", the table definition is
create table master_mstr
(
big char(1) not null,
mid char(2) not null,
useno char(7) not null,
......
constraint pk_master_mstr primary key (big, mid, useno)
)
2.the source reference table, named "id_cmp", the table definition is
create table id_cmp
(
big char(1) not null,
mid char(2) not null,
useno char(7) not null,
......
water_id char(14) null,

constraint pk_id_cmp primary key (big, mid, useno)
)
3.the target table, named "cus_water_main", the table definition is
create table id_cmp
(
water_id char(14) not null,
big char(1) not null,
mid char(2) not null,
useno char(7) not null,
......

constraint pk_cus_water_main primary key (water_id)
)

The description of the job design is : load data from the source table (master_mstr), and get the "water_id" value from the source reference talbe (id_cmp), if the "water_id" is not null, then update or write to the target table (cus_water_main).

In the input link, "Update action" is "Update existing row or insert new rows",
"Transaction size" is "150" .
According to the "Update action", it will check existing row first, then insert new rows.
So I don't know why it aborted.

Maybe I don't descript the logic clear.

Could you understand?

thanks!

[atan003]

In the target table, the key is "water_id", and the SQL statement is as follow:

UPDATE bisbas.dbo.cus_water_main SET water_id=?,custom_id=?,big=?,mid=?,use=?,....... WHERE water_id=?;
INSERT INTO bisbas.dbo.cus_water_main (water_id,custom_id,big,mid,use,......) VALUES (?,?,?,?,?,?......);

I hope it will be helpful for you.

Thanks

[atan003]

Could anyone help me....

thanks.

[chulett]

Patience. If anyone can, they will.

Sybase Server warning 2601 (severity 14) from stored procedure 'Input_1', line 1: Attempt to insert duplicate key row in object 'cus_water_main' with unique index 'pk_cus_water_main'
At row 36844, link "DSLink2"

This is the heart of your problem. Look at the data in row 36844 and attempt the update sql yourself through some other tool, it will throw the same error I'll wager.

From what you posted, you are updating a PK in the table. If you attempt to change a PK to a set of values that already exists, that 'Attempt to insert duplicate key row' error will be generated. So, in spite of the fact that it says 'insert' in the error message, it can come from your update DML. First thing I'd check in your shoes.