SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE

Use the SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure to import data to a table, where the LOB data is stored in a separate file. The main import file contains all of the other data and a reference to the location of the LOB data.

Syntax

SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE (
   IN SCHEMANAME VARCHAR(128), 
   IN TABLENAME VARCHAR(128), 
   IN FILENAME VARCHAR(32672), 
   IN COLUMNDELIMITER CHAR(1), 
   IN CHARACTERDELIMITER CHAR(1), 
   IN CODESET VARCHAR(128), 
   IN REPLACE SMALLINT)
   IN LOCKTABLE SMALLINT, IN NUMTHREADS INTEGER, IN CASESENSITIVENAMES SMALLINT
   IN IMPORTCLASSNAME VARCHAR(32672), IN ERRORFILE VARCHAR(32672))

The import utility looks in the main import file for a reference to the location of the LOB data.

SCHEMANAME
Specifies the schema of the table. You can specify a NULL value to use the default schema name. The SCHEMANAME parameter takes an input argument that is a VARCHAR (128) data type.
TABLENAME
Specifies the name of the table into which the data is to be imported. This table cannot be a system table or a declared temporary table. The string must exactly match case of the table name. Specifying a NULL value results in an error. The TABLENAME parameter takes an input argument that is a VARCHAR (128) data type.
FILENAME
Specifies the name of the file that contains the data to be imported. If the path is omitted, the current working directory is used. The specified location of the file should refer to the server side location if using the Network Server. Specifying a NULL value results in an error. The FILENAME parameter takes an input argument that is a VARCHAR (32672) data type.
COLUMNDELIMITER
Specifies a column delimiter. The specified character is used in place of a comma to signify the end of a column. You can specify a NULL value to use the default value of a comma. The COLUMNDELIMITER parameter takes an input argument that is a CHAR (1) data type.
CHARACTERDELIMITER
Specifies a character delimiter. The specified character is used in place of double quotation marks to enclose a character string. You can specify a NULL value to use the default value of a double quotation mark. The CHARACTERDELIMITER parameter takes an input argument that is a CHAR (1) data type.
CODESET
Specifies the code set of the data in the input file. The code set name should be one of the Java-supported character encoding sets. Data is converted from the specified code set to the database code set (UTF-8). You can specify a NULL value to interpret the data file in the same code set as the JVM in which it is being executed. The CODESET parameter takes an input argument that is a VARCHAR (128) data type.
REPLACE
SQLFire does not implement this argument; specify any value of type SMALLINT. Imported data is always added (inserted) to the target table without changing the existing table data. Use TRUNCATE TABLE if you want to clear existing table data before importing data.
LOCKTABLE
A input argument of type SMALLINT. With a non-zero value, this procedure operates similar to SYSCS_UTIL.IMPORT_TABLE, and it locks the table during the import operation. Specify a value of zero to prevent SQLFire from locking the table during the import operation.
NUMTHREADS
Specifies the number of threads to use for the import process. Each thread processes a different portion of the file in parallel, and performs a bulk insert to the SQLFire system.
CASESENSITIVENAMES
A input argument of type SMALLINT. With a non-zero value, SQLFire treats the SCHEMANAME and TABLENAME arguments as case-sensitive. With a zero value, SQLFire treats the schema and table names as case insensitive.
IMPORTCLASSNAME
The argument can specify a custom class that extends com.vmware.sqlfire.load.Import. The specified class overrides the default import process, and can perform on-the-fly data conversions from external formats to formats that SQLFire supports. If you specify NULL for this argument, then SQLFire uses the default Import class.
ERRORFILE
This argument is reserved for future use. Always specify NULL for the argument in this release

If you create a schema, table, or column name as a non-delimited identifier, you must pass the name to the import procedure using all uppercase characters. If you created a schema, table, or column name as a delimited identifier, you must pass the name to the import procedure using the same case that was used when it was created.

Usage

This procedure will read the LOB data using the reference that is stored in the main import file. If you are importing from a non-Derby source, the format of the reference to the LOB stored in the main import file must be lobsFileName.Offset.length/.


For additional information on using this procedure see the section "Using the bulk import and export procedures" in the Apache Derby Tools and Utilities Guide.

Example importing data from a main import file that contains references which point to a separate file that contains LOB data

The following example shows how to import data into the STAFF table in a sample database from a delimited data file staff.del. This example defines a comma as the column delimiter. The data will be appended to the existing data in the table. The import procedure uses the default import implementation with 2 threads, and does not lock the target table:

CALL SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE(
    'APP','STAFF','c:\data\staff.del',',','"','UTF-8',0,0,2,0,null,null);