UNLOAD

The UNLOAD command copies data from one or more tables into an output file.

Scope

ISQL only.

ISQL Syntax

U [NLOAD] TO { E [XTERNAL] I [NTERNAL]} OutputFileName FROM {[Owner.] TableName [Owner.] ViewName "SelectStatement" } ExternalOutputSpec

Parameters

EXTERNAL

indicates that the output file is a text file; files in this format are intended for use by user programs. This type of unloading allows data to be easily manipulated.

INTERNAL

indicates that the output file is in a format recognizable only by ALLBASE/SQL; files in this format can later be loaded with the LOAD command's INTERNAL option. This format allows data to be loaded faster by ALLBASE/SQL. However, this type of unloading should not be used for migration.

OutputFileName

identifies the output file. The file's name must follow MPE/iX naming conventions:

   FileName[/Lockword][.Group[.Account]]

If the output file names does not exist, ISQL creates a new file. If the file already exists, ISQL uses the existing file, overwriting its current contents. You can use the MPE/iX BUILD and FILE commands to control the size of the output file.

[Owner.]TableName

identifies the table from which you want to unload data. If you specify this option, all columns and rows from the table are unloaded, in the order specified in the table definition.

[Owner.]ViewName

identifies a view to unload from.

SelectStatement

is an ALLBASE/SQL SELECT statement that identifies one or more tables in the DBEnvironment you are using from which you want to unload data. The SelectStatement may specify criteria for limiting columns and rows to be unloaded. Columns are unloaded in the order specified in the select list. The SELECT statement must be enclosed in double quotation marks.

ExternalOutputSpec

describes an EXTERNAL file. The syntax for this option is:

DescriptionFileName { OutputLength [FractionLength] [NullIndicator]} [...]

DescriptionFileName

identifies a description file. The file name follows the same conventions as the name of the output file.

The description file contains at least one line for each column unloaded. The first line describes the first column unloaded, the second line describes the second column unloaded, and so on. Each line contains seven fields. Data in all the fields except the column name field is right-justified; the column name is left-justified.

Column(s)	Contents
1-20	Column name.
25	LOAD/UNLOAD type code (see following list): 0 = SMALLINT & INTEGER 1 = BINARY 2 = CHAR DATE^[3] TIME^[3] DATETIME^[3] INTERVAL^[3] 3 = VARCHAR 4 = FLOAT 5 = DECIMAL 6 = TID (tuple identifier) 8 = NATIVE CHAR^[4] 9 = NATIVE VARCHAR^[4] 14 = VARBINARY 15 = LONG BINARY 16 = LONG VARBINARY
30-40	Output length.
41-50	Fraction length (number of decimal places).
51-60	Starting location of data.
61-70	Starting location of length of VARCHAR data.
75	Null indicator.
^[3]DATE, TIME, DATETIME and INTERVAL columns are known to the LOAD and UNLOAD commands as CHAR data. ^[4]NATIVE CHAR or VARCHAR is what SQLCore uses internally when a CHAR or VARCHAR column is defined with a LANG= clause.

OutputLength

is the number of columns (bytes) to allocate in the output line for data from each column unloaded. Specify OutputLength (and, optionally, FractionLength and NullIndicator) for each column to be unloaded in the order in which they are to be unloaded. For numeric values, allow one additional space for the sign. For float and decimal numbers, allow one extra space for the decimal point.

If the output length is smaller than the actual column length, CHAR and VARCHAR data is truncated and question marks are written instead of numeric data. Fixed-length fields are written to their maximum length; unused space is filled with blanks.

The actual length of VARCHAR data is prefixed to the data as a 10-byte field.

FractionLength

is the number of decimal places to allocate. You must specify a FractionLength for data of type FLOAT or DECIMAL. Conversely, you may not specify this attribute for data of other types.

NullIndicator

is the character to insert in the external file when ISQL encounters a null value. You must specify a NullIndicator for any column that can contain null values. The null indicator can be any one-byte character except a blank, a semicolon, a single or double quotation mark, a minus sign, or the current SET command's ESCAPE character. If you use a slash (/), precede it with the current ISQL escape character. ISQL uses the null indicator to mark a null value in the output file when a given column is null (or empty).

Description

You must establish a DBE session with a CONNECT or a START DBE command before using the UNLOAD command.

You can unload an external file directly to tape. For example:

   isql=>UNLOAD;

   Output file format (i[nternal] or e[xternal])> external;

   Output file name> TAPE;

   TableName or "SelectStatement"> ManufDB.TestData;

   Command in progress.

   Number of rows processed is 12

The description file needs to be a separate file, and cannot be unloaded directly to tape.

If you are unloading to tape and using multiple tapes, you are notified when you reach the end of each tape. At this point, dismount the current tape from the tape drive and mount the next tape. When the new tape is online, enter a Y at the ISQL prompt to continue the UNLOAD process. Alternatively, you can stop at this point by entering N at the prompt. Number the tapes if record order is important. When reloading them, each tape has to be loaded separately.
When unloading has begun, ISQL displays the following message:
Command in progress.
During the unloading, ISQL displays as follows the cumulative number of rows unloaded as it unloads each group of rows; this number does not necessarily equal the number of rows unloaded when you are unloading to an internal file:
Number of rows processed is n
With UNLOAD EXTERNAL, the number of rows equals the number of records. With UNLOAD INTERNAL, the number of records is greater than the number of rows unloaded.

In prompting mode, ISQL prompts you for the output options:

   isql=> UNLOAD;



   Output file format (i[nternal] or e[xternal])>  Enter INTERNAL; or EXTERNAL;.

   Output file name> OutputFileName;

   TableName or "SelectStatement"> Enter Tablename; or "SelectStatement";

