SYSCS_UTIL.IMPORT_DATA_EX

An extended version of SYSCS_UTIL.IMPORT_DATA that enables you to import data into specific columns without locking the target table. Use this procedure during long import operations when you require continued access to the table.

Syntax

Note: 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 constraints.
SYSCS_UTIL.IMPORT_DATA (IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128), IN INSERTCOLUMNS VARCHAR(32672),
IN COLUMNINDEXES VARCHAR(32672), 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))

No result is returned from the procedure.

SCHEMANAME
Input argument of type VARCHAR(128) that specifies the schema of the table.
TABLENAME
Input argument of type VARCHAR (128) that specifies the table name of the table into which the data is to be imported. This table cannot be a system table or a declared temporary table. Passing a null results in an error.
INSERTCOLUMNS
Input argument of type VARCHAR (32762) that specifies the column names (separated by commas) of the table into which the data is to be imported. Passing a NULL value imports the data into all columns of the table.
COLUMNINDEXES
Input argument of type VARCHAR (32762) that specifies the indexes (numbered from 1 and separated by commas) of the input data fields to be imported. Passing a NULL value uses all input data fields in the file.
FILENAME
Input argument of type VARCHAR(32672) that specifies the file that contains the data to be imported. If you do not specify a path, the current working directory is used. Passing a NULL value will result in an error.
COLUMNDELIMITER
Input argument of type CHAR(1) that specifies a column delimiter. The specified character is used in place of a comma to signal the end of a column. Passing a NULL value uses the default value; the default value is a comma (,).
CHARACTERDELIMITER
Input argument of type CHAR(1) that specifies a character delimiter. The specified character is used in place of double quotation marks to enclose a character string. Passing a NULL value uses the default value; the default value is a double quotation mark (").
CODESET
Input argument of type VARCHAR(128) that specifies the code set of the data in the input file. The name of the code set should be one of the Java-supported character encodings. Data is converted from the specified code set to the database code set (utf-8). Passing a NULL value interprets the data file in the same code set as the JVM in which it is being executed.
REPLACE
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.
Note: 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.
LOCKTABLE
Input argument of type SMALLINT. With a non-zero value, this procedure operates similarly to SYSCS_UTIL.IMPORT_TABLE, 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.
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 GemFire XD system.
CASESENSITIVENAMES
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.
IMPORTCLASSNAME
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 gfxd 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.
ERRORFILE
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.

Usage

For additional information and example usage for this procedure see Exporting and Importing Bulk Data from Text Files.

Example

The following example imports some of the data fields from a delimited data file called data.del into the APP.STAFF table. The import procedure uses the default import implementation with 2 threads, and does not lock the target table:
CALL SYSCS_UTIL.IMPORT_DATA
    ('APP', 'STAFF', null, '1,3,4', 'data.del', null, null, null, 0, 0, 2, 0, null, null)