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:
<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 |
In your column description, specify a position description field_spec for every column. The position description depends on the format of the data stream.
See also:
The data fields do not have a uniform format but are separated by commas and end with a line break.
Enter position descriptions field_spec as 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",... |
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
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 |
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.
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