TEMP_EXTRACT_NAMEn Options

Specifies the names of the output files or named pipes used by the data extraction facility. There are eight options: TEMP_EXTRACT_NAME1 through TEMP_EXTRACT_NAME8.

Allowed Values

string

Default

'' (the empty string)

Scope

Can be set for an individual connection. Takes effect immediately.

Description

TEMP_EXTRACT_NAME1 through TEMP_EXTRACT_NAME8 specify the names of the output files used by the data extraction facility. You must use these options sequentially. For example, TEMP_EXTRACT_NAME3 has no effect unless both the options TEMP_EXTRACT_NAME1 and TEMP_EXTRACT_NAME2 are already set.

The most important of these options is TEMP_EXTRACT_NAME1. If TEMP_EXTRACT_NAME1 is set to its default setting (the empty string ''), extraction is disabled and no output is redirected. To enable extraction, set TEMP_EXTRACT_NAME1 to a path name. Extract starts extracting into a file with that name. Choose a path name to a file that is not otherwise in use. Sybase recommends setting the TEMP_EXTRACT_NAME1 option as TEMPORARY.

You can also use TEMP_EXTRACT_NAME1 to specify the name of the output file, when the TEMP_EXTRACT_APPEND option is set ON. In this case, before you execute the SELECT statement, set WRITE permission for the user name used to start Sybase IQ (for example, sybase) on the directory or folder containing the named file and on the named file. In append mode, the data extraction facility adds extracted rows to the end of the file and does not overwrite the data that is already in the file. If the output file does not already exist, the data extraction facility creates the file.

Warning! If you choose the path name of an existing file and the TEMP_EXTRACT_APPEND option is set OFF (the default), the file contents are overwritten. This might be what you require if the file is for a weekly report, for example, but not if the file is one of your database files.

The options TEMP_EXTRACT_NAME2 through TEMP_EXTRACT_NAME8 can be used in addition to TEMP_EXTRACT_NAME1 to specify the names of multiple output files.

If you are extracting to a single disk file or a single named pipe, leave the options TEMP_EXTRACT_NAME2 through TEMP_EXTRACT_NAME8 and TEMP_EXTRACT_SIZE1 through TEMP_EXTRACT_SIZE8 at their default values.

When TEMP_EXTRACT_NAME1 is set, you cannot perform these operations:

LOAD, DELETE, INSERT, or INSERT...LOCATION to a table that is the top table in a join
SYNCHRONIZE JOIN INDEX (issued explicitly or executed as part of CREATE JOIN INDEX)
INSERT...SELECT

Also note these restrictions on the data extraction facility:

Extract works only with data stored in the IQ store.
Extract does not work on system tables or cross database joins.
Extract does not work with queries that use user-defined functions or system functions, except for the system functions suser_id() and suser_name().
If you run Interactive SQL with the -q (quiet mode) option and the data extraction commands are in a command file, you must first set and make permanent the Interactive SQL option “Show multiple result sets.” If this option is not set, the output file is not created.

To set the “Show multiple result sets” option, select Tools > Options in the Interactive SQL window, then check the box “Show multiple result sets” and click “Make permanent.”

The directory path specified using the TEMP_EXTRACT_NAMEn options can be overridden with the TEMP_EXTRACT_DIRECTORY option.

For details on the data extraction facility and using the extraction options, see System Administration Guide: Volume 1 > Data Import and Export > Methods of Exporting Data from a Database > Data Extraction Facility.