Standard representation of the data in the input and output files

This section is only of significance for you if you do not wish to work with readable input or output data. In this case, you describe the individual data formats explicitly using a data type which is compatible with the data type of the corresponding column in the table or you implicitly use the data type of the corresponding column in the table.

You should note that in the case of the standard representation, all values are represented in full according to the data type definition. This also applies to values which are not significant. For example, UNLOAD generates values in the output file for columns, which have the NULL value in the table, according to the rules described in WHEN NULL THEN (see page 249). The only exceptions are values of data type (NATIONAL) CHARACTER VARYING; these have always a significant length (the length field is decisive); accordingly, UNLOAD, for example, only sets NULL values that are to be represented as (NATIONAL) CHARACTER VARYING, to a length field with the content 0 in the absence of the WHEN NULL THEN clause.

It is particularly important to note the special nature of NULL values for the standard representation of the data when the output file of an UNLOAD is to serve as the input file of a subsequent LOAD. Given that NULL values of a certain length are also represented in the case of UNLOAD, it is important to prevent these values from being interpreted as significant in the subsequent LOAD. To do this, you must specify the appropriate WHEN ... THEN NULL constraints for LOAD, unless the table to be used for loading is a CALL-DML table and the affected value is the non-significant attribute value of the corresponding column.

In table 5 you will find the data types available for the load formats (LOAD) and unload formats (UNLOAD) and information on how the corresponding values have to be represented in the input file and what representation SESAM/SQL uses to write these values to the output file for UNLOAD.

You specify the data type in the format descriptions for LOAD and UNLOAD just as you do at the SQL interface (see the “SQL Reference Manual, Part 1: SQL statements”) with the following exceptions:

● You cannot specify the data type FLOAT. You must use REAL or DOUBLE PRECISION instead of FLOAT.

Data type Length of the value in the file

Standard representation in the input and output files CHARACTER max. 32 000 bytes

(256 characters)

Any alphanumeric (EBCDIC) string

CHARACTER VARYING max. 32 000 bytes (32000 characters) preceded by: 2 bytes

(as length field)

Length field (contains the length of the subsequent string in binary representation);

any alphanumeric (EBCDIC) string

NATIONAL CHARACTER

max. 256 bytes (128 code units)

Any Unicode string

NATIONAL CHARACTER VARYING max. 32000 bytes (16000 code units) preceded by: 2 bytes

(as length field)

Length field (contains the length, in bytes, of the subsequent string in binary representation); any Unicode string

NUMERIC max. 31 bytes (number of digits)

Unpacked representation, representation of sign in the first half-byte of the last byte.

Example

Representation of 364 with 3 digits: X'F3 F6 F4' Representation of -364 with 3 digits: X'F3 F6 D4' Representation of 364 with 4 digits: X'F0 F3 F6 F4' DECIMAL max. 16 bytes

([no. of digits/2])

[... ] means “next- largest integer”

Packed representation, representation of sign in the second half-byte of the last byte.

Example

Representation of 364 with 3 digits: X'36 4F' Representation of -364 with 3 digits: X'F3 F6 4D' Representation of 364 with 4 digits: X'00 36 4F' INTEGER 4 bytes Binary representation, representation of the sign in the

first bit (representation of negative numbers as a twos complement).

Example

Representation of 364 as X'00 00 01 6C' Representation of -364 as X'FF FF FE 94'

SMALLINT 2 bytes Binary representation, representation of the sign in the first bit (representation of negative numbers as a twos complement).

Example

Representation of 364 as X'01 6C' Representation of -364 as X'FE 94'

eM ak er V 7 .x v o m 0 9 .0 2 .2 0 1 0 © c o gni ta s Gm b H 20 01- 20 10 2 6 . M a i 2 0 1 4 S tan d 08 :3 9. 39 P fad : P :\ F T S -B S \DB \S E S A M \1 3 0 1 50 0_ S e s a m 8 0 \1 3 0 1 50 4_ s b t2 \e n\ s e s s

REAL 4 bytes Representation according to BS2000 conventions, i.e.: binary representation of the exponent in the first byte with representation of the exponent’s sign in the first bit (representation of negative numbers as a twos complement).

Binary representation of the mantissa in bytes 2 to 4. Representation of the mantissa’s sign in the first bit of byte 2 (representation of negative numbers as a twos complement).

DOUBLE PRECISION

8 bytes Representation according to BS2000 conventions, i.e.: binary representation of the exponent in the first byte with representation of the exponent’s sign in the first bit (representation of negative numbers as a twos complement).

Binary representation of the mantissa in bytes 2 to 8. Representation of the mantissa’s sign in the first bit of byte 2 (representation of negative numbers as a twos complement).

DATE 6 bytes A pair of bytes is used to represent the year, the month, the day and is to be interpreted as SMALLINT.

Example

DATE'1994-06-08' is represented as X'07CA 0006 0008'

TIME(3) 8 bytes A pair of bytes is used to represent the hours, the minutes, the seconds, the milliseconds and is to be interpreted as SMALLINT.

Example

TIME'13:57:19.210' is represented as X'000D 0039 0013 00D2'

TIMESTAMP(3) 14 bytes The first 6 bytes represent the date, the other 8 bytes represent the time.

Data type Length of the value in the file

Standard representation in the input and output files