NULL Value Representation 
Loader can process different NULL value representations in the data streams. You can use the default NULL value representation or specify a representation for a Loader session or for individual Loader commands.
Structure
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, the Loader default value (question mark ?) is used.
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 data type CHAR.
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, 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, the Loader's default value (question mark ?) is used. You can only specify NULL value representations for data streams with CSV and FWV formats; NULL value representation is neither necessary nor possible for the formats PAGES and RECORDS.
Syntax
<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 data type CHAR.
Example
You want to import data from the data stream room_cvs.data to the database. 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.
Example
You want to import data from the data stream room_cvs.data to the database. 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
Loader manages the NULL value representation for data streams in the PAGES or RECORDS formats internally. 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 (Output Column). 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, 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, 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, the Loader's default value (question mark ?) is used.
Syntax
<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 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 current or standard decimal setting. The generated NULL value representation is written to the same location 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 Loader writes a warning in the log file.
If you define a NULL value representation for exporting NOT NULL columns, it does not cause an error. Loader ignores the specifications.
Example
You want to export the ROOM table from the database to 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, the respective NULL value representation is entered into the data stream. Loader’s default value for NULL value representation (question mark ?) is used for all other columns.
Example
You want to export the ROOM table from the database to 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'
Since you have not defined NULL value representation for any of the columns in the EXPORT command, but all values have external data type CHAR, Loader default NULL value representation ? is used.
Example
You want to export the ROOM table from the database to 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
Since you have not defined a NULL value representation for any of the columns in the EXPORT command, 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 Loader 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 (Column Description). If you specify all the columns in the IMPORT or UPDATE but do not specify a null_condition, the values are imported from the data stream to the target table as they are in the data stream. In other words, Loader does not attempt to interpret the individual values as NULL values.
Syntax
<null_condition> ::= NULL [IF] <condition> | DEFAULT NULL
NULL [IF] <condition>: Before each line of the data stream is imported to (or changed in) the table, the system checks if the 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 of 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 default NULL value is determined as follows: the value set for the data stream with 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, 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, the Loader 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. Loader inserts a NULL value instead of the DEFAULT value on columns that you have defined as NOT NULL DEFAULT <value>.
Example
You want to import the data from the room_cvs.data data stream to the database. You want the NULL value to be imported In some columns of the target table if a specific 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.
Example
You want to import data from the customer_csv.data data stream to the database. A NULL value is to be imported in some columns of the target table if an explicitly specified condition is fulfilled. In other columns, the NULL value is only to be imported if the Loader 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 is inserted in the corresponding table column if the Loader 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 in the corresponding table column.
Example
You want to import data from the data stream room_cvs.data into the database. You want to import the NULL value in 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 of 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 is inserted in the corresponding table column if the Loader NULL representation (?) is found in the data stream.
Example
You want to import data from the data stream room_cvs.data to the database. You want to import the NULL value to 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 is inserted in the corresponding table column if the Loader NULL representation (NULL) is found in the data stream.