Error message help

[Robert Stearns]

I don't understand the reference "2" in the following message. There is
only a primary key constraint on the named table. It is generated by
default and Im not inserting a value for it.

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 "2"
constrains table "IS3.ENTITIES_P UB" from having duplicate rows for those
columns. SQLSTATE=23505

The DDL for the offending table:

CREATE TABLE IS3.ENTITIES_PU B
(ENTITY_ID INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 20, INCREMENT BY 1, CACHE 20, MINVALUE 20,
MAXVALUE 2147483647, NO CYCLE, NO ORDER),
NAMEX VARCHAR(50) NOT NULL,
CITY VARCHAR(30),
STATE VARCHAR(30),
COUNTRY VARCHAR(30) DEFAULT 'United States'
)
DATA CAPTURE NONE
IN USERSPACE1;

ALTER TABLE IS3.ENTITIES_PU B
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

ALTER TABLE IS3.ENTITIES_PU B
ADD CONSTRAINT PRIMARY PRIMARY KEY
(ENTITY_ID
);

The failing statement:

merge into is3.entities_pu b t1
using an_pub_names_in t2
on t2.ranch=t1.nam ex
and t2.city=t1.city
and t2.state=t1.sta te
and t2.country=t1.c ountry
when not matched and t2.entity_id is null
then insert (namex,city,sta te,country)
values(ranch,ci ty,state,countr y);

[Robert Stearns]

Re: Error message help

Robert Stearns wrote:
[color=blue]
> I don't understand the reference "2" in the following message. There is
> only a primary key constraint on the named table. It is generated by
> default and Im not inserting a value for it.
>
> 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 "2"
> constrains table "IS3.ENTITIES_P UB" from having duplicate rows for those
> columns. SQLSTATE=23505
>
> The DDL for the offending table:
>
> CREATE TABLE IS3.ENTITIES_PU B
> (ENTITY_ID INTEGER NOT NULL GENERATED BY DEFAULT
> AS IDENTITY (START WITH 20, INCREMENT BY 1, CACHE 20, MINVALUE 20,
> MAXVALUE 2147483647, NO CYCLE, NO ORDER),
> NAMEX VARCHAR(50) NOT NULL,
> CITY VARCHAR(30),
> STATE VARCHAR(30),
> COUNTRY VARCHAR(30) DEFAULT 'United States'
> )
> DATA CAPTURE NONE
> IN USERSPACE1;
>
> ALTER TABLE IS3.ENTITIES_PU B
> LOCKSIZE ROW
> APPEND OFF
> NOT VOLATILE;
>
> ALTER TABLE IS3.ENTITIES_PU B
> ADD CONSTRAINT PRIMARY PRIMARY KEY
> (ENTITY_ID
> );
>
> The failing statement:
>
> merge into is3.entities_pu b t1
> using an_pub_names_in t2
> on t2.ranch=t1.nam ex
> and t2.city=t1.city
> and t2.state=t1.sta te
> and t2.country=t1.c ountry
> when not matched and t2.entity_id is null
> then insert (namex,city,sta te,country)
> values(ranch,ci ty,state,countr y);
>[/color]
I realize it is bad netiquette to reply to one's own message, but I have
further information which gets me no closer to the solution of the
problem and, in fact confuses me (easily done) even more. I discovered
(via db2 ? sql0803n, et seq) that the problem was the following index:

CREATE UNIQUE INDEX IS3.E_P_N_C_S_C
ON IS3.ENTITIES_PU B
(NAMEX ASC,
CITY ASC,
STATE ASC,
COUNTRY ASC
)
PCTFREE 10
DISALLOW REVERSE SCANS;

My insert is predicated on at least one of these not matching as I
(mis)understand the merge statement given above. Just in case I didn't
understand the precedence or some foolishness like that, I tried various
combinations of parentheses in the 'when' clause; bad move, the error
messages got STRANGE at that point.

[Robert Stearns]

Re: Error message help

Robert Stearns wrote:[color=blue]
> Robert Stearns wrote:
>[color=green]
>> I don't understand the reference "2" in the following message. There
>> is only a primary key constraint on the named table. It is generated
>> by default and Im not inserting a value for it.
>>
>> 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 "2" constrains table "IS3.ENTITIES_P UB" from having
>> duplicate rows for those columns. SQLSTATE=23505
>>
>> The DDL for the offending table:
>>
>> CREATE TABLE IS3.ENTITIES_PU B
>> (ENTITY_ID INTEGER NOT NULL GENERATED BY DEFAULT
>> AS IDENTITY (START WITH 20, INCREMENT BY 1, CACHE 20, MINVALUE 20,
>> MAXVALUE 2147483647, NO CYCLE, NO ORDER),
>> NAMEX VARCHAR(50) NOT NULL,
>> CITY VARCHAR(30),
>> STATE VARCHAR(30),
>> COUNTRY VARCHAR(30) DEFAULT 'United States'
>> )
>> DATA CAPTURE NONE
>> IN USERSPACE1;
>>
>> ALTER TABLE IS3.ENTITIES_PU B
>> LOCKSIZE ROW
>> APPEND OFF
>> NOT VOLATILE;
>>
>> ALTER TABLE IS3.ENTITIES_PU B
>> ADD CONSTRAINT PRIMARY PRIMARY KEY
>> (ENTITY_ID
>> );
>>
>> The failing statement:
>>
>> merge into is3.entities_pu b t1
>> using an_pub_names_in t2
>> on t2.ranch=t1.nam ex
>> and t2.city=t1.city
>> and t2.state=t1.sta te
>> and t2.country=t1.c ountry
>> when not matched and t2.entity_id is null
>> then insert (namex,city,sta te,country)
>> values(ranch,ci ty,state,countr y);
>>[/color]
> I realize it is bad netiquette to reply to one's own message, but I have
> further information which gets me no closer to the solution of the
> problem and, in fact confuses me (easily done) even more. I discovered
> (via db2 ? sql0803n, et seq) that the problem was the following index:
>
> CREATE UNIQUE INDEX IS3.E_P_N_C_S_C
> ON IS3.ENTITIES_PU B
> (NAMEX ASC,
> CITY ASC,
> STATE ASC,
> COUNTRY ASC
> )
> PCTFREE 10
> DISALLOW REVERSE SCANS;
>
> My insert is predicated on at least one of these not matching as I
> (mis)understand the merge statement given above. Just in case I didn't
> understand the precedence or some foolishness like that, I tried various
> combinations of parentheses in the 'when' clause; bad move, the error
> messages got STRANGE at that point.
>[/color]
More bad netiquette:

I found the problem by luck, not skill. It turns out that two rows of t2
satisfied the not matched condition at the same time, so an attempted
double update caused the problem. I found the data error accidentally,
looking at another incidental error. Maybe better diagnostics are in order.