DB2 SQL Error: SQLCODE = -204, SQLSTATE = 42704

Question

I created a local database in DB2 called " TestDB ", and then created a table called " TestTable ".
I found that the table is placed under the schema name " yasmin ".
I am trying to connect to a DB2 database using JDBC , but I got this exception

  R SQLException information [1/4/14 11:32:59:289 EST] 0000004d SystemErr R Error msg: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TESTTABLE, DRIVER=3.61.86 [1/4/14 11:32:59:290 EST] 0000004d SystemErr R SQLSTATE: 42704 [1/4/14 11:32:59:290 EST] 0000004d SystemErr R Error code: -204 [1/4/14 11:32:59:290 EST] 0000004d SystemErr R com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TESTTABLE, DRIVER=3.61.86 

I have tried many solutions online. As for the circuit, but, unfortunately, does not work.

This is the JDBC code I used

  String urlPrefix = "jdbc:db2:"; String url; String user; String password; String empNo; Connection con; Statement stmt; ResultSet rs; url = urlPrefix + "//127.0.0.1:50000/TestDB"; user = "db2admin"; password = "db2admin"; try { // Load the driver Class.forName("com.ibm.db2.jcc.DB2Driver"); System.out.println("**** Loaded the JDBC driver"); // Create the connection using the IBM Data Server Driver for JDBC and SQLJ con = DriverManager.getConnection (url, user, password); // Commit changes manually con.setAutoCommit(false); System.out.println("**** Created a JDBC connection to the data source"); stmt = con.createStatement(); con.createStatement(); System.out.println("**** Created JDBC Statement object"); // Execute a query and generate a ResultSet instance rs = stmt.executeQuery("select *from TestTable"); System.out.println("**** Created JDBC ResultSet object"); } catch (ClassNotFoundException e) { System.err.println("Could not load JDBC driver"); System.out.println("Exception: " + e); e.printStackTrace(); } catch(SQLException ex) { System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions } } 

Answer 1

As @Mark Rotteveel said, error -204 is related to a missing object, but it is missing for another reason other than what it said.

It is not found because you do not prefix it with the name of the schema. You said above that it is in the yasmin , but you are connecting to db2admin , so it is trying to look for db2admin.TestTable .

 SELECT * FROM yasmin.TestTable 

should be what you are looking for.

By default, the search path for schemas is the name of the current connected user. You can see that he uses

 SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1 

If you want to change it, you can use the SET SCHEMA command to change the search path, but it is usually easier to just include the schema name in your query.

Answer 2

Error SQLERROR -204, SQLSTATE 42704 is the missing / unknown name of the object and is most likely caused by the missing space in:

 rs = stmt.executeQuery("select *from TestTable"); 

What should be:

 rs = stmt.executeQuery("select * from TestTable"); 

Answer 3

The problem was the table I created in db2 has the schema name "yasmin"
I used the username and password "db2admin/db2admin" in the JDBC connection
so he is looking for a table in this db2admin schema
so i need to install the "yasmin" scheme
so I added the following lines of code after creating the connection

  String schemaName="yasmin"; if (schemaName != null && schemaName.length() != 0) { try { statement = connection.createStatement(); statement.executeUpdate("set current sqlid = " + schemaName); System.out.println("The schema is set successfully."); } catch (SQLException exception) { exception.printStackTrace(); } 

and grant db2admin all privileges in this table in db2

1-Right click on the table, select privileges
2-Click "Add User"
3 - then write the username "db2admin" and click "apply"

4. Then select the user you just added and click the "gtant all" button.

Now he can see the table.

Many thanks to @Mark Rotteveel and @bhamby for your help.

Answer 4

If you are connecting to Spring using Hibernate, use the following property line

 <property name="url" value="jdbc:db2://<ip>:<port>/<database>:currentSchema=<currentSchema>;" /> 

Answer 5

Another way is to just create ALIAS, like this:

 CREATE ALIAS USER1.YOURTABLE FOR TABLE USER2.YOURTABLE;