SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure

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.

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 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 utility overwrites the contents of the file; it does not append the information. 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');