Use the SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system
procedure to export the result of a SELECT statement to a main export file,
and place the LOB data into a separate export file. A reference to the location
of the LOB data is placed in the LOB column in the main export file.
For security concerns, and to avoid accidental file damage, this
EXPORT procedure does not export data into an existing file. You must specify
a filename in the EXPORT procedure that does not exist. When you run the
procedure the file is created and the data is exported into the new file.
The
data is exported using a delimited file format.
Syntax
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)
)
When you run this procedure, the column data is written
to the main export file in a delimited data file format.
- SELECTSTATEMENT
- Specifies the SELECT statement query that returns the data to be exported.
Specifying a NULL value will result in an error. The SELECTSTATEMENT parameter
takes an input argument that is a VARCHAR (32672) data type.
- FILENAME
- Specifies the name of a new file to which the data is to be exported.
If the path is omitted, the current working directory is used. If the name
of a file that already exists is specified, the export procedure returns an
error. The specified location of the file should refer to the server-side
location if you are 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
must be 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 export file. The code set name
should be one of the Java-supported character encoding sets. Data is converted
from the database code page to the specified code page before writing to the
file. You can specify a NULL value to write the data in the same code page
as the JVM in which it is being executed. The CODESET parameter
takes an input argument that is a VARCHAR (128) data type.
- LOBSFILENAME
- Specifies the file that the large object data is exported to. If the
path is omitted, the lob file is created in the same directory as the main
export file. If you specify the name of an existing file, the export utility
overwrites the contents of the file. The data is not appended to the file.
If you are using the Network Server, the file should be in a server-side location.
Specifying a NULL value results in an error. The LOBSFILENAME parameter
takes an input argument that is a VARCHAR (32672) data type.
Usage
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 exporting data from a query using a separate export
file for the LOB data
The following example shows how to export employee
data in department 20 from the STAFF table in a sample database to the main
file
staff.del and the lob data to the file
pictures.dat.
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(
'SELECT * FROM STAFF WHERE dept=20',
'c:\data\staff.del', ',' ,'"',
'UTF-8','c:\data\pictures.dat');