Hi !!
here's my problem with DB2 version 7.1 (zos, cobol, cics transaction)
I have a table client which contains 3 cols (id_client, sequence_number, data)
the primary key is id_client and sequence number.
When i created a new row and a new client, the sequence_number = 1
When i created a new row with old client, the sequence_number = (max of sequence number of the same id_client +1).
The solution is :
1/ to do a select max of sequence_number
2/ add 1 to this sequence_number
3/ make the insert.
But i'm afraid if there are 2 persons who do the same things at the same time, they will have the same sequence number. What about concurrency ?
Thanks a lot.

or create a sequence and retrieve this unique nbr or
select .... for update --> this will acquire a higher lock and avoid any other application to do a select.. (lock wait)

If you retrieve a duplicate sequence number, you will get an RI error when you try the insert since you have a PK, so there will be no data integrity problem. You can code retry logic if that specific error code is returned (duplicate PK).