Re: duplicate key error in identity column

On 15-Jul-2015 08:38 -0600, Dale Janus 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.

A recommendation as possible means to resolve:

Create the history table with the noted IDENTITY column, but also with the TIMESTAMP column as both WITH DEFAULT CURRENT TIMESTAMP and with ROW CHANGE TIMESTAMP tracking enabled. Then use for the update-capable history file, the identical format as the input file; define that identical format [that omits those two additional columns] for a logical file to be named on the F-spec instead of the physical file.