Re: Identity Columns

So, this issue has reared it's head again on another file.

I think I see the problem.

Here is what happened this morning.
I signed on and the first thing I did was select and try to copy a record
using my maintenance program.
It failed with a duplicate key.
I came out of the program and went back in. It continued to fail.
I did this 2 or 3 times.

I signed off and back on.
I tried the same thing again. It worked...

Here is the way the ID column is defined:

Trigger_UniqueID for column UniqueID Integer
not Null
generated always as identity ( start with 1 increment by 1 cycle )
primary key


Here's the error:
Message ID . . . . . . : CPF5009 Severity . . . . . . . : 10

Message type . . . . . : Diagnostic

Date sent . . . . . . : 22/06/18 Time sent . . . . . . :
08:27:31


Message . . . . : Duplicate record key in member TRGCTL00.

Cause . . . . . : The output or update operation to member number 1
record
number 0 format TRGCTL0R, for member TRGCTL00 file TRGCTL00 in library

CISYS, failed. Member number 1 record number 3 format TRGCTL0R has the
same
record key as member number 1 record number 0 format TRGCTL0R. If the

record number is zero, the duplicate record key occurred on an output

operation.

Recovery . . . : Change either record key so that the keys are unique.

Then try your request again.

Here is how the file looked at that point:
( There are gaps on the UniqueID numbers as you would expect as records get
deleted )

REL_REC UNIQUEID
--------------------------------
1 3
2 4
3 9
4 10
5 11
6 12
7 16
8 17
9 18
10 19
11 20
12 21
13 22
14 23
15 24
16 25
17 26
18 27
19 28
20 29
21 37
22 38
23 5
24 8

So, record number 3 has a UniqueID of 9.
That's the one it's complaining about.

It seems like it's not a coincidence that the highest relative record
number in the file has a UniqueID of 8.
( The record with a UniqueID of 9 IS NOT the one I was copying )

As I mentioned, I tried again a few times which failed, and then eventually
worked.
The file now looks like this:

REL_REC UNIQUEID
--------------------------------
1 3
2 4
3 9
4 10
5 11
6 12
7 16
8 17
9 18
10 19
11 20
12 21
13 22
14 23
15 24
16 25
17 26
18 27
19 28
20 29
21 37
22 38
23 5
24 8
25 13

So, my few attempted writes that failed with a duplicate key caused the
UniqueID to bump up through the failing 9, 10, 11, 12 to where there was a
gap at 13 which is when the write succeeded.

So now I would expect the next two writes for UniqueID 14 and 15 to be ok
as that is a gap but then 16 will fail.

To re-iterate, the environment - it's a service program supporting the
database access of this file for a maintenance subfile and detail program.
The only maintenance of this new file at all on the system is via this new
program.
The service program uses a cursor to build a page of subfile records but
all updates of the file are done via RPGLE RLA.
The RPG program uses the UniqueID to retrieve a record for UPDATE but never
manipulates the value of the UniqueID .
The RPG program DOES NOT use data structures on the WRITE operation to
limit the list of fields specified ( e.g to try to take the UniqueID out of
the equation )

My understanding is that once you create an IDENTITY column, you don't need
to concern yourself with how it gets populated on writes to the table via
RLA or SQL.
The Database Manager should take care of that.

Does anyone disagree with this?
Does this seem like a bug or am I making incorrect assumptions or doing
something foolish? ( it wouldn't be the first time... )

I'm going to change my writes to this table to SQL inserts.

best regards,
Craig



On 15 June 2018 at 18:12, Justin Taylor <JUSTIN@xxxxxxxxxxxxx> wrote:

I just meant whatever you're writing to the PF, whether a DS or just
individual variables.



-----Original Message-----
From: Craig Richards [mailto:craig@xxxxxxxxxxxxxxxx]
Sent: Thursday, June 14, 2018 4:22 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <
rpg400-l@xxxxxxxxxxxx>
Subject: Re: Identity Columns

I'm not sure which buffer you mean Justin.

But if you are talking about the memory location that the program uses for
the ID Column of the table - well that is never referenced by the program.

If assignment of ID Column values was down to my program, the first ID
would write as zero and all subsequent ones would fail as duplicates.

The file is Prefixed with Cnt_

The ID Column is called UniqueID.

The field Cnt_UniqueID is never referenced in the program.

Debugging the program - stopped on the WRITE. The File contains UniqueID
values of 1,2,3,4,5,6,7,8 and 32.

The first test I try is to Add a record so the Service Program has not yet
accessed the table.
Well, it has to build a subfile page but that bit has been done in SQL.

The value of Cnt_UniqueID has a Value of 0 because no RLA has occurred yet
in the Service Program.

After the WRITE there is a new UniqueID of 46 on the file. I've been
adding and deleting a few records in testing which would account for the
gap.
So the RLA WRITE ( or whatever goes on between my program and the Database
Manager ) has ignored the value of 0 which was in CNT_UniqueID and
generated the new value of 46.

Second test. I Update a record with UniqueID 5 and then go on to Copy a
Record to create a new record on the file..
This time, before the WRITE there is a value of 5 in CNT_UniqueID ( from
the RLA Access for the previous Update ) After the WRITE there is a new
UniqueID of 47 in the file.

So this all appears as I expected, that for RLA Writes to this table the
values my program has for the ID field are inconsequential.
Hence my confusion about how a Duplicate Key Error can occur if the
Database Manager is looking after it.

Anyway, I've tried adding / deleting / copying / updating records and
switching from one library to another ( after coming out of the program
which correctly closes everything down AND reclaims the named activation
group ) and now it's decided to behave itself.

I'll just have to wait and see if it appears again...
I like a good mystery /sigh

As usual - thanks all for your thoughts. It is very helpful to share
knowledge on these forums and I'm always grateful for the people who take
time to add their thoughts.
best regards,
Craig

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD