Re: duplicate key error in identity column

Luis might be on to something...

The v7.1 reference manual has:
ALWAYS
Specifies that the database manager will always generate a value for the
column when a row is inserted or updated and a default value must be
generated. ALWAYS is the recommended value.

Whereas the v5r4 manual has:
ALWAYS Specifies that the database manager will always generate a value for
the column when a row is inserted into the table. ALWAYS is the recommended
value.


Note the lack of the "and a default value must be generated"

I believe what you are trying to do worked back on v5r4, but assuming your
on 7.1 ... you'd need a logical files that didn't have the ID and timestamp
columns for the RPG to write to.

Not sure how ALWAYS and BY DEFAULT are different at this point.

Charles



On Wed, Jul 15, 2015 at 11:07 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Dale,

Are you using your QTID in the O-spec? Just did a quick test using a table
with an ID column like yours without any problem, but omiiting the field in
the O-Spec (Boy, that sounds strange)


Regards,

Luis

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--


On Wed, Jul 15, 2015 at 10:08 AM, Dale Janus <dalejanus@xxxxxxxxxxxxxxxxx>
wrote:

I am having trouble with an ILE program reading a quotation file,
selecting by date and then writing to a history file that is almost
identical. It just has two extra fields: A time stamp and an identity
column called QTID defined in DDL.(actually defined using Ops Nav). I am
not using SQL, just the RPG write op code.

I found in the Midrange.com archives that I should change the default of
the identity column to "generated always as identity", which I did in OPS
NAV. I then generated the SQL to prove I did it right.
QTID INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 )

When I run the program, I get an error CPF5009, duplicate key. Joblog
says:" If the record number is zero, the duplicate record key occurred

on

an output operation. " which is correct. An rpg dump confirms QTID is

0.

We are trying to utilize the new DDL features like record identity and
time stamp, but we still have to work with our old programs.

I don't know if it has any bearing, but both files are defined with the
same field names and same record ID (QTREC). The quote file is defined

as

UP. The history file is defined as O and renamed on the F spec:
rename(qtrec:qthrec). The input file does NOT have record id column or

time

stamp column, but all other fields have identical names.


I know using embedded SQL could solve the problem, but that is not an
option.

---Dale






--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.