Error executing simple query with JDBC Type 4 connectivity

arviman · #1 (**permalink**) 11-22-09, 01:48

Hi,

I tried connecting to the DB2 server using a JDBC Type 4 connection as shown here: The simple query i have however throws an error in the executeQuery statement, although it works fine when i run it through the Control Center's command editor.

Code:

  Class.forName("com.ibm.db2.jcc.DB2Driver");
	            // establish a connection to DB2
	            Connection db2Conn = 
	         DriverManager.getConnection
	             ("jdbc:db2://Host:50000/EMPLOYEE","root","pass");
	            // use a statement to gather data from the database
	            Statement st = db2Conn.createStatement();
	            String myQuery = "SELECT name FROM SCHEMA_NAME.TABLE"; 
	           // execute the query
	            ResultSet resultSet = st.executeQuery(myQuery);

The error I am getting is:
com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=ROOT;SELECT;ARVIND.DEPT_DEPTNAME, DRIVER=3.57.82
at com.ibm.db2.jcc.am.bd.a(bd.java:676)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.km.c(km.java:2506)
at com.ibm.db2.jcc.am.km.d(km.java:2483)
at com.ibm.db2.jcc.am.km.a(km.java:1963)
at com.ibm.db2.jcc.t4.db.g(db.java:139)
at com.ibm.db2.jcc.t4.db.a(db.java:39)
at com.ibm.db2.jcc.t4.t.a(t.java:32)
at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
at com.ibm.db2.jcc.am.km.eb(km.java:1934)
at com.ibm.db2.jcc.am.km.a(km.java:2863)
at com.ibm.db2.jcc.am.km.a(km.java:628)
at com.ibm.db2.jcc.am.km.executeQuery(km.java:612)

Does anyone know what sqlcode=-551 means, and what might be causing the error? Thank you for your help. I've been mulling over for this for a while without any solution.

My Configuration:
DB2 v9.7.0.441
JRE 6
Windows 7

shammat · #2 (**permalink**) 11-22-09, 05:01

Quote:

Originally Posted by arviman

Does anyone know what sqlcode=-551 means

Sure, the manual:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.messages.sql.doc/doc/rsqlmsg.html

Or just enter:
db2 "? -551" on the commandline

If you want to get the full error message in the SQLException (using getMessage()) you need to add a connection property to the URL

Details are here:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.java.doc/doc/tjvjcerr.html

arviman · #3 (**permalink**) 11-22-09, 15:04

Hi,
The sql messages does not list the sqlcode details. And the command db2 "? -551" does not work. Any ideas?

So I used getMessage as mentioned in the second link, and the result I got was
"ROOT" does not have the required authorization or privilege to perform operation "SELECT" on object "SCHEMA_NAME.TABLE"
However Root is the only user in the system, but does not have SYSADM privileges. How would I be able to grant the privilege to the user?

Mods, I hope I am not straying off too much from the topic, or should I repost this question elsewhere?

Marcus_A · #4 (**permalink**) 11-22-09, 16:19

Quote:

Originally Posted by arviman

Hi, the sql messages does not list the sqlcode details. And the command db2 "? -551" does not work. Any ideas?

The command is db2 ? SQL0551N

You could also look up the error in the DB2 documentation (Messages Vol 2) or the online doc, or even google it.

Hint: root user does not have authority to do anything in DB2 unless you have specifically done a DB2 GRANT statement to that user id. However, the default is to allow PUBLIC to connect, so root can probably connect to your database.

shammat · #5 (**permalink**) 11-22-09, 16:26

Quote:

Originally Posted by arviman

Hi, the sql messages does not list the sqlcode details. And the command db2 "? -551" does not work. Any ideas?

Did you look up the error in the links I posted?
It's all explained there...

Raanon · #6 (**permalink**) 11-22-09, 19:08

Quote:

Originally Posted by arviman

So I used getMessage as mentioned in the second link, and the result I got was
"ROOT" does not have the required authorization or privilege to perform operation "SELECT" on object "SCHEMA_NAME.TABLE"
However Root is the only user in the system, but does not have SYSADM privileges. How would I be able to grant the privilege to the user?

Since you said that you can successfully use the Control Center, use it to check the contents of SYSCAT.DBAUTH. I queried it from the command line to demonstrate the columns you should look for:

Quote:

C:\DB2Work>db2 select GRANTOR, GRANTEE, DBADMAUTH, SECURITYADMAUTH from syscat.dbauth

GRANTOR GRANTEE DBADMAUTH SECURITYADMAUTH
--------- -------- ------------ -------------------
SYSIBM RAANON Y Y
SYSIBM PUBLIC N N

2 record(s) selected.

In my example, RAANON is the DBADM (database administrator) and can GRANT privileges (to ROOT, for example).
You could also try connecting from your Java application using the DBADM user indicated on your system.

arviman · #7 (**permalink**) 11-22-09, 21:43

Quote:

Originally Posted by shammat

Did you look up the error in the links I posted?
It's all explained there...

Quote:

Originally Posted by Marcus_A

The command is db2 ? SQL0551N

Yes, thanks I did not realize that SQL0551N corresponded to SQLCODE -551.

Quote:

Originally Posted by raanon

In my example, RAANON is the DBADM (database administrator) and can GRANT privileges (to ROOT, for example).
You could also try connecting from your Java application using the DBADM user indicated on your system.

I tried doing that, unfortunately I think I've forgotten the password I used for my DMADM

None of my usual passwords work, so I wonder if it was created automatically as the name is the same as my windows user account. Hmm, might have to reinstall DB2 and export/import the database.

Thanks a lot everyone, you've been very helpful!

arviman · #8 (**permalink**) 11-22-09, 22:26

NVM my previous post. I realized that control center was logging in automatically in my DBADM, and I granted all permissions to the root account from there. It works swimmingly, thanks again!

Raanon · #9 (**permalink**) 11-23-09, 05:18

Quote:

Originally Posted by arviman

I tried doing that, unfortunately I think I've forgotten the password I used for my DMADM None of my usual passwords work, so I wonder if it was created automatically as the name is the same as my windows user account. Hmm, might have to reinstall DB2 and export/import the database.

By the way, just so it's clear. The password of your DBADM IS the same as your Windows user. Passwords are NOT maintained inside the DB2 database. The passwords are checked using the operating system mechanism.
Another thing you should know... When the Control Center connects to the database, unless it prompts you for a userid/password, it is using IMPLICIT authentication, which means that it's using your current windows logged-in user. (This would be the same as issuing the CONNECT statement from the command line WITHOUT a userid/password at all.) Your Type-4 Java application, however, must use EXPLICIT authentication, providing an explicit userid and password.

arviman · #10 (**permalink**) 11-23-09, 14:39

Quote:

Originally Posted by Raanon

By the way, just so it's clear. The password of your DBADM IS the same as your Windows user. Passwords are NOT maintained inside the DB2 database. The passwords are checked using the operating system mechanism.

Got it. But since my windows account does not have a password, does it mean that I cannot connect via Type 4 since it does not accept an empty password ?

Internet Services

Web Development

Digital Marketing

Consumer Tech