Results 1 to 2 of 2

Thread: DB2 load

  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Red face Unanswered: DB2 load

    I'm new to DB2 and am trying to loard a source file with date values coming through as 0000-00-00 on the file is the a way in DB2 LOAD commant to default this value to null if it's the correct date value is not passed through
    below is the script that I run: db2 'load
    from /tmp/wkstaging/IDMI04.MI_D_TDA_ACCT_T_DB2_2.TXT OF ASC
    MODIFIED BY fastparse dateformat="YYYY-MM-DD"
    METHOD L (1 6, 7 12, 13 18, 19 21, 22 37, 38 40, 41 65, 66 75, 76 85, 86 100, 101 109, 110 111,112 122, 123 128, 129 138, 139 148, 149 158, 159 164, 165 166, 167 167, 168 169, 170 186, 187 195, 196 205, 206 215, 216 229, 230 231, 232 233, 234 246, 247 247, 248 262, 263 264, 265 281, 282 298, 299 315, 316 331, 332 332, 333 344, 345 354, 355 361, 362 364, 365 370, 371 385, 386 401, 402 402, 403 417, 418 434, 435 444, 445 450, 451 457, 458 472, 473 473, 474 483, 484 484, 485 497, 498 512, 513 514, 515 529, 530 531, 532 540, 541 548, 549 557, 558 562, 563 567, 568 577, 578 585, 586 587, 588 597, 598 607, 608 617, 618 632, 633 647, 648 657, 658 665, 666 675, 676 677,678 679, 680 689, 690 699, 700 714, 715 724, 725 734, 735 749, 750 759, 760 769, 770 784, 785 794, 795 804, 805 819, 820 829, 830 839, 840 854, 855 864, 865 874, 875 889, 890 899, 900 909, 910 924, 925 934, 935 944, 945 959, 960 969, 970 979, 980 994, 995 1004, 1005 1014, 1015 1029, 1030 1042, 1043 1043, 1044 1045, 1046 1047, 1048 1057)
    Replace INTO IDMI04.MI_D_TDA_ACCT_T(CO_CDE,RGN_CDE,BRN_CDE,PROD _CDE,ACCT_NO,SUB_PROD_CDE,ACCT_NAME,OPEN_DATE,LST_ TRNS_DATE,CUR_BAL_AMT,CR_INT_RATE,INT_PAY_CYCLE_CD E,TERM,TIMES_TO_RENEW,MTRTY_DATE,FNL_MTRTY_DATE,CL S_DATE,NO_NOTC,CR_INT_CDE,TERM_CDE,STAT_CDE,AVE_CR _BAL_AMT,NACM_RATE,CUST_DOB,CUR_ISSUE_DATE,TAX_WIT HELD_MTD,TAX_WITHELD_CDE,OPENING_REASON,INT_PAID_I N_ADVNC,TAKEON_IND,OPEN_DEP_AMT,CLOSING_REASON,BAL _BFR_CLOS,TAX_WITHHELD_YTD,CUST_CDE,CUST_NO,HARD_H OLD_IND,LRI_BASE_IDX,LRI_BASE_IDX_DATE,CAP_RATE,CU RRENCY_CODE,CSV_NO_DEP,CSV_DEP_AMT,TEL_NO,CONT_MET H,ALIAS_NAME,TARGET_AMT,TARGET_DATE,DAYS_LEFT,PERC _ACHV,UNCLR_FUNDS,RESET_CNT,RESET_DATE,CCL_FLAG,WH ELD_YTD,DAD_INT_PD,MARKET_SEG,CONT_PAY_AMT,CONTRA_ DUE,COMM_PERC,BIBLIFE_NO,TOP_UP_RATE,OFFICER,SEC_O FFICER,TDS_PD_ON_ACCR_INT,RATE_CDE,LETTER_MAIL_CDE ,DATE_DORMANT,DATE_LAST_DEP,DATE_LAST_MAINT,AMT_LA ST_DEP,AMT_LAST_WD,DATE_LAST_WD,OPERATOR,DATE_LAST _INT_ACCR,INT_PAY_METHOD,ACC_WHELD_CDE,NTCE_GIVEN_ DTE1,NTCE_MAT_DATE1,NTCE_MAT_AMT1,NTCE_GIVEN_DTE2, NTCE_MAT_DATE2,NTCE_MAT_AMT2,NTCE_GIVEN_DTE3,NTCE_ MAT_DATE3,NTCE_MAT_AMT3,NTCE_GIVEN_DTE4,NTCE_MAT_D ATE4, NTCE_MAT_AMT4, NTCE_GIVEN_DTE5,NTCE_MAT_DATE5,NTCE_MAT_AMT5,NTCE_ GIVEN_DTE6,NTCE_MAT_DATE6,NTCE_MAT_AMT6,NTCE_GIVEN _DTE7,NTCE_MAT_DATE7,NTCE_MAT_AMT7,NTCE_GIVEN_DTE8 ,NTCE_MAT_DATE8,NTCE_MAT_AMT8,NTCE_GIVEN_DTE9,NTCE _MAT_DATE9,NTCE_MAT_AMT9,NTCE_GIVEN_DTE10,NTCE_MAT _DATE10,NTCE_MAT_AMT10,REST_PLED_GUARAN,REST_HHOLD _IND,STMNT_CYCLE,STMNT_MAIL_CDE,STMNT_LAST_DTE)'

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    One way to handle this is to pre-process the ASC file with a tool (such as awk) to replace unwanted literal values by a null or other value. If the file is gigabytes in size you can send the awk output to a file and load from a pipe instead of a separate file. No doubt other suggestions will arrive.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •