Safe way to obtain IDs

[Powernic]

Hello,

The persistence layer of our java application needs and ID table to obtain new IDs.

The table has one row and one column. What we do to obtain ID's is quite simple :
1. select id from table
2. newid = id + range
3. update table set id = newid
4. commit

This is running fine with a single server.

We are now considering clustered servers, and we must find a way to lock that table/row to avoid another server running the same action at the same time, which would result in duplicate ID's.

I've been told that 'select for update' is not suitable for this situation with DB2.

Could anyone give me ideas on how to procede?

Thanks a lot

Nicolas

 

[rlarson]

Create a stored procedure in the db to handle this. Hope this isn't to late.