Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Table output DB2 9.7

  1. #1
    Join Date
    Jun 2009
    Posts
    23

    Default Table output DB2 9.7

    Inserting fails:
    INSERT INTO GTOJOBS(jobtitle, jobdate, referencenumber, apply_url, url, company, jobcity, jobstate, country, postalcode, description, salary, education, jobtype, industry, category, experience, dispcategory) VALUES ('Experienced Financial Services Representative','9/11/2013 7:32:40 AM','ATL03653','url','http://jobs.com/us/united-states/sales/jobid1065367-experienced-financial-services-representative',null,'Atlanta','Georgia','USA',null,'<h2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:htm="http://www.w3.org/1999/xhtml" xmlns:ftl="http://www.taleo.net/ftl" class="no-change-header-inline"><br/> <span class="subtitle">Description</span><br/></h2> <span class="blockpanel"><span class=""> </span></span><span id="requisitionDescriptionInterface.d310978e333.row1" class="text" title=""><br/> <strong><font size="2"><u>Summary of Responsibilities</u>:</font></strong><br/> Requisition Number: ATL03653',null,null,'\n ','\n ','Sales',null,4);

    As you can see the SQL that is sent over to DB2 which I pulled from trace logs have no Double quotes for Column names and values are surrounded with double quotes

    DB2 is not accepting this type of syntax and throwing:
    Code:
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=JOBTITLE, DRIVER=4.16.53
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1415)
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:273)
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :     ... 3 more
    2013/09/24 11:59:46 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=JOBTITLE, DRIVER=4.16.53
    Last edited by chandrajeet; 09-24-2013 at 12:07 PM.

  2. #2
    Join Date
    Jun 2009
    Posts
    23

    Default

    I tried instead outputting to a SQL output file. But with thousands of rows I can't implement batch insertion thru shell scripts. So, could you please fix the Table output for DB2 or let me know a work-around
    I actually tried to have double-quotes around the field Names but can't fix the values

  3. #3
    Join Date
    Sep 2013
    Posts
    3

    Default

    You want null,'Atlanta','Georgia','USA',null,')


    NOT

    null,'Atlanta','Georgia','USA',null,'

    See the right parenthesis.

    And a -206 error means one of the specified columns is not a column. That might be caused by the missing paren. It might not.
    Last edited by oakland; 09-26-2013 at 04:14 PM. Reason: better formatting

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Because DB2 names are case sensitive, names must be quoted when they are not all uppercase.
    There is a database connection setting on the Advanced tab to force quoted names.
    So long, and thanks for all the fish.

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.