Fixed-length format files

Fixed-length format files use ordinal positions, which are offsets to identify where fields are within the record. There are no field delimiters. An end-of-record delimiter is required, even for the last record. Data in fixed-length format files usually does not have decimal or time delimiters because delimiters are not necessary and use space. Because the fields are fixed in size, the locations of delimiters are fixed and are specified in the layout definition, which accompanies the fixed-length format data file.

Loading fixed-format data into the database requires that you define the target data type for the fields and the locations within the record.

You do not have to load all fields in a fixed-length format file. You can skip them by using the "filler" specification. The order of fields in the data file must match the order in the target table, or you must create an external table definition, which specifies the order of the fields as database columns. An external table definition in combination with an insert-select statement allows the field order to be changed.

Unknown or null values are typically represented by known data patterns, which are classified as representing null. The IBM® Netezza® system identifies and acts on these values.

Data attributes

The typical data attributes in fixed-length format files are as follows:

Data type: The data at a particular offset in a record is always of the same type.
Representation: The representation is constant, and each field has a fixed width. Data within a field is always presented in the same way. Certain items such as radix points, time separators, and date delimiters are always at the same place and are typically implied, rather than being present in the data file.
Value: The value can be an actual value or a null indicator. Data representations that indicate a null value are specified by the layout definition. This assumes that null is allowed.
Length: There is no length specification within the data file, because length in the file is fixed and the length attribute is specified by the layout definition.
Nullness: Nullness is identified in the layout definition as either a specific data pattern, such as all spaces, or as being flagged by a value in another column.