Data Types

With most Loader commands for exporting and importing data, you can specify the data type that the relevant field values in the data stream have or should have.

A distinction is made between database-internal and external data types.

?     Internal Database Data Types

Internal data types are the data types defined for the columns in the database table.
For an explanation of all internal database data types, see Data Type (data_type) in the SQL Reference Manual.

?     External Data Types

External data types are the data types into which the Loader can convert internal database data types when it exports application data, or they are the data types that the Loader converts into internal database data types when it imports data.
You can specify the external data type for each field value to be exported or imported in the commands for exporting or importing using syntax element 
field_type.

The specification of the data types tells the Loader how to interpret the data in the data stream.

?     When application data is exported from a table, the internal database data types are converted to the specified external data types and the corresponding column values are exported.

?     When application data is imported into a table, the specified external data types are converted to internal database data types and the corresponding column values of a table are imported.

The external data type and the corresponding internal database data type of the column value in the target table do not have to be identical.

See also:

Column Description

Output Column

Selecting Data Records

Syntax

<field_type> ::= /* empty */ | CHAR | DECIMAL [(<unsigned_integer>)]
| INTEGER | REAL | ZONED [(<unsigned_integer>)]

/* empty */

The data field is read or output as data type CHAR. Every internal database column format in the database can be read and output in CHAR format.

CHAR
(plain text value)

ASCII, UCS2, or UTF8-coded, depending on the computer, max. 254 bytes long

DECIMAL
(binary value)

Packed decimally: one digit per half byte, sign in extreme right half byte, 1 to 10 bytes long, maximum of 18 digits, the position of the decimal point is derived from the table column type

DECIMAL(<unsigned_integer>)
(binary value)

unsigned_integer: number of digits on the right of the decimal point

INTEGER
(binary value)

Binary coded (computer-specific), 1, 2, or 4 bytes long, sign in bit 0, negative values in two's complement notation

REAL
(binary value)

Floating point notation with mantissa and exponent, 4 or 8 bytes long, in computer-specific notation

ZONED
(binary value)

Zoned decimal: /370 zoned data format is permitted, the position of the decimal point is derived from the table column type

ZONED(<unsigned_integer>)
(binary value)

unsigned_integer: number of digits on the right of the decimal point

Examples

IMPORT TABLE hotel.reservation
DATA INSTREAM 'reservation_fwv.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

For the values in the columns RNO, CNO and HNO, the database table RESERVATION requires the internal database data type FIXED.

The IMPORT TABLE command defines the external data type INTEGER for these columns. The data in the columns RNO, CNO and HNO is converted to the internal database data type FIXED when it is imported.

IMPORT TABLE room
DATA
INSTREAM 'room_fwv_binary.data' FWV BINARY
  hno     01-05
  type    06-11
  free    12-19  INTEGER  
HEX
  price   20-27  INTEGER  
HEX

The IMPORT TABLE command defines hexadecimal values FREE and PRICE as the external data type. This is achieved using the additional HEX specification.

Explanation

If no external data type is specified for a field value, the Loader selects the CHAR data type as the default for this field value. Therefore, if the field value does not have the data type CHAR or the data type CHAR is not desired, you must specify the data type.

Binary Values

All numeric, external data types (INTEGER, REAL, DECIMAL, ZONED) are interpreted as binary values. These can be converted to any of the internal data types (FIXED, SMALLINT, INTEGER, FLOAT) used by the database.

?     The Loader always interprets data of the data type INTEGER as signed values. The way in which this data is represented in a data stream can be adjusted using the syntax element int_spec.

?     Data of the data type REAL is coded in a computer-specific format and cannot be transported between different operating systems and computer platforms without being converted again.

Plain Text Values

Field values with the external data type CHAR are plain text values, that is, these values consist of readable characters.

?     Plain text values can conform to different specifications: ASCII, UCS2, or UTF8 (code specification).

?     You can specify the data formats for plain text values that represent the date, time, or a timestamp (date, time and time stamp specification).

You can specify these data formats in import and export commands as a processing specification for the data stream.

Plain text values can be used as inputs for columns of any internal data type used by the database and converted to them. If you want to import plain text values into numerical columns, the values must be able to be interpreted as numbers.

HEX Values

You can use an external data type with the additional specification HEX (for this, see: Column Description, Output Column) as a hexadecimal value.

This enables you to import or export all data types in hexadecimal form. Every byte of data in the result is then represented by two hexadecimal numbers. Every value in the data stream then occupies twice as much space as the value of the same data type that is not specified as a HEX value.

Converting the Data Types

The Loader can convert between numeric data types, if necessary. You can use the external data type CHAR to read or output any internal database data type.

?     When data is exported from a table, the internal database data types are converted to the specified external data types and the corresponding column values are exported.
The database-internal data types INT[EGER], FIXED and SMALLINT can be transported to the external data types DECIMAL, INTEGER, REAL or ZONED.
The database-internal data type FLOAT can be transferred to the external data types CHAR(ACTER) or REAL.

?     When data is imported into a table, the specified external data types are converted to internal database data types and the corresponding column values of a table are imported.
The external data types DECIMAL, INTEGER, REAL and ZONED can be transferred to the database-internal data types INTEGER, FIXED, FLOAT or SMALLINT.

See also:

Data Stream

Data Format