Data Extraction Option Examples

There are various data extraction scenarios.

Example: Extracting to a Single Disk File

The statements extract to a single disk file daily_report.txt:

SET TEMPORARY OPTION Temp_Extract_Name1 = 'daily_report.txt';

SET TEMPORARY OPTION Temp_Extract_Name2 = '';

SELECT ....;

SET TEMPORARY OPTION Temp_Extract_Name1 = '';

Temp_Extract_Name2 is set to the empty string before the SELECT statement is executed, restricting output to a single file.

Temp_Extract_Name1 is set to the empty string after the SELECT statement to disable extraction. If extraction is not disabled, then the next SELECT statement executed overwrites the daily_report.txt file.

Example: Extracting in Append Mode

The disk output file hourly_report.txt is already created and has write permission set for the user sybase. The following statements extract to hourly_report.txt, appending the output from each SELECT statement to the end of the file:

SET TEMPORARY OPTION Temp_Extract_Append = ON;
SET TEMPORARY OPTION Temp_Extract_Name1 = 'hourly_report.txt';
SET TEMPORARY OPTION Temp_Extract_Name2 = '';
SELECT ....;
SELECT ....;
SELECT ....;
SET TEMPORARY OPTION Temp_Extract_Name1 = '';

All output from the three SELECT statements is written to hourly_report.txt. Temp_Extract_Name1 is set to the empty string after the last SELECT statement, to disable extraction. If extraction is not disabled, output from the next SELECT statement executed is added to the end of hourly_report.txt.

Example: Extracting to Multiple Disk Files

The statements extract to disk files file1.out, file2.out, and file3.out.

First set the file name options:

SET TEMPORARY OPTION Temp_Extract_Name1 = 'file1.out';
SET TEMPORARY OPTION Temp_Extract_Name2 = 'file2.out';
SET TEMPORARY OPTION Temp_Extract_Name3 = 'file3.out';
SET TEMPORARY OPTION Temp_Extract_Name4 = '';

Now limit the size of the files to 1MB each, by setting the corresponding extract size options:

SET TEMPORARY OPTION Temp_Extract_Size1 = '1024';
SET TEMPORARY OPTION Temp_Extract_Size2 = '1024';
SET TEMPORARY OPTION Temp_Extract_Size3 = '1024';

The size options are in KB (1024 bytes).

With these settings, the extraction output is first written to file1.out. When the next row to be written to file1.out would cause the file size to exceed 1MB, the output is redirected to file2.out. When file2.out is full (writing another row to file2.out would cause the file size to exceed 1MB), the output is redirected to file3.out. An error is reported, if the size of file3.out exceeds 1MB before IQ extracts all rows.