What about leaving off the key column ?
INSERT INTO VMC.PARTICIPANTS
(LASTNAME,FIRSTNAME)
VALUES
('Potter','Harry');

Does the use of sequence objects help any ?
What version are you using -- on zos v7 + there is a sequence. You
would define the table as a normal table and use the sequence object
instead.


darrin


-----Original Message-----
From: Joanna Grossman
[mailto:db2udbdba-ezmlmshield-x7918444.[Email address protected]
Sent: Friday, May 16, 2008 2:24 PM
To: LazyDBA Discussion
Subject: generated by Default v. Always

Sorry if this is top-posting. I think that I do need to stick with
GENERATED BY DEFAULT, but something weird is going on here.

I can see from the documentation, that GENERATED BY DEFAULT should
create an ID if I don't supply one:

* Another clause that may be used in creating identity columns is
the |GENERATED BY DEFAULT AS IDENTITY| clause. This will cause DB2
to generate unique values for the identity column during insert
operations if no value is specified for the identity column.
However, if a value is specified for the identity column, DB2 will
use it in the insert operation.


So, does anyone have any idea why this:

INSERT INTO VMC.PARTICIPANTS
(PARTICIPANTS_ID,LASTNAME,FIRSTNAME)
VALUES
(DEFAULT,'Potter','Harry')



would yield this output?

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 "VMC.PARTICIPANTS" from having duplicate values for the
index key. SQLSTATE=23505


DDL is:

"PARTICIPANTS_ID" INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 2000, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page