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.
If you execute this procedure on a connection that sets the skip-constraint-checks
property to true, then GemFire XD uses the PUT INTO
DML syntax instead of INSERT to import the data.
In addition, GemFire XD ignores all primary key, foreign key, and unique
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.
- Specifies the schema of the table. The SCHEMANAME parameter takes an
input argument that is a VARCHAR (128) data type.
- 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.
- 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.
- 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.
- 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.
- 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.
- A input argument of type SMALLINT. A non-zero value runs the procedure in REPLACE mode,
while a value of zero runs the procedure in INSERT mode. REPLACE mode uses
the PUT INTO DML syntax to insert rows without first
checking primary key constraints. The table definition and the index
definitions are not changed. INSERT mode adds the imported data to the table
using the standard INSERT syntax, checking existing primary key values if
necessary. Passing a NULL value causes an error.
If you execute this
procedure over a connection that sets the skip-constraint-checks
property to true, then all inserts
use the PUT INTO
DML syntax regardless of what value
you set for the REPLACE option.
- A input argument of type SMALLINT. With a non-zero value, this
procedure operates similar to
and it locks the table during the import operation. Specify a value of zero to
prevent GemFire XD from locking the table during the import operation.
- 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 GemFire XD system.
- A input argument of type SMALLINT. With a non-zero value, GemFire XD
treats the SCHEMANAME and TABLENAME arguments as case-sensitive. With a zero
value, GemFire XD treats the schema and table names as case insensitive.
- The argument can specify a custom class that extends com.pivotal.gemfirexd.load.Import. You must install
the class to the GemFire XD distributed system using install-jar before you execute this procedure; see
Storing and Loading JAR Files in GemFire XD. The specified class overrides the default import process, and can
perform on-the-fly data conversions from external formats to formats that
GemFire XD supports. If you specify NULL for this argument, then GemFire XD
uses the default Import class.
- Specifies an error file in which the procedure logs any data formatting
errors that may occur during the import operation. Keep in mind that the
import table operation only logs (and then skips) data formatting errors
that occur during the import. An import operation may still terminate before
completion due to system errors or table constraint errors. (You can
optionally skip over constraint errors by setting the skip-constraint-checks connection property.)
- You can use the contents of a generated error file to try to correct and
reapply the failed import operations at a later time.
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.
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
- Offset is
position in the external file in bytes
- length is
the size of the LOB column data in bytes
For additional information and example usage for this procedure see Exporting and Importing Bulk Data from Text Files.
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 APP.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: