Extract Options

SET OPTION TEMP_EXTRACT_SIZE1 = 1073741824 KB

The most important of these options is TEMP_EXTRACT_NAME1; if it 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. Choose a path and file name that are not otherwise in use. If the file does not already exist, the data extraction facility creates the file.

Both the directory / folder containing the named file, and the named file itself, must have write permission set for the user who started IQ (for example, sybase). 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.

Use the options TEMP_EXTRACT_NAME2 through TEMP_EXTRACT_NAME8 to specify the names of multiple output files. You must use options sequentially. For example, TEMP_EXTRACT_NAME3 has no effect unless both TEMP_EXTRACT_NAME1 and TEMP_EXTRACT_NAME2 are already set.

Use TEMP_EXTRACT_SIZE1 through TEMP_EXTRACT_SIZE8 to specify the maximum size of the corresponding output files. TEMP_EXTRACT_SIZE1 specifies the maximum size of the output file specified by TEMP_EXTRACT_NAME1, TEMP_EXTRACT_SIZE2 specifies the maximum size of the output file specified by TEMP_EXTRACT_NAME2, and so on.

The default minimum for the data extraction size options is 0. IQ converts this default to the following values:

TEMP_EXTRACT_APPEND is incompatible with the TEMP_EXTRACT_SIZEn options. If you try to restrict the size of the extract append output file, SAP Sybase IQ reports an error.

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

Controlling Access

The TEMP_EXTRACT_DIRECTORY option controls whether a user is allowed to use the data extraction facility. It also controls the directory into which temporary extraction files are placed and overrides a directory path specified in the TEMP_EXTRACT_NAMEn options.

If the TEMP_EXTRACT_DIRECTORY option is set to the string FORBIDDEN (case-insensitive) for a user, then that user is not allowed to perform data extracts. Any attempt to do so results in the error: You do not have permission to perform Extracts.

If TEMP_EXTRACT_DIRECTORY is set to FORBIDDEN for the PUBLIC role, no one can run data extraction.

If TEMP_EXTRACT_DIRECTORY is set to a valid directory path, temporary extraction files are placed in the specified directory, overriding paths in the TEMP_EXTRACT_NAMEn options.

If TEMP_EXTRACT_DIRECTORY is set to an invalid directory path, an error occurs: File does not exist File: <invalid path>.

If TEMP_EXTRACT_DIRECTORY is blank, then temp extract files are placed in directories according to their specification in TEMP_EXTRACT_NAMEn. If no path is specified as part of TEMP_EXTRACT_NAMEn, the extract files are, by default, placed in the server startup directory.

The TEMP_EXTRACT_DIRECTORY option provides increased security and helps control disk management by restricting the creation of large data extraction files to the directories for which a user has write access. Setting the option requires the SET ANY SYSTEM OPTION system privilege. This option takes effect immediately.

Types of Extraction

A binary extraction produces a file with an overall "binary" format and a per-column "binary with null byte" format. You can use a LOAD TABLE statement to load the file.

A binary/swap extraction is the same as a binary extraction, except it is designed to be loaded on another machine with opposite endianness.

An ASCII extraction produces a text file.

The two options Temp_Extract_Binary and Temp_Extract_Swap determine which of the three types of extraction is done:

The default extraction type is ASCII.

If the data is unloaded using the extraction facility with the TEMP_EXTRACT_BINARY option ON, you must use the LOAD TABLE statement BINARY WITH NULL BYTE parameter for each column when you load the binary data.

Column and Row Delimiters

The default for the Temp_Extract_Row_Delimiter option is ' ' (an empty string). IQ converts the empty string default for this option to the newline on UNIX platforms and to the carriage return/newline pair on Windows platforms.

The option Temp_Extract_Column_Delimiter controls the delimiter between columns. If this option is set to an empty string for ASCII extractions, the extracted data is written in fixed-width ASCII with no column delimiter. Numeric and binary data types are right-justified on a field of n blanks, where n is the maximum number of bytes needed for any value of that type. Character data types are left-justified on a field of n blanks.

During ASCII extraction, these control the use of quotes:

The quote string specified in the Temp_Extract_Quote option has the same restrictions as delimiters. The default for this option is the empty string, which IQ converts to the single quote mark.

Representation of Null Values

When TEMP_EXTRACT_NULL_AS_EMPTY is set to ON, a null value is represented as ' ' (the empty string) for all data types.

The quotes shown above are not present in the extract output file. When TEMP_EXTRACT_NULL_AS_ZERO and TEMP_EXTRACT_NULL_AS_EMPTY are set to OFF (the default value), the string 'NULL' is used in all cases to represent a NULL value.

If TEMP_EXTRACT_NULL_AS_ZERO is ON, the number of characters that an ASCII extract writes to a file for a CHAR or VARCHAR column equals the number of characters in the column, even if that number is fewer than four.

Message Logging

When the Query_Plan option is ON, a timestamped list of the extracted columns appears in the IQ message log.