Column Description 
The syntax rules listed here are part of the column description in the commands for importing, exporting and updating application data.
If you want to use keywords as column names, you must place them in double quotation marks.
Syntax
<export_column_spec> ::=
<map_column_field>
|
<field_assignment>
<import_column_spec> ::=
<map_field_column> [<null_condition>]
|
<column_assignment>
<update_column_spec> ::=
<map_field_column>
|
<column_assignment>
<map_column_field> ::=
output
column
<field_assignment> ::= output
column
<map_field_column> ::=
<column_name> <field_spec> <field_function>
<column_name> ::=
<identifier>
<field_spec> ::=
position
description
<field_function> ::=
<field_type> [HEX]
|
<numerical_functions>
|
<field_type> [HEX] <numerical_functions>
<field_type>
::= data
types
<numerical_functions> ::= <scale_spec>
|
<round_or_trunc_spec>
|
<scale_spec> <round_or_trunc_spec>
<scale_spec> ::=
SCALE <unsigned_integer>
<round_or_trunc_spec> ::= ROUND <unsigned_integer>
|
TRUNC <unsigned_integer>
<null_condition> ::= NULL value representation
<column_assignment> ::=
<column_name> '<literal>'
|
<column_name> <column_function>
<column_name> ::=
<identifier>
<column_function> ::= importing constants
and special constants
<column_name> |
Name of column See the SQL Reference
Manual, |
HEX |
You can define HEX values. |
SCALE <unsigned_integer> |
The scaling factor unsigned_integer can be positive or negative. The value to which the syntax rule refers is multiplied by the corresponding power of ten. See the
SQL
Reference Manual, |
ROUND <unsigned_integer> |
Number of unsigned_integer decimal places The value must be between 0 and 18. This function does not have any effect if the number does not have any decimal places. |
<column_name> '<literal>' |
Specification of a literal as a column value See the
SQL
Reference Manual, |
Examples
In the Loader Tutorial
sections 
IMPORT TABLE, IMPORT
COLUMNS, UPDATE COLUMNS and
in the following sections
Explanation
In a command for importing or updating application data (IMPORT TABLE, IMPORT COLUMNS, UPDATE COLUMNS), you use the import_column_spec syntax rule to describe the data records from a data stream that are to be imported. You assign the data fields in the data stream to the columns in the target table and specify the external data types as well as the conditions for importing the data. Specifications such as column name and position description are also used in the descriptions of output columns.
IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
cno 1
title 2
firstname 3
name 4
zip 5
address 6
IMPORT TABLE hotel.reservation
DATA INSTREAM 'reservation_fwv_binary.data' FWV BINARY
rno 01-04 INTEGER
cno 05-08 INTEGER
hno 09-12 INTEGER
type 13-18 CHAR
arrival 19-28 CHAR
departure 29-38 CHAR
Data must exist in the data stream for every column that you specify in the import command.
If you do not specify columns in the target table in the command, the entire column is populated with the default value defined for this column during the import operation. The NULL value is imported if no specific default value is defined for the column.
IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
cno 1
name 4
address 6
The values for the TITLE, FIRSTNAME and ZIP columns are populated with the default values during the import operation.
Key columns and mandatory columns (columns that are defined as NOT NULL without a default value) must be specified in the import command. Otherwise, processing terminates with an SQL error.
IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
name 4
zip 5
address 6
The command is terminated with an SQL error, as the column CNO is missing.
If you do not specify any columns for the target table in the import command, the table is imported as if all columns in the target table were specified in the command. If this is the case, data must exist in the data stream for all of the columns in the target table.
IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
Data must exist in the source file for all of the columns in the data stream.
<map_field_column>
In a command for importing or updating application data, use the map_field_column syntax rule to assign a data field in the data stream to a column in the target table.
To do so, you specify the column name, the position, and the external data type of the data in the data stream.
IMPORT TABLE hotel.reservation
DATA INSTREAM FILE 'reservation.data' FORMATTED BINARY
rno 01-04
INTEGER
cno 05-08
INTEGER
hno 09-12
INTEGER
type 13-18
INTEGER
arrival 19-28 CHAR
departure 29-38
CHAR
<field_function>
In a command for importing or exporting application data, use the field_function syntax rule to specify the data fields in the data stream (external data type, position, and so on).
You can use the syntax rule numerical_function to scale (scale_spec), round and/or truncate (round_or_trunc_spec) numeric data values when you import data from the data stream to the target table or export data from the source table to the data stream.
Note that you must always specify scale_spec before round_or_trunc_spec.
EXPORT COLUMNS * FROM hotel.room
MAP COLUMNS
hno 1
type 2
free 3
price 4 SCALE -1 TRUNC 2
DATA OUTSTREAM 'room_csv.data'
You use the syntax rule round_or_trunc_spec to specify the number of decimal places for a number.
●
ROUND <unsigned_integer>
The value is rounded off at the (<unsigned_integer>+1) th decimal place. If this number
is >= 5, the value is rounded up. If it is < 5, the value is rounded
down. The result is a number in which the (<unsigned_integer>+1)th and all subsequent decimal places are equal to
0. However, the first digits in the number may have been changed by
rounding.
●
TRUNC <unsigned_integer>
The (<unsigned_integer>+1)th and all subsequent decimal
places of the value are set to 0. The first <unsigned_integer> decimal places remain unchanged.
<column_assignment>
You can use the column_assignment syntax rule to define that the specified general or special constants are imported into or updated in the specified column instead of the corresponding value from the data stream.
For more information, see the section: Importing Constants and Special Constants.
If the data stream is empty, the constants specified in the command are not imported.
See also: