Skip navigation
Up to Discussions in TwinFin Talk
13728 Views 5 Replies Latest reply: Jan 21, 2010 3:07 PM by Paul Froggatt RSS
RRama209 New Enzee
RRama209
25 posts since
Nov 30, 2009
Currently Being Moderated

Jan 21, 2010 1:29 PM

please look at the naload error

Hi

i am using below syntax to load data:


/vol/edws/Netezza/linux/4.5.4/bin/nzload -host nznwtest -u spl_poc_dev -pw Netezza1 -db specialty_poc -t PLOSSES -delim '|' -datestyle DMY -maxErrors 100 -df /thidata/dwload/pictsftp/plosses.txt

if i use 'DMY' also i am getting same error

i am extracting the data from oracle by to_char(date 'yymmdd') because oracle data is like DD-MM-YY

Sample data:

40001000496|2001|1|L|F|0|010223|657.14
20000000259|2001|2|L|F|0|010223|1650
99901001395|2001|1|L|F|0|010224|661.58
99901001227|2101|1|L|F|0|010226|1288.06
20000005944|2001|1|L|F|0|010226|5060
99900008222|1905|1|L|P|0|010226|232
99900009832|2001|1|L|S|0|010226|1487.7
99900003072|1905|3|L|P|0|010226|1319.2
20000009655|2001|1|L|F|0|010226|1957.66
20000002930|2103|1|L|S|0|010226|250
20000010835|2103|1|L|S|0|010226|500
99901000574|1912|1|L|S|0|010226|319.64
99900004165|1901|2|L|F|0|010226|3800
99900007156|1905|2|L|S|0|010226|368.3
20001001612|2104|1|L|S|0|010227|40
99999009038|1905|2|L|S|0|010227|102
40098000120|1901|1|L|F|0|010227|9500
99901001297|2001|1|L|F|0|010227|1221.26
99900004540|1905|1|L|S|0|010227|177.71

error:log file

Load completed at: 20-Jan-10 14:03:05 EST
=============================================================================

Load started at:20-Jan-10 14:07:30 EST

  Database:         SPECIALTY_POC
  Tablename:        PLOSSES
  Datafile:         /thidata/dwload/pictsftp/plosses.txt
  Host:             nznwtest1

Load Options

  Field delimiter:     '|'                File Buffer Size (MB): 8
  NULL value:          NULL               Quoted data:           No
  Checkpoint:          0                  Max errors:            100
  Skip records:        0                  Max rows:              0
  FillRecord:          No                 Truncate String:       No
  Escape Char:         None               Accept Control Chars:  No
  Distribution stats:  No                 Allow CR in string:    No

  BoolStyle:           ONE_ZERO

  Date Style:          DMY                Date Delim:            '-'
  Time Style:          24 Hour            Time Delim:            ':'
  Number of Restarts:  1                  Encoding:              Latin9
  Ignore Zero:         No                 Require Quotes:        No
  Time extra zeros:    No                 Load Replay Region (MB): 0

Found bad records

bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]
----------------------------------------------------------------------------------------------------------------------------
1: 1(27) [7, DATE] expected date delimiter, "00"[0]
2: 2(27) [7, DATE] expected date delimiter, "00"[0]
3: 3(27) [7, DATE] expected date delimiter, "00"[0]
4: 4(27) [7, DATE] expected date delimiter, "00"[0]
5: 5(27) [7, DATE] expected date delimiter, "00"[0]
6: 6(27) [7, DATE] expected date delimiter, "00"[0]
7: 7(27) [7, DATE] expected date delimiter, "00"[0]

 

 

 

