Db2 connection problem with java

Question

I am having problem with DB2. I just installed the db2 as a db2admin and with a password. When i try to connect to database it is success full and while running any simple select query it give me following error:-

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.LOGIN, DRIVER=3.57.82

I have a database named onp and a table in it called 'login' in which there is one table called 'login' with two fields username and password.

Query that i am running

1. Select * from login; gives me error

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.LOGIN, DRIVER=3.57.82

2. Select * from system.login; gives me error:- (//system is schema name)

DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2ADMIN;SELECT;SYSTEM.LOGIN, DRIVER=3.57.82

I have tried all the resources on the net and exhausted completely. Please help me

Catholicism

Answer 1

I don't know a lot about DB2, but looking up the error codes...

The first error is because you didn't specify a schema, so it couldn't find the login table.

SQLCODE -204 Object not defined to DB2

DB2 apparently requires you to specify the schema name or it looks in the schema with the same name as your login user.

You must use SET SCHEMA or fully qualify the table name.

The second error is because you don't have the privileges to perform that select:

SQLCODE -551, Error: DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION ON OBJECT

I'm not sure why the db2admin user wouldn't be able to select from this table...

Resources:
List of DB2 SQLCODEs

Bratislava

-204 would also indicate that the specified schema in a fully qualified table name is incorrect or doesn't exist. – Goodoh

If you have a commandline, the following helps db2 ? SQLnnnn where nnnn is the 4 digit sql code (extend with 0 on the left if needed). This will print an explanation of the error. – Fodder

Answer 2

SQL CODE 551 occurred because the connecting user does not have privileges to perform operations.

Go to Control Center - Go to User Group and Object and select DB2ADMIN(assume this user is the one use to connect to DB2)

Check all the check box as the following

Grant Schema access to the user

Grant Tables access to the user

Annihilation

Answer 3

I had the same problem and i resolved it by adding Schema in my entity :

@Entity
@Table(name="MyTable", schema="MySchemaName")
public class MyClass implements Serializable {
...
}

Metastasis

Thank you @ilias, this resolved my problem. – Offense

This works great with DB2 but not with H2 database, any idea why? – Offense

Answer 4

You can also resolve the issue as:

Just give the proper authority to the user by which you are connection to DB2.

Rabin