How to set schema in datasource for DB2 connection in Spring boot

Question

I have different database connections but same tables in them. So for reusing those entity classes I have to fetch schema name dynamically. Tried to set schema in properties file and in datasource and is not working.

application.properties file :

spring.datasource1.url=jdbc:db2://localhost:5054/AB01
spring.datasource1.username=abc
spring.datasource1.password=abc
spring.datasource1.driver-class-name=com.ibm.db2.jcc.DB2Driver
spring.datasource1.testWhileIdle=true
spring.datasource1.validationQuery=SELECT 1

Have tried to set schema in properties file as below and got error "The DDM parameter value is not supported. DDM parameter code point having unsupported value : 0x2110." error.

spring.datasource.url=jdbc:db2://localhost:5054/AB01?currentSchema=schema
spring.datasource.url=jdbc:db2://localhost:5054/AB01?search_path=schema
spring.datasource.url=jdbc:db2://localhost:5054/AB01?searchpath=schema

Tried like below in configuration class also but not working.

public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource(databaseURL, username, pwd);
    ds.setDriverClassName(driverClassName);
    Properties connectionProperties = new Properties();
    connectionProperties.setProperty("spring.datasource.schema", "schema");
    ds.setConnectionProperties(connectionProperties);
    return ds;
}

did you see this answer : https://stackoverflow.com/questions/24278659/change-database-schema-used-by-spring-boot — jacques, Nov 22 '19 at 09:06
if you are using jdbc try this: spring.datasource.jdbc-url=jdbc:db2://localhost:5054/AB01:currentSchema=YOURSCHEMA; — Jaime S, Nov 22 '19 at 09:10

score 0 · Accepted Answer · answered Jan 10 '20 at 05:42

Schema can be referred in application.properties as below

spring.datasource1.serverName=160.60.660.6
spring.datasource1.database=ABC
spring.datasource1.port=5083
spring.datasource1.schema=fdsf
spring.datasource1.username=usr
spring.datasource1.password=pwd

use DB2SimpleDataSource to create database

public DataSource dataSource() {
                    DB2SimpleDataSource  datasource = new DB2SimpleDataSource ();
                    datasource.setUser(username);
                    datasource.setPassword(password);
                    datasource.setServerName(server);
                    datasource.setDatabaseName(databse);
                    datasource.setPortNumber(port);
                    datasource.setDriverType(4);  
                    datasource.setCurrentSchema(schema);
                    return datasource;
}

How to set schema in datasource for DB2 connection in Spring boot

1 Answers1