Re: Trigger pgm to increment seq# in file

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