Examples of data loading with the fixed-length format

Fixed-length format files use ordinal positions, which are offsets to identify the location of fields in the record. To load fixed-format data into a database, you must define the target data type for the field and the location within the record.

The following examples show how to use the fixed-length format with external tables:
  • The following command loads data in fixed format: 
    INSERT INTO t SELECT * FROM EXTERNAL '/data/fixed' USING ( FORMAT 
'FIXED' LAYOUT (BYTES 20, REF BYTES 3, BYTES @2) )
  • The following command loads data with different date and time delimiters for different zones: 
    INSERT INTO t SELECT * FROM EXTERNAL '/data/fixed' USING ( FORMAT 
'FIXED' LAYOUT ( YMD '-' BYTES 15, DMY '/' BYTES 15 ) )
  • The following command loads spatial data (binary data into varchar data): 
    INSERT INTO t SELECT * FROM EXTERNAL '/data/fixed' USING ( FORMAT 
'FIXED' CTRLCHARS true LAYOUT ( BYTES 100, REF BYTES 4, BYTES @2) )
  • The following command loads fixed-format data with a record length and no record delimiter: 
    INSERT INTO t SELECT * FROM EXTERNAL '/data/fixed' USING ( FORMAT 
'FIXED' RECORDDELIM '' RECORDLENGTH @1 LAYOUT( REF BYTES 2, BYTES 
120, REF BYTES 2, BYTES @3) )
  • The following command loads data with different NULLIF clauses for different zones: 
    INSERT INTO t SELECT * FROM EXTERNAL '/data/fixed' USING ( FORMAT 
'FIXED' LAYOUT ( BYTES 15 NULLIF '2000-10-10', BYTES 2  & = '12') )
  • The following command loads data with NULLIF clauses that refer to other zones: 
    INSERT INTO t SELECT * FROM EXTERNAL '/data/fixed' USING ( FORMAT 
'FIXED' LAYOUT ( REF BYTES 2, BYTES @1 NULLIF @1 = -1, REF BYTES 4, 
BYTES 100 NULLIF &&3 = 'null' ) )