Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: db2 query preview error

  1. #1
    Join Date
    Sep 2017
    Posts
    4

    Default db2 query preview error

    we hit the following error when query review from db2 table, any advice would be appreciated:
    2017/09/19 17:01:05 - Carte - Installing timer to purge stale objects after 1440 minutes.
    2017/09/19 17:28:36 - C:\Projects\愛買\bbb.ktr : bbb - Dispatching started for transformation [C:\Projects\愛買\bbb.ktr : bbb]
    2017/09/19 17:28:39 - Table input 2.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Unexpected error
    2017/09/19 17:28:39 - Table input 2.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2017/09/19 17:28:39 - Table input 2.0 - An error occurred executing SQL:
    2017/09/19 17:28:39 - Table input 2.0 - SELECT *
    2017/09/19 17:28:39 - Table input 2.0 - FROM TB_SALE
    2017/09/19 17:28:39 - Table input 2.0 -
    2017/09/19 17:28:39 - Table input 2.0 - DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TB_SALE, DRIVER=3.67.27
    2017/09/19 17:28:39 - Table input 2.0 -
    2017/09/19 17:28:39 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1764)
    2017/09/19 17:28:39 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:236)
    2017/09/19 17:28:39 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:140)
    2017/09/19 17:28:39 - Table input 2.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2017/09/19 17:28:39 - Table input 2.0 - at java.lang.Thread.run(Thread.java:745)
    2017/09/19 17:28:39 - Table input 2.0 - Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TB_SALE, DRIVER=3.67.27
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.gd.a(gd.java:749)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.gd.a(gd.java:66)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.gd.a(gd.java:135)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.c(so.java:2763)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.d(so.java:2751)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.a(so.java:2200)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.t4.ab.h(ab.java:141)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.t4.ab.b(ab.java:41)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.t4.o.a(o.java:32)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.t4.tb.i(tb.java:145)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.ib(so.java:2169)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.a(so.java:3248)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.a(so.java:691)
    2017/09/19 17:28:39 - Table input 2.0 - at com.ibm.db2.jcc.am.so.executeQuery(so.java:670)
    2017/09/19 17:28:39 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1753)
    2017/09/19 17:28:39 - Table input 2.0 - ... 4 more
    2017/09/19 17:28:39 - Table input 2.0 - Finished reading query, closing connection.
    2017/09/19 17:28:39 - Table input 2.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    2017/09/19 17:28:39 - C:\Projects\愛買\bbb.ktr : bbb - Transformation detected one or more steps with errors.
    2017/09/19 17:28:39 - C:\Projects\愛買\bbb.ktr : bbb - Transformation is killing the other steps!

  2. #2
    Join Date
    Sep 2017
    Posts
    4

    Default db2 query preview error

    2017/09/19 17:01:05 - Carte - Installing timer to purge stale objects after 1440 minutes.
    2017/09/19 17:54:00 - C:\Projects\愛買\bbb.ktr : bbb - Dispatching started for transformation [C:\Projects\愛買\bbb.ktr : bbb]
    2017/09/19 17:54:03 - Table input 2.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Unexpected error
    2017/09/19 17:54:03 - Table input 2.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2017/09/19 17:54:03 - Table input 2.0 - An error occurred executing SQL:
    2017/09/19 17:54:03 - Table input 2.0 - SELECT *
    2017/09/19 17:54:03 - Table input 2.0 - FROM TB_SALE
    2017/09/19 17:54:03 - Table input 2.0 -
    2017/09/19 17:54:03 - Table input 2.0 - DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TB_SALE, DRIVER=3.67.27
    2017/09/19 17:54:03 - Table input 2.0 -
    2017/09/19 17:54:03 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1764)
    2017/09/19 17:54:03 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:236)
    2017/09/19 17:54:03 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:140)
    2017/09/19 17:54:03 - Table input 2.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2017/09/19 17:54:03 - Table input 2.0 - at java.lang.Thread.run(Thread.java:745)
    2017/09/19 17:54:03 - Table input 2.0 - Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TB_SALE, DRIVER=3.67.27
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.gd.a(gd.java:749)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.gd.a(gd.java:66)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.gd.a(gd.java:135)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.c(so.java:2763)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.d(so.java:2751)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.a(so.java:2200)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.t4.ab.h(ab.java:141)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.t4.ab.b(ab.java:41)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.t4.o.a(o.java:32)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.t4.tb.i(tb.java:145)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.ib(so.java:2169)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.a(so.java:3248)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.a(so.java:691)
    2017/09/19 17:54:03 - Table input 2.0 - at com.ibm.db2.jcc.am.so.executeQuery(so.java:670)
    2017/09/19 17:54:03 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1753)
    2017/09/19 17:54:03 - Table input 2.0 - ... 4 more
    2017/09/19 17:54:03 - Table input 2.0 - Finished reading query, closing connection.
    2017/09/19 17:54:03 - Table input 2.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    2017/09/19 17:54:03 - C:\Projects\愛買\bbb.ktr : bbb - Transformation detected one or more steps with errors.
    2017/09/19 17:54:03 - C:\Projects\愛買\bbb.ktr : bbb - Transformation is killing the other steps!

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    The error says that TB_SALE does not exist or your user does not have access to it.

  4. #4
    Join Date
    Aug 2016
    Posts
    142

    Default

    Based on the other thread you commented on, it appears you need to specify the database in the query

    So your query should be something like

    SELECT *
    FROM DB2ADMIN.TB_SALE

  5. #5
    Join Date
    Sep 2017
    Posts
    4

    Default

    we hit the same error after SELECT * FROM DB2ADMIN.TB_SALE, any more advice would be appreciated

  6. #6
    Join Date
    Sep 2017
    Posts
    4

    Default

    solved by : SELECT * FROM db_virtualbusiness.TB_SALE instead of SELECT * FROM db2admin.TB_SALE,
    the schema name had been switched to mysql's databasename once we migrated database from mysql to db2
    Last edited by vkjuju; 09-27-2017 at 04:22 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.