The DATAEXTRACT statement consists of a database query and the OUTFILE description.
Example 1:
DATAEXTRACT
firstname, name, city FROM customer;
OUTFILE cmaster.data
Example 2:
DATAEXTRACT WITH LOCK
name, city FROM hotel
WHERE zip LIKE '9*'
ORDER BY name;
OUTFILE hotel.list
Example 3:
DATAEXTRACT
customer.cno, name, reservation.arrival, price
FROM customer, reservation
WHERE customer.cno = reservation.cno;
OUTFILE cres.data
The database query is formulated in the same way as a SELECT statement in SQL, except that the keyword DATAEXTRACT or DATAEXTRACT WITH LOCK is used instead of SELECT. The query must produce an unnamed result table. All options of the SELECT statement are allowed here:
- selecting the result columns and determining their sequence in the result table,
- joining several tables,
- selecting result rows by using qualifications,
- defining a particular sort sequence.
The query must always end with a semi-colon (;).
If the option WITH LOCK is specified, all tables from which rows are to be selected are read-locked so that other users cannot modify these tables during the extract run.
The name of the target file is specified after OUTFILE. Usually, the target file is a disk file. The DATAEXTRACT statement has the effect that the result table is written to the target file. If the target file already exists, it is completely overwritten; otherwise a new one is created.
The data of the target file can be sent directly to the tape device or printer. For testing purposes, some rows of the result table can be displayed on the screen. The filename specific to the operating system (see the platform-specific User Manual) can be used for selection.
If two OUTFILE descriptions are specified, LOAD generates a DATALOAD statement for the extracted data. The statement will, however, only be executable if only one table was used for data selection and all mandatory columns were included in the SELECT list. As usual, the first filename designates the statement file, the second the data file.