Export the results of a SELECT statement to an operating system
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. For example, you
can use this command to export the results of a query as comma-separated values
(a CSV file).
Syntax
SYSCS_UTIL.EXPORT_QUERY(IN SELECTSTATEMENT VARCHAR(32672),
IN FILENAME VARCHAR(32672), IN COLUMNDELIMITER CHAR(1),
IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128))
No result is returned from the procedure.
- SELECTSTATEMENT
- An input argument of type VARCHAR(32672) that specifies the
select statement (query) that will return the data to be exported. Passing a
NULL value will result in an error.
- 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
- An input argument of type CHAR(1) that specifies a column
delimiter. The specified character is used in place of a comma to signal the
end of a column. Passing a NULL value will use the default value; the default
value is a comma (,).
- CHARACTERDELIMITER
- An input argument of type CHAR(1) that specifies a character
delimiter. The specified character is used in place of double quotation marks
to enclose a character string. Passing a NULL value will use the default value;
the default value is a double quotation mark (").
- CODESET
- An input argument of type VARCHAR(128) that specifies the code
set of the data in the exported file. The name of the code set should be one of
the Java-supported character encodings. Data is converted from the database
code set to the specified code set before writing to the file. Passing a NULL
value will write the data in the same code set as the node in which it is being
executed.
Example
The following example shows how to export the information about
employees in Department 20 from the STAFF table in the SAMPLE database to the
myfile.del file.
CALL SYSCS_UTIL.EXPORT_QUERY('select * from staff where dept =20',
'c:/output/awards.del', null, null, null);