How to set DB2 global variable on session from spring data jpa context

Question

I have a spring data jpa repository. I want to query a database view. The database is DB2 and supports a feature called 'global variable' which actually is a session variable. The db view declaration uses a global variable.

How do I set the value of this variable at runtime before the view is executed?

Would something like this work?

public interface DomainRepository extends org.springframework.data.jpa.repository.JpaRepository<Domain, IdType> {
      @Query(value = "SET SCHEMA.VAR_GLOBAL = :param; SELECT * FROM SCHEMA.DOMAIN", nativeQuery = true)
      List<Domain> findByDomain(@Param("param") String param);
         }

Are there any alternative solutions?

Tom Lensburry · Accepted Answer · 2016-06-13 08:11:38Z

That's what I want to achieve in SQL:

SET CURRENT APPLICATION COMPATIBILITY = 'V11R1';
SET SCHEMA.VAR_GL = 'Value';
SELECT * FROM SCHEMA.VIEW;

The SCHEMA.VIEW is declared as:

SELECT * FROM SCHEMA.TABLE WHERE field = VAR_GL

I have to mark the public method on the controller with @Transactional annotation to execute the queries together in one db session.

public interface Controller {  
  @Transactional
  List<Options> loadOptions();
}
public class ControllerImpl implements Controller {
  @Autowired
  private DomainRepository repo;
  @Override
  public List<Option> loadOptions() {
    this.repo.setCompatibilityMode();
    this.repo.setGlobalVariableA("Value");
    List<Option> list = this.repo.loadDropdown();
    return list;
  }

In my jpa repository I need a method for each global variable:

public interface DomainRepository extends JpaCrudRepository<Option, OptionPK> {
  @Modifying
  @Query(value = "SET CURRENT APPLICATION COMPATIBILITY = 'V11R1'", nativeQuery = true)
      void setCompatibilityMode();    
  }
  @Modifying
  @Query(value = "set SCHEMA.VAR_GL = :value", nativeQuery = true)
  void setGlobalVariableA(@Param("value") String value);
  @Query(value = "SELECT * FROM SCHEMA.VIEW", nativeQuery = true)
  List<Option> loadDropdown();
}

That's how it works. I'd like to improve this solution further but I have no idea how to make the variable query more reusable by making the variable name a parameter.

Stavr00 · Accepted Answer · 2016-06-09 17:48:03Z

0

Variable declarations don't work over JDBC but temp tables do:

DECLARE GLOBAL TEMPORARY TABLE SESSION.myvars
(
VAR_GLOBAL VARCHAR(255)
) ON COMMIT PRESERVE ROWS;

answered Jun 9, 2016 at 17:48

Stavr00

3,3562 gold badges19 silver badges30 bronze badges

4 Comments

mustaccio Over a year ago

Although you can certainly use a temporary table, there's no reason why global variables wouldn't work over JDBC.

Stavr00 Over a year ago

Perhaps in LUW but I get SQLCODE -104 SQLSTATE 42601 when trying to CREATE VARIABLE on zOS

mustaccio Over a year ago

Which version? Global variables are supported since DB2 for z/OS v11

Stavr00 Over a year ago

SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1 returns DSN10015

mustaccio · Accepted Answer · 2016-06-09 20:34:21Z

0

You cannot lump two statements together like in your example, but you should be able to create two different Querys and execute them in separate calls to executeUpdate() and getResultList() respectively. (I'm not very familiar with JPA, I may have used wrong method names, hopefully you get the idea.)

edited Jun 9, 2016 at 20:34

answered Jun 9, 2016 at 18:45

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

1 Comment

Tom Lensburry Over a year ago

That's one point, I cannot concatenate statements in the Query annotation.

Arif Acar · Accepted Answer · 2016-06-09 21:00:20Z

0

You can try CRUD methods:

public interface DomainRepository extends CrudRepository<Domain, Long>{
    List<Domain> findAll();
}

answered Jun 9, 2016 at 21:00

Arif Acar

1,5812 gold badges19 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to set DB2 global variable on session from spring data jpa context

4 Answers 4

Comments

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related