Consider the following when you are determining the format of your
data:
- Check how many data fields there are in each input line of the
data file. Are there the same number of columns that are defined in
the target-table definition?
- If there are fewer columns than fields, is it acceptable to extend
the schema to have filler columns? If not, then the load will not
succeed.
- If there are more columns than fields, is it acceptable to use
null values to insert into those columns? If it is acceptable, specify
the -fillRecord option.
- Check the field delimiter. It should be a character that is used
to separate one field value from another. This field delimiter must
be unique and must not appear in a field value, especially in a char
or varchar string. Use the -delim option to specify
the field delimiter.
- Check whether there are any NULL values in the data source. How
is the null value expressed in the data file? The RDBMS industry convention
is to use the string “null” to represent a null value. If the data
file uses a different representation, use the -nullValue option
to override the default null value. The new value can be an empty
string or a value in the range of a-z or A-Z and no longer than four
characters.
- Check whether there are any date, time, time with time zone, or
timestamp data types in the table schema. If there are, what style
is the date value? The style of these data type values must be consistent
throughout the nzload job.
- Check the handling of string fields for char() or varchar() data
types. Does the longest or largest value fit into the storage of the
char() or varchar() declaration? If not, is it possible to alter the
schema to accommodate the longest string?
- If schema cannot be altered, is truncating a string an acceptable
solution?
- If truncation is acceptable, specify the -truncString option.
- If neither is acceptable, the nzload command
treats the record with the long string as an error record. The nzload command
discards the record to the nzbad file and logs
an error with the record and column numbers in nzlog file.
- See whether there are any special characters that are used in
the string fields. For example, CR, CRLF, or a character in a string
that is the same as the field delimiter? This violates the unique
character rule.
- If there are special characters, can you regenerate the data file
to have an escape character added to these special characters? If
so, then use the -escapeChar '\\' option to process
the strings.
- If you cannot regenerate the data file, then the load contains
incomplete and invalid records.