Identity columns (Was Trigger pgm to increment seq# in file)

Hi Dieter,

I don't see how this would benefit when the insert fails on the table that
has a column that needs to be filled with an autonumber.
When for that table the insert is rolled back, it won't roll back the value
that was written in the AUTOINC table, since that has already been
committed in the GET_KEY function.

Regards,
-Arco

Op zaterdag 6 mei 2017 heeft D*B <dieter.bender@xxxxxxxxxxxx> het volgende
geschreven:

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