Re: High level record lock

<Charles>
You should be using a UDF to retrieve the "next number"..

Your UDF could ensure that commitment control is use to lock the record.

Honestly, I'd probably go with an external UDF in RPG as the "read w/lock,
then update" is more straight forward.

If you want to do it in SQL, you'd need to declare a cursor, fetch from it
and UPDATE WHERE CURRENT OF CURSOR...

Charles
</Charles>

For the SQL way you should start with the update, to avoid elementar deadlock situations.
An example from my open Source site:

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

Dieter