Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    26

    Unanswered: DB2 :Exception :com.ibm.db2.jcc.b.nn: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQ

    Hello Everyone,
    I am trying to execute a simple query which is nothing but "select * from schemname.tablename"

    I am using type 4 driver and i am using db2jcc.jar .
    What i am doing is first i am connecting to the data base.
    And getting all the tables and schemas by using the following query
    1)"SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA NOT LIKE 'SYS%' AND TYPE = 'T' ";

    2)Then once i get all the table names with schema names i am iterating through it and try to execute the following query to get all the rows of the table
    SELECT * FROM TABLENAME.SCHEMANAME;

    For some of the table in the same data base it works fine .It is able to get all the rows and columns but for some tables it is not executing ...throwing an exception which is wired

    This is the method what i am using to execute any query to get the back the resultset
    public ResultSet executeQuery(Connection con,String query){
    try{

    PreparedStatement stmt= con.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery();
    return rs;

    }
    catch(Exception e){
    e.printStackTrace();
    }


    And the query is "selct * from tableName"

    The exception what i am getting is ....


    com.ibm.db2.jcc.b.nn: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=3.52.110
    at com.ibm.db2.jcc.b.ad.a(ad.java:676)
    at com.ibm.db2.jcc.b.ad.a(ad.java:60)
    at com.ibm.db2.jcc.b.ad.a(ad.java:127)
    at com.ibm.db2.jcc.b.rl.c(rl.java:2424)
    at com.ibm.db2.jcc.b.rl.d(rl.java:2401)
    at com.ibm.db2.jcc.b.rl.a(rl.java:1902)
    at com.ibm.db2.jcc.t4.db.g(db.java:138)
    at com.ibm.db2.jcc.t4.db.a(db.java:38)
    at com.ibm.db2.jcc.t4.t.a(t.java:32)
    at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
    at com.ibm.db2.jcc.b.rl.Y(rl.java:1873)
    at com.ibm.db2.jcc.b.sl.bc(sl.java:2526)
    at com.ibm.db2.jcc.b.sl.e(sl.java:3227)
    at com.ibm.db2.jcc.b.sl.Ob(sl.java:570)
    at com.ibm.db2.jcc.b.sl.executeQuery(sl.java:544)
    at com.ibm.test.hadr.utility.JDBC.JDBCUtility.execute Query(JDBCUtility.java:244)
    at com.ibm.test.hadr.manager.verifier.database.JDBCGl obalOperation.getRowsOfAllTheTablesOfTheDataBase(J DBCGlobalOperation.java:290)
    at com.ibm.test.hadr.manager.verifier.database.Junit. JDBCGlobalOperationforMEDB1DataBase.testgetRowsOfA llTheTablesOfTheDataBase(JDBCGlobalOperationforMED B1DataBase.java:45)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:618)
    at junit.framework.TestCase****nTest(TestCase.java:16 8)
    at junit.framework.TestCase****nBare(TestCase.java:13 4)
    at junit.framework.TestResult$1.protect(TestResult.ja va:110)
    at junit.framework.TestResult****nProtected(TestResul t.java:128)
    at junit.framework.TestResult****n(TestResult.java:11 3)
    at junit.framework.TestCase****n(TestCase.java:124)
    at junit.framework.TestSuite****nTest(TestSuite.java: 232)
    at junit.framework.TestSuite****n(TestSuite.java:227)


    I dont know why i am getting this exception for some of the tables only in the same data base....

    I would really appreciate if some one can answer my question...
    I am struggling for this from last 2 days..

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Reason code 63 for SQL0270N means that
    A column with a LOB type, distinct type on a LOB type, A column with a LONG VARCHAR, LONG VARGRAPHIC, DATALINK, LOB, XML type, distinct type on any of these types, or structured type cannot be specified in the select-list of an insensitive scrollable cursor.
    Change the cursor type to FORWARD_ONLY.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    26
    Thanks a lot for your reply..
    But unfortunately in my database tabel i dont have the following data types
    1)LOB type,2)LONG VARCHAR,3)LONG VARGRAPHIC 4)DATALINK 5)LOB 6)XML
    I have data types such as 1)BigINT 2)SMALLINT 3)BLOB
    I dont know wheather for these types of data types also it gill throw exception or not....
    I would really appreciate if some once can answer to this question...

    Thanks,
    swati

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by swati malla
    1)LOB type,2)LONG VARCHAR,3)LONG VARGRAPHIC 4)DATALINK 5)LOB 6)XML
    I have data types such as 1)BigINT 2)SMALLINT 3)BLOB
    What do you think BLOB stands for?

    Also, why do you think you need to use the scrollable cursor?
    Last edited by n_i; 09-14-09 at 16:25.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2009
    Posts
    26
    The idea is i am running a test case ...before the test case i am checking in my jdbctable how many rows are there...After the test case again checking how many rows are there ..
    For this i am running the query "select * from databasename" getting all the rows of the table ...And catching the result....I am catching all the results into catchedResultset....
    This is my first step...
    The second step is after my test case again run the query get back the result ..catch the resultset and compare the results..

    If any changes to any of the rows after the test case to the same table just report it... this is the main idea..
    For this i am getting the two results sets ...and then trying to compare the cached resultsets..
    When i compare the result i need to scroll up and down to the resultset...

    That is the reson why i need a curser which can scroll up and down..

    By the way i tried running my query "sectlt * from table name" by using the new result set which can move forward only..
    PreparedStatement stmt= con.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    In my data base i have blob data type..
    For now when i execute this query i get the following exception which i have no idea why i am getting also....

    com.ibm.db2.jcc.b.nn: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=APPMSG1.SIBOO, DRIVER=3.52.110
    at com.ibm.db2.jcc.b.ad.a(ad.java:676)
    at com.ibm.db2.jcc.b.ad.a(ad.java:60)
    at com.ibm.db2.jcc.b.ad.a(ad.java:127)
    at com.ibm.db2.jcc.b.rl.c(rl.java:2424)
    at com.ibm.db2.jcc.b.rl.d(rl.java:2401)
    at com.ibm.db2.jcc.b.rl.a(rl.java:1902)
    at com.ibm.db2.jcc.t4.db.g(db.java:138)
    at com.ibm.db2.jcc.t4.db.a(db.java:38)
    at com.ibm.db2.jcc.t4.t.a(t.java:32)
    at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
    at com.ibm.db2.jcc.b.rl.Y(rl.java:1873)
    at com.ibm.db2.jcc.b.sl.bc(sl.java:2526)
    at com.ibm.db2.jcc.b.sl.e(sl.java:3227)
    at com.ibm.db2.jcc.b.sl.Ob(sl.java:570)
    at com.ibm.db2.jcc.b.sl.executeQuery(sl.java:544)
    at com.ibm.test.hadr.utility.JDBC.JDBCUtility.execute Query(JDBCUtility.java:245)
    at com.ibm.test.hadr.utility.JDBC.Junit.JDBCUtilityTe st.testmyDB2TESTtablle(JDBCUtilityTest.java:245)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Object APPMSG1.SIBOO does not exist.

    You may find the Message Reference manual useful in the future: IBM - DB2 database product documentation
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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