Parse rows

For loads, the sequence of rows are parsed one-by-one from the external data file, and converted into internal records of the external table. There can be errors during the parsing of each row or each column. For example, there can be errors in identifying the column value itself, as in the case of a missing delimiter. Or there can be errors during the conversion from external format to internal records of the external table, such as alphabets mentioned for an integer column in text-delimited format.

Each error is logged in detail in an nzlog file, and bad rows are logged in an nzbad file. These files help user to identify bad rows in the external data file and correct them for reloading. Depending on the load options of the external table in use, each bad row would either cause the row to be skipped, or the entire load to be aborted. Similarly, each bad column of a bad row can cause the rest of the row to be ignored, or if possible to recover, the load can continue to parse subsequent columns of the same row.

If there is an error in the project-expression on the external table columns, then the entire load is aborted and the transaction rolled back. Errors of this nature are not logged in nzbad or nzlog files, as they are outside of the scope of the external table load mechanism. When the processing reaches the normal SQL engine, the external table is treated as if it is a normal table.

Unlike an external table that has external rows in an ordered sequence, normal user tables have no implicit row order other than hidden rowid columns. So there is no way for a user who is not using rowids to identify the bad row in a SQL engine. In this case, the IBM® Netezza® system returns an error that a particular column caused an error, without identifying the bad row. It is as if the query was selecting from a normal table and inserting into another normal table, with some row that caused the error during insertion.