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
