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

Question

I created local database in DB2 called "TestDB" then I created table called "TestTable".
I found that the table is put under schema name is "yasmin".
I am trying to connect to the 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 tried many solutions on the internet Like set schema but unfortunately doesn't 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

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

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

Answer 2

As @Mark Rotteveel said, the -204 error is from a missing object, but it's missing for a reason other than what he said.

It's not found because you did not prefix it with the schema name. You said above that it's in schema yasmin, but you're connecting with db2admin, so it's trying to look for db2admin.TestTable.

SELECT * FROM yasmin.TestTable

should be what you're looking for.

By default, the search path for schemas is the name of the currently connecting user. You can see what it is using

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 usually it's easier just to include the schema name in your query.