SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure

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.

Derby 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 Derby Server and Administration Guide.

Syntax

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 LOBSFILENAME VARCHAR(32672)
    ) 

When you run this procedure, the column data is written to the main export file in a delimited data file format.

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 table name of the table or view from which the data is to be exported. This table cannot be a system table or a declared temporary table. The string must exactly match the 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.

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.

If you create a schema, table, or column name as a non-delimited identifier, you must pass the name to the export procedure using all uppercase characters. If you created a schema or table name as a delimited identifier, you must pass the name to the export 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 "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's Guide for more information. The database owner can grant access to other users. The user must also have SELECT privileges on the table.

Example exporting all data from a table, using a separate export file for the LOB data

The following example shows how to export data from the STAFF table in a sample database to the main file staff.del and the LOB export file pictures.dat.

CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(
    'APP', 'STAFF', 'c:\data\staff.del', ',' ,'"', 
    'UTF-8', 'c:\data\pictures.dat');