CODEWITHSUNDEEP
javajdbcdb2
Yasmin
Yasmin

Reputation: 183

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

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
          }
        }

Upvotes: 12

Views: 133021

Answers (5)

Thang Vinh Tran
Thang Vinh Tran

Reputation: 1

Other way is just create an ALIAS, likes this:

CREATE ALIAS USER1.YOURTABLE FOR TABLE USER2.YOURTABLE;

Upvotes: 0

punkck
punkck

Reputation: 83

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>;" />

Upvotes: 1

Yasmin
Yasmin

Reputation: 183

The problem was The table I created in db2 has schema name "yasmin"
I used username and password "db2admin/db2admin" In JDBC connection
so it search for the table in this schema "db2admin"
so I need to set the schema to be "yasmin"
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 privilages on this table in db2

1-Right click on the table choose privilages
2-Click on "Add user" button
3-then write the username "db2admin" and click "apply" button
4-Then select the user you just add and click "gtant all" button

Now he can see the table.

Many Thanks @Mark Rotteveel and @bhamby for your help.

Upvotes: 2

bhamby
bhamby

Reputation: 15469

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.

Upvotes: 15

Mark Rotteveel
Mark Rotteveel

Reputation: 109256

The error SQLERROR -204, SQLSTATE 42704 is a missing/unknown objectname and it is most likely caused by a missing space in:

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

Which should be:

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

Upvotes: 3

Related Questions