9

What is the correct syntax or what are the correct steps for creating a schema in DB2 using JDBC?

When running create schema test1 or create schema test1 authorization db2admin as db2admin, I consistently get DB2 SQL Error: SQLCODE=-552, SQLSTATE=42502, SQLERRMC=DB2ADMIN;CREATE SCHEMA, DRIVER=3.64.106

The exact same command works fine using the DB2 command-line tools.

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137

2 Answers2

9

I found it.

It turns out that for some reason DB2 Express-C does not grant the DBADM privilege to db2admin by default.

This can be fixed by connecting to the DB and then issuing

GRANT DBADM ON DATABASE to db2admin

Thanks Ian Bjorhovde for providing the inspiration spark!

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
1

Looks like it is not a matter of Syntax but authentication, you have to check the credentials that you are passing through JDBC:

-552 authorization-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.codes%2Fsrc%2Ftpc%2Fn552.htm

the_marcelo_r
  • 1,847
  • 22
  • 35
  • As I said, the user is DB2Admin and has all the rights. This same user can execute the statement with the DB2 command-line tools without problems. – Axel Fontaine Dec 21 '12 at 15:52
  • I would try to narrow down the root cause, can you use another DB2 client and try to perform some DDL with the same credentials? http://java-source.net/open-source/sql-clients – the_marcelo_r Dec 21 '12 at 16:09
  • 1
    Already did that. I also tried 3 different versions of the jdbc driver: an older one and the newest jdbc3 and jdbc4 ones. No luck. – Axel Fontaine Dec 21 '12 at 16:16
  • So the next steps would be to try the same code against a fresh DB server and see what happens, it might give you some clues: http://www-01.ibm.com/software/data/db2/express-c/download.html – the_marcelo_r Dec 21 '12 at 16:19
  • Already did that. Have Express-C running (latest version) and tried against a fresh instance. – Axel Fontaine Dec 21 '12 at 16:23
  • Got the same result? Is it just for the "create schema" operation? How about some "alter table"? Did you try something else like Hibernate or Apache Ddlutils http://db.apache.org/ddlutils/ ? – the_marcelo_r Dec 21 '12 at 16:31
  • Well, it must be a bug then; e.g., http://www-01.ibm.com/support/docview.wss?uid=swg1IZ80983. Did you try to create a new DB with a different user and run the same code with these new credentials? – the_marcelo_r Dec 21 '12 at 17:32
  • 1
    Please post the results of the query `select dbadmauth from syscat.dbauth where grantee = current user` when you are connected to the database as the user that is trying to execute `CREATE SCHEMA` – Ian Bjorhovde Dec 21 '12 at 22:23