Syntax:
Example:
DATAEXTRACT FOR DATALOAD
TABLE customer ;
OUTFILE customer.load ;
OUTFILE customer.data ;
This statement generates a command file that allows a table to be completely restored. In contrast to the simple DATAEXTRACT statement, it is therefore not possible to exclude columns or rows from the table.
The command file contains a CREATE TABLE statement only when the table is a base table and belongs to the executing user. The command file always contains a DATALOAD statement and the complete table contents.
When the option WITH LOCK is specified, the table will be read locked during execution so that no simultaneous modifications can be made to it.
When FASTLOAD is specified, a FASTLOAD statement is generated instead of the usual DATALOAD statement. The FASTLOAD statement contains a USAGE clause. 80 is the default percentage. According to the user's requirements, this percentage can be changed to any value between 50 and 100 by editing the generated command file.
If the order of the table rows in the file is important, e.g., for FASTLOAD, you need to formulate an ORDER BY statement.
As a precaution, table and column names are treated as <special identifier>s in the output file and are enclosed in double quotation marks. This notation is mandatory if a name contains special characters or is identical to an SQL keyword, or if upper- and lowercase characters are to be distinguished.
When two OUTFILE specifications are made, the first file contains the statements, the second one the data. This allows statements to be edited when the table contains BYTE columns or is very large.
File options like ASCII/EBCDIC, DATE format, etc. refer only to the file for which they have been specified. The SET values will be inserted for missing options.
When the COMPRESS option is specified for the generated file of statements, it has the effect that the column names are only output in their actual lengths. When the COMPRESS option is specified for the data, it has the same effect as for a normal DATAEXTRACT statement.
The generated DATALOAD statement contains all the file options so that the user is independent of the current SET statement values for loading.
As the same character string is always used to represent NULL values when extracting data, the generated DATALOAD statement contains DEFAULT NULL conditions for all optional columns. The NULL representation used is recorded as file option NULL '<string>'.
The selected data is written into the file in accordance with the default conventions. An explicit description of the output format (e.g., INTEGER, SCALE specification) is not possible.
If the table is empty, a special DATALOAD statement is generated that contains a selection condition in the format IF POS n-m < > '<literal>'.
A line containing this literal at the specified position is written to the desired data file.
Thus a file having the specified name is created with a separate command and data files, even if a table contains no data. This ensures that a load run is not interrupted because LOAD does not find the specified file.
If the data is written to the file containing the statements, then there is at least one input line included for a DATALOAD statement. This guarantees that the next statement of the command file is not misunderstood as data input.
No data is entered into the table formerly empty or its counterpart, because the input line generated by LOAD does not meet the selection criterion.