Exception while connecting spring data config to IBM DB2 z/os

Question

I am getting the following exception while bringing up the spring boot app(app doesnt not have any code just the DB related configs and connection parameters which are defined in the application.properties file)

Could not fetch the SequenceInformation from the database com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SYSCAT.SEQUENCES, DRIVER=4.19.49

pom.xml

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>db2.jcc</groupId> <!-- internal from private repo -->
        <artifactId>db2jcc_license_cu</artifactId>
        <version>4.19.49</version>
    </dependency>

    <dependency>
        <groupId>db2.jcc</groupId> <!-- internal from private repo -->
        <artifactId>db2jcc4</artifactId>
        <version>4.19.49</version>
    </dependency>

DB connection properties:

     spring.jpa.hibernate.ddl-auto=validate
     spring.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
     spring.jpa.hibernate.dialect=org.hibernate.dialect.DB2Dialect
     spring.jpa.hibernate.synonyms=true
     spring.jpa.show-sql=true
     spring.db2.datasource.url=jdbc:db2://HOSTNAME:PORT/DBNAME
     spring.db2.datasource.username=somename
     spring.db2.datasource.password=password
     spring.datasource.testWhileIdle=true
     spring.datasource.validationQuery=SELECT 1
     spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
     spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

DB config code in spring boot:

     @Configuration
     @EnableTransactionManagement
     @EnableJpaRepositories(entityManagerFactoryRef = "db2EntityManagerFactory", transactionManagerRef = "db2TransactionManager", basePackages = {
    "com.example.db.repositories" })
     public class DB2Config {

private Logger log = LogManager.getLogger(DB2Config.class);

@Value("${spring.datasource.driver-class-name}")
String driverClassName;

@Value("${spring.db2.datasource.url}")
String dataSourceUrl;

@Value("${spring.db2.datasource.username}")
String username;

@Value("${spring.db2.datasource.password}")
String passkey;

@Value("${spring.jpa.hibernate.ddl-auto}")
String hbm2ddl;

@Value("${spring.jpa.hibernate.dialect}")
String dialect;

@Bean
public DataSource db2DataSource() {
    log.info("Loading db2 datasource");
    final DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(driverClassName);
    dataSource.setUrl(dataSourceUrl);
    dataSource.setUsername(username);
    dataSource.setPassword(passkey);
    return dataSource;
}

@Bean
public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory() {
    final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(db2DataSource());
    em.setPackagesToScan("com.example.db");
    final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    em.setJpaVendorAdapter(vendorAdapter);
    final HashMap<String, Object> properties = new HashMap<String, Object>();
    properties.put("hibernate.hbm2ddl.auto", hbm2ddl);
    properties.put("hibernate.dialect", dialect);
    em.setJpaPropertyMap(properties);
    em.setPersistenceUnitName("db2");

    return em;
}

@Bean
public PlatformTransactionManager db2TransactionManager() {
    final JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(db2EntityManagerFactory().getObject());
    return transactionManager;
}

}

Yes this did the trick. Thanks mao. – Tarun Shedhani Nov 25 '19 at 20:49 — Tarun Shedhani, Nov 25 '19 at 20:49

score 4 · Accepted Answer · answered Nov 26 '19 at 09:18

4

Per comment thread, when working with Db2, always be aware that the target platform (Z/OS, i-series , Linux/Unix/Windows) determines the SQL dialect , along with many other things. The platform determines the SQL dialect.

In your case, as you are working with Db2 for Z/OS, it was necessary to use the DB2390Dialect with your toolchain so that the correct catalog objects get referenced on the target database. Specifically SYSIBM is the schema for Db2-for-Z/OS catalog objects, while SYSCAT is the schema used for Linux/Unix/Windows.

answered Nov 26 '19 at 09:18

mao

11,321
2
13
29

Could you show how you set that up please and thank you – pixel Oct 12 '21 at 01:47
After setting dialect to ```spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2390Dialect``` as you suggested, I am now getting error **"SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=MY_USER_NAME;CREATE TABLE;DSNDB04, DRIVER=4.29.24"** – pixel Oct 12 '21 at 01:54
I fixed it by adding schema to my @Table annotation on my entity class like: ```@Entity @Immutable @Table(name = "CAR_VIEW", schema = "MY_SCHEMA") public class CarEntity implements Serializable { ... }``` but I was able to go this far only thanks to your answer above @mao. Thank you – pixel Oct 12 '21 at 15:27

Exception while connecting spring data config to IBM DB2 z/os

1 Answers1