UNLOAD INTERNAL may not be used with LONG columns.

When you are unloading to an external file in prompting mode, ISQL also prompts you for a description file name and for information on each column to be unloaded.

   Description file name> FileName;

   Column COLUMNNAME1, Type (Size):

   Output length> OutputLength;

   Fraction length> FractionLength;

   Null indicator> NullIndicator;

   Column COLUMNNAME2, Type (Size):



   .

   .

   .

You are prompted for a fraction length only if the column contains DECIMAL or FLOAT data. You are prompted for a null indicator only if the column allows null values.

When you are prompted for the length of a LONG column in using the UNLOAD EXTERNAL command, respond with the length of the output device, not the size of the LONG data type. The length of the output device is shown as the size of the LONG column in the description that precedes the prompt. Example:
Column c2, Long Binary (46): Output length> 46;
Note that the value 46 is the maximum length of the output device plus the option or options prefixed to it. You may choose a different size if you wish.
Within a SELECTSTATEMENT, if you wish to refer to an object name that was created with double quotes, you must precede each inner quote with an escape character, as in the following example:
UNLOAD TO EXTERNAL EParts FROM "Select * FROM \"PurchDB\".Parts";
To prevent any possible conflict of double-quoted strings, avoid the use of double-quoted object names.
Object names enclosed in double quotes cannot be split across lines.
External files containing LONG columns display only the output device name, not the data. The output device itself will contain the actual data which is selected during the UNLOAD.
In external files, DATE, TIME, DATETIME, and INTERVAL columns appear as characters. Internally, they are stored as binary values, although ISQL returns a code of 2 (CHAR) for them in creating description files.

Authorization

You must have the authority to select from the table(s) named. Refer to the SELECT statement in the ALLBASE/SQL Reference Manual.

Example

   isql=> UNLOAD;



   Output file format (i[nternal] or e[xternal])> e>

   Output file name> EXTD;

   TableName or "SelectStatement"> ManufDB.TestData;

   Description file name> DTD;

   Column BATCHSTAMP, DateTime (23):

   Output length> 24;

   Column TESTDATE, Date (10):

   Output length> 11;

   Null indicator> ?

   Column TESTSTART, Time (8):

   Output length> 9

   Null indicator> ?

   Column TESTEND, Time (8):

   Output length> 9

   Null indicator> ?

   Column LABTIME, Interval (20):

   Output length> 20;

   Null indicator> ?

   Column PASSQTY, Integer:

   Output length> 3

   Null indicator> ?

   Column TESTQTY, Integer:

   Output length> 3

   Null indicator> ?

   Command in progress.

   Number of rows processed is 12



   isql=>

   isql=> LIST FILE EXTD;



   --------------

   |    EXTD    |

   --------------

   1984-06-19 08:45:33.123 1984-06-23 08:12:19 13:23:01       0 05:10:42.000 49 50

   1984-06-14 11:13:15.437 1984-06-17 08:05:02 14:01:27       0 05:56:25.000 47 50

   1984-07-02 14:54:07.984 1984-07-05 14:03:21 19:33:54       0 05:30:33.000 48 50

   1984-07-22 09:06:23.319 1984-07-29 14:01:28 20:16:07       0 06:14:39.000 50 50

   1984-06-19 08:45:33.123 1984-06-27 08:02:29 14:13:31       0 06:11:02.000 49 50

   1984-07-09 16:07:17.394 1984-07-13 08:43:16 13:22:44       0 04:39:28.000 46 50

   1984-07-13 09:25:53.183 1984-07-18 14:07:01 20:03:22       0 05:56:21.000 49 50

   1984-07-15 13:22:13.782 1984-07-22 09:01:48 14:47:02       0 05:45:14.000 50 50

   1984-07-09 16:07:17.394 1984-07-19 08:13:26 13:45:34       0 05:32:08.000 49 50

   1984-07-15 15:32:03.529 1984-07-23 14:02:34 19:56:02       0 05:53:28.000 49 50

   1984-07-25 10:15:58.159 1984-07-30 08:25:11 13:34:22       0 05:09:11.000 48 50

   1984-07-25 10:15:58.159 1984-08-02 08:01:13 14:29:03       0 06:27:50.000 47 50

   1984-08-19 08:45:33.123 1984-08-25 08:12:19 19:30:00       5 04:23:00.090 49 50



   isql=> LIST FILE DTD;



   --------------

   |    DTD     |

   --------------

   BATCHSTAMP           2             24         0         1         0     

   TESTDATE             2             11         0        25         0    ?

   TESTSTART            2              9         0        36         0    ?

   TESTEND              2              9         0        45         0    ?

   LABTIME              2             20         0        54         0    ?

   PASSQTY              0              3         0        75         0    ?

   TESTQTY              0              3         0        78         0    ?

   ENDATA



   isql=> unload;



   Output file format (i[nternal] or e[xternal])> internal;

   Output file name> DTD;

   TableName or "SelectStatement"> ManufDB.TestData;

   Command in progress.

   Number of rows processed is 13



   isql=>

   isql=> UNLOAD TO EXTERNAL EXTD FROM ManufDB.TestData

   > DTD

   > 24

   > 11 ?

   > 9  ?

   > 9  ?

   > 20 ?

   > 3  ?

   > 3  ?;



   Command in progress.

   Number of rows processed is 13



   isql=>

The same command in a script file would look like this:

   UNLOAD TO EXTERNAL EXTD FROM ManufDB.TestData

   DTD 24 11 ? 9 ? 9 ? 20 ? 3 ? 3 ?;