please look at the error and advise me!!

  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,486 posts since
    Aug 15, 2006
    Currently Being Moderated
    1. Jan 21, 2010 2:02 PM (in response to RRama209)
    Re: please look at the naload error

    Use

     

    -datestyle DMY2 -y2Base 1950 -dateDelim ''

  • RRama209 New Enzee
    RRama209
    25 posts since
    Nov 30, 2009
    Currently Being Moderated
    2. Jan 21, 2010 2:54 PM (in response to Shawn Fox)
    Re: please look at the nzload error

    syntax:

     

    /vol/edws/Netezza/linux/4.5.4/bin/nzload -host nznwtest -u spl_poc_dev -pw Netezza1 -db specialty_poc -t PLOSSES -delim '|' -datestyle DMY2 -y2Base 1950 -dateDelim '' -maxErrors 100 -df /thidata/dwload/pictsftp/plosses.txt

     

     

    error:1

     

    Load started at:21-Jan-10 14:50:38 EST

      Database:         SPECIALTY_POC
      Tablename:        PLOSSES
      Datafile:         /thidata/dwload/pictsftp/plosses.txt
      Host:             nznwtest1

    Load Options

      Field delimiter:     '|'                File Buffer Size (MB): 8
      NULL value:          NULL               Quoted data:           No
      Checkpoint:          0                  Max errors:            100
      Skip records:        0                  Max rows:              0
      FillRecord:          No                 Truncate String:       No
      Escape Char:         None               Accept Control Chars:  No
      Distribution stats:  No                 Allow CR in string:    No

      BoolStyle:           ONE_ZERO

      Date Style:          DMY2               Date Delim:            ''
      Y2Base:              1950
      Time Style:          24 Hour            Time Delim:            ':'
      Number of Restarts:  1                  Encoding:              Latin9
      Ignore Zero:         No                 Require Quotes:        No
      Time extra zeros:    No                 Load Replay Region (MB): 0

    Found bad records

    bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]
    ----------------------------------------------------------------------------------------------------------------------------
    1: 1(31) [7, DATE] day is zero, "000425"[|]
    2: 2(31) [7, DATE] day is zero, "000425"[|]
    3: 3(31) [7, DATE] day is zero, "000425"[|]
    4: 4(31) [7, DATE] day is zero, "000425"[|]
    5: 5(31) [7, DATE] day is zero, "000425"[|]
    6: 6(31) [7, DATE] day is zero, "000425"[|]

     

     

    error:2

     

     

    93: 93(29) [7, DATE] expected date delimiter, "0005"[1]
    94: 94(29) [7, DATE] expected date delimiter, "0005"[1]
    95: 95(29) [7, DATE] expected date delimiter, "0005"[1]
    96: 96(29) [7, DATE] expected date delimiter, "0005"[1]
    97: 97(29) [7, DATE] expected date delimiter, "0005"[1]
    98: 98(29) [7, DATE] expected date delimiter, "0005"[1]
    99: 99(29) [7, DATE] expected date delimiter, "0005"[1]
    100: 100(29) [7, DATE] expected date delimiter, "0005"[1]

    Statistics

      number of records read:      100
      number of bad records:       100
      number of discarded records: 0
      -------------------------------------------------
      number of records loaded:    0

      Parsing Time (sec): 1.0
      Elapsed Time (sec): 1.0

     

     

     

    please correct me

  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,486 posts since
    Aug 15, 2006
    Currently Being Moderated
    3. Jan 21, 2010 3:05 PM (in response to RRama209)
    Re: please look at the nzload error

    Should have been: -datestyle Y2MD -y2Base 1950 -dateDelim ''

     

    I didn't look that closely at your data and just copied the DMY format which you had used in your original command line.

     

    What is the difference between "error 1" and "error 2".  It looks like in the case of "error 2" that you have not set -dateDelim ''

  • Paul Froggatt Active Enzee
    Paul Froggatt
    220 posts since
    May 20, 2006
    Currently Being Moderated
    4. Jan 21, 2010 3:06 PM (in response to RRama209)
    Re: please look at the nzload error

    Looks like you've now got a mismatch between the input data and the table definition.

     

    You need to learn how to read the error messages, the first error:

     

    1: 1(31) [7, DATE] day is zero, "000425"[|]

    The clue is in the heading above it:

     

    bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]

    i.e. the first bad row #1 comes from input row #1 at byte offset 31, the first 7 is defined as DATE, the error you have is "day is zero" and the data you are trying to load into that DATE column is "000425", you have defined the dateStyle as DMY2 so the load is expecting to find a valid day as the first 2 digits of the date, but 00 is not a valid day, hence the error produced is "correct".

  • Paul Froggatt Active Enzee
    Paul Froggatt
    220 posts since
    May 20, 2006
    Currently Being Moderated
    5. Jan 21, 2010 3:07 PM (in response to Paul Froggatt)
    Re: please look at the nzload error

    Just spotted Shawn's reply, changing the dateStyle would fix this if the data is indeed in that style.

More Like This

  • Retrieving data ...

Bookmarked By (0)