Loading large object data

LONG BINARY and LONG VARCHAR data can be loaded using extended syntax of the LOAD TABLE statement. You can specify a secondary load file in the primary load file. Each individual secondary data file contains exactly one LONG BINARY or LONG VARCHAR cell value.

LOAD [ INTO ] TABLE [ owner ].table-name
... ( column-name load-column-specification [, ...] )
... FROM 'filename-string' [, ...]
... [ QUOTES { ON | OFF } ]
... ESCAPES OFF
... [ FORMAT { 'ascii' | 'binary' } ]
... [ DELIMITED BY 'string' ]
... 

load-column-specification:
... 
| { BINARY | ASCII } FILE( integer )
| { BINARY | ASCII } FILE ( ‘string‘ )

The keywords BINARY FILE (for LONG BINARY) or ASCII FILE (for LONG VARCHAR) specify to the load that the primary input file for the column contains the path of the secondary file (which contains the LONG BINARY or LONG VARCHAR cell value), rather than the LONG BINARY or LONG VARCHAR data itself. Tape devices are not supported for the secondary file. Note that IQ supports loading LONG BINARY and LONG VARCHAR values of length less than or equal to 32767 bytes in the primary load file.

This example shows the SQL statements to create and load a table with LONG BINARY data.

CREATE TABLE ltab (c1 INT, filename CHAR(64),
	ext CHAR(6), lobcol LONG BINARY NULL);

LOAD TABLE ltab (
	c1,
	filename,
	ext NULL(‘NULL’),
	lobcol BINARY FILE (‘,’) NULL(‘NULL’)
)
FROM ‘abc.inp’
QUOTES OFF ESCAPES OFF;

The primary file abc.inp contains the following data:

1,boston,jpg,/s1/loads/lobs/boston.jpg,
2,map_of_concord,bmp,/s1/loads/maprs/concord.bmp,
3,zero length test,NULL,,
4,null test,NULL,NULL,

After the LONG BINARY data is loaded into table ltab, the first and second rows for column lobcol contain the contents of files boston.jpg and concord.bmp, respectively. The third and fourth rows contain a zero-length value and NULL, respectively.

The database option SECONDARY_FILE_ERROR allows you to specify the action of the load, if an error occurs while opening or reading from a secondary BINARY FILE or ASCII FILE.

If the option SECONDARY_FILE_ERROR is ON, the load will rollback, if an error occurs while opening or reading from a secondary BINARY FILE or ASCII FILE.

If the option SECONDARY_FILE_ERROR is OFF, the load continues, regardless of any errors that occur while opening or reading from a secondary BINARY FILE or ASCII FILE. The LONG BINARY or LONG VARCHAR cell is left with the following value:

• NULL, if the column allows nulls

• zero-length value, if the column does not allow nulls

The allowed values of the SECONDARY_FILE_ERROR option are ON and OFF. The default value is OFF. This option can be set for the PUBLIC group or temporary by any user and takes effect immediately.

When logging integrity constraint violations to the load error ROW LOG file, the information logged for a LONG BINARY or LONG VARCHAR column is:

• actual text as read from the primary data file, if the logging occurs within the first pass of the load operation

• zero-length value, if the logging occurs within the second pass of the load operation

The LOAD TABLE...STRIP option has no effect on LONG VARCHAR data. Trailing blanks are not stripped from LONG VARCHAR data, even if the STRIP option is ON.

The LOAD TABLE...QUOTES option does not apply to loading LONG BINARY (BLOB) or LONG VARCHAR (CLOB) data from the secondary file, regardless of its setting, A leading or trailing quote is loaded as part of CLOB data. Two consecutive quotes between enclosing quotes are loaded as two consecutive quotes with the QUOTES ON option.

Partial multi-byte LONG VARCHAR data is truncated during the load according to the value of the TRIM_PARTIAL_MBC database option:

• If TRIM_PARTIAL_MBC is ON, a partial multi-byte character is truncated for both primary data and the LOAD with ASCII FILE option.

• If TRIM_PARTIAL_MBC is OFF, the LOAD with ASCII FILE option handles the partial multi-byte character according to the value of the SECONDARY_FILE_ERROR database option.