IBM PureData System for Analytics, Version 7.1

Fixed-length format definition

Fixed-length format files must have a format definition. This topic shows examples of typical fixed-length format definitions for typical data types.

End-of-record

When fixed-format records end in a newline character, no action is required. Newline is the default end-of-record delimiter. When there is no record separator, use single quotation marks side by side, as in the following example:

RecordDelim ''

RecordDelim is a literal sequence of up to 8 bytes, which does not translate common escape representations or support functions like CHAR(8).

Record Length

Record Length is optional, but can provide feedback that the format definition has the correct length. This excludes the end-of-record delimiter. The following is an example:

Recordlength NNN

Skip fields

The following clause skips 4 bytes:

“filler char(4)  bytes 4”

However, the preferred method is to indicate that the field is being skipped, as in the following example:

“filler fld_name char(4)  bytes 4”

Temporal values

Temporal values in fixed-length format files often omit delimiters. The following table shows clauses that load dates, times, and timestamps without delimiters.

Table 1. Temporal values
Data type	Value	Format clause
Date	20101231	`date1 date YMD'' bytes 8`
Time	231559	`time1 time(6) 24hour '' bytes 6`
Timestamp	0101231231559	`stamp1 timestamp(6) 24hour '' bytes 14`
Timestamp	20101231231559000001	`(Load as char(24), then use insert-select)` `to_timestamp(col,'YYYYMMDDHH24MISSUS')`
Date	2010-12-31	`date2 date YMD'-' bytes 10`
Time	23.15.59	`time2 time(6) 24hour '.' bytes 8`
Timestamp	2010-12-31 23:15:59	`tms2 timestamp(6) YMD '-' 24hour ':' bytes 19`
Timestamp	2010-12-31 23:15:59.0001	`tms3 timestamp(6) YMD '-' 24hour ':' bytes 26`
Timetz	12:30:45+03:00	`Tz1 TIMETZ(6) 24HOUR ':' bytes 14`
Timetz	123045+-0300	`(Load as char(11) then use insert-select)` `(substring(col1,1,2)\|\|':'\|\| substring(col1,3,2)\|\|':'\|\|substring(col1,5,5)\|\|':'\|\| substring(col1,10,2))::timetz`

Numeric values

The following table shows numeric values.

Table 2. Numeric values
Data type	Value	Format clause
Integer	32767	`int1 int2 bytes 5`
Int8	9123456789123456	`int2 int8 bytes 16`
Numeric	2315.59	`num1 numeric(6,2) bytes 7`
Numeric	231559	`(Load as char(6) then use insert-select) (col/100)::numeric(6,2)`
Floating	1.2345678	`flt1 floating bytes 9`
Floating	12345678	`(Load as char(8) then use insert-select) (substring(col1,1,1)\|\|'.'\|\|substring(col1,2,7))::float`
Double	1.2345678	`flt1 double bytes 9`
Double	12345678	`(Load as char(8) then use insert-select) (substring(col1,1,1)\|\|'.'\|\|substring(col1,2,7))::double`

Logical values

The following table shows logical values.

Table 3. Logical values
Data type	Value	Format clause
Boolean	Y or y, N or n	`BOOL Y_N BYTES 1`
Boolean	1, 0	`BOOL 1_0 BYTES 1`
Boolean	T or t, F or f	`BOOL T_F BYTES 1`

Null values

Fixed-length format files typically use ‘magic’ values to represent nulls. Adding a nullif clause to any specification allows the column to be checked for null. A nullif clause has the following parts:

The keyword “nullif”
The column reference
The test expression

As an example, a file specification where field1 is a date and is considered null if it has the value ’99991231’ would have the following characteristics:

The nullif specification would be as follows: “nullif &=’99991231’”
The entire specification would be as follows: “fld1 date YMD'' bytes 8 nullif &=’99991231’”
All format specifications support the nullif clause.

In addition to &=, which evaluates to ‘string must exactly match,’ the nullif clause also supports &&=, which allows substring matching. This is useful in cases where the string might occur anywhere in a field with space padding. For example nullif &&=’N’ matches the different expressions “ N “, “N “, “ N”.

The following table shows null values:

Table 4. Null values
Data type	Null value	Format clause
Boolean	' ' (1 space)	`BOOL Y_N BYTES 1 NULLIF &=' '(1 space)`
DATE	000000	`DATE YMD '' BYTES 6 NULLIF &='000000'`
INT	' ' (6 spaces)	`INT BYTES 6 NULLIF &=' ' (6 spaces)`