Spring Data JPA optimistic locking with legacy DB2/400

Question

I am writing new code to update a legacy AS400/iSeries/IBMi DB (DB2/400). I can't change the tables. I understand that Spring Data JPA can use optimistic locking via the @Version annotation, but this requires an existing field in the table to annotate.

DB2/400 has a feature already which increments each time a record is updated, e.g:

select row change token for mylib.table1 from mylib.table1 where someid=123

I am using (from my application.properties file):

spring.datasource.driver-class-name=com.ibm.as400.access.AS400JDBCDriver
spring.jpa.database-platform = org.hibernate.dialect.DB2400Dialect

What I would really like is for there to be a way of using the 'select row change token' feature as the version. Any ideas most welcome.

Thanks in advance.

Answer 1

After quite a bit of research I think the answer is that it is not possible.

The 'row change token' feature is quite interesting but (as far as I can discern) unhelpful. For DB2/400 at least if a record is changed the 'row change token' will indeed increment by 1. However several other records will also increase by 1 at the same time. This can be seen if listing all the records ordered by 'row change token' descending. Initially all the 'row change token's might be 0, update a record and do the listing again and a bunch of records will have their 'row change token' at 1. Also, it appears that 'row change token's get reset, perhaps at IPL or other machine type event.

This seems to be because the records are held internally in pages, and the 'row change token' should really be 'page change token'. So while this might appear to work, if another user updated another record in the same page then the code might think that a record has been changed when actually is has not, just a neighouring record.

I have essentially solved the problem using an old fashioned method. I'm not sure if it has a name but I'm going to call it 'Fairly Optimistic Locking'. If I need to update some fields in a record then I first save the old values, then use the old values in the 'where' clause on the update so that the update will fail if any have changed. It can mean a lot of parameters on the update statement but it works.