NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-120
Considerations—COPY
If you use COPY to write to a nonaudited file or table within a user-defined
transaction, COPY issues a warning but performs the COPY anyway.
You can press the Break key to interrupt COPY. If the target file or table is audited,
the COPY is rolled back and all the work is undone. If the target is nonaudited, all
the work done by COPY up to the point of the break is committed.
If COPY fails and the target table is audited, TMF performs the recovery operation.
If the target table is nonaudited, the data might be partially copied.
•
Position of copied data in target file
COPY does not overwrite existing data. If out-file has key-sequenced file
organization, COPY inserts data at the appropriate locations in the table or file, as
determined by the key. COPY reports an error if a record already exists that has the
same key as a record to be inserted. If out-file has entry-sequenced, relative, or
unstructured file organization, COPY appends data to the end of the file.
•
Access and exclusion mode
When you enter a COPY command without the SHARE option, COPY opens the
input file in read-only access mode and protected exclusion mode (unless the source
file is a terminal, which is always opened with shared exclusion mode). If, however,
you include the SHARE option in the command when copying a disk file, COPY
opens the file with shared exclusion mode.
By default, COPY opens the output file with protected exclusion mode unless the
file is a terminal.
•
Zero-length records and files with relative organization
If you copy a table or file that contains data records and zero-length (empty) records
to a table or file with relative file organization, all records are written with the
following exception: if the in-file also has relative file organization, the empty
records are skipped unless you specify NO COMPACT.
For example, when you copy a table or file with relative file organization that
contains a combination of eight data records and two empty records, out-file
file has eight records instead of ten. Thus, if you copy empty records from a table or
file with relative file organization to another table or file with relative file
organization, you lose the empty records. To transfer empty records from a table or
file with relative file organization, include the NO COMPACT option.
•
Using COPY with SQL tables
If the target is a table, using the COPY command is equivalent to using a set of
INSERT commands with STABLE ACCESS and APPEND options. Values are
inserted in the indexes of the table automatically. All the conditions that must be
satisfied for an INSERT must be satisfied for each target row.
When a source column is undefined and the target column is defined with the NO
DEFAULT clause, an error occurs. The source row must supply a value for every
column of the target row that is defined with the NO DEFAULT clause. In addition,
a target row must satisfy any constraint defined on the table in order to be inserted in