Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Table Output step seems to be creating an invalid INSERT statement

  1. #1

    Default Table Output step seems to be creating an invalid INSERT statement

    I'm using Pentaho 3.2.5 with DB2 9.1.5 for the Z/OS. I have a simple transformation that is reading a CSV file, converting/formatting some date fields and then outputting to an empty DB2 table. Not all fields in the input stream are written to table and not all fields in table will be updated.

    I used Table Output step and the "Specify data fields[ ]" check-box to specify the table fields and their matching stream fields. When I run the transformation it fails. In examining the log files I see the following (fake data):

    INSERT INTO MyTable
    (CLIENT, GRP_CODE, CLIENT_MBR_ID, PSN_CLIENT, EFF_DT, TERM_DT, LAST_NAME, FIRST_NAME, MI, GENDER, REL_CLIENT, BRTH_DT, ADDR_LINE_1, ADDR_LINE_2, CITY, ST, ZIPCODE, CLIENT_CARD_ID, SSN, CLIENT_INT_ID, SUFFIX)
    VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

    Error inserting row into table [
    MyTable] with values:
    [111111], [2222222], [CCCCCCCCCC], [01], [20011201], [99991231], [SOMELAST], [SOMEFIRST], [M], [M], [1], [19700101], [1234 MAIN ST], [APT 2], [ANYTOWN], [CA], [90210], [null], [SSSSSSSSSSS], [null], [null], [null], [null], [null], [null], [null], [null], [1970-01-01], [2001-12-01], [9999-12-31]

    DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null

    Note: the stream fields that I specified (for the question mark placeholders) are:
    CLIENT, GRP_CODE, MBR_ID, PSN_CODE, EFF_DT_D, END_DT_D, LAST_NM, FRST_NM, MI, GND, REL, BRTH_DT_D, ADDR1, ADDR2, CITY, STATE, ZIPCODE, CARD_ID, SSN, CLIENT_INT_ID, SUFFIX

    The proposed INSERT statement is correct and lists the correct table fields and the correct number of placeholders (21). In the next line with the values the number of values is too many (30) and not in the correct order. It is supplying every field in the input stream in the order they happen to appear. It is not using just the stream fields specified and in the correct order.

    This would seem to be an issue. Is this a problem with this step? With the DB2 JDBC driver?

    Any help would be appreciated.

    Dave

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The row is printed as information to see what input the step gets.

    I Googled the SQL code and this is what I got: http://publib.boulder.ibm.com/infoce...e/rsql0100.htm

    "SQL0181N The string representation of a datetime value is out of range."

    So perhaps you are feeding a String into a date somehow? Or perhaps the 99991231 dates are a problem?

    There are 6 fields that look like dates. It should be easy to check their data types.

  3. #3

    Default

    It's getting the date error because it is not using the stream field I specified. That's part of the problem. You can see that it is trying to insert 30 fields into 21 table columns. It is not using the correct number or the correct fields for the insert.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It would surprise me if that were the case, but good luck anyway.

  5. #5

    Default

    The highlighted lines from the log seem pretty clear. The example INSERT and the VALUES do not match. 21 fields in one and 30 in the other.

    Am I missing something???

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

    Default

    What Matt mentioned earlier is that the log is slightly misleading...

    1) Stream Row comes into Step
    2) Filter Row
    3) Attempt Insert
    4) If Error show row as it is at (1) Above

    You are interpreting the log line (4) as If Error show row as it is at step (3)

    I'm betting that DB2 does not accept the 9999 date. Try running it with changing the 9999 date to something else that is unlikely (2200-12-31) and see if it works.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7

    Default

    The 9999-12-31 works fine in DB2, we use it all the time. That's not the problem. The problem is that I specified 20 fields to update and insert is apparently creating a VALUES clause of 30 columns. Since they don't line up and some are the wrong type for the field you get the date error.

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

    Default

    Try doing in the 3.0 Style just to show that the dates are an issue...

    1) CSV Input
    2) Format Fields
    3) Select Fields (Only pass the fields you want for the next step)
    4) Table Output

    This will be slower than doing it the way it's designed, but should show where the problem really is.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  9. #9

    Default

    I was able to work around the issue by inserting a "Select Values" step. I only selected the 20 stream fields I needed and I put them in the correct order to match the table. Then the Table Output worked correctly. Thanks.

    My next problem is that some of the input fields are empty and insert as NULL and I need them to be the empty string. The "If Null" step doesn't seem to have a way to specify the empty string as a value. I haven't worked this one out yet....

    Dave

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.