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
.
SyntaxSYSCS_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
for more information. The
database owner can grant access to other users. The user must also have INSERT
privileges on the table.
ExampleThe 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.