NULL Value Loads

The most expedient way to insert NULL values is to use the NULL byte in the input file and specify WITH NULL BYTE in the column specification of the LOAD TABLE statement.

This is done by terminating each data field in the input file with “x00” or “x01”. Terminating a data field in the input file with “x01” instructs the load to insert NULL into the column. For example:

create table d1 ( c1 date );
load table d1 ( c1 binary with null byte ) from 'filename' quotes off escapes off format binary;

If the content of the load input file is 000b32cb00000b32cc00, two rows are loaded to the table. The first row is May 7, 2009 and the second May 8, 2009. A NULL byte is added to the input file after each binary date. If you want NULL loaded into the first row, change the value of the NULL byte in the input file to “x01”.

000b32cb01000b32cc00

As another example, to load the value 32769 into a NUMERIC(20) column, the input file contains:

0x0102 0x5000 0x0ad1 0x0003 0x0000 0x00

This includes the NULL byte.

To load 23456789012345678.12 into a column defined as NUMERIC(19,2), the load input file contains:

0x0106 0x4f00 0x04b0 0x162e 0x04d2 0x1ed2 0x0d80 0x0002 0x0000 0x00

The digits are followed by the NULL BYTE ( 0x00 ).

There are seven (numbered 0 – 6) unsigned shorts in the digits array of the structure that represents this numeric quantity. “digits[0]” contains the least-significant digits.

digits[0] = 0x04b0 (decimal 120)
digits[1] = 0x162e (decimal 5678)
digits[2] = 0x04d2 (decimal 1234)
digits[3] = 0x1ed2 (decimal 7890)
digits[4] = 0x0d80 (decimal 3456)
digits[5] = 0x0002 (decimal 2)
digits[6] = 0x0000

The NULL portion of the column specification indicates how to treat certain input values as NULL values, when loading into the table column. These characters can include BLANKS, ZEROS, or any other list of literals you define. When you specify a NULL value or read a NULL value from the source file, the destination column must be able to contain NULLs.

ZEROS is interpreted as follows:

The column is set to NULL if the input data is entirely binary zeros (as opposed to character zeros).

If the input data is character zero:

NULL(ZEROS) never causes the column to be NULL.

NULL('0') causes the column to be NULL. For example:

Load:

CREATE TABLE t1 ( c1 INT, c2 INT );

View the input data file, which uses big-endian byte ordering:

od -x data.inp
3030 3030 0000 04d2

Execute:

LOAD TABLE t1 ( c1 ASCII(4) NULL( '0000' ), 
    c2 BINARY )
FROM 'data.inp'
    FORMAT BINARY
    QUOTES OFF
    ESCAPES OFF;

The results:

SELECT * FROM t1;
c1     c2
NULL   1234

If the input data is binary zero (all bits clear):
- NULL(ZEROS) causes the column to be NULL.
- NULL('0') never causes the column to be NULL, for example:
  Load:
```
CREATE TABLE t1 ( c1 INT, C2 INT ); 
```
  VIEW the input data file, which uses big-endian byte ordering:
```
od -x data.inp
0000 0000 0000 04d2 
```
  Execute:
```
LOAD TABLE t1 ( c1 ASCII(4) NULL( zeros ), 
    c2 BINARY )
FROM 'data.inp' 
    FORMAT BINARY
    QUOTES OFF 
    ESCAPES OFF;
```
  The results:
```
SELECT * FROM T1;
c1     c2
NULL   1234 
```
  As another example, if your LOAD TABLE statement includes col1 date( 'yymmdd' ) null(zeros) and the data to load is 000000, you receive an error indicating that 000000 cannot be converted to a DATE(4). To get LOAD TABLE to insert a NULL value in col1 when the data is 000000, either write the NULL clause as null('000000'), or modify the data to equal binary zeros and use NULL(ZEROS).

Another way to load NULLs during a binary load is not to supply data for the column in the LOAD TABLE statement, if the destination column accepts null values. For example:

CREATE TABLE t1 ( c1 INT, c2 INT );
LOAD TABLE T1 ( c2 BINARY ) FROM 'data.inp'
    FORMAT BINARY 
    QUOTES OFF 
    ESCAPES OFF;

SELECT * FROM T1;
c1     c2
NULL   1234
NULL   1234

View the input data file, which uses big-endian byte ordering:

od -x data.inp
0000 04d2 0000 04d2