Layout is an ordered collection of zone (field) definitions,
and is a required option for fixed-length format. Each zone definition
is made up of mutually exclusive (non-overlapping) clauses.
These clauses must be in the following order, although some are
optional and can be empty:
- Use-type
- Indicates whether a zone is a normal (data) zone or a filler zone.
For data zones, this value is omitted. Filler zones can only be specified
in bytes. Other use-types exist, but are not used for fixed-length
format data.
- Name
- The name of the zone. Duplicate zone names are not allowed. This
definition is not currently used, but is typically provided to identify
the field.
- Type
- Defines the zone type. When not specified, type is defaulted
to the corresponding type of a table column. Filler-zones have no
default type. Valid values are as follows:
- CHAR
- VARCHAR
- NCHAR
- NVARCHAR
- INT1
- INT2
- INT4
- INT8
- INT
- UINT1
- UINT2
- UINT4
- UINT8
- UINT
- FLOATING
- DOUBLE
- NUMERIC
- BOOL
- DATE
- TIME
- TIMESTAMP
- TIMETZ
- Style
- Defines the zone representation, and is optional. This representation
is defaulted based on the zone-type and ‘Format’ option. All other
styles are only valid for their corresponding non-textual zone-types.
Valid values are the following values:
- INTERNAL
- Valid only for textual zones (CHAR/VARCHAR/NCHAR/NVARCHAR)
- DECIMAL
- Valid for integer/numeric zone types
- DECIMALDELIM
- Valid for numeric, float, double, and time-styles (time, timetz,
and timestamp) zone type
- FLOATING
- Valid for float or double zone type
- SCIENTIFIC
- Valid for float or double zone type
- YMD <'date-delim'>
- Valid for date zones, including other date-styles currently supported
in external table options DateStyle and DateDelim
- 12Hour <'time-delim'>
- Valid for time zones, including other time-styles currently supported
in external table options TimeStyle and TimeDelim
- 24Hour <'time-delim'>
- Valid for time zones other time-styles currently supported in
external table options TimeStyle and TimeDelim
- YMD <'date-delim'> 24Hour <'time-delim'>
- Valid for timestamp and timetz zones, including other combinations
of date and time styles currently supported for external table options DateStyle, DateDelim, TimeStyle,
and TimeDelim.
- TRUE_FALSE, Y_N, 1_0
- Valid for boolean zones, including other boolean styles currently
supported for external table option BoolStyle. Style
must be in accordance with format
- Length
- Specified in bytes.
- Nullif
- Defines the zone null-ness attribute. For fixed-format files,
this clause specifies a known data pattern within the field which
when present signifies the field is null. Length is equal to or less
than the column width, and maximum length is 39 bytes.
Nulls are
detailed in the following table:
Table 1. Layout
example| Use type |
Name |
Type |
Style |
Length |
Nullness |
|---|
| NA |
f1 |
Int4 |
DECIMAL |
Bytes 10 |
Nullif @ = 0 |
| NA |
f2 |
Date |
YMD |
Bytes 10 |
Nullif &= '2000-10-10' |
| NA |
f3 |
Char(20) |
INTERNAL |
Chars 10 |
Nullif && '' |
| Filler |
f4 |
Char(10) |
NA |
Bytes 10 |
NA |
IBM PureData System for Analytics, Version 7.1