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
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. Passing a NULL value uses the default schema name.
- 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
- 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
- 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 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
- 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.
Example
The following example imports some of the data fields from a delimited
data file called
data.del into the
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
(NULL, 'STAFF', null, '1,3,4', 'data.del', null, null, null, 0, 0, 2, 0, null, null)