Re: duplicate key error in identity column

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Are you sure the duplicate key isn't on some other LF or index?

I would expect QTID to be 0 from the RPG program.

Are you doing anything else to the history file.  For example, if you copy
the data out, recreate the file and copy the data back, the identity value
isn't updated to the current max value.  So you'd see a duplicate key when
you first try to write to it.

Charles

On Wed, Jul 15, 2015 at 10:38 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.