Exporting data using the built-in procedures
Derby Server and Administration Guide
74
In this case, suppose that you wish to use the existing identity column values from
the input file. To import the data, you may simply pass
null
for the insert column
list and column indexes parameters when you call the procedure. For example:
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', NULL,
NULL, 'empfile.del',null, null, null, 0)
· Suppose (again) that you want to import data into
tab1
from a file,
empfile.del
,
that also has identity column information, but in this case, suppose that you do
not wish to use the identity column values from the input file, but would prefer to
allow Derby to generate new identity column values instead. In this case, to import
the data, you must specify an insert column list without the identity column
c2
and specify the column indexes without identity column data when you call the
procedure. For example:
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4',
'1,3,4', 'empfile.del',null, null, null, 0)
Exporting data using the built-in procedures
You can use the Derby export procedures to export all of the data from table or query, or
to export LOB data separately from the other data.
1. Use the following table to choose the correct procedure for the type of export that
you want to perform. For examples of these procedures, see
Table 11.
Using the built-in export procedures
Type of Export
Procedure to Use
To export all the data
from a table to a single
export file, including the
LOB data
SYSCS_UTIL.SYSCS_EXPORT_TABLE
(IN
SCHEMANAME
VARCHAR(128),
IN
TABLENAME
VARCHAR(128),
IN
FILENAME
VARCHAR(32672),
IN
COLUMNDELIMITER
CHAR(1),
IN
CHARACTERDELIMITER
CHAR(1),
IN
CODESET
VARCHAR(128))
To export all the data
from a table, and place
the LOB data into a
separate export file
SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE
(IN
SCHEMANAME
VARCHAR(128),
IN
TABLENAME
VARCHAR(128),
IN
FILENAME
VARCHAR(32672),
IN
COLUMNDELIMITER
CHAR(1),
IN
CHARACTERDELIMITER
CHAR(1),
IN
CODESET
VARCHAR(128),
IN
LOBSFILNAME
VARCHAR(32672))
A reference to the location of the LOB data is placed
in the LOB column in the main export file.
To export the result of a
SELECT statement to a
single file, including the
LOB data
SYSCS_UTIL.SYSCS_EXPORT_QUERY
(IN
SELECTSTATEMENT
VARCHAR(32672),
IN
FILENAME
VARCHAR(32672),
IN
COLUMNDELIMITER
CHAR(1),
IN
CHARACTERDELIMITER
CHAR(1),
IN
CODESET
VARCHAR(128))
To export the result of
a SELECT statement to
a main export file, and
place the LOB data into a
separate export file
SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
(IN
SELECTSTATEMENT
VARCHAR(32672),
IN
FILENAME
VARCHAR(32672),
IN
COLUMNDELIMITER
CHAR(1),
IN
CHARACTERDELIMITER
CHAR(1),
IN
CODESET
VARCHAR(128),
IN
LOBSFILENAME
VARCHAR(32672))
