Determine your data format

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.