Troubleshooting Load Failures 

Resolving The Problem

Using the log file produced when loading data, you can identify the source of a load failure. When a load fails, two files are produced:

1. A log file having load session information and stats called TABLE.DATABASE.nzlog
2. A file listing all the records in which an error occurred called TABLE.DATABASE.nzbad

If a log file already exists in the current working directory for that table and database, the information will be appended to the file. However, the .nzbad files only contain the records that failed during the most recent load.

The following is an example of a load failure:

MYDATABASE(ADMIN)=> \d mytable
                        Table "MYTABLE"
 Attribute |          Type          | Modifier | Default Value
-----------+------------------------+----------+---------------
 INT1      | INTEGER                |          |
 DT1       | DATE                   |          |
 VC1       | CHARACTER VARYING(100) |          |
Distributed on hash: "INT1"

[nz@cs-spubox2 ~]$ cat loadme.dat
1|2009-08-02|good record
A|2009-08-02|bad record
1|08-02-2009|bad record

The first record should load successfully because all of its values match the table's column layouts. The second row should fail because the value 'A' in column 1 is not an acceptable integer value. The third record should also fail because the date value is not in the default format.

If you run the load with maxerrors set to 0, that is unlimited errors, good records are loaded and all bad records are logged and skipped.

[nz@cs-spubox2 ~]$ nzload -db mydatabase -t mytable -df loadme.dat -delim '|' -maxerrors 0
Load session of table 'MYTABLE' completed successfully

Next, review the current directory for any logs or errors:

[nz@cs-spubox2 ~]$ ls -l MYTABLE*.nz*
-rw-rw-rw-  1 nz nz   48 Aug  2 11:25 MYTABLE.MYDATABASE.nzbad
-rw-rw-r--  1 nz nz 3958 Aug  2 11:25 MYTABLE.MYDATABASE.nzlog

The bad file lists the two records that were expected to fail:

[nz@cs-spubox2 ~]$ cat MYTABLE.MYDATABASE.nzbad
A|2009-08-02|bad record
1|08-02-2009|bad record

The log file provides additional insight into the cause of the failures. Below is the complete log file:

Load started at:02-Aug-09 11:25:10 EDT

  Database:         MYDATABASE
  Tablename:        MYTABLE
  Datafile:         /home/nz/loadme.dat
  Host:             cs-spubox2

Load Options

  Field delimiter:     '|'                File Buffer Size (MB): 8
  NULL value:          NULL               Quoted data:           No
  Checkpoint:          0                  Max errors:            0
  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:          YMD                Date Delim:            '-'
  Time Style:          24 Hour            Time Delim:            ':'
  Number of Restarts:  2                  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: 2(0) [1, INT4] contents of field, ""[A]
2: 3(4) [2, DATE] expected year digit, "08"[-]

Statistics

  number of records read:      3
  number of bad records:       2
  number of discarded records: 0
  -------------------------------------------------
  number of records loaded:    1

  Elapsed Time (sec): 0.0

-----------------------------------------------------------------------
Load completed at: 02-Aug-09 11:25:10 EDT

The section of the log file to focus upon is the section just under 'Found bad records'. The first bad record found produced the following information:

1: 2(0) [1, INT4] contents of field, ""[A]

• The first bad record was found on input row 2.
• The last character examined was the first one.
• The load operation was targeting the first column in the table which is of type integer.
• The actual contents of the input file was 'A' so the problem was a data type mismatch.

A less obvious mismatch occurs with dates. The second load failure produced the following information:

2: 3(4) [2, DATE] expected year digit, "08"[-]

• The second bad record was found on input row 3. (The operation read the first four bytes of the line.)
• The error occurred when targeting the second column of the table, which is of type date.
• The load operation expected a year digit but found "08-" so there was a mismatch in the format of the date and the default date format.

This type of error can be resolved by adding the following option to the nzload command:

-dateStyle DMY

The default date style is YMD (year-month-day).