Background documentationColumn Description Locate this document in the navigation structure

 

The syntax rules listed here are part of the column description in the Loader commands for importing, exporting and updating application data.

Note Note

If you want to use keywords as column names, you must place them in double quotation marks.

End of the note.

Structure

Syntax Syntax

  1. <export_column_spec> ::=
  <map_column_field>
| <field_assignment>
<import_column_spec> ::=
  <map_field_column> [<null_condition>]
| <column_assignment>
<update_column_spec> ::=
  <map_field_column>
| <field_assignment>

<map_field_column> ::=
  <column_name> <field_spec> <field_function>
<field_function> ::=
  <field_type> [HEX]
| <numerical_functions>
| <field_type> [HEX] <numerical_functions>
<numerical_functions> ::=
  <scale_spec>
| <round_or_trunc_spec>
| <scale_spec> <round_or_trunc_spec>
<scale_spec> ::=
  SCALE <unsigend_integer>
<round_or_trunc_spec> ::=
  ROUND <unsigend_integer>
| TRUNC <unsigend_integer>

<column_assignment> ::=
  <column_name> '<literal>'
| <column_name> <column_function>
End of the code.

In a Loader command for importing or updating application data (IMPORT TABLE, IMPORT COLUMNS, UPDATE COLUMNS), you use the import_column_spec or update_columns_spec syntax rule to describe the data records to be imported from a data stream. 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 (export_column_spec) in the EXPORT COLUMNS commands.

<map_field_column>

In a command for importing or updating application data, you can 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 this, you specify the column name, position, and external data type of the data in the data stream. You can define HEX values. Data must exist in the data stream for each column that you specify in the import command.

Example Example

IMPORT TABLE hotel.customer

DATA INSTREAM 'customer_csv.data'

  cno       1

  title     2

  firstname  3

  name      4

  zip       5

  address    6

End of the example.

Example Example

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

End of the example.

If you do not specify target table columns 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.

Example Example

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.

End of the example.

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.

Example Example

IMPORT TABLE hotel.customer

DATA INSTREAM 'customer_csv.data'

  name      4

  zip       5

  address    6

The IMPORT command is terminated with an SQL error because the column CNO is missing.

End of the example.

If you do not specify any columns for the target table in the import command, the table is imported as if all columns of 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.

Example Example

IMPORT TABLE hotel.customer

DATA INSTREAM 'customer_csv.data'

Data must exist for all of the columns in the data stream.

End of the example.
<field_function>

In a command for exporting or importing application data, you 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_functions to scale (<scale_spec> ::= SCALE <unsigned_integer>: Scaling factor unsigned_integer can be positive or negative; value to which the syntax rule refers is multiplied by the corresponding power of ten), round and/or truncate numeric data values (<round_or_trunc_spec> ::= ROUND/TRUNC <unsigned_integer> : unsigned_integer defines the number of decimal places; value must be between 0 and 18; function does not have any effect if the number does not have any decimal places) when you import data from the data stream to the target table or export data from the source table to the data stream.

Note Note

You always have to specify scale_spec before round_or_trunc_spec.

End of the note.

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 (<unsigend_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 (<unsigend_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 (<unsigend_integer>+1)th and all subsequent decimal places of the value are set to 0. The first unsigned_integer decimal places remain unchanged.

Example Example

EXPORT COLUMNS * FROM hotel.room

MAP COLUMNS

  hno 1

  type 2

  free 3

  price 4 SCALE —1 TRUNC 2

DATA OUTSTREAM 'room_csv.data'

End of the example.
<column_assignment>

You can use the column_assignment syntax rule to define that the specified general or special constants, and not the corresponding value from the data stream, are imported into or updated in the specified column. For more information, see the section: Importing Constants and Special Constants.

Note Note

If the data stream is empty, the constants specified in the command are not imported.

End of the note.

More Information

Data Types

Position Description

Selecting Data Records

Output Column

Data Description

EXPORT COLUMNS Command

IMPORT COLUMNS Command

IMPORT TABLE Command

UPDATE COLUMNS Command

Loader Commands and SQL Statements