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, Column Name (column_name)

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, Unsigned Integer (unsigned_integer)

  ROUND <unsigned_integer>
| TRUNC <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, Literal (literal)

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_functionsyntax 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_specto 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:

Output Column

EXPORT COLUMNS Command

IMPORT COLUMNS Command

IMPORT TABLE Command

UPDATE COLUMNS Command