Position Description

You use the syntax rule field_spec to describe:

?     The position of the input field in the data stream when importing application data (Column Description)

?     The position of an output field in the data stream when exporting application data (Output Column)

?     The position of a comparison value for importing application data selectively (Selecting Data Records).

See also:

Data Stream

Syntax

<field_spec> ::= <unsigned_integer>
               | <unsigned_integer> - <unsigned_integer>

<unsigned_integer>

Start position of a data field

See the SQL Reference Manual, Unsigned Integer (unsigned_integer)

<unsigned_integer> - <unsigned_integer>

Start position - end position of a data field

Explanation

In your column description, specify a position description field_specfor every column. The position description depends on the format of the data stream.

See also:

CSV, FWV, FWV BINARY

Format CSV

The data fields do not have a uniform format but are separated by commas and end with a line break.

Enter position descriptions field_specas relative positions only.

If you enter position descriptions with start and end positions for single columns or all columns, the Loader generates an error and terminates the command.

EXPORT COLUMNS * FROM hotel.reservation
MAP COLUMNS
  rno       1
  cno       2
  hno       3
  type      4
  arrival   5
  departure 
6-15    ERROR
DATA OUTSTREAM 'reservation_csv.data'
CSV

Assign position 1 to the first column in your list. The position numbers of the following columns increase by 1 each time. This also means that each position number can only be assigned once.

If you do not observe these rules, the Loader generates an error and terminates the command.

EXPORT COLUMNS * FROM hotel.reservation
MAP COLUMNS
  rno        1
  cno        2
  hno        3
  
type       4
  arrival    5
  departure  6
  type       7
DATA OUTSTREAM 'reservation_csv.data' CSV

You can assign a column to multiple different positions.

Data stream customer_csv.data

Position No.

   1    2       3    4       ....

"3000","Mrs","Jenny","Porter",...
"3100","Mr","Peter","Brown",...
"3200","Company","?","Datasoft",...

IMPORT command

IMPORT TABLE hotel.customer
  DATA INSTREAM 'customer_csv.data'
  
  cno       1
    title     2
    firstname 3
    name      4
    zip       5
    address   6
  
PAGE WITH 80% USAGE

Format FWV

The data fields have a standard format and end with a line break.

Specify all position descriptions field_spec with exact start and end positions.

Specify only ascending, non-overlapping values for position descriptions. If the positions entered are not consecutive, the Loader fills the gaps with blank characters. This also applies to binary data.

EXPORT COLUMNS * FROM hotel.reservation
  MAP COLUMNS
    rno       01-05
    cno       06-10
    hno       11-15
  
  type      16-21
    arrival   25-34
    departure  36-45
  
DATA OUTSTREAM 'reservation_fwv.data' FWV

Data stream customer_fwv.data

Position No.

1 2 3 4 5 6 7 8 9 ....

- 3 0 0 0 M r s - - - - J e n n y - - - - - P o r t e r
- 3 1 0 0 M r - - - - - P e t e r - - - - - B r o w n -
- 3 2 0 0 C o m p a n y ? - - - - - - - - - D a t a s o

IMPORT command

IMPORT TABLE hotel.customer
  DATA INSTREAM 'customer_fwv.data'
FWV
    cno       
01-05
    title     
06-12
    firstname 
13-22
    name      
23-32
    zip       
33-37
    address   
38-62
  PAGE WITH 80% USAGE

Define the positions for the data stream so that they are at least as long as the length of the values in the database.

If you define a position for the data stream that is longer than the length of the value in the database, the following occurs:

?     Character strings are aligned left and blank characters entered to make them the correct length.

?     Numeric values are aligned right and blank characters entered to make them the correct length.

If you define a position for the data stream that is shorter than the length of the value in the database, the Loader generates an error and terminates the command.

Format FWV BINARY

The data fields have a standard format but do not end with a line break.

IMPORT TABLE hotel.customer
  DATA INSTREAM 'customer_fwv_binary.data'
FWV BINARY
    cno       
01-05
    title     
06-12
    firstname 
13-22
    name      
23-32
    zip       
33-37
    address   
38-62
  PAGE WITH 80% USAGE