Re: Trigger pgm to increment seq# in file

Sql has an object called sequence generator why not use it in this case,
for example it has to be created this way

create sequence mysequence
start with 10
increment by 10
no maxvalue
no cycle

And then you can use it like this: let's use this example I have a table
with two colums, name and seq# if I do an insert like this the seq# will be
generated automatically
Insert into mytwocolstable(name,seq#)
Values
('Name1', next value for mysequence),
('Name2', nedt value for mysequence);

This update your table with a sequence and at the same time increment the
sequence object by 10 since I defined the increment to be by 10 when
creating the object.



On Saturday, May 6, 2017, D*B <dieter.bender@xxxxxxxxxxxx> wrote:

<Arco>
Years ago when we implemented identity columns I have played with that.
Trying for example to do an ALTER TABLE xyz ALTER COLUMN abc RESTART WITH
thelostvalue. Or saving thelostvalue to another table and let the next
insert uae that with an OVERRIDING SYSTEM VALUE. But I ran in too much
issues (like locking issues) that I cannot remember exactly anymore, and in
the end decided that it was not worth the effort for us.
</Arco>

... locks in DB2/400 is an important topic, especeally in a heavy
transactional workload environment. DB2 on AS/400 (and compatible systems).
Reading a record for update takes a lock to the record, only preventing
concurrent updates, but not guaranteing that an update is successfull. At
update time it tries to escalate the lock and this could cause elementary
deadlock situations. In conjunction with the standard record wait of 60 sec
(it must have been one of the fools...) this wouldn't work as expected.
Best is: first make the update and read afterwards. Following SQL
functions as an example (it's from the open source section of my homepage):

-- erstellen mit:
-- D*B RUNSQLSTM SRCFILE(QSQLSRC) B*D
-- D*B+ SRCMBR(GETKEY) B*D
-- D*B+ NAMING(*SQL) B*D
-- D*B+ DFTRDBCOL(KURSSQL) B*D
-- generieren unique Key
create function GET_KEY
(
I_TABLE VARCHAR(18)
, I_FIELD VARCHAR(18)
)
returns int
language sql
modifies sql data
set option dbgview = *LIST
begin
declare O_OUT int;
declare sqlcode int;
update AUTOINC
set HIGHVAL = HIGHVAL + 1
where TABLENAME = I_TABLE
and FIELD = I_FIELD;
if sqlcode = 100
then set O_OUT = 1;
insert
into AUTOINC
values(
I_TABLE
, I_FIELD
, O_OUT
);
else
select HIGHVAL
into O_OUT
from AUTOINC
where TABLENAME = I_TABLE
and FIELD = I_FIELD;
end if;

return O_OUT;
end
;
commit
;


--
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.

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