SQL "SERIAL" field work around with a trigger.

[Mark Adkins <adkinsm@xxxxxxxxxx>]

I want to set up a serial field that auto-increments when you insert a new
record.  For a table that has two fields SEQUENCE BIGINT, DATTIM TIMESTAMP.
If I insert/write the values (0, CURDATE) and the last sequence number was
100, it would insert a record as 101.  I would then use my timestamp to
chain the record to get the sequence number created.  I didn't readily find
a way to alter the record buffer on an RPG trigger, but an SQL trigger did
the trick.

CREATE TRIGGER TransactionBeforeTrigger BEFORE INSERT ON TEST
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2ROW
BEGIN
  IF NEW_ROW.SEQ = 0 THEN
   SET NEW_ROW.SEQ =
       (SELECT IFNULL(MAX(SEQ),0) + 1 FROM TEST);
  END IF;
END

The problem I have is locking the table so only one process tries to write
a seqence number at a time.  If I add, "LOCK TABLE TEST IN EXCLUSIVE MODE
ALLOW READ;", it never releases the lock and eventually crashes.  Any
ideas?

Regards,

Mark