Re: Identity Columns

Craig,

When you performed those "quick tests" did you sign-off and sign-on again, or just change the library list?

Using multiple activation groups can be quite tricky; there could still be programs or service programs already activated in one or more AGs and this can cause all sorts of seemingly strange problems when "testing" in such an environment.

Another way to "clean up" and reset the interactive job environment is to issue:

    RRTJOB RTGDTA(QCMDI) RQSDTA(*NONE)

This will have much the same effect as signing-off and signing back on again.

Then, change your *LIBL as needed, and begin the next set of tests.

Hope that helps,

Mark S. Waterbury

> On 6/14/2018 11:25 AM, Craig Richards wrote:

Hi Arco,

Thanks for taking the time to reply.

My code also seemed to be working fine with RLA and generated always
columns until I did a couple of quick tests to make sure the named
activation group was being cleaned up, which involved switching library
lists and then I seemed to get the intermittent problem.

I noticed when switching environments and inserting records that it seemed
to skip one identity value ( e.g the highest INT ID I had was 12 and it
would generated 14 instead of 13 as the next number )
I know this makes it sound like I've leaving something open but I'm sure I
am not. Although there are 2 programs and a service program involved, only
the service program does database I/O. Both programs run in QILE and end
with *INLR. The Service Program runs in *CALLER and closes all files and
cursors ( of which there are very few ) and the Activation Group is being
reclaimed so I can't see what resource might be left around.

I notice that you are using DS for I/O whereas I am not.

I think I will leave the program as it is for now and see if I can find a
way to recreate the problem to better understand it.

But thanks for your reply - it's good to hear that other people are using
GENERATED ALWAYS ID columns with RLA without issues.

best regards,
Craig



On 14 June 2018 at 16:04, Arco Simonse <arco400@xxxxxxxxx> wrote:

Hi Craig,
You do not need to omit the identity column from your write. We use RLA
write/update of tables defined with GENERATED ALWAYS in many programs here.
Here's a substract of how we do this. Sorry, not totally free code but I'm
to short of time to beautify it.
Hope that helps.
Regards,
-Arco


fadminmas uf a e k disk commit usropn
f alias qualified
d wwFileIn...
d ds likerec(adminmas.adminmasrf
d :*input)
d wwFileOut...
d ds likerec(adminmas.adminmasrf
d :*output)
d based(wwFilePtr)
d wwFilePtr...
d s * inz(%addr(wwFileIn))
/free

clear wwFileIn ;
chain formPanel1ID.id1 adminmas wwFileIn ;
// copy records from posted datastructure
eval-corr wwFileOut = formPanel1Rec ;
if not %found(adminmas) ;
write(e) adminmas.adminmasrf wwFileOut ;
if %error ;
peErrMsgHasSet = MSGHND_composeFileIOErrorMessage
( %status : curPortalLang : pePostVarName);
return FALSE ;
endif ;
wwIDval = rtvLastProvidedID() ;
formPanel1ID.id1 = wwIDval;
else ;
update(e) adminmas.adminmasrf wwFileOut ;
if %error ;
peErrMsgHasSet = MSGHND_composeFileIOErrorMessage
( %status : curPortalLang : pePostVarName);
return FALSE ;
endif ;
endif ;

return TRUE ;
/end-free



2018-06-14 14:41 GMT+02:00 Craig Richards <craig@xxxxxxxxxxxxxxxx>:

Hi All,

I have some files with Identity Columns typically defined something like:

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

When performing writes to a table with one of these fields using RGPLE
RLA
I thought that the UniqueID column in the RPG program was just ignored
and a new unique ID assigned.

However I find I'm sometimes getting some duplicate key errors which are
definitely with the Table ( not other indices ) and the primary key on
the table is just the identity column.

Obviously with an SQL Write, I can specify a list of fields, excluding
the identity column.

In RPG is it necessary to use Data Structures on the DB operations to
also specify a list of fields which excludes the ID column?

I haven't convinced myself 100 percent, but this issue only seemed to
appear when I was calling the program and then switching library lists
and calling the program again.

There is a subfile program and maintenance screen which use the same
service program for DB access which all run in Activation Group QILE. But
these are called via a wrapper CL which does a RCLACTGRP between calls.

I'm a little stumped about what is causing this intermittent issue.
Is it dodgy to perform RLA with an identify column defined as above?
Or are there any special considerations I'm missing?

thanks kindly for any comments.

best regards,
Craig