HP 3000 Manuals HP 3000 Manuals
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]}OutputFileName FROM
{I[NTERNAL]}
{[Owner.]TableName}
{[Owner.]ViewName }ExternalOutputSpec
{"SelectStatement"}
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]]
[REV BEG]
If the output file names does not exist, ISQL
creates a new file.[REV END] 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 |
| | DATE1 |
| | TIME1 |
| | DATETIME1 |
| | INTERVAL1 |
| | 3 = VARCHAR |
| | 4 = FLOAT |
| | 5 = DECIMAL |
| | 6 = TID (tuple identifier) |
| | 8 = NATIVE CHAR2 |
| | 9 = NATIVE VARCHAR2 |
| | 14 = VARBINARY |
| | 15 = LONG BINARY |
| | 16 = LONG VARBINARY |
| | |
| | |
| | 1 DATE, TIME, DATETIME and INTERVAL columns are known to the LOAD |
| | and UNLOAD commands as CHAR data. |
| | |
| | |
| | 2 NATIVE CHAR or VARCHAR is what SQLCore uses internally when a |
| | CHAR or VARCHAR column is defined with a LANG= clause. |
| | |
---------------------------------------------------------------------------------------------
| | |
| 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. |
| | |
---------------------------------------------------------------------------------------------
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 ?;