将spring data config连接到IBM DB2 z/OS时出现异常

Question

我在启动spring boot应用程序时遇到以下异常（应用程序没有任何代码，只有与DB相关的配置和连接参数，这些配置和连接参数在application.properties文件中定义）

无法从数据库com.ibm.DB2.jcc.am.sqlsyntaxerrorexception：DB2 SQL错误：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连接属性：

     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

     @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;
}

Answer 1

在使用Db2时，请始终注意目标平台(z/OS、i-series、Linux/UNIX/Windows)决定SQL方言以及其他许多东西。平台确定SQL方言。

在您的例子中，当您正在使用Db2 for z/OS时，需要在工具链中使用DB2390Dialect，以便在目标数据库中引用正确的catalog对象。具体地说，SYSIBM是用于DB2-for-z/OS目录对象的模式，而SYSCAT是用于Linux/UNIX/Windows的模式。