Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: DB2 - Number of Columns or other Limitation?

  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Default DB2 - Number of Columns or other Limitation?

    I am attempting to write to a 167 column table contained on DB2 from data I am transforming from our legacy AS400. I have found that I can only write out to 60 columns (upto but not including PMBITF/BT_FLG) else I receive an error to the write:

    2007/08/16 15:22:25 - Table output IPOLMSTR.0 - ERROR (version 2.4.1-M1, build 910 from 2007/02/19 15:21:08) : Error inserting row into table [IPOLMSTR] with values: [POLICY_NO=BA451101, COUNTY_CODE=01, AGENT_NO=759, AGENT_NAME=HARVEY SUSAN , SPP_TYPE=MC, SPP_HOLD=, NEXT_BILL_DATE= 01070511, NEXT_BILL_CODE=, MORT_DUE_DATE= 00000000, AMOUNT_REC_MORT=0000000000.00, SPP_ACTION_CODE=, SHORT_BILL_MONTH=00, AMT_REC_MORT_ADJ=0000000000.00, AMT_REC_INS_ADJ=0000000000.00, SPP_START_CODE=, SPP_STOP_CODE=, INSR_PAY_BAL=0000000018.00, INSR_REN_AMT=0000000000.00, SPP_RE_CALCULATE=, AUDIT_YEAR=00, COMMERCIAL_TYPE_CODE=M, CONT_5YR_EXP=, SEC2_BI_DEDUCTIBLE=, SEC2_PD_DEDUCTIBLE=, SEC3_BI_DEDUCTIBLE=, SEC3_PD_DEDUCTIBLE=, FED_TYPE=, INTERIM_EFF_DATE= 01061026, UNINS_MOT=, ACC_DD=, COMM_S2_BI=F, COMM_S2_PD=, CUST_DISC_AUDIT=, CUST_DISCOUNT_CODE=, CUST_NUMBER=, TOT_ANNUAL_PREMIUM=0000000173.00, RENEWAL_PREMIUM=0000000000.00, REINSTATEMENT_AMOUNT=0000000173.00, ACCOUNTS_REC=0000000086.48, ADVANCE_AMOUNT=0000000000.00, AMOUNT_RECEIVED=0000000000.00, ACTUAL_TOT_PREM=0000000173.00, COVERAGE_CHANGE_DATE= 00000000, LAST_PAYMENT_DATE= 01070326, LAST_MAINT_DATE= 01061026, CYCLE_DATE= 01061026, SERVICE_AGENT=, CONT_DEC=, DATE_ISSUED= 01011026, DATE_EFFECTIVE= 01061026, SEC1_DATE_EFFECTIVE= 01061026, SEC2_DATE_EFFECTIVE= 01061026, SEC3_DATE_EFFECTIVE= 01061026, SEC4_DATE_EFFECTIVE= 01061026, EXPIRATION_DATE= 01071026, CANCELLATION_DATE= 00000000, REASON_CANCELLED=, SHORT_RATE=, DECLARATION=, BILL=, POLICY_STATUS=2, FED_NUMBER=, LIABILITY_LIMITS=, MEDICAL_LIMITS=, AUTO_MAT_DAMAGE=3, MORTGAGEE_BILL_MONTH=00, BT_FLG=2007/08/16 15:24:34 - SharedObjects - Reading the shared objects file [C:\Documents and Settings\db2admin.WCREPORT1\.kettle\shared.xml]
    2007/08/16 15:24:34 - SharedObjects - Reading the shared objects file [C:\Documents and Settings\db2admin.WCREPORT1\.kettle\shared.xml]

    Attached, you will find my select statement...... Most of the columns are only flags or codes being 1 or 2 characters in length.....

    Thank you.

    John
    Attached Files Attached Files

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Did you get a db2 error? is the row already in the table?

    Regards,
    Sven

  3. #3
    Join Date
    Oct 2006
    Posts
    6

    Default

    Didn't get a DB2 error - table is initialized......

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Check the db2alert log ... normally the problem is not in Kettle or you would get a DB2 error... my hunch would be that it's something with the data in your target table.

    Regards,
    Sven

  5. #5
    Join Date
    Oct 2006
    Posts
    6

    Default

    Thanks for getting back to me, Sven.

    I checked out the alerts and found none. The table I am writing out to is composed of all character data types with the sizes determined by Kettle and verified by myself that none were too long.

    I went with all characters because I was getting the subject error with using other types of data types (decimal and date). I thought maybe that was why things were not working correctly.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    There's not really much more to do like this. What I can suggest is to try it on a a development database with an empty target table and a small subset of your data.

    Regards,
    Sven

  7. #7
    Join Date
    Oct 2006
    Posts
    6

    Default

    Thanks once more, Sven. I will try subsets.....

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.