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