3 Replies Latest reply on Mar 26, 2009 1:25 PM by rareddy

    DB2 ODBC Connector error

      Greetings,

      Kindly provide us the solution for the following problem. My Java program is in need of fetching the result from the VDB. We are going with teiid-embedded to get our result from the VDB as and when needed.
      I first created a ODBC connector and DSN for Accessing the MS-Access Tables. I created a model for an MS-Access DB Table. I associated the Model of the table with the ODBC connector. After that, I created a simple VDB to access the data from that table,I executed the same using a simple SQL query. It was then set in the Classpath for the teiid-embedded stand alone Java ,it executed properly and I got my result set.
      Again I created a DB2 Connector with DSN using ODBC connectivity. I followed the same steps that I followed for the MS-Access table that I have explained before for the DB2 table. This time teiid-embedded is giving me the "General error" when the sql Statement is executed. I was returning me the connection object properly and the statement object as well as properly. But at the executeQuery it was throwing the "General error". Please find the error message as follows.

      com.metamatrix.jdbc.MMSQLException: Error occurred on connector harini_1.DB21 - com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:85)
      at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:66)
      at com.metamatrix.jdbc.MMStatement.setException(MMStatement.java:364)
      at com.metamatrix.jdbc.MMStatement.processQueryMessage(MMStatement.java:441)
      at com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:540)
      at com.metamatrix.jdbc.MMStatement.executeQuery(MMStatement.java:507)
      at JDBCClient.execute(JDBCClient.java:81)
      at JDBCClient.main(JDBCClient.java:42)
      Caused by: [MetaMatrixComponentException]Error occurred on connector harini_1.DB21 - com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      1 [ConnectorException]Error occurred on connector harini_1.DB21 - com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      2 [ConnectionPoolException]com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      3 [ConnectorException]java.sql.SQLException: General error
      4 [SQLException]General error
      at com.metamatrix.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:149)
      at com.metamatrix.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:90)
      at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:155)
      at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:243)
      at com.metamatrix.query.processor.relational.BatchCollector.collectTuples(BatchCollector.java:59)
      at com.metamatrix.query.processor.relational.BatchCollector.collectTuples(BatchCollector.java:53)
      at com.metamatrix.query.processor.relational.SourceState.collectTuples(SourceState.java:100)
      at com.metamatrix.query.processor.relational.JoinStrategy.loadRight(JoinStrategy.java:70)
      at com.metamatrix.query.processor.relational.MergeJoinStrategy.loadRight(MergeJoinStrategy.java:358)
      at com.metamatrix.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:178)
      at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:243)
      at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:175)
      at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:243)
      at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:111)
      at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:149)
      at com.metamatrix.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:276)
      at com.metamatrix.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:220)
      at com.metamatrix.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:46)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
      Caused by: com.metamatrix.data.exception.ConnectorException: Error occurred on connector harini_1.DB21 - com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      at com.metamatrix.dqp.internal.datamgr.impl.ConnectorWorkItem.handleError(ConnectorWorkItem.java:245)
      at com.metamatrix.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:184)
      at com.metamatrix.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:46)
      at com.metamatrix.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:70)
      ... 3 more
      Caused by: com.metamatrix.data.pool.ConnectionPoolException: com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      at com.metamatrix.data.pool.ConnectionPool.createConnection(ConnectionPool.java:378)
      at com.metamatrix.data.pool.ConnectionPool.obtain(ConnectionPool.java:302)
      at com.metamatrix.connector.jdbc.JDBCConnector.getConnection(JDBCConnector.java:164)
      at com.metamatrix.dqp.internal.datamgr.impl.ConnectorWorkItem.createConnection(ConnectorWorkItem.java:149)
      at com.metamatrix.dqp.internal.datamgr.impl.ConnectorWorkItem.createExecution(ConnectorWorkItem.java:355)
      at com.metamatrix.dqp.internal.datamgr.impl.SynchConnectorWorkItem.createExecution(SynchConnectorWorkItem.java:101)
      at com.metamatrix.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:162)
      ... 5 more
      Caused by: com.metamatrix.data.exception.ConnectorException: java.sql.SQLException: General error
      at com.metamatrix.connector.jdbc.JDBCSourceConnectionFactory.createJDBCConnection(JDBCSourceConnectionFactory.java:116)
      at com.metamatrix.connector.jdbc.JDBCSingleIdentityConnectionFactory.createConnection(JDBCSingleIdentityConnectionFactory.java:114)
      at com.metamatrix.data.pool.ConnectionPool.createConnection(ConnectionPool.java:372)
      ... 11 more
      Caused by: java.sql.SQLException: General error
      at com.metamatrix.common.util.exception.SQLExceptionUnroller.unRollException(SQLExceptionUnroller.java:63)
      ... 14 more
      Exception in thread "main" java.lang.NullPointerException
      at JDBCClient.execute(JDBCClient.java:87)
      at JDBCClient.main(JDBCClient.java:42).....




      Could you please suggest me where I am failing regarding this?
      Thanks in advance for the help.

      Thanks and Regards,
      Dr.Prasad

        • 1. Re: DB2 ODBC Connector error
          rareddy

          The exception looks like you are not able to make connection to your DB2 database through ODBC. Can you check and validate the connection to DB2 externally without using Teiid? You can use any ODBC client tools if you have one, like one at

          http://gpoulose.home.att.net

          Otherwise, switch it to use DB2 JDBC driver instead ODBC, and see if that works. I am suspecting that the ODBC driver for the DB2 is not installed correctly, or some issues with it. "General Error" is too abstract of an error without any details.

          • 2. Re: DB2 ODBC Connector error

            Greetings! Thanks for the reply. I understand that it is really not the connection problem. The reason is
            1. When I execute that in designer, it is perfectly working.
            2. The DSN testconnectivity was successful.
            3. As I mentioned earlier, the Query that works on Access DB is perfectly operating where as when it comes to DB2 alone it is showing the error.
            The problem is still persistent. Could you please help me out ? The source Java Code is the same that has been enclosed with the Teiid Embedded.
            Thanks and Regards,
            Dr.Prasad

            • 3. Re: DB2 ODBC Connector error
              rareddy

              Currently Teiid does not how the SQLSTATE code based on error in the logs. I added this https://jira.jboss.org/jira/browse/TEIID-440 to take care of logging issue.

              Are you saying that you connected to DB2 in the Designer? Designer uses the same embedded as internal engine as the Teiid embedded, so technically if one works, both of them work.

              Another test you could do to further verify the connection is through JDBC layer is, you could use tool like http://www.squirrelsql.org/ and use the "JdbcOdbcDriver" and try to connect to the DB2 DSN. If that works correctly, then that is same way Teiid is consuming this source. Make sure the URL is same as the one used in Designer.

              Teiid does not put any layers on top of the JDBC Connection or does any specific tasks based on the type of connection. Teiid does not have any exception state called "General Error".