Data Extraction Option Examples

These examples describe various data extraction scenarios.

Example: Extracting to a Single Disk File

The following 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 = '';

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

Also note that 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

In this example, 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 of the output from the three SELECT statements is written to the file 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, then the output from the next SELECT statement executed is also added to the end of the file hourly_report.txt.

Example: Extracting to Multiple Disk Files

The following 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, for example 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.