Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    5

    Unanswered: Error executing simple query with JDBC Type 4 connectivity

    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
    Last edited by arviman; 11-22-09 at 01:56.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by arviman View Post
    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

  3. #3
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    5
    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?
    Last edited by arviman; 11-22-09 at 16:49.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by arviman View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by arviman View Post
    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...

  6. #6
    Join Date
    Sep 2002
    Posts
    2
    Quote Originally Posted by arviman View Post
    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:

    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.

  7. #7
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    5
    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!

  8. #8
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    5
    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!

  9. #9
    Join Date
    Sep 2002
    Posts
    2
    Quote Originally Posted by arviman View Post
    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.

  10. #10
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    5
    Quote Originally Posted by Raanon View Post
    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 ?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •