Home page  
Help >
DDL Command UNLOAD
Version 7.11
DDL Command UNLOAD

UNLOAD

UNLOAD exports data from a Table in the database to an external file. The
"filename" names the external file, the "collection tablename" arguments specify which records to read in order to export them, and "rdname" is the name of a description telling us what the records in the external file are to look like.

Syntax:
                UNLOAD/options collection tablename rdname [(AIname)] filename -
                                                                                                [max-storage] [ERROR=collection]

                Notation standards              Options Answers

Options:
                A             -Append to existing file rather than overwrite it (See Notes).
                B             -Binary data (integer or floating point) is involved in this UNLOAD.
                C             -Count option. Unload the first "n" records only. See Options.
                H             -For DBF files, create new header.
                K             -Create a unique file name.
                M            -Stop after max storage is used.
                O             -Copy MEMO fields as pure ASCII rather than SGML.
                R             -Reverse byte order of binary data types.
                T             -If DOS, output data as UNIX format, if UNIX, out as DOS format.
                X             -Suppress the command response.
                Y             -Do not alter the command answer cells.

Answers:
                Ctl.Ans1                =Number of records unloaded. When the M option is used in
                                                conjunction with a max storage specification, this answer
                                                contains the last record number written.
                Ctl.Ans2                =The number of records with truncation errors.
                Ctl.Ans3                =The name of the output file. If the NUL filename was used,
                                                this answer is the total storage used by the combined
                                                output files, based on a cluster size of 1024. We say
                                                "combined" because if MEMO fields are involved in the
                                                unload, two files are created. .

Notes:
The previously determined isolation in "collection tablename" determines which records are being unloaded.

The filename can be a full path filename. If the filename is the literal "NUL" no actual output occurs, but this operation can be used to predict the size of the output file, which is returned in %answer3 when NUL is used.

If the destination filename does not exist it is created by the UNLOAD execution. If the filename exists, the contents are overwritten by the new content. If the A option is present, the current UNLOAD appends to the end of the existing file, thus growing the file. The A option does not apply to DBF, DIF, or Delimited UNLOADs.

When the K option is used and the specified filename already exists, we will "uniqueify" the file name, create a new file, and output to it rather than over-store the existing file. The new name is reported to you in %answer3.

The "rdname" refers to a Record Description (RD). RDs are defined in an
Application Interface (AI). The RD controls the format of the output file.
For more information about this, see the LOAD command description, and the information in File Import and Export . Remember that all the information contained in these sections about file formats and data transfer apply both ways, that is, for LOAD and UNLOAD.

If the RD that you desire to use for a particular UNLOAD is defined in the currently open AI, then simply refer to its name in the UNLOAD command syntax. If the RD is defined in an AI that is not the currently open one, then you can qualify the rdname with the AI name in which it is defined. The qualifying AI name must be enclosed in parenthesis in the command syntax. For example:

                UNLOAD A CUSTOMERS CUS_REC (MAINT_AI) PEOPLE.DAT

If you wish the output file to be an exact replica of the data as contained in the Table, then you can use an asterisk (*) in place of the RD name. Remember that this short cut is generally not suitable for Data Base Maintenance techniques, especially if you change anything about the Table definition after unloading the data.

If the UNLOAD involves binary data (data types I, ID, F, or FD) you must use the B option! For both LOAD and UNLOAD!

When the C option is used, UNLOAD stops when the number of records specified by the count have been written to the output file.


Unloading MEMO Fields

When you UNLOAD a Table that contains a MEMO field (or fields), and the Record Description that controls the UNLOAD includes the MEMO field (or when the * Record Description is used), then two parallel files are created. The first contains the fixed portion of each record and is named as you specified in the UNLOAD command, the second contains the variable-length MEMO field data and is named the same as specified in the UNLOAD command but with a .TTX extension instead of whatever extension you may have specified. (If the RD has specified a DBF file, then the parallel file gets a
.DBT extension instead of .TTX).

For example, if an UNLOAD involving MEMO fields had specified a file named CUSTDATA.DAT, two files are created, CUSTDATA.DAT and CUSTDATA.TTX.
There are pointers in each record of the first file that locate the memo field data for that record in the second file.


Unloading to DBF Files

When writing to a DBF file, and the DBF file already exists, and if the H option is not specified, then the existing DBF file header is preserved and the output file maintains its dBase II, III, III+, or IV format. This mode also preserves the native DBF field names and field types.

If the specified DBF file does not exist, or if the H option is specified, a new header is created and the resulting DBF file is written in dBase III format, using the fieldnames and types from the Thunderbolt record description.

The ERROR clause

If the ERROR=collection clause is specified, then any records which contained bad dates or had truncation errors will be isolated in the error collection at the completion of the Unload. The error collection must be different from the main collection specified in the command. So the error collection, if specified, contains at the completion of the unload the subset isolation best expressed as "Find me all the records for which errors were detected during the unload."
The error collection is cleared at the beginning of the command so it does not automatically accumulate from one unload to the next.

The M Option and Max Storage

The purpose for the M option and the max storage specification is for when you are unloading to limited-extent media like floppies and there is no way to compute ahead of time how many records you can get on each floppy. This will be the case when the Unload involves MEMO fields. So basically you are telling us to stop the unload after the specified amount of storage has been consumed and report back to you how far we got (see %answer), so you will know where to pick up for the next floppy.

Copyright © 2019 , WhamTech, Inc.  All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. Names may be trademarks of their respective owners.