SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK system procedure

The SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK system procedure supports all functionalities of SYSCS_UTIL.SYSCS_IMPORT_DATA with the additional feature to skip column headers in the input file and recognize columns in the input file by name that are parsed to the COLUMNINDEXES argument.

Derby issues a COMMIT or a ROLLBACK statement after each import and export procedure is run (a COMMIT if the procedure completes successfully, a ROLLBACK if it fails). For this reason, you should issue either a COMMIT or ROLLBACK statement to complete all transactions and release all table-level locks before you invoke an import or export procedure.

For more information on using this procedure, see the section "Importing and exporting data" in the Derby Server and Administration Guide.

Syntax

SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK (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 SKIP SMALLINT)

No result is returned from the procedure.

SCHEMANAME: An input argument of type VARCHAR(128) that specifies the schema of the table. Passing a NULL value will use the default schema name.
TABLENAME: An 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 will result in an error.
INSERTCOLUMNS: An input argument of type VARCHAR(32672) that specifies the column names (separated by commas) of the table into which the data is to be imported. Passing a NULL value will import the data into all of the columns of the table.
COLUMNINDEXES: An input argument of type VARCHAR(32672) that specifies the indexes (numbered from 1 and separated by commas) and column names (double quoted, case sensitive and seperated by commas) of the input data fields to be imported. The input file columns can only be referenced by the column names if the SKIP argument is greater than 0. Passing a NULL value will use all of the input data fields in the file.
FILENAME: An 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: An 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 will use the default value; the default value is a comma (,).
CHARACTERDELIMITER: An 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 will use the default value; the default value is a double quotation mark (").
CODESET: An 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 will interpret the data file in the same code set as the JVM in which it is being executed.
REPLACE: An input argument of type SMALLINT. A non-zero value will run in REPLACE mode, while a value of zero will run in INSERT mode. REPLACE mode deletes all existing data from the table by truncating the data object, and inserts the imported data. The table definition and the index definitions are not changed. You can only use the REPLACE mode if the table exists. INSERT mode adds the imported data to the table without changing the existing table data. Passing a NULL will result in an error.
SKIP: An input argument of type SMALLINT. SKIP number of header lines will be ignored and rest of lines in the input file will be imported to the table

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.

Execute privileges

If authentication and SQL authorization are both enabled, only the database owner has execute privileges on this procedure by default. See "Configuring user authentication" and "Configuring user authorization" in the Derby Security Guide for more information. The database owner can grant access to other users. The user must also have INSERT privileges on the table.

Example

The following examples import data fields from a delimited data file called petlist.del into the PET table ignoring SKIP number of header lines and importing rest of the input file. This examples also show parsing column name to the COLUMNINDEXES argument.

Example 1

This example shows one header line in the input file. Data contained in the input file petlist.del is given below.

Pet Name,Kind of Animal,Age
Rover,Dog,4
Spot,cat,2
Squawky,Parrot,37

CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK
    (NULL, 'PET', NULL, '\"Pet Name\",2,3', 'petlist.del', NULL, NULL, NULL, 0, 1)

This procedure identifies the column by name as given in the example. When data is imported from the input file, first line in the input file is ignored. That is the number of lines given in the SKIP argument. Rest of the lines in the input file are imported. This means that first line

Pet Name,Kind of Animal,Age

is ignored and next three lines,

Rover,Dog,4
Spot,cat,2
Squawky,Parrot,37

are imported.

Example 2

This example shows three header lines in the input file. Data contained in the input file petlist.del is given below.

Pet,Kind,Age
Name,of,
,Animal,
Rover,Dog,4
Spot,cat,2
Squawky,Parrot,37

CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK
(NULL, 'PET', NULL, '1,\"Kind of Animal\",3', 'petlist.del', NULL, NULL, NULL, 0, 3)

When data is imported from the input file, first three lines in the input file are ignored. That is the number of lines given in the SKIP argument. Rest of the lines in the input file are imported. This means that first three lines

Pet,Kind,Age
Name,of,
,Animal,

are ignored and next three lines,

Rover,Dog,4
Spot,cat,2
Squawky,Parrot,37

are imported.