SQLSTATE: 23505 UDB DB2 8.1.5

[Bob Stearns]

Given the DDL:

CREATE TABLE JOHNTEST.TASK_LIST (
TASK BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
PRI CHAR(1) NOT NULL,
SUBP DECIMAL(7,4),
ATO VARCHAR(25),
REASON VARCHAR(25),
DESCRIPTION VARCHAR(2000),
FILES varchar(500),
TOLD CHAR(1),
last_changed timestamp not null default current_timestamp
)

CREATE INDEX JOHNTEST.tl_primary
ON JOHNTEST.TASK_LIST(TASK)

Alter TABLE JOHNTEST.TASK_LIST add constraint primary
PRIMARY KEY(TASK)

Why does the sql statement:

INSERT INTO JOHNTEST.TASK_LIST(TASK, "ATO", "PRI", "SUBP", "DESCRIPTION")
VALUES(default,'Bob', 'z', 999.999, 'testing')

caues the error message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC:
1;JOHNTEST.TASK_LIST
Message: 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 "JOHNTEST.TASK_LIST" from having duplicate rows for
those columns. 

I thought my primary key would be generated every time, uniquely

[Jan M. Nelken]

Bob Stearns wrote:
....

I thought my primary key would be generated every time, uniquely

I cannot recreate this on my V8.2 FP9 database; how did you populated this
table? Only by using quoted INSERT or did you use LOAD?

Jan M. Nelken

[Bob Stearns]

Jan M. Nelken wrote:

Bob Stearns wrote:
...

I thought my primary key would be generated every time, uniquely

I cannot recreate this on my V8.2 FP9 database; how did you populated
this table? Only by using quoted INSERT or did you use LOAD?

Jan M. Nelken

Good guess. My colleague was using a set of insert statements generated
by an export, so that the rows had the task value. All I had to do was
set START WITH greater than the largest inserted row.

[Serge Rielau]

Bob Stearns wrote:

Given the DDL:

CREATE TABLE JOHNTEST.TASK_LIST (
TASK BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
PRI CHAR(1) NOT NULL,
SUBP DECIMAL(7,4),
ATO VARCHAR(25),
REASON VARCHAR(25),
DESCRIPTION VARCHAR(2000),
FILES varchar(500),
TOLD CHAR(1),
last_changed timestamp not null default current_timestamp
)

CREATE INDEX JOHNTEST.tl_primary
ON JOHNTEST.TASK_LIST(TASK)

Alter TABLE JOHNTEST.TASK_LIST add constraint primary
PRIMARY KEY(TASK)

Why does the sql statement:

INSERT INTO JOHNTEST.TASK_LIST(TASK, "ATO", "PRI", "SUBP", "DESCRIPTION")
VALUES(default,'Bob', 'z', 999.999, 'testing')

caues the error message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC:
1;JOHNTEST.TASK_LIST
Message: 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 "JOHNTEST.TASK_LIST" from having duplicate rows for
those columns. 

I thought my primary key would be generated every time, uniquely

There are a couple of options:
1. At some point an insert was doen providing a value instead of letting
DB2 do it. This value is now cauing a collision.
2. A LOAD was performed which provided the values (OVERRIDE).

Given the flexibility (CYCLE, RESTART, flip INCREMENT, ...) of IDENTITY
DB2 cannot automagically synch up.

Here is a handy proc which you can use to "synch up". The procedure
assumes positive increments:

CREATE PROCEDURE GOODSTUFF.SYNCIDENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."'
|| tablename || '" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
COMMIT;
END
$
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

Given the DDL:

CREATE TABLE JOHNTEST.TASK_LIST (
TASK BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
PRI CHAR(1) NOT NULL,
SUBP DECIMAL(7,4),
ATO VARCHAR(25),
REASON VARCHAR(25),
DESCRIPTION VARCHAR(2000),
FILES varchar(500),
TOLD CHAR(1),
last_changed timestamp not null default current_timestamp
)

CREATE INDEX JOHNTEST.tl_primary
ON JOHNTEST.TASK_LIST(TASK)

Alter TABLE JOHNTEST.TASK_LIST add constraint primary
PRIMARY KEY(TASK)

Why does the sql statement:

INSERT INTO JOHNTEST.TASK_LIST(TASK, "ATO", "PRI", "SUBP", "DESCRIPTION")
VALUES(default,'Bob', 'z', 999.999, 'testing')

caues the error message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC:
1;JOHNTEST.TASK_LIST
Message: 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 "JOHNTEST.TASK_LIST" from having
duplicate rows for those columns. 

I thought my primary key would be generated every time, uniquely

There are a couple of options:
1. At some point an insert was doen providing a value instead of letting
DB2 do it. This value is now cauing a collision.
2. A LOAD was performed which provided the values (OVERRIDE).

Given the flexibility (CYCLE, RESTART, flip INCREMENT, ...) of IDENTITY
DB2 cannot automagically synch up.

Here is a handy proc which you can use to "synch up". The procedure
assumes positive increments:

CREATE PROCEDURE GOODSTUFF.SYNCIDENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."'
|| tablename || '" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
COMMIT;
END
$

Thanks a lot. I don't use identity keys often (3 times in two years) but
this is a handy tool for when I make a mistake.