Re: sql identity column generate always (WHY HAVE TO RESET)

ive learned to do the following instead of cpyf (but my issue lies with

the

lurking WRITE within rpg pgms)

Jay - I do a lot of WRITES in RPG programs to these files.
I've never had an issue due to that.

You can't influence the ID from the RPG program, but you can write records
using

native WRITE (RLA) or
embedded SQL INSERT

and in both cases use

Exec Sql Set :UniqueID = Identity_val_local();

to retrieve the ID value assigned from your native WRITE or embedded SQL
INSERT.

I don't know if there are issues with older-style RPG but I've certainly
not had any issues in my code using RPG programs to write to tables with
autogen IDs

On Fri, 24 May 2019 at 16:54, Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

ive learned to do the following instead of cpyf (but my issue lies with

the

lurking WRITE within rpg pgms)
(why they refer to the id column as "user" value, I have no idea)

insert into targetTable overriding user value
(select * from sourceTable)

Jay

On Fri, May 24, 2019 at 11:48 AM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

I can't answer the question of why an identity column may get out of

wack.

I never trusted them in the first place.

I certainly was close to falling out with ID columns before I figured

out

what was going on, but they've worked very well for me since.

Admittedly I'm working at a small site at the moment and our biggest

table

using auto-gen IDs has a little under half a million rows currently,

but

it

gets hit by a web service and an internal application server

concurrently

and I've not seen it misbehave.

I don't mistrust the auto-gen ID columns.
The only misgiving I'd have about using them would be if I couldn't
guarantee that no one would CPYF data into them.

(Yes, I suppose you could argue that I can't actually guarantee no one
would ever do that... )

On Fri, 24 May 2019 at 16:22, Nathan Andelin <nandelin@xxxxxxxxx>

wrote:

I can't answer the question of why an identity column may get out of

wack.

I never trusted them in the first place.

Rather, we created a table that stores all our "next incremental"

values

for all our files that need them, and pair that with generic

procedure

that

returns the next incremental value like so:

rec.siep100k = genNextSeqn('SIEP100K');

We generally place that call within a service program that we call a
"database event handler", in a procedure named on_write(), which is a
trigger event handler. I'll post a code sample. The majority of our
database tables use surrogate keys, so this setup is invoked a lot.

I'm

not

aware of any cases where an identity value has ever gotten out of

wack.

But

if there were, we have a program that can query and maintain the next
identity values for any column in any table. As I indicated earlier,

those

values are just stored in a table. That table had hundreds of rows

that

contain the next incremental value for hundreds of identity columns.

p on_write b export

/free

recptr = %addr(dbe.after);

if not is_valid();
return;
endif;

rec.siep100k = genNextSeqn('SIEP100K');

return;

/end-free

p on_write e
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com