NULL Value Representation 
The Loader is able to process different NULL value representations in the data streams.
You can use the default NULL value representation or specify the representation for a Loader session or for individual Loader commands.
Specifying NULL Value Representation for a Loader Session
You can specify the NULL value representation for the current Loader session using the SET command. This representation remains valid for the Loader session until it is overwritten by a new SET command. If you do not specify a NULL value representation with the SET command, then the Loader's default value (question mark ?) is used.
<SET_null_spec_command> ::= SET <null_spec>
<null_spec> ::= NULL '<literal>'
You can use the SET command to specify the character string literal, with which NULL values are represented in data streams. A NULL value representation can have a maximum length of 20 characters and is of the data type CHAR.
See also:
Reference Manual, 
Literal (literal),
NULL
Value
Specifying NULL Value Representation for the Data Stream
You can specify the current value for NULL value representation in data streams for individual Loader commands. To do this, use syntax element null_spec to define the data format of a data stream. If you do not use syntax element null_spec to specify a NULL value representation for the data stream, then the value set for the Loader session using the SET command is used. If you have not specified a NULL value representation with the SET command, then the Loader's default value (question mark ?) is used.
You can only carry out NULL value representations for data streams with the CSV and FWV formats; NULL value representation is neither necessary nor possible for the formats RECORDS and PAGES.
<csv_or_fwv_format_null_spec> ::= <null_spec>
<null_spec> ::= NULL '<literal>'
You use the null_spec syntax rule to specify the character string literal, with which NULL values are represented in data streams. A NULL value representation can have a maximum length of 20 characters and is of the data type CHAR.
See also:
You want to import the data from the data stream room_cvs.data to the database instance. You want to import the NULL value into the columns of the target table in a separate representation. A SET command was not used.
IMPORT TABLE hotel.room
DATA INSTREAM 'room_csv.data' NULL 'NULL'
The literal NULL was selected for the representation of the NULL value in the room_csv.data data stream.
You want to import the data from the data stream room_cvs.data to the database instance. You want to import the NULL value into the columns of the target table with its default value. A SET command was not used.
IMPORT TABLE hotel.room
DATA INSTREAM 'room_csv.data'
The literal ? was selected for the representation of the NULL value in the room_csv.data data stream.
Specifying NULL Value Representation for a Data Field
The NULL value representation for data streams in the RECORDS or PAGES formats is managed internally by the Loader. The following explanations are therefore not relevant for the EXPORT DB|USER|SCHEMA and IMPORT DB|USER|SCHEMA commands.
Output Columns in EXPORT Commands
You can specify the NULL value representation for individual data fields in EXPORT TABLE or EXPORT COLUMNS commands. You use the syntax element null_assign to specify which value for the data field of the source table is written to the data stream if the value in the source table is a NULL value. If you do not use the syntax element null_assign to specify a NULL value representation, then the value set for the data stream using the null_spec syntax element is also used for the data field. If you have not specified a NULL value representation for the data stream using syntax element null_spec, then the value set for the Loader session using the SET command is used. If you have not specified a NULL value representation with the SET command, then the Loader's default value (question mark ?) is used.
<null_assign> ::= [IF] NULL SET '<literal>'
You can use the null_assign syntax element to specify the character string literal, with which NULL values are represented in data streams. A NULL value representation can have a maximum length of 20 characters and is of the data type CHAR.
If you want to display the NULL value representation in one of the external data types for binary values, it must have a valid numeric format. This means either a floating decimal number in mantissa/exponent representation or a fixed point number with the currently agreed or standard decimal setting.
The generated NULL value representation is written to the same place in the data stream as the actual column value.
● If the NULL value representation is shorter than the length of the value specified by the position (for data streams in the FWB format), the value is filled with blank characters. If the NULL value representation is longer, it is shortened to the specified length, and the Loader generates a warning in the log file.
● If you define a NULL value representation for exporting NOT-NULL columns, it does not cause an error. Where this is the case, the Loader ignores the specifications.
See also:
You want to export the ROOM table from the database instance into the room_csv.data data stream. Some of the columns in the table contain NULL values. A SET command was not used.
EXPORT COLUMNS * FROM hotel.room
MAP COLUMNS
hno 1
type 2
free 3 IF NULL SET '0'
price 4 IF NULL SET 'X'
DATA OUTSTREAM
'room_csv.data'
You use the syntax element null_assign for the FREE and PRICE columns. If a NULL value is found in the column, then the respective NULL value representation is entered into the data stream. The Loader’s default value for NULL value representation (‘?’) is used for all other columns.
You want to export the ROOM table from the database instance into the room_csv.data data stream. Some of the columns in the table contain NULL values. A SET command was not used.
EXPORT COLUMNS * FROM hotel.room
MAP COLUMNS
hno 1
type 2
free 3
price 4
DATA OUTSTREAM 'room_csv.data'
or
EXPORT TABLE hotel.room
DATA OUTSTREAM 'room_csv.data'
As you have not defined NULL value representation for any of the columns in the EXPORT command, but all values have the external data type CHAR, the Loader default NULL value representation ? is used.
You want to export the ROOM table from the database instance into the room_fwv_binary.data data stream. Some of the columns in the table contain NULL values. A SET command was not used.
EXPORT COLUMNS * FROM hotel.room
MAP COLUMNS
hno 01-05
type 06-11
free 12-15 INTEGER
price 16-19 INTEGER
DATA OUTSTREAM 'room_fwv_binary.data' FWV BINARY
As you have not defined a NULL value representation for any of the columns in the EXPORT command, the Loader attempts to use the default NULL value representation (data type CHAR). This causes an error if a numeric external data type has been defined for the columns. The command terminates with an error message.
Column Description in IMPORT and UPDATE Commands
In the IMPORT TABLE, IMPORT COLUMNS and UPDATE COLUMNS commands, you can use the null_condition syntax element to specify the conditions that must be fulfilled for a NULL value to be imported into a column of the target table.
If you specify all the columns in the IMPORT or UPDATE but do not specify a null_condition, then the values are imported from the data stream to the target table as they are in the data stream. In other words, the Loader does not attempt to interpret the individual values as NULL values.
<null_condition> ::= NULL [IF]
<condition>
|
DEFAULT NULL
● NULL [IF] <condition>: before each line from the data stream is imported to (or changed in) the table, the system checks if the condition (condition) formulated for the columns applies. If it does, the NULL value is inserted in this table column. If not, the value from the assigned field in the data stream is inserted. When the condition is evaluated, the shorter of the comparison values (value in the data stream or the NULL value representation) is filled with blank characters to make the comparison possible.
● DEFAULT NULL: before each line from the data stream is imported (or changed), the system checks if the value in the data stream for the column matches the Loader’s default NULL value. If it does, the NULL value is inserted in this column. If not, the value from the assigned field in the data stream is inserted. When the values are compared, the shorter of the comparison values (value in the data stream or the NULL value representation) is filled with blank characters to make the comparison possible.
If you use DEFAULT NULL, the Loader’s default NULL value is determined as follows: the value set for the data stream via syntax element null_spec is used as the NULL value. If you have not specified a NULL value representation for the data stream using syntax element null_spec, then the value set for the Loader session using the SET command is used. If you have not specified a NULL value representation with the SET command, then the Loader's default value (question mark ?) is used.
You cannot set a NULL value for columns defined as key columns (KEY) or as NOT NULL. If you import or change the table, the action terminates and the relevant error message is displayed.
In columns that you have defined as NOT NULL DEFAULT <value>, the Loader inserts a NULL value instead of the DEFAULT value.
See also:
You want to import the data from the room_cvs.data data stream to the database. In some of the target table’s columns, you want the NULL value to be imported if an explicitly specified condition is fulfilled.
IMPORT TABLE hotel.room
DATA INSTREAM 'room_csv.data'
hno 1
type 2
free 3 NULL IF POS 3 = '0'
price 4 NULL IF POS 4 <> 'XXXX'
You specify a separate condition for the specified data field for each FREE and PRICE column. The NULL value is entered into the corresponding table column if this condition is met.
You want to import the data from the customer_csv.data data stream to the database. In some of the target table's columns, a NULL value is to be imported if an explicitly specified condition is fulfilled. In other columns, the NULL value is only to be imported if the Loader’s default NULL value representation is found. A SET command was not used.
IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
cno 1
title 2
DEFAULT NULL
firstname 3 NULL IF POS 3 = 'XXXX'
name 4
zip 5 DEFAULT NULL
address 6
The condition for the TITLE and ZIP columns is that the NULL value will be inserted in the corresponding table column if the Loader’s default NULL representation (?) is found in the data stream. A separate condition was defined for the FIRSTNAME column for the values in the data stream. If this condition is fulfilled, the NULL values are inserted into the corresponding table column.
You want to import the data from the data stream room_cvs.data into the database. You want to import the NULL value into certain columns of the target table. A SET command was not used.
IMPORT TABLE hotel.room
DATA INSTREAM 'room_csv.data'
hno 1
type 2
free 3 DEFAULT NULL
price 4 DEFAULT NULL
The same representation of the NULL value in the data stream (?) applies to all columns in the IMPORT command where DEFAULT NULL is specified. This is the default value for NULL value representation in this case. The condition for the FREE and PRICE columns is that the NULL value will be inserted in the corresponding table column if the Loader’s NULL representation (?) is found in the data stream.
You want to import the data from the data stream room_cvs.data to the database instance. You want to import the NULL value into some columns of the target table in a separate representation.
IMPORT TABLE
hotel.room
DATA INSTREAM 'room_csv.data' NULL 'NULL'
hno 1
type 2
free 3 DEFAULT NULL
price 4 DEFAULT NULL
The same representation of the NULL value in the data stream (the literal NULL) applies to all columns in the IMPORT command where DEFAULT NULL is specified. The condition for the FREE and PRICE columns is that the NULL value will be inserted in the corresponding table column if the Loader’s NULL representation (NULL) is found in the data stream.