Use the SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system
procedure to import data to a subset of columns in 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.
Syntax
SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE (
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)
)
The import utility looks in the main import file for a reference
to the location of the LOB data.
- SCHEMANAME
- Specifies the schema of the table. You can specify a NULL value to use
the default schema name. The SCHEMANAME parameter takes
an input argument that is a VARCHAR (128) data type.
- TABLENAME
- 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.
- INSERTCOLUMNS
- Specifies the comma separated column names of the table into which the
data will be imported. You can specify a NULL value to import into all columns
of the table. The INSERTCOLUMNS parameter takes an input
argument that is a VARCHAR (32672) data type.
- COLUMNINDEXES
- Specifies the comma separated column indexes (numbered from one) of the
input data fields that will be imported. You can specify a NULL value to use
all input data fields in the file. The COLUMNINDEXES parameter
takes an input argument that is a VARCHAR (32762) data type.
- FILENAME
- 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.
- COLUMNDELIMITER
- 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.
- CHARACTERDELIMITER
- 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.
- CODESET
- 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.
- REPLACE
- A non-zero value for the replace parameter will import in REPLACE mode,
while a zero value will import in INSERT mode. REPLACE mode deletes all existing
data from the table by truncating the table and inserts the imported data.
The table definition and the index definitions are not changed. You can only
import with REPLACE mode if the table already exists. INSERT mode adds the
imported data to the table without changing the existing table data. Specifying
a NULL value results in an error. The REPLACE parameter
takes an input argument that is a SMALLINT data type.
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
This procedure will read the LOB data using
the reference that is stored in the main import file. The format of the reference
to the LOB stored in the main import file must be lobsFileName.Offset.length/.
- Offset is position in the external file in bytes
- length is the size of the LOB column data in bytes
For additional information on using this procedure see the section
"Using the bulk import and export procedures" in the Derby Tools and Utilities Guide.
Example importing data into specific columns, using a separate
import file for the LOB data
The following example shows how to import
data into several columns of the STAFF table. The STAFF table includes a LOB
column in a sample database. The import file
staff.del is
a delimited data file. The
staff.del file contains references
that point to a separate file which contains the LOB data. The data in the
import file is formatted using double quotation marks (") as the string delimiter
and a comma (,) as the column delimiter. The data will be appended to the
existing data in the STAFF table.
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
(null, 'STAFF', 'NAME,DEPT,SALARY,PICTURE', '2,3,4,6',
'c:\data\staff.del', ',','"','UTF-8', 0);