19

I used to use currentSchema=MYSCHEMA; in my JDBC URL connection, but the version of DB2 we're using no longer supports that, showing the error 'The "currentSchema" property is not allowed on the target server'. I've tried using hibernate.default_schema, but it's not automatically adding the schema to my table names. I don't want to set the schema on every @Table annotation since I'll need to change it between test and production. Is there another way to set on the connection or via Hibernate?

Update: it must have been a driver version issue. I upgraded to later drivers and currentSchema worked.

Brian Deterling
  • 13,556
  • 4
  • 55
  • 59

2 Answers2

52

With DB2 JDBC type 4 driver (com.ibm.db2.jcc.DB2Driver), I'm using this URL to connect :

jdbc:db2://<HOST>:<PORT>/<DATABASE>:currentSchema=<SCHEMA>;

Source: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvdsprp.htm

Stéphane B.
  • 3,260
  • 2
  • 30
  • 35
  • 3
    This answer made me so happy I could cry – maggix Jan 29 '17 at 23:18
  • 19
    For those who will use this solution DON'T forget the semicolumn at the end otherwise you'll get an errorcode=-4461 it's extremely important I lost a lot of time investigating this lol jjst for a stupid mistake – Akyo Jan 26 '18 at 16:16
  • @goat many thanks, this should be emphasized in the answer itself. – obe6 Nov 07 '19 at 13:53
  • how can we deal with this, if we have multiple schema in springboot. for single we can configure it in application.properties – sparsh610 May 04 '20 at 11:45
7

All the properties for the 9.7 (Latest) db are here...

https://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/r0052607.html

use:

currentSchema

Specifies the default schema name that is used to qualify unqualified database objects in dynamically prepared SQL statements. The value of this property sets the value in the CURRENT SCHEMA special register on the database server. The schema name is case-sensitive, and must be specified in uppercase characters.

Romain Hippeau
  • 24,113
  • 5
  • 60
  • 79
  • Right, I mentioned that I had tried that and it didn't work. It looks like I had an older version of the JDBC driver and updating fixed it. Thanks. – Brian Deterling Jun 02 '10 at 02:57