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_PUB" from having duplicate rows for those
columns. SQLSTATE=23505

The DDL for the offending table:

CREATE TABLE IS3.ENTITIES_PUB
(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_PUB
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

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

The failing statement:

merge into is3.entities_pub t1
using an_pub_names_in t2
on t2.ranch=t1.namex
and t2.city=t1.city
and t2.state=t1.state
and t2.country=t1.country
when not matched and t2.entity_id is null
then insert (namex,city,state,country)
values(ranch,city,state,country);

[Robert Stearns]

Robert Stearns wrote:

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_PUB" from having duplicate rows for those
columns. SQLSTATE=23505

The DDL for the offending table:

CREATE TABLE IS3.ENTITIES_PUB
(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_PUB
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

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

The failing statement:

merge into is3.entities_pub t1
using an_pub_names_in t2
on t2.ranch=t1.namex
and t2.city=t1.city
and t2.state=t1.state
and t2.country=t1.country
when not matched and t2.entity_id is null
then insert (namex,city,state,country)
values(ranch,city,state,country);

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_PUB
(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]

Robert Stearns wrote:

Robert Stearns wrote:

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_PUB" from having
duplicate rows for those columns. SQLSTATE=23505

The DDL for the offending table:

CREATE TABLE IS3.ENTITIES_PUB
(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_PUB
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

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

The failing statement:

merge into is3.entities_pub t1
using an_pub_names_in t2
on t2.ranch=t1.namex
and t2.city=t1.city
and t2.state=t1.state
and t2.country=t1.country
when not matched and t2.entity_id is null
then insert (namex,city,state,country)
values(ranch,city,state,country);

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_PUB
(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.

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.