DB2 "With UR" Spring Data JPA

Question

I am using a Spring Data Repository interface to retrieve data from DB2 (z/OS). I have a couple of methods that rely on the method signatues and one that relies on an explicit @Query.

How do I make the SQL generated have the "WITH UR" clause? I added @Transactional(isolation=Isolation.READ_UNCOMMITTED) on the line above the @Query annotation and also above the two methods but it doesn't seem to append the clause to the generated SQL.

Thanks

Bearden

were you able to get solution for this? If yes can you post your solution. – Hus

Sorry...we moved away from Spring Data to conventional JPQL the following year. – Bearden

Answer 1

The only way I found to do this on a query by query basis was through the usage of Hibernate comments hint and a custom Dialect.

I'm using Spring Boot 2.7 with spring-data-jpa 2.7.12.

In your Repository interface, use @QueryHints and @QueryHint annotations above your Repository methods.

@QueryHints(value = {
        @QueryHint(name=org.hibernate.jpa.QueryHints.HINT_COMMENT, value=DB2zOSCustomDialect.UNCOMMITTED_READ)})
Optional<MyEntity> findByProperty(String property);

The custom dialect class should be extended from DB2390Dialect and a straight forward implementation is provided below (not thoroughly tested).

public class DB2zOSCustomDialect extends DB2390Dialect{

    Logger logger = LoggerFactory.getLogger(getClass());
    public static final String UNCOMMITTED_READ = "with ur";
    
    @Override 
    public String addSqlHintOrComment(java.lang.String sql, QueryParameters parameters, boolean commentsEnabled){
        String uncommittedRead = parameters.getComment();
    
        if(uncommittedRead != null && uncommittedRead.equalsIgnoreCase(UNCOMMITTED_READ)) {
            parameters.setComment(null);
            return super.addSqlHintOrComment(sql, parameters, commentsEnabled) + " " + UNCOMMITTED_READ;
        } else {
            return super.addSqlHintOrComment(sql, parameters, commentsEnabled);
        }
    }
}

If you don't need to check query by query, a custom Dialect extended from DB2390Dialect overring transformSelectString method from Dialect class should suffice.

Bondy