DB2 Server for VSE & VM: Database Services Utility
The DATALOAD command and subcommands are contained on more than one input
record. If, for example, you want to load data into 10 columns of a
table, the first input record contains the DATALOAD command, and the next 10
input records contain the Table Column Identification (TCI)
subcommands.
The DATALOAD command cannot be continued onto a second input record;
it must be completed on a single record. The record immediately
following the DATALOAD command must contain a TCI subcommand.
Format:
>>-DATALOAD--TABLE----(table_name)--+------------------------+--><
'-input_record_id_clause-'
>>-table_column_id_subcommand----------------------------------><
>>-infile_subcommand--+------------------------------------+---><
| .-.------------------. |
| V | |
'----user_data_record---+---ENDDATA--'
input-record-id-clause:
>>-IF POS---(--startpos--+---------+--)----+- = --+--constant--><
'--endpos-' +- <> -+
+- ^= -+
+- < --+
+- > --+
+- <= -+
'- >= -'
table_column_id_subcommand
.-.------------------------------------------------.
V |
|------| parameters |---+-------------------------+---+---------|
'-| null_current_clause |-'
null_current_clause
|--+-NULL--------------+---+-----------------------------+------|
+-CURRENT DATE------+ | |
+-CURRENT TIME------+ '--| parameters |---constant--'
'-CURRENT TIMESTAMP-'
(1)
>>--------INFILE------------------------------------------------>
>-----+-(--*--+----------------------------+---+------------------------+---)--+>
| | .-No--. | | .-Yes--. | |
| '-CONTINUED--(--+-----+---)--' '-LIST--(--+------+---)--' |
| '-Yes-' '-No---' |
| |
'-(--ddname--| option_b |--)---------------------------------------------'
>-----+------------------------+---+-------------------------+-><
'-COMMITCOUNT--(ccount)--' '-RESTARTCOUNT--(rcount)--'
Notes:
- Option B is valid in DB2 Server for VSE only.
option-b:
tape/disk options for DB2 Server for VSE
|--+------------------------+----------------------------------->
| .-2048--. |
'-BLKSZ--(-+-size--+--)--'
>-----+----------------------------------+---------------------->
| .-REWIND---. |
| .-(TAPE)--+-NOREWIND-+--. |
'-PDEV--+-(DASD)----------------+--'
>-----+------------------+---+----------------+-----------------|
'-RECFM--(format)--' '-RECSZ--(size)--'
|
Example:
DATALOAD TABLE(SMITH.ACTIVITY)
ACTNO 1-3
ACTKWD 5-10
ACTDESC 12-21
INFILE(NEWACT) <------- in DB2 Server for VM
INFILE(NEWACT BLKSZ (2048) PDEV (TAPE) NOREWIND RECFM(FB)) <--in DB2 Server for VSE
Authorization: You must have the INSERT and
SELECT &cont. privilege on the tables affected by the
command.
- TABLE (table_name)
- identifies the table (called table_name) to be loaded.
(The table must already exist.) You can further identify the table by
specifying the owner of the table. For more information about
identifying tables see Qualifying Object Names. A synonym cannot be used as
table_name. You can specify a view name instead of a table
name if the view meets the following requirements:
- The view is defined on a single table.
- The view definition includes all NOT NULL columns in the table.
That is, all columns outside of the view definition must permit the insertion
of nulls.
- The view does not contain a column that is a virtual column.
A virtual column is a column of a view that is not derived
directly from a column of a table. For example, view columns defined
with expressions such as BONUS+COMM, PRSTAFF*1.5, or AVG(BONUS) are
virtual columns.
When loading data into a view that was created using the WITH CHECK OPTION
clause, the database manager checks all inserts and updates to the view
against the view definition and rejects them if the row to be inserted or
updated does not conform to the view definition.
- table_name
- identifies the table to be loaded.
- input_record_id_clause
- is optional; it allows you to selectively load records into the
table. Records are used for DATALOAD processing only if they contain
the value specified in the input_record_id_clause.
All input data records are loaded into the table if you omit this
parameter.
If multiple DATALOAD commands are supplied before an INFILE command and an
input_record_id_clause appears on one of them, all the
DATALOAD commands must have the clause. Database Services Utility error
messages are generated and no DATALOAD processing is performed if you break
this rule.
The parameters of input_record_id_clause are:
- startpos
- identifies the starting position in the input record of the identification
value. Position 1 of the input record is the first position of the
logical record. If variable-length input records are used,
startpos 1 to 4 refers to the record length control field. As
a result, startpos 5 refers to the first data position.
- endpos
- identifies the last position of the identification value. If the
value occupies only one position, you need only specify
startpos. Blanks are not allowed between the starting
position, hyphen, or ending position values.
- constant
- identifies the identification value. If an input record contains
this value in the specified location, it is used for loading the specified
table.
The value cannot be continued onto a second input record. It can be
one of the following:
- A character-string constant that satisfies both of these
requirements:
- Must be enclosed in single quotation marks (')
- Has a value of maximum length equal to
endpos-startpos+1.
- A one-position unsigned integer constant (range is 0 to 255)
- A two-position optionally signed integer constant (default is a positive
value)
- A four-position optionally signed integer constant (default is a positive
value).
Notice that one-, two-, or four-position refers to the length the value
occupies in the input record, not the length it occupies in the clause
itself. See Figure 79 through Figure 82 for examples to clarify this
definition.
Figure 79. Character-String Constant Value Used in the Input-Record-Id-Clause
IF POS (20-22) = 'RT1' (1)
IF POS (20-21) ¬= '01'
(1)
To be used for DATALOAD, positions 20 to 22 of the input data record
must contain the character string RT1.
|
Figure 80. One-Position Integer Constant Value Used in the Input_Record_Id_Clause
IF POS (20) = 255 (1)
IF POS (16) ¬= 25
IF POS (35) > 3
(1)
To be used for DATALOAD, positions 20 of the input data record
must contain a hex FF value.
|
Figure 81. Two-Position Integer Constant Value Used in the Input_Record_Id_Clause
IF POS (20-21) = 1 (1)
IF POS (35-36) <> 50
IF POS (5-6) >= +32767
IF POS (3-4) <= 32767
(1)
To be used for DATALOAD, positions 20 through 21 of the input
record must contain a hex 0001 value.
|
Figure 82. Four-Position Integer Constant Value Used in the Input_Record_Id_Clause
IF POS (20-23) = 15 (1)
IF POS (16-19) ¬= 50
IF POS (21-24) >= +2000123563
IF POS (20-23) < 1839107489
(1)
To be used for DATALOAD, positions 20 through 23 of the input
record must contain a hex 0000000F value.
|
Format:
>>-column_name--startpos--| options |---| null_current_clause |-->
>--------------------------------------------------------------><
options
.-.----------------.
V .-CHARacter--. |
|--+---------+-----+------------+--+----------------------------|
'--endpos-' '-data_type--'
|
The next record following the DATALOAD command must contain a Table Column
Identification (TCI) subcommand.
If it does not, the Database Services Utility issues an error message.
TCI subcommands identify the location in the input records of the data for a
table column. Only one TCI subcommand can appear in an input
record. The command parameters must not span input records, and the
column_name, startpos, and endpos parameters
must be specified first in the command and in that order.
The data must be in the same record positions in all records that relate to
the table.
- column_name
- specifies the name of the table column where the input data is to be
stored.
- startpos
- identifies the starting position of the data in each input data
record. Position 1 of the input record corresponds to the first
position of the logical input record. If variable-length input records
are used, startpos 1 to 4 will refer to the record length control
information. As a result, startpos 5 refers to the first data
position.
- endpos
- identifies the end position of the data in each input data record.
You can omit this parameter if the data occupies only one position in the
input record. If you specify this parameter, do not place blanks
between the starting position and the hyphen, or between the hyphen and the
ending position.
- data-type
- identifies whether character, fixed-binary, floating-point, zoned, packed
decimal, or graphic data values are contained in the record positions
specified.
The data type specification can appear either before or after null or current
if both parameters are entered. The data type parameter is optional,
and the default data type is character. The valid data type identifiers
that you can specify are:
- CHAR or CHARACTER
- If the column-type is CHAR, an all-blank input record data field results
in a sequence of blanks being inserted in the table.
If the table column is defined with a column-type of VARCHAR, trailing
blanks are removed from the input record data field before the length of the
field is established. An all-blank input record data field targeted for
a varying-length character column results in a length of 0.
(A sequence of blanks is not inserted in the database.)
If extended DBCS is in effect for a database, character input data can
contain DBCS characters with shift-in and shift-out delimiters, but the
Database Services Utility does not ensure that shift-in and shift-out
delimiters are balanced.
If the table column is defined as numeric (SMALLINT, INTEGER, DECIMAL, or
FLOAT), character (EBCDIC) input data must be in the form of an SQL INTEGER,
DECIMAL, or FLOAT constant. The character input data is then converted
by Database Services Utility processing.
| Note: | If a data field contains an EBCDIC numeric value that is not in the form of
an SQL INTEGER, DECIMAL, or FLOAT constant, (or if it contains an implied
decimal point), the data field can meet the requirements of a DATALOAD ZONED
input data field.
|
The type of representation allowed for numeric values in character (CHAR or
CHARACTER) input data fields depends on the data type of the target
numeric column:
- A value in an SQL INTEGER constant format is valid for a SMALLINT or
INTEGER column.
- A value in an SQL INTEGER or DECIMAL constant format is valid for a
DECIMAL column.
- A value in an SQL INTEGER, DECIMAL, or FLOAT constant format is valid for
a FLOAT column.
The number is converted regardless of its position in the field, but
leading and trailing blanks are ignored. If you specify a data field in
positions 1 to 5 and type a 2-character number in positions 4 and 5, the
number is recognized. In the example below, the TCI subcommand says
that data for ACTNO (which has a SMALLINT data type) is in record positions 1
through 5. Additionally, it identifies that the data within those
record positions is character. Thus, the Database Services Utility must
convert the character data on the input record to a SMALLINT value before it
can insert it into the ACTNO column shown in Figure 83.
Figure 83. Character Input Data Used in Data type-n
The precision and scale represented in the CHAR field targeted for a table
column with a data type of DECIMAL must be less than or equal to the precision
and scale defined for the column. Leading zeros after the optional sign
are ignored; thus, you can code:
+000000011.1
on an input data record, and it fits into a column with a data type of
DECIMAL(3,1).
Character is the default data type for input records.
- DATE
- If the table column is defined with a column type of DATE, input data can
be in one of the following formats:
yyyy-mm-dd (ISO, JIS format)
dd.mm.yyyy (EUR format)
mm/dd/yyyy (USA format)
installation defined (Local format)
where:
yyyy is the year
mm is the month
dd is the day
- Local Date Format
- A database administrator can change the date default format, which is
defined in the SYSTEM.SYSOPTIONS table, from ISO (which is the
system-supplied database default form) to any installation-defined
format. See the DB2 Server for VSE System
Administration, or DB2 Server for VM System Administration manuals for information about installation-defined formats and their
interface.
| Note: | You can omit leading zeros from months and days, but do not replace them with
blanks. For example, 2000-1-1 is valid while
2000- 1- 1 is not.
|
- TIME
- If the table column is defined with a column type of TIME, input data can
be in one of the following formats:
hh:mm AM or hh:mm PM (USA format)
hh.mm[.ss] (ISO, EUR format)
hh:mm[:ss] (JIS format)
installation defined (Local format)
where:
hh is the hour
0 <= hh <= 12 for USA format
0 <= hh <= 24 for ISO, EUR, JIS format
mm is the minutes
ss is the seconds
In the USA time format, you can specify zero in the hh field only for
00:00 a.m.
- Local Time Format
- A database administrator can change the time default format, which is
defined in the SYSTEM.SYSOPTIONS table, from ISO (which is the
system-supplied database default form) to any installation-defined
format. See the DB2 Server for VSE System
Administration or the DB2 Server for VM System Administration manual for information about installation-defined formats and their
interface.
| Note: | Leading zeros can be omitted from hours. The specification of seconds
is optional.
|
- TIMESTAMP
- If the table column is defined with a column type of TIMESTAMP, input data
must be in the following format:
yyyy-mm-dd-hh.mm.ss[.[nnnnnn]]
where
yyyy-mm-dd is the date (see ISO DATE format)
hh.mm.ss is the time (see ISO TIME format)
nnnnnn is the microseconds
Notes:
- Leading zeros can be omitted from the month, day, and hour.
- The microsecond format is optional.
- FIXED or INT or INTEGER
- If the table column is defined with a data type of SMALLINT,
the input can be in a 1-byte or
2-byte binary data field. Table columns defined with a data type of
INTEGER can be loaded from a 1-byte, 2-byte, or 4-byte binary input data
field.
The value ranges for binary input data fields are:
- A 1-byte binary data field can contain an 8-bit binary integer with a
value range of 0 to 255.
- A 2-byte binary data field can contain a 15-bit binary integer with the
value range described for a table column defined with the data type
SMALLINT.
- A 4-byte binary data field can contain a 31-bit binary integer with the
value range described for a table column defined with the data type
INTEGER.
- FLOAT or REAL
- If FLOAT is specified for 4-byte floating-point binary input data, set
startpos and endpos so that (endpos-startpos+1) =
4.
The table column identified must be defined with a data type of REAL or
FLOAT(n) where n is from 1 to 21.
- FLOAT or DOUBLE PRECISION
- If FLOAT is specified for 8-byte floating-point binary input data, set
startpos and endpos so that (endpos-startpos+1) =
8.
The table column identified must be defined with a data type of FLOAT, DOUBLE
PRECISION, or FLOAT(n) where n is from 22 to 53.
| Note: | If 8-byte floating-point binary data is loaded into a 4-byte floating-point
table column, a number of digits of precision are lost.
|
- DECIMAL or DEC (scalevalue)
- If you have packed decimal input data, the table column must be defined
with a DECIMAL data type. The precision of the input decimal data
field value must be equal to or less than the precision of the target decimal
column. The Database Services Utility takes the scale (number of
positions to the right of the implied decimal point) of an input record
decimal data value from the scale of the target column unless you specify the
optional DECIMAL(scalevalue) form of the command parameter.
The optional scalevalue is an integer value (0 through
31) identifying the number of scale positions in the input record decimal data
value. A scalevalue equal to or less than the scale of the
target DECIMAL column is allowed. A Database Services Utility
processing error occurs if the scalevalue is greater than the scale
of the target DECIMAL column.
If the DECIMAL(scalevalue) form of the parameter is used, no
blanks are allowed within the parameter specification.
Columns defined as NUMERIC are treated as DECIMAL data types.
- ZONED (scalevalue)
- If the input record data field has a zoned value, the target table
column must be defined as numeric (SMALLINT, INTEGER, DECIMAL, FLOAT).
See Figure 84 for examples.
Three variations or types of zoned data input are supported (see
description below). The type of zoned data is identified by Database
Services Utility processing. Database Services Utility processing
converts the zoned data to the data type of the target numeric column.
| Note: | If a data field contains an EBCDIC numeric value with an explicit decimal
point, or otherwise does not meet the requirements of a zoned input data
field, the data field can meet the requirements of a DATALOAD character (CHAR)
input data field. DATALOAD character input data is described in this
section.
|
If the zoned field is for a DECIMAL column, it must contain a value with a
precision less than or equal to the precision of the target
DECIMAL column. The Database Services Utility uses the scale (number
of positions to the right of the
implied decimal point) of the target DECIMAL column for the scale of a zoned
value in the input record unless the optional ZONED(scalevalue) form
of the command parameter is specified.
The optional scalevalue is an integer value (0 through
31) identifying the number of scale positions in each input record zoned data
field value. A scalevalue equal to or less than the scale of
the target DECIMAL column is allowed. A Database Services Utility
processing error occurs if scalevalue is greater than the scale of
the target DECIMAL column.
If the ZONED(scalevalue) form of the parameter is used, no blanks
are allowed within the parameter specification. Also,
scalevalue is ignored if the target column is defined as a SMALLINT,
INTEGER, or FLOAT column.
The Database Services Utility zoned data support is based on the definition
of a zoned field that is described in the publication IBM System/370
Principles of Operation manual. It includes support for standard and extended decimal items
(zoned decimal items).
The following three variations of a zoned input data field are
supported:
- A standard zoned data field.
A numeric value within a standard zoned data field has the following
format:
- Each digit of a number is represented by a single byte.
- The 4 high-order bits of each byte are zone bits except for the 4
high-order bits of the low-order byte, which represent the sign of the
number.
- The 4 low-order bits of each byte contain the value of the digit.
The valid zone bit configuration for a standard zoned data field is:
- 1111 (hex F)
The valid plus-sign bit configurations for a standard zoned data field
are:
- 1010 (hex A)
- 1100 (hex C)
- 1110 (hex E)
- 1111 (hex F)
The valid minus-sign bit configurations for a standard zoned data field
are:
- 1011 (hex B)
- 1101 (hex D)
- A zoned field with a leading sign.
A zoned data field value with a leading sign has a format identical to the
zoned data format described above except that the 4 high-order bits of the
high-order byte represent the sign of the number. The 4 high-order bits
of the low-order bytes contain zone bits.
The valid plus-sign bit configuration for a zoned data field with a leading
sign is:
- 1100 (hex C)
The valid minus-sign bit configuration for a zoned data field with a
leading sign is:
- 1101 (hex D)
The valid zone bit configurations for a zoned data field with a leading
sign is:
- 1111 (hex F)
- A zoned field with a trailing sign in a separate
position.
A numeric value within a zoned data field with a trailing sign in a
separate position has a format similar to the zoned data format described
above except that the high-order 4 bits of the high-order and low-order
numeric value bytes contain zone bits. The sign of the numeric data
value is contained in a separate low-order data value byte.
The valid zone bit configuration for the numeric value bytes in a zoned
data field with a trailing sign in a separate position is:
- 1111 (hex F)
A plus-sign is represented in the separate low-order data field position by
an EBCDIC plus (+) sign (hex 4E) or by a blank (hex 40). A minus-sign
is represented in the low-order data field position by an EBCDIC minus
(-) sign (hex 60).
Figure 84. Examples of Valid Zoned Data Input
.----------------.-----------------------------------------------------.
| HEXADECIMAL | DESCRIPTION OF VALUE LOADED INTO NUMERIC COLUMN |
| CONTENTS OF +-----------------------------------------------------+
|FIVE(5) POSITION+-----------.-----------.----------------.------------+
|ZONED DATA FIELD| SMALLINT | INTEGER | DECIMAL(5,2) | FLOAT |
+----------------+-----------+-----------+----------------+------------+
| | | | | |
| (...System/370* Zoned Data Formats...) | | |
| F1F1F1F1A1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| F1F1F1F1B1 | -11111 | -11111 | -111.11 |-1.1111E+04 |
| F1F1F1F1C1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| F1F1F1F1D1 | -11111 | -11111 | -111.11 |-1.1111E+04 |
| F1F1F1F1E1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| F1F1F1F1F1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| | | | | |
| (...COBOL Standard Zoned Data...) | | |
| F1F1F1F1F1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| F1F1F1F1C1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| F1F1F1F1D1 | -11111 | -11111 | -111.11 |-1.1111E+04 |
| | | | | |
| (...COBOL Zoned Data with Leading Sign...) | |
| F1F1F1F1F1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| C1F1F1F1F1 | 11111 | 11111 | 111.11 | 1.1111E+04 |
| D1F1F1F1F1 | -11111 | -11111 | -111.11 |-1.1111E+04 |
| | | | | |
| (...COBOL Zoned Data with Trailing Sign in Separate Position...) |
| F1F1F1F140 | 1111 | 1111 | 11.11 | 1.111E+03 |
| F1F1F1F14E | 1111 | 1111 | 11.11 | 1.111E+03 |
| F1F1F1F160 | -1111 | -1111 | -11.11 | -1.111E+03 |
| | | | | |
| (...Miscellaneous Other Formats Accepted...) | |
| 4040404040 | 0 | 0 | 0 | 0.0E0 |
| 404040F0F2 | 2 | 2 | .02 | 2.0E+00 |
| 4040F24040 | 2 | 2 | .02 | 2.0E+00 |
| 40C1F1F1F1 | 1111 | 1111 | 11.11 | 1.111E+03 |
| 40D1F1F1F1 | -1111 | -1111 | -11.11 | -1.111E+03 |
| 404040F24E | 2 | 2 | .02 | 2.0E+00 |
| F24E404040 | 2 | 2 | .02 | 2.0E+00 |
| 404040F260 | -2 | -2 | -.02 | -2.0E+00 |
| F260404040 | -2 | -2 | -.02 | -2.0E+00 |
'----------------'-----------'-----------'----------------'------------'
- GRAPHIC or GR or G
- If the input field contains double-byte character set (DBCS) data, the
table columns must be
defined with a data type of GRAPHIC, VARGRAPHIC, or long fields.
One DBCS character is contained in 2 data-field bytes. The input
data field must be an even number (2, 4, 6...100, and so
forth) of positions (bytes) in length, or a Database Services Utility
processing error occurs.
The shift-out and shift-in delimiters are optional in the input data
field.
If the first position of the input data field contains a shift-out
delimiter (hex 0E), the last position of the data field must contain a
shift-in delimiter (hex 0F); if it does not, a Database Services Utility
processing error occurs.
If the first position of the input data field does not contain a shift-out
delimiter, no shift-in delimiter is expected.
See Table 9 for a summary of data type conversions.
- null-current-clause
- allows you to specify that a NULL, CURRENT DATE,
CURRENT TIME, or CURRENT TIMESTAMP is to be loaded in place of the input
record data for a table column. To determine when a null or current
value is to be loaded, a comparison is done between two values. The
first value is taken from the input record; you specify the positions of
the input record that contain this value. The second value is specified
in the null or current clause.
No embedded blanks are allowed in the null or current clause within the
left and right parentheses enclosing the startpos and endpos
values. The format of the null or current clause is:
null-current-clause
>>-+-NULL--------------+---------------------------------------->
+-CURRENT DATE------+
+-CURRENT TIME------+
'-CURRENT TIMESTAMP-'
>-----+--------------------------------------------------------------------+>
| .-IF-. |
'-+----+----POS--(--startpos--+---------+--)----+- = --+---constant--'
'--endpos-' +- <> -+
+- ^= -+
+- < --+
+- > --+
+- <= -+
'- >= -'
>--------------------------------------------------------------><
|
where the following is true, as appropriate:
- NULL IF POS is for null columns.
- CURRENT DATE IF POS is for date columns.
- CURRENT TIME IF POS is for time columns.
- CURRENT TIMESTAMP IF POS is for timestamp columns.
These special registers identify the start of a null or current
clause. Note that IF is optional; for example, you can specify the
keyword phrase NULL IF POS or NULL POS.
| Note: | CURRENT TIMEZONE is not supported.
|
When CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP is to be loaded, you
must provide a value for the endpos parameter of the TCI subcommand
so that the correct length of the data field can be loaded. You need a
minimum of 10 bytes for CURRENT DATE, a minimum of 5 bytes for CURRENT TIME,
and a minimum of 19 bytes for CURRENT TIMESTAMP. These values follow
the rules for ISO formats. To ensure that each data field is
sufficiently large to accommodate the maximum value that can be entered for
the field, you should define 10 bytes for CURRENT DATE, 8 bytes for CURRENT
TIME, and 26 bytes for CURRENT TIMESTAMP. Refer to the input data
formats required for date, time, and timestamp.
- startpos
- identifies the starting position in the input data record of the value
that identifies a null or current table-column value. Position 1 of the
input data record is the first position of the logical record. If
variable-length input records are used, startpos 1 to 4 will refer to
the record length control information. As a result, startpos 5
refers to the first data position. The null or current identifier value
positions can be the same as, or different from, those specified for the
associated data field.
- endpos
- identifies the last input data record position of the null or current
table column identification value. If the value occupies only one input
data record position, this parameter is not required.
- constant
- specifies the null or current table column identification value.
The value cannot be continued to a second input record but can be one of the
following:
| Overlapping Column Position Specifications |
|---|
|
The startpos and endpos in the
null-current-clause need not depend on the positions occupied by data
fields in the sequential input file specified in the INFILE subcommand;
however, if the positions of the data fields and the positions specified by
startpos and endpos in the null-current-clause
overlap, data can be overlaid.
During DATALOAD processing, the database manager generates an input buffer
to hold one row of data for the table. When a TCI subcommand is
encountered, one row of the data, either embedded or in the specified input
file, is written to the input buffer. The TCI subcommand then writes
the CURRENT DATE, CURRENT TIME or NULL characters to the buffer as
required. The data from the specified positions in the input buffer is
then written to the table. This process is performed for every row in
the table. If the positions of the data fields and the positions
specified by startpos and endpos in the
null-current-clause overlap, the data in the buffer may be overlaid
and cause unexpected results or errors. The following example
illustrates how data may be overlaid.
CREATE TABLE TIMING
( START_DATE DATE,
START_TIME TIME);
DATALOAD TABLE(TIMING)
START_DATE 3-12 CURRENT DATE IF POS(1-10) = ' '
START_TIME 5-12 CURRENT TIME IF POS(4-11) = ' '
INFILE(*)
-- FIRST 12 COLUMNS ARE BLANK
ENDDATA;
In this example, the START_DATE, START_TIME and the startpos
and endpos of the IF POS clause overlap. The first row of the
embedded data is loaded into the input buffer. The first TCI subcommand
in the DATALOAD command checks column 1 to 10 in the buffer and determines
that POS(1-10) = ' '
is true. The CURRENT DATE is then written into positions 3 to 12 in the
input buffer. The second TCI subcommand checks positions 4 to 11 of the
input buffer; however, positions 3 to 12 contain part of CURRENT
DATE; therefore the IF POS(4-11)
' ' clause is not true.
The data for START_TIME is then taken from column 5 to 12 in the input
buffer when the START_TIME column is written to the TIMING table.
Because positions 5 to 12 in the input buffer were already overwritten by the
first TCI command, those positions now contain part of CURRENT DATE and the
data for START_TIME is not in the correct time format. A syntax
error therefore occurs. |
Figure 85. Character-String Constant Value Used in the Null-Current-Clause
NULL IF POS(20-23) = 'SKIP'
CURRENT DATE IF POS(20-21) ¬= ' '
CURRENT TIME IF POS(20-21) >= '01'
CURRENT TIMESTAMP IF POS(20-21) <> 'XX'
|
Figure 86. One-Position Integer Constant Value Used in the Null-Current-Clause
NULL IF POS(20) = 255
CURRENT DATE IF POS(16) ¬= 25
CURRENT TIME IF POS(35) < 3
CURRENT TIMESTAMP IF POS(50) > 16
|
Figure 87. Two-Position Integer Constant Value Used in the Null-Current-Clause
NULL IF POS (20-21) = 1
CURRENT DATE IF POS (35-36) ¬= 50
CURRENT TIME IF POS (5-6) <= +32767
CURRENT TIMESTAMP IF POS(9-10) >= 116
NULL IF POS (3-4) > -32768
|
Figure 88. Four-Position Integer Constant Value Used in the Null-Current-Clause
NULL IF POS (20-23) = 1
CURRENT DATE IF POS (16-19) ¬= 50
CURRENT TIME IF POS (21-24) <= +2000123563
CURRENT TIMESTAMP IF POS (20-23) >= -1839107489
|
(1)
>>--------INFILE------------------------------------------------>
>-----+-(--*--+----------------------------+---+------------------------+---)--+>
| | .-No--. | | .-Yes--. | |
| '-CONTINUED--(--+-----+---)--' '-LIST--(--+------+---)--' |
| '-Yes-' '-No---' |
| |
'-(--ddname--| option_b |--)---------------------------------------------'
>-----+------------------------+---+-------------------------+-><
'-COMMITCOUNT--(ccount)--' '-RESTARTCOUNT--(rcount)--'
Notes:
- Option B is valid in DB2 Server for VSE only.
|
The INFILE subcommand identifies the sequential input file containing the
data referenced by the preceding DATALOAD and Table Column Identification
subcommands.
This INFILE subcommand not only tells the utility the file the data is in,
but also tells it to read that file and load the data into the table(s)
identified by the previous DATALOAD TABLE command(s).
The sequential input file can contain fixed, variable, or variable-length
spanned records. The records can be blocked or unblocked.
- *
- identifies that input data is embedded within the control statements
immediately following this control statement. Subsequent records are
processed as user data records until an ENDDATA statement is
encountered.
If the (input) control file is exhausted before an ENDDATA statement is
encountered, a Database Services Utility processing error occurs, and the
current logical unit of work is rolled back.
| Note: | The CONTINUED and LIST parameters are applicable only if the * parameter has
been specified.
|
- CONTINUED (No or Yes)
- indicates whether or not the input data that is embedded within the
control
statements can span more than
one (input) control file record. Continued record processing is
supported only for data records embedded within the (input) control file
because data records in sequential tape or DASD are not restricted to a
maximum length of 80 positions. No blanks are allowed between or within
this parameter keyword and value specification.
- No
- indicates that the input data does not span (input) control file
records. Specify either NO or N. This is the default.
- Yes
- indicates that the input data can span (input) control file
records. Specify either YES or Y.
If you specify CONTINUED(YES), the actual input data is constructed from
one or more (input) control file data records. An input data record
with a nonblank value in position 1 indicates that the input data is continued
in the next (input) control file data record. An input data record with
a blank (hex 40) in position 1 indicates that the input data is not continued
in the next (input) control file data record. The first position
(position 1) of each (input) control file data record is not included in the
actual input data. Data for a column can then be contained in more than
one (input) control file data record.
For example, if 10 input control card file data records are required to
contain the data for each row of a table, DATALOAD processing constructs a
single input data record from 10 consecutive input control card file data
records. The relationship between the positions of each of the 10 input
control card file data records and the positions of the actual input data
record is:
Figure 89. Relationship of Data Records
Control File Actual Input
Data Data Record Data Record
Record Positions Positions
1 2-80 1-79
2 2-80 80-158
3 2-80 159-237
4 2-80 238-316
5 2-80 317-395
6 2-80 396-474
7 2-80 475-553
8 2-80 554-632
9 2-80 633-711
10 2-80 712-790
|
The maximum possible length of the input data is calculated from the
highest endpos value specified in any DATALOAD command or TCI
subcommand comprising the DATALOAD command set.
The endpos value specified for an input record data field or the
endpos value specified in an
input-record-id-clause or null or current clause
is included in this consideration. The maximum length of the actual
input data (rounded to the next multiple of 80) is computed by the following
formula:
Figure 90. Formula
Maximum Length highest endpos value + 80
Actual Input = -------------------------------- X 80
Data Record 80
Notes:
- Any continuation records that would cause the actual input data record
length to exceed the length computed by this formula are read and ignored by
DATALOAD processing.
- Actual input data records containing data to be loaded into a table must
be at least as long as the highest endpos value specified in a TCI
subcommand.
- LIST (Yes or No)
- indicates whether or not the input data
that is embedded within the control statements should be displayed in the
report or message file. The LIST parameter is applicable only if the
data records are embedded within the (input) control file.
No blanks are allowed between or within this parameter keyword and value
specification.
- Yes
- indicates that the embedded data records should be displayed in the report
or message file. Specify either YES or Y as the parameter value.
The default is LIST(YES).
- No
- indicates that the embedded data records should not be displayed in the
report or message file. Specify either NO or N as the parameter
value.
If a data field error is detected in an input data record while the
LIST(NO) and CONTINUED(NO) are in effect, the input data record is displayed
in the report or message file before the message describing the error.
If LIST(NO) and CONTINUED(YES) are in effect, no input data is displayed in
the report or message file if a data field error occurs. A meaningful
display of the input data record might not be possible because the data for a
record might span multiple 80-byte data records or the data field in error
might span input data records. Also, it is likely that continued
records contain unprintable data. The commands and input data can be
rerun with LIST(YES) specified if the problem cannot be identified.
- ddname
- in DB2 Server for VM is the name of the sequential input file defined with
a CMS FILEDEF command. The file characteristics specified in the
FILEDEF command or the default FILEDEF specifications are the source of the
input record definition information for the Database Services Utility.
Input files with RECFM U, A, or M are not supported.
If you define DATALOAD CMS input files with variable-length spanned records
(RECFM=VS or RECFM=VBS), you must use the file-mode number
4.
If variable-length input records are used, the data fields referenced by
the TCI subcommands must be in the same position for each occurrence of the
data record type.
Do not specify SYSIN or SYSPRINT as the ddname.
in DB2 Server for VSE: is the TLBL or DLBL job control statement file
name for the sequential (SAM) input file or for SYSIPT if you are using the
READ member statement. For more information, refer to the DB2 Server for VSE Program Directory manual.
You must specify the ddname parameter first; that is, you
cannot specify BLKSZ, PDEV, RECFM, and RECSZ before the
ddname. You can specify the other keyword parameters in any
order.
- BLKSZ (size) (DB2 Server for VSE Only)
- is a parameter that specifies the block size of the sequential input
file. The default block size is 2048 bytes per block.
- PDEV (TAPE or DASD) (DB2 Server for VSE Only)
- is an optional parameter that specifies the device type (DASD or TAPE) of
the sequential (SAM) input file. If PDEV(DASD) is specified, the file
resides on any device supported by the VSE DTFSD macro. Managed SAM
does not support spanned records. If PDEV(TAPE) is specified, the file
resides on any device supported by the VSE DTFMT macro. The default is
PDEV(TAPE).
- NOREWIND or REWIND (DB2 Server for VSE Only)
- controls tape file rewind processing performed during OPEN
processing.
This parameter is valid only if you specify TAPE for PDEV. The default
processing is REWIND.
- NOREWIND (DB2 Server for VSE Only)
- specifies that the tape file will not be rewound by OPEN
processing. If NOREWIND is specified for input tape files referenced by
a series of DATALOAD commands, you must ensure that the tape files being
referenced are in ascending sequence. For example, if NOREWIND is
specified in a sequence of two DATALOAD commands and the first command reads
tape file 2, then the second command must reference tape file 3 or a higher
number. If it references tape file 1, an OPEN error occurs.
- REWIND (DB2 Server for VSE Only)
- specifies OPEN processing to rewind the tape file.
- RECFM (format) (DB2 Server for VSE Only)
- is an optional parameter that specifies the format of the records in the
input data file. For format, substitute one of the following
values:
Value Meaning
F fixed, unblocked
FB fixed, blocked
V variable, unblocked
VB variable, blocked
S variable spanned, unblocked
SB variable spanned, blocked
|
The default is RECFM(F).
If variable-length input records are used, the data fields referenced by
the TCI subcommands must be in the same position for each occurrence of the
data record type.
- RECSZ(size) (DB2 Server for VSE Only)
- is a parameter that specifies the length of a logical record for the input
data file.
Default record size values are specified as follows:
- If RECFM = F or FB, the default record size is the block size.
- If RECFM = V or VB, the default record size is the block size minus
four.
- If RECFM = S or SB, the default record size is the block size minus four
or the highest input record position referenced, whichever is greater.
- COMMITCOUNT (ccount)
- identifies the frequency of COMMIT action during
DATALOAD processing.
- ccount
- is a number from 1 to 2,147,483,647 indicating that a COMMIT statement
should be executed after the number of input data records equal to
ccount are processed by DATALOAD.
Database Services Utility AUTOCOMMIT ON processing must be in effect when
you use DATALOAD COMMITCOUNT processing. If AUTOCOMMIT is OFF and the
COMMITCOUNT parameter is used, an error message is written. DATALOAD
command processing is not performed.
If a SET ERRORMODE CONTINUE command is in effect during DATALOAD
COMMITCOUNT processing, input data records with incorrect data fields might
not be used. The incorrect input records are skipped if:
- Multiple DATALOAD commands were used preceding an INFILE subcommand and
the records were not used for successful inserts by any other DATALOAD
commands.
- An SQL insert error occurs identified by SQLCODE -405, -424, -530, -802,
or -803, followed by message ARI0862E, and insert blocking is not in
effect.
Insert blocking is not in effect under the following conditions:
- Database Services Utility is running with single user mode.
- Database Services Utility is running with multiple user mode, but was
preprocessed with the NOBLOCK option.
- Insert blocking is suppressed by the database manager.
If an invalid ccount value is specified, an error message is
written, and DATALOAD command processing is not performed.
For DATALOAD CONTINUED record processing, the ccount value refers
to the number of physical input data records, not the number of logical
records constructed from input records. A COMMIT statement is performed
when the number of physical input data records processed equals or exceeds the
ccount value.
- RESTARTCOUNT (rcount)
- identifies the restart point for
DATALOAD processing.
- rcount
- is a number from 1 to 2147483647 that indicates the number of input data
records to be skipped before DATALOAD record processing begins.
If this parameter is omitted, no records are skipped and DATALOAD
processing begins with the first input data record.
If an invalid rcount value is specified, an error message is
written and DATALOAD processing is not performed.
If an end-of-file condition occurs before the number of records specified
by the rcount value are read, an error condition exists. Error
message ARI0844E is written to the message file before DATALOAD processing
ends.
For DATALOAD CONTINUED processing, the rcount value refers to the
number of physical input data records, not the number of logical records
constructed from input records. If the rcount+1 input record
is not the first physical record of a set of physical records comprising a
logical record, error message ARI0887E is issued.
Format:
>>-ENDDATA-----------------------------------------------------><
|
The ENDDATA subcommand
identifies the end of user data embedded within the (input) control
file.
This command is valid only if the previous Database Services Utility command
processed was an INFILE(*) subcommand.
No other information is allowed in this subcommand. If ENDDATA is
not alone on the input record, the utility reads it as data. If ENDDATA
is terminated by a semicolon (;), no blanks are permitted between the
keyword and the semicolon. SQL comments are not allowed on the ENDDATA
subcommand.
During CONTINUED(YES) processing, an ENDDATA command is recognized only if
the previous (input) control file data record contains a blank (hex 40) in
position 1. If the previous (input) control file data record contains a
nonblank in position 1, the ENDDATA command is processed as a continuation
data record.
Table 9 summarizes the data conversion performed by
DATALOAD processing.
YES means that the utility performs the conversion; NO means that the
utility cannot convert the input data into the data type of the target
column. The numbers in the chart refer to the notes below.
Table 9. DATALOAD Data Conversion Table
| Input Field Data Type
| Target Column Data Type
|
| CHAR, VAR- CHAR, or LONG VAR- CHAR
| DECIMAL
| SMALL- INT
| INTEGER
| REAL11
| DOUBLE PRECI- SION12
| DATE, TIME or TIME- STAMP13
| DBCS GRAPHIC, VAR- GRAPHIC, or LONG VAR- GRAPHIC
|
|---|
| CHAR
| Yes1,4
| Yes2
| Yes2
| Yes2
| Yes2
| Yes2
| Yes
| No
|
| 1-Byte FIXED
| No
| No
| Yes
| Yes
| No
| No
| No
| No
|
| 2-Byte FIXED
| No
| No
| Yes
| Yes
| No
| No
| No
| No
|
| 4-Byte FIXED
| No
| No
| No
| Yes
| No
| No
| No
| No
|
| 4-Byte FLOAT
| No
| No
| No
| No
| Yes
| Yes10
| No
| No
|
| 8-Byte FLOAT
| No
| No
| No
| No
| Yes9
| Yes
| No
| No
|
| DECIMAL
| No
| Yes3
| No
| No
| No
| No
| No
| No
|
| ZONED
| No
| Yes5,6
| Yes5
| Yes5
| Yes5
| Yes5
| No
| No
|
| GRAPHIC (G)
| No
| No
| No
| No
| No
| No
| No
| Yes7
|
| DATE, TIME, or TIME-STAMP
| No
| No
| No
| No
| No
| No
| Yes8
| No
|
Notes for Table 9:
- Character (CHAR) input data fields for VARCHAR or long field columns have
trailing (low-order) blanks truncated before the length of the varying column
is established. All-blank CHAR input data fields result in a length of
0.
- The first trailing blank after the number within a character (CHAR) input
data field terminates the character string used for character-to-numeric data
conversion. An all-blank CHAR field or a CHAR field with only a sign (+
or -) results in a numeric value of 0. The data can be
in the form of an integer, decimal, or float constant.
- Decimal (DECIMAL) input data fields should contain data with a precision
less than or equal to the precision of the target DECIMAL column. The
Database Services Utility uses the scale defined for the target column for the
input data unless a scale value equal to or less than that defined for the
target column is specified. A Database Services Utility processing
error occurs if the scale specified for the input data field is greater than
that of the target column.
- Character (CHAR) input data fields for CHAR columns are padded with
trailing blanks if they are less than the length of the target column.
CHAR input data fields with a length greater than the length of the target
CHAR, VARCHAR, or long field columns are not allowed.
- Leading and trailing blank positions within a zoned input data field are
ignored. An all-blank zoned input data field results in a numeric
(SMALLINT, INTEGER, DECIMAL, or FLOAT) column value of 0. A
zoned data input field containing only an EBCDIC plus (+) sign or minus
(-) sign is not valid.
- A zoned (ZONED) data input field should contain a value with a precision
less than or equal to the precision of the target DECIMAL column. The
Database Services Utility uses the scale defined for the target column as the
scale for the zoned data value unless a scale value equal to or less than the
scale of the target column is specified. A Database Services Utility
processing error occurs if the scale specified for the input data field is
greater than that of the target column.
- A DBCS input data field must be an even number (2, 4,
6,...100, and so forth) of positions (bytes) in
length.
The shift-out (hex 0E) and shift-in (hex 0F) delimiters can be in the first
position (startpos) and last position ( endpos) of the data
field. A two-position DBCS data field containing only the shift-out and
shift-in delimiter values is treated as a blank input data field.
If the shift-out and shift-in delimiters are present in the data field, the
Database Services Utility treats data field positions startpos+1 to
endpos-1 as DBCS data. The hex value 4040 is treated as
a blank DBCS character.
A DBCS input data field for VARGRAPHIC or a long field has trailing
(low-order) DBCS blank characters truncated before the length of the varying
column is established. An all-blank DBCS input data field results in a
column value with a length = 0 for columns defined with the data
type VARGRAPHIC or long field.
- The datetime data type input and target type must match; for example,
the input data type of TIME is valid only for the target column data type of
TIME.
- When 8-byte floating-point data is loaded into a 4-byte floating-point
table column, a number of digits of precision are lost. The fraction
(mantissa) is reduced from 14 to 6 digits of precision. If an error
occurs during the conversion process, the message ARI0864E is generated, and
processing of the DATALOAD command stops.
- 4-byte floating-point data is padded with hex
0000 0000.
- The REAL input data field represents single-precision
floating-point data and is synonymous with FLOAT(N), where 1 is less
than or equal to N, and N is less than or equal to 21.
- DOUBLE PRECISION represents double-precision floating-point
data and is synonymous with FLOAT or FLOAT(N), where 22 is less than or equal
to N, and N is less than or equal to 53.
- When a current date, current time, or current timestamp value is to be
loaded, you must provide a value for the endpos parameter of the TCI
subcommand so that the correct length of the data field can be loaded.
You need a minimum of 10 bytes for current date, a minimum of 5 bytes for
current time, and a minimum of 19 bytes for current timestamp. These
values follow the rules for ISO formats. To ensure that each data field
is sufficiently large to accommodate the maximum value that can be entered for
the field, you should define 10 bytes for current date, 8 bytes for current
time, and 26 bytes for current timestamp. Refer to the input data
formats required for date, time, and timestamp.
Format:
>>-DATAUNLOAD--------------------------------------------------><
>>-select_statement--;-----------------------------------------><
>>-+--------------------------------------+--------------------><
| .-,------------------------------. |
| V | |
'----| data_field_id_subcommand |---+--'
>>-OUTFILE--(--ddname--| option_a |--)-------------------------><
data_field_id subcommand (DFI)
>>-+-column_reference-+--startpos--+---------+------------------>
'-integer----------' '--endpos-'
.-CHARacter--.
>-----+------------+---+---------------------+-----------------><
'-data_type--' '-| set_null_clause |-'
set_null_clause
.-IF-. .-SET-.
>>-+----+--NULL--+-----+--POS--(--startpos--+---------+--) =--value-->
'--endpos-'
>--------------------------------------------------------------><
data_field_id subcommand (DFI)
set_null_clause
option_a valid in DB2 Server for VSE only
|--+----------------+---+-----------------------------------+--->
'-BLKSZ--(size)--' | .-NOREWIND--. |
| .-(TAPE)--+-REWIND----+--. |
'-PDEV--+-(DASD)-----------------+--'
>-----+------------------+---+----------------+-----------------|
'-RECFM--(format)--' '-RECSZ--(size)--'
Example 1:
DATAUNLOAD
SELECT AVG(BONUS) FROM EMPLOYEE;
OUTFILE(EXTRA)
Example 2:
DATAUNLOAD
SELECT SALARY FROM EMPLOYEE;
SALARY 1-10 CHAR
OUTFILE(REGULAR)
Authorization:
All normal SELECT privilege ground rules apply.
|
- DATAUNLOAD
- identifies the start of the DATAUNLOAD command sequence. A Database
Services Utility processing error occurs if other information is present in
the (input) control file record after the command identifier
DATAUNLOAD.
- select-statement
- is any valid SQL SELECT statement without host variables. The SQL
SELECT statement must begin in the next (input) control file record following
the one containing the DATAUNLOAD command.
A semicolon must be used to terminate the SQL SELECT
statement.
The results of the SQL SELECT statement supplied after a DATAUNLOAD command
are not written to the Database Services Utility report or message
file. An output file data record is written for each row (except those
containing data values that exceed the capacity of numeric output record data
fields) returned as a result of executing the SQL SELECT statement.
If the user-supplied SQL SELECT statement is not valid, or is not
terminated by a semicolon, a Database Services Utility processing error
occurs.
If an arithmetic exception occurs, the
DATAUNLOAD command handles it in a way similar to arithmetic exceptions under
the SELECT statement. (See SELECT and Arithmetic Exceptions for a description of the way arithmetic exceptions are
handled under the SELECT statement.) If an arithmetic exception occurs
when the data is to be placed into an output numeric data type field (FIXED,
FLOAT, DECIMAL, or ZONED), an error message is issued and processing is
terminated because the DB2 Server for VSE & VM system incorrectly reads
the number (or pound) symbols (#) used under SELECT as real data.
Stopping the processing prevents the arithmetic exception from generating
incorrect output.
Format:
>>-+-column_reference-+--startpos--+---------+------------------>
'-integer----------' '--endpos-'
.-CHARacter--.
>-----+------------+---+---------------------+-----------------><
'-data_type--' '-| set_null_clause |-'
|
The next record following the end of the SQL SELECT statement can contain
one or more Data Field Identification (DFI) subcommands or an OUTFILE
subcommand. If the OUTFILE subcommand is missing, a Database Services
Utility processing error occurs. If DFI subcommands are omitted, the
default output record format described in the section DATAUNLOAD Output Data Field Defaults is used.
A DFI subcommand identifies the location in the output record where
the data for a column in the select-list should be placed. The
subcommand also identifies the output record data-field data type. If
the output record data-field data type is different from the select-list
column data type, the Database Services Utility converts the column
data. The data conversions performed by DATAUNLOAD processing are
described in Table 13.
Only one DFI subcommand can appear in an input record. The command
parameters must not span input records, and the column_reference,
startpos, and endpos parameters must be specified first in
the command and in that order.
If DFI subcommands are specified, only the data for the select-list columns
referenced by these subcommands is unloaded to the output data record.
The data for a column in the select-list (explicitly specified, or implicitly
specified by the * specification in the SQL SELECT) not referenced by a DFI
subcommand is not unloaded. A Database Services Utility warning message
identifies each select-list column that is ignored by DATAUNLOAD
processing.
The data for the same column in the select-list can be unloaded to
more than one output record data field by specifying two or more DFI
subcommands that reference the column.
- column_reference
- identifies the select-list column to be used as the source of the output
data field value. Column_reference can be any valid form of
a table column name or integer that refers to a select-list column. For
example, the integer value 1 (hex F1) refers to the first item in the
select-list; the integer value 10 (hex F1F0) refers to the 10th item in
the select-list.
A Database Services Utility processing error occurs if:
- The column name specified in the DFI subcommand is not in the
select-list.
- The integer value identifying the column name exceeds the number of
columns in the select-list.
Use the integer notation for column_reference to identify the
column if:
- The select-list contains columns from different tables with the same
column name. For example:
SELECT...,EMPLOYEE.EMPNO,EMP_ACT.EMPNO,
...FROM EMPLOYEE,EMPLOYEE.ACTIVITY...;
- The select-list contains a column that is a constant or is derived from an
expression or function:
SELECT...,'SALARY(+6%)=',SALARY*1.06,MAX(SALARY)...
...FROM EMPLOYEE...;
- startpos
- identifies the starting position (byte) of the data in each output data
record. Position 1 of the output record corresponds to the first
position of the logical output record. If variable-length input records
are used, startpos 1 to 4 refers to the record length control
field. As a result, startpos 5 refers to the first data
position.
- endpos
- identifies the end position (byte) of the data in each output data
record. You can omit this parameter if the data occupies only one
position in the output record. If you specify this parameter, do not
place blanks between the starting position and the hyphen, or between the
hyphen and the ending position.
To unload a column defined with a double-byte character set (DBCS) data
type, the length of the output data field must be an even number (4, 6,
..., 100, ...) of positions (bytes)
other than 2. A Database Services Utility processing error occurs if
DBCS data is identified for an output record data field with an odd number of
positions, or with only two positions.
- data-type
- identifies whether character, graphic,
fixed-binary, floating-point, packed decimal, or zoned data values should be
placed in the output data record positions specified. The data type
specification must appear after the startpos-endpos values
in the subcommand. The default data type is character. The valid
data type identifiers that you can specify are:
- CHAR or CHARACTER
- If the table column data type is anything but GRAPHIC, you can create a
CHAR output data field.
CHAR output data derived from a CHAR, VARCHAR, DATE, TIME, TIMESTAMP, or
long field select-list column is left-justified and padded on the right with
blanks (hex 40). Trailing (low-order) data is truncated if the output
data field length is less than the length of the column data except for TIME,
DATE, and TIMESTAMP. For TIME and DATE, an error occurs if the output
data field length is less than the length of the column data. For
TIMESTAMP, if the output data field length is less than 19 bytes, an error
occurs; if the output data field length is less than 26 but greater than
or equal to 19 bytes, trailing digits of the MICROSECONDS part of the
timestamp are truncated.
The CHAR output data can also be derived from select-list columns with data
type SMALLINT, INTEGER, DECIMAL, and FLOAT. See the section, DATAUNLOAD Data Conversion Summary, for a description of the content of CHAR output data fields
derived from numeric column data types.
If extended DBCS processing is in effect, character data can contain
DBCS/EBCDIC mixed data. See page *** for a discussion of extended DBCS support.
When an arithmetic exception occurs and the data is to be placed into an
output data type field, no error message is issued and processing
continues. Number (or pound) symbols (#) are used, as under SELECT, to
fill the data type field and to indicate that an exception occurred during
processing.
CHAR is the default data type specification.
- GRAPHIC or GR or G
- If the table column is defined with the data type GRAPHIC, VARGRAPHIC, or
long field, you can create a DBCS
output data field.
The startpos and endpos for a DBCS output record data
field reflect the number of bytes the data field occupies in the data
record; they do not reflect the number of DBCS characters that the data
field contains.
The startpos of the output data field contains a shift-out (hex
0E) delimiter. The endpos of the output data field contains a
shift-in (hex 0F) delimiter. Two intervening positions are required for
each DBCS character.
The DBCS output record data field must occupy an even number of bytes in
the data record, or a Database Services Utility processing error
occurs.
DBCS column data is truncated if the length of the output record data field
is less than the column data length plus 2. For a DBCS column, the
column data length equals the number of DBCS characters times 2.
A blank DBCS output data field contains the hex 40 value in all positions
except for the first and the last. A null source column value also
results in a blank output record data field.
- DATE
- If the table column is defined with a column type of DATE, output data is
in one of the following formats:
yyyy-mm-dd (ISO, JIS format)
dd.mm.yyyy (EUR format)
mm/dd/yyyy (USA format)
installation defined (Local format)
where:
yyyy is the year
mm is the month
dd is the day
The format is dependent on the SYSOPTIONS default format value or is
specified by the CHAR function in the SELECT statement.
- Local Date Format
- A database administrator can change the date default format, which is
defined in the SYSTEM.SYSOPTIONS table, from ISO (which is the
system-supplied database default form) to any installation-defined
format. See the DB2 Server for VM System
Administration |and DB2 Server for VSE System
|Administration manuals for information about installation-defined formats and their
interface.
| Note: | See page *** for information on arithmetic error handling.
|
- TIME
- If the table column is defined with a column type of TIME, output data is
in one of the following formats:
hh:mm AM or hh:mm PM (USA format)
hh.mm[.ss] (ISO, EUR format)
hh:mm[:ss] (JIS format)
installation defined (Local format)
where:
hh is the hour
0 <= hh <= 12 for USA format
0 <= hh <= 24 for ISO, EUR, JIS format
mm is the minutes
ss is the seconds
The format is dependent on the SYSOPTIONS default format value or is
specified by the CHAR function in the SELECT statement.
- Local Time Format
- A database administrator can change the time default format, which is
defined in the SYSTEM.SYSOPTIONS table, from ISO (which is the
system-supplied database default form) to any installation-defined
format. See the DB2 Server for VSE System
Administration, or DB2 Server for VM System Administration manual for information about installation-defined formats and their
interface.
| Note: | See page *** for information on arithmetic error handling.
|
- TIMESTAMP
- If the table column is defined with a column type of TIMESTAMP, output
data is in the following format:
yyyy-mm-dd-hh.mm.ss[.[nnnnnn]]
where
yyyy-mm-dd is the date (see ISO DATE format)
hh.mm.ss is the time (see ISO TIME format)
nnnnnn is microseconds
If the output data field length is less than 19 bytes long, an error
occurs. If the output data field is less than 26 bytes, but greater
than or equal to 19 bytes, trailing digits of the microseconds part of the
timestamp are truncated.
- FIXED or INT or INTEGER
- If the table column is defined with a data type of
SMALLINT or INTEGER, you can define a fixed-point binary-output data
field.
If a row selected from the database by the SQL SELECT statement supplied for
DATAUNLOAD processing contains a column value that exceeds the capacity of a
1-byte or 2-byte FIXED output data field, an error message is issued, and no
output data file record is written for the row.
The value ranges for binary-output data fields are:
- A 1-byte binary data field can contain an 8-bit binary integer with a
value range of 0 to 255.
- A 2-byte binary data field can contain a 15-bit binary integer with the
value range described for a table column defined with the data type
SMALLINT.
- A 4-byte binary data field can contain a 31-bit binary integer with the
value range described for a table column defined with the data type
INTEGER.
| Note: | See page *** for information on arithmetic error handling.
|
- FLOAT or REAL
- If the table column is defined with a data type of REAL or
FLOAT(n), where n is from 1 to 21, you can define 4-byte
floating-point binary-output data, where (endpos-startpos+1) = 4.
If a row selected
from the database by the SQL SELECT statement supplied for DATAUNLOAD
processing contains a column value that exceeds the capacity of the FLOAT
output data field, an error message is issued, and no output data file record
is written for the row.
| Note: | See page *** for information on arithmetic error handling.
|
- FLOAT or DOUBLE PRECISION
- If the table column is defined with a data type of FLOAT, DOUBLE
PRECISION, or FLOAT(n), where n is from 22 to 53, you can
define 8-byte floating-point binary-output data, where
(endpos-startpos+1) = 8.
| Note: | If 8-byte floating-point binary data is unloaded into a 4-byte floating-point
output data field, a number of digits of precision is lost. If a row
selected from the database by the SQL SELECT statement supplied for DATAUNLOAD
processing contains a column value that exceeds the capacity of the FLOAT
output data field, an error message is issued, and no output data file record
is written for the row.
|
See page *** for information on arithmetic error handling.
- DECIMAL or DEC
- If the table column is defined with a DECIMAL data type, you can specify
DECIMAL or DEC for packed decimal output data.
The length of the output data field must be large enough
to accommodate all significant digits of the column data value. The
minimum length of an output field derived from DECIMAL column data is (column
scale/2)+1. The implied scale of the output data field value is the
same as that defined for column.
If a row selected from the database by the SQL SELECT statement supplied
for DATAUNLOAD processing contains a column value that exceeds the capacity of
a decimal output data field, an error message is issued, and no output data
file record is written for the row.
Columns defined as NUMERIC are treated as DECIMAL data types.
| Note: | See page *** for information on arithmetic error handling.
|
- ZONED
- If the table column is defined with a data type of SMALLINT, INTEGER, or
DECIMAL, you can specify ZONED for zoned output data.
The length of a zoned output record data field derived from a DECIMAL column
must be equal to or greater than the column scale.
Each digit of the table column value is represented by a single byte in the
zoned output data field. The 4 high-order bits of each byte are the
zone bits. The 4 high-order bits of the low-order byte are the sign of
the value. The 4 low-order bits of each byte contain the value of the
digit.
The zone bits are 1111 (hex F). A plus-sign is represented by the
bits 1100 (hex C), and a minus-sign is represented by the bits 1101 (hex
D).
The output data field value is right-justified. Leading (high-order)
zeros are either added to, or truncated from, the column value depending on
the length of the output data field.
If a row selected from the database by the SQL SELECT statement supplied
for DATAUNLOAD processing contains a column value that exceeds the capacity of
a zoned output data field, an error message is issued, and no output data file
record is written for the row.
Examples of zoned output data fields:
The hexadecimal content of a 5-position zoned data field containing the
value +00011 is:
| Hexadecimal Value
| F0
| F0
| F0
| F1
| C1
|
| Field Position
| 1
| 2
| 3
| 4
| 5
|
| Note: | See page *** for information on arithmetic error handling.
|
The hexadecimal content of a 5-position zoned data field containing the
value -00011 is:
| Hexadecimal Value
| F0
| F0
| F0
| F1
| D1
|
| Field Position
| 1
| 2
| 3
| 4
| 5
|
See Table 13 for a table summarizing the data conversion performed by
Database Services Utility DATAUNLOAD processing.
- set_null_clause
- specifies the output data record position and value that identifies
a null table column value. The null identifier value can be a
character or an integer value (see below); it does not assume the data
type specified for the output record data field.
The set_null_clause must appear after the
startpos-endpos values in the subcommand. No embedded
blanks are allowed in the set_null_clause within the left and
right parentheses enclosing the startpos and endpos
values.
set_null_clause:
.-IF-. .-SET-.
>>-+----+--NULL--+-----+--POS--(--startpos--+---------+--)--= value->
'--endpos-'
>--------------------------------------------------------------><
The parameters are:
- IF NULL SET POS
- identifies the start of the set_null_clause. You can
use either the keyword phrase IF NULL SET POS or NULL POS.
- startpos
- identifies the starting position (byte) in the output data record of the
value that identifies a null table column value. The null identifier
value positions can overlap the positions assigned to an output record data
field.
Position 1 of the output data record is the first position of the logical
record. If variable-length output records are used, startpos 1
to 4 refer to the record length control information and the data begins at
startpos 5.
- endpos
- identifies the last output data record position (byte) of the null table
column identification value. If the value occupies only one output data
record position, this parameter is not required.
- value
- specifies the null table column identification value. If an
occurrence of the column value is null, the value specified is placed in the
output data record positions specified after the data field value for the
default output record is set.
If an occurrence of the column value is not null, no value is placed in the
output data record positions specified in the
set_null_clause. These positions contain blanks (hex 40)
if they do not contain a default output record data field value for null
column data.
The set_null_clause value cannot be continued to a second
output record. It can be one of the following:
- A character-string constant that:
- Must be enclosed in single quotation marks (')
- Has a maximum length equal to endpos
-startpos+1.
- A one-position unsigned integer constant (0 to 255)
- A two-position optionally signed integer constant (default is a positive
value)
- A four-position optionally signed integer constant (default is a positive
value).
Examples of Set-Null-Clause:
- Character-string constant:
IF NULL SET POS(20-23) = 'NULL'
IF NULL SET POS(20-22) = ' ? '
- One-position integer constant:
IF NULL SET POS(20) = 255
- Two-position integer constant:
IF NULL SET POS(20-21) = 32767
- Four-position integer constant:
IF NULL SET POS(20-23) = -1839107489
VSE Format:
>>-OUTFILE--(--ddname------------------------------------------->
>----+----------------+---+----------------------------------+-->
'-BLKSZ--(size)--' | .-NOREWIND--. |
| .-(TAPE)-+-REWIND----+--. |
'-PDEV--+-(DASD)----------------+--'
>----+------------------+---+----------------+--)--------------><
'-RECFM--(format)--' '-RECSZ--(size)--'
VM Format:
>>-OUTFILE----(ddname)-----------------------------------------><
|
The OUTFILE subcommand identifies the sequential
output file that contains the data referenced by the preceding DATAUNLOAD
commands and subcommands. It tells the utility the file in which to put
the data and to begin to unload the data.
The sequential output file can contain fixed, variable-length, or
variable-length spanned records. The records can be blocked or
unblocked. If you want variable length records to be generated,
variable-length spanned records must be used if the total length of the column
values to be unloaded exceeds 32752 bytes.
A blank (hex 40) is placed in all positions (bytes) of the output data
record before the data record field values are inserted.
- ddname
- in DB2 Server for VM: this is the name of the sequential output file
defined with a CMS FILEDEF command. If you define DATAUNLOAD CMS output
files with variable-length spanned records (RECFM=VS or RECFM=VBS), you must
use file-mode number 4. If a tape output file is
used, performance can be improved by using a large block size value (greater
than 8244) on the FILEDEF.
The file characteristics specified in the FILEDEF command or the default
FILEDEF specifications are the source of the output record definition
information for the Database Services Utility. Output files with RECFM
U, A, or M are not supported.
The length of the output record supplied by the FILEDEF must be long enough
to contain column data selected for unload (including intervening data field
blanks if the default format is used). If it is not, a Database
Services Utility processing error occurs. If the length of the output
record is greater than the length required to unload the data, the remaining
positions of the output record are set to blanks (hex 40).
If variable length output records are used, the data fields referenced by
DFI subcommands appear in the same positions on each output data
record.
Do not specify SYSIN or SYSPRINT as the ddname.
in DB2 Server for VSE: this is the TLBL or DLBL job control statement
file name for the sequential (SAM) output file. This parameter must be
the first parameter specified; you cannot specify BLKSZ before the
ddname. The other keyword parameters can be specified in any
order.
- BLKSZ (size)
- is an optional parameter that specifies the block size of the sequential
output file.
If a tape output file is used, performance can be improved by using a large
block size value (greater than 8244).
The default block size values depend upon the output file record
format:
- If RECFM = F or FB, the default block size is equal to the highest data
field end-position value
- If RECFM = V or VB, the default block size is equal to the highest data
field end-position value plus four
- If RECFM = S or SB, the default block size is 2048 bytes.
- PDEV (TAPE or DASD)
- is an optional parameter that specifies the device type (DASD or TAPE) of
the sequential (SAM) output file. If PDEV(DASD) is specified, the file
resides on any device supported by the VSE DTFSD macro. An exception to
this is VSAM-managed SAM files. VSAM-managed SAM does not
support spanned records. If PDEV(TAPE) is specified, the file resides
on any device supported by the VSE DTFMT macro. The default is
PDEV(TAPE).
- NOREWIND or REWIND
- controls tape file rewind processing performed during CLOSE
processing. This parameter is valid only if you specify TAPE for
PDEV. The default is NOREWIND.
- NOREWIND
- specifies that the tape file will not be rewound by CLOSE
processing.
- REWIND
- specifies that the tape file is rewound by CLOSE
processing.
- RECFM (format)
- is an optional parameter that specifies the format of the records in the
output data file. For format, substitute one of the following
values:
Value Meaning
F fixed, unblocked
FB fixed, blocked
V variable length, unblocked
VB variable length, blocked
S variable spanned, unblocked
SB variable spanned, blocked
|
If the length of the logical record (RECSZ) is equal to or less than 32760,
the default is RECFM(F). Otherwise, the default is RECFM(SB).
The output record format is identified in a Database Services Utility
informational message.
| Note: | If variable-length output records are used, the data fields referenced by the
DFI subcommands will be in the same position for each occurrence of a data
record. Positions 1-4 of each variable-length record contain record
length control information.
|
- RECSZ (size)
- is an optional parameter that specifies the length of a logical record for
the output data file. The length of the output record must be long
enough to contain column data selected for DATAUNLOAD processing (including
intervening data field blanks if the default format is used). If it is
not, a Database Services Utility processing error occurs.
The default record size values depend upon the output file record
format:
- If RECFM = F or FB, the default record size is the block size.
- If RECFM = V or VB, the default record size is the block size minus
four.
- If RECFM = S or SB, the default record size is equal to the highest data
field end-position value.
If no DFI commands are supplied, the output data fields
appear in the output record in the same order as the associated columns in the
select-list.
The output data field associated with the first select-list column starts in
position 1 of the fixed length output records or position 5 of variable length
(or variable length spanned) output records. Positions 1-4 of variable
length or variable length spanned records are reserved for the record length
control field. In DB2 Server for VSE, if the record format (RECFM) is
not supplied by the OUTFILE subcommand, DATAUNLOAD processing writes either
fixed length or variable length spanned output records. Fixed length
records are written if the required logical record length is less than 32760
positions; otherwise, variable length spanned records (RECFM=S) are
written.
In DB2 Server for VM, the DFI subcommand will refer to the first data
position as startpos 5. The FILEDEF command that defines the
output file always supplies the record format information.
One blank (hex 40) position separates each output record data field.
The output data field associated with the next select-list column starts two
positions after the trailing (low-order) position of the data field derived
from the preceding select-list column.
Figure 91. Default Fixed-Length Output Logical Record Content
Figure 92. Default Variable-Length Spanned Logical Output Record Content
Table 10 summarizes the default output
field formats generated by the DATAUNLOAD processing if no DFI subcommands
are supplied. The default data type of the output data field is CHAR
(or GRAPHIC if the source column contains DBCS data). The format of the
data in the output data field depends on the data type, length, or maximum
length of the select-list column from which the data is derived.
Table 10. Default Output Formats
| Source Column Data Type
| Default Database Services Utility DATAUNLOAD Output Data Fields Default
Data Type = CHAR
|
| CHAR
| Length: Defined length of source column.
|
| VARCHAR length <= 254
| Length: Defined maximum length of column.
Note: If the actual length of an occurrence of the column data is
less than the defined maximum length of the column, the data is left-justified
in the output data field and padded with trailing (low-order) blanks.
|
| VARCHAR length > 254 or LONG VARCHAR
| Length: 512 positions (bytes).
Notes: If the actual length of an occurrence of the column data is
greater than 512, the column data is truncated.
If the actual length of an occurrence of the column data is less than 512,
the column data is left-justified and padded with trailing (low-order)
blanks.
|
| SMALLINT
| Length: 6 Format: snnnnn
|
| INTEGER
| Length: 11 Format: snnnnnnnnnn
|
| DECIMAL
| Length: Precision of source column + 2.
Format: Examples:
Column Precision=7, Scale=2: snnnnn.nn
Column Precision=5, Scale=5: s.nnnnn
Note: NUMERIC is a synonym for DECIMAL.
|
| REAL or FLOAT (N) 1 <= N <= 21
| Length: 12 (single precision float).
Format: sn.nEsnbbbbb (minimum
value) sn.nnnnnEsnn (maximum value)
Note: The value is left-justified and, if necessary, padded with
trailing (low-order) blanks in the output data field.
|
| FLOAT or DOUBLE PRECISION or FLOAT (N) 22 <= N <= 53
| Length: 20 (double precision float).
Format: sn.nEsnbbbbbbbbbbbbb
(minimum value) sn.nnnnnnnnnnnnnEsnn
(maximum value)
Note: The value is left-justified and, if necessary, padded with
trailing (low-order) blanks in the output data field.
|
Legend For FLOAT:
s = EBCDIC SIGN: Plus (+) sign (hex 4E)
Minus (-) sign (hex 60)
Blank (hex 40) if null value.
n = EBCDIC numeric character (hex F0-F9)
. = EBCDIC decimal point (hex 4B)
b = Blank (hex 40)
|
| Source Column Data Type
| Default Database Services Utility DATAUNLOAD Output Data Fields Default
Data Type = CHAR
|
| DATE
|
Default
DATE length format
ISO 10 yyyy-mm-dd
JIS 10 yyyy-mm-dd
EUR 10 dd.mm.yyyy
USA 10 mm/dd/yyyy
LOCAL installation defined
yyyy is the year
mm is the month
dd is the day
Note: The length and format of the output data field depends on the
default DATE for the database. You can query the
SYSTEM.SYSOPTIONS catalog to determine the output format for
DATE.
|
| TIME
|
Default
TIME length format
ISO 8 hh.mm.ss
JIS 8 hh:mm:ss
EUR 8 hh.mm.ss
USA 8 hh.mm AM (or hh.mm PM)
LOCAL Installation defined
hh is the hour
0 <= hh <= 24 for ISO, JIS, EUR formats
0 <= hh <= 12 for USA format
mm is the minute
ss is the second
Note: The length and format of the output data field depends on the
default TIME for the database. You can query the
SYSTEM.SYSOPTIONS catalog to determine the output format for
TIME.
|
| TIMESTAMP
| Length: 26 Format:
yyyy-mm-dd-hh.mm.ss.nnnnnn
yyyy-mm-dd is the date (ISO format)
hh.mm.ss is the time (ISO format)
nnnnnn is the microsecond
|
| GRAPHIC
| Length: (Defined length of column * 2) + 2.
Note: The first position of the output record DBCS data field
contains an SO delimiter and the last position contains an SI
delimiter.
|
| Source Column Data Type
| Default Database Services Utility DATAUNLOAD Output Data Fields Default
Data Type = CHAR
|
| VARGRAPHIC with defined length <= 127
| Length: (Defined maximum length of column * 2) + 2.
Notes: The first position of the output record DBCS data field
contains an SO delimiter and the last position contains an SI
delimiter.
If the actual length of an occurrence of the DBCS column data is less than
the defined maximum length of the column, the data is left-justified and
padded with trailing blanks in the second through n-1 positions of the output
record field.
|
| VARGRAPHIC with defined length >127 or LONG VARGRAPHIC
| Length: 512 positions.
Notes: The first position of the output record DBCS field contains an
SO delimiter and the last position contains an SI delimiter.
If the actual length of an occurrence of the DBCS column data is greater
than 510 (255 DBCS characters), the column data is truncated.
If the actual length of an occurrence of the DBCS column data is less than
510 (255 DBCS characters), the data is left-justified and padded with trailing
blanks in the second through n-1 positions of the output record field.
|
This example unloads data for the columns EMPNO, PROJNO, and EMPTIME in the
tables EMP_ACT and EMPLOYEE, based on the selection criteria specified in
the WHERE clause. The output records are generated in EMPNO value
ascending sequence. Because no DFI subcommands are present, the default
DATAUNLOAD output record data field format is used.
The DATAUNLOAD command sequence is:
Figure 93. DATAUNLOAD Command without DFI Subcommands
DATAUNLOAD
SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME
FROM EMP_ACT,EMPLOYEE
WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
ORDER BY EMP_ACT.EMPNO;
OUTFILE(OUTPUT1)
|
The Database Services Utility message file output that results is shown in
the following examples.
Figure 94. DB2 Server for VM Database Services Utility Message File Output
1ARI0801I DBS Utility started: 11/13/89 16:48:16.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
0------> DATAUNLOAD
------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME
------> FROM EMP_ACT,EMPLOYEE
------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
------> ORDER BY EMP_ACT.EMPNO;
------> OUTFILE(OUTPUT1)
ARI0852I DATAUNLOAD processing started. *--------*
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80 <------*See Note|
ARI0836I Default output record data field positions: *--------*
ARI0837I EMPNO 1-6
ARI0837I PROJNO 8-13
ARI0837I EMPTIME 15-21
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes sucessful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 11/13/89 16:48:20.
|
| Note: | The RECFM, RECSZ, and BLKSIZE information displayed in the message ARI0868I
depends on the CMS FILEDEF command specifications for the output file with
ddname=OUTPUT1.
|
Figure 95. DB2 Server for VSE Database Services Utility Report Output
ARI0801I DBS Utility started: 11/13/89 16:48:16.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
------> CONNECT "SQLDBA " IDENTIFIED BY ********;
ARI8004I User SQLDBA connected to database SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------>
------> DATAUNLOAD
------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME
------> FROM EMP_ACT,EMPLOYEE
------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
------> ORDER BY EMP_ACT.EMPNO;
------> OUTFILE(OUTPUT1)
ARI0852I DATAUNLOAD processing started.
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80
ARI0836I Default output record data field positions:
ARI0837I EMPNO 1-6
ARI0837I PROJNO 8-13
ARI0837I EMPTIME 15-21
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes sucessful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 11/13/89 16:48:20.
|
The format of the records in the output file identified by the
ddname OUTPUT1 is shown in Table 11:
Table 11. Default Output Record Format
| Record Position
| Data Value Source (Column or Other)
| Output Record Field Data Type
|
| 1-6
| EMPNO
| CHAR
|
| 7
| blank
| CHAR
|
| 8-13
| PROJNO
| CHAR
|
| 14
| blank
| CHAR
|
| 15-21
| EMPTIME
| CHAR
|
Figure 96 selects data for the
columns EMPNO, PROJNO, and EMPTIME in the table EMP_ACT, and data for the
column JOB in the EMPLOYEE table based on the selection criteria specified in
the WHERE clause. Only data for the columns EMPNO, PROJNO, and EMPTIME
is unloaded because JOB does not have a DFI subcommand. The output
records are generated in EMPNO sequence.
The Database Services Utility DATAUNLOAD command sequence is:
Figure 96. DATAUNLOAD Command with DFI Subcommands
DATAUNLOAD
SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME,JOB
FROM EMP_ACT,EMPLOYEE
WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
ORDER BY EMP_ACT.EMPNO;
EMPNO 1-6
PROJNO 8-13
EMPTIME 15-21 DECIMAL IF NULL SET POS(22) = '?'
OUTFILE(OUTPUT1)
|
The Database Services Utility report or message file output generated as a
result of these commands is shown in the following:
Figure 97. DB2 Server for VSE Database Services Utility Report Output
ARI0801I DBS Utility started: 10/05/89 14:54:41.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
------> CONNECT "SQLDBA " IDENTIFIED BY ********;
ARI8004I User SQLDBA connected to database SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------>
ARI8003I ...Extended DBCS (DBCS=YES) processing was in effect.
------> DATAUNLOAD
------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME
------> FROM EMP_ACT,EMPLOYEE
------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
------> ORDER BY EMP_ACT.EMPNO;
------> EMPNO 1-6
------> PROJNO 8-13
------> EMPTIME 15-21 DECIMAL IF NULL SET POS(22) = '?'
------> OUTFILE(OUTPUT1)
ARI0831I Column JOB data will not be unloaded.
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes sucessful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 10/05/89 14:54:44.
|
Figure 98. DB2 Server for VM Database Services Utility Message File Output
1ARI0801I DBS Utility started: 10/05/89 14:54:41.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
ARI8003I ...Extended DBCS (DBCS=YES) processing was in effect.
0------> DATAUNLOAD
------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME,JOB
------> FROM EMP_ACT,EMPLOYEE
------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
------> ORDER BY EMP_ACT.EMPNO;
------> EMPNO 1-6
------> PROJNO 8-13
------> EMPTIME 15-21 DECIMAL IF NULL SET POS(22) = '?'
------> OUTFILE(OUTPUT1)
ARI0852I DATAUNLOAD processing started.
ARI0831I Column JOB data will not be unloaded. *-----------*
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80 <----* See Note |
ARI0835I 74 record(s) written to the output data file. *-----------*
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes sucessful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 10/05/89 14:54:44.
|
| Note: | The RECFM, RECSZ, and BLKSIZE information displayed in the message ARI0868I
depends on the CMS FILEDEF command specifications for the output file with
ddname=OUTPUT1.
|
The format of the records in the output file identified by the ddname
OUTPUT1 is shown in Table 12.
Table 12. User-Defined Output Record Format
| Record Position
| Data Value Source (Column or Other)
| Output Record Field Data Type
|
| 1-6
| EMPNO
| CHAR
|
| 7
| blank
| CHAR
|
| 8-13
| PROJNO
| CHAR
|
| 14
| blank
| CHAR
|
| 15-21
| EMPTIME
| DECIMAL
|
| 22
|
EMPTIME
null indicator
| CHAR
|
Table 13 summarizes the data conversion performed by
Database Services Utility DATAUNLOAD processing. Yes means that the
utility performs the conversion. No means that the utility cannot
convert the source column data type into the data type specified for the
output record data field and that any attempt to do so results in a Database
Services Utility processing error. The numbers in the chart refer to
the notes below the figure.
Table 13. DATAUNLOAD Data Conversion Table
| Output Field Data Type
| Source Column Data Type
|
| CHAR, VAR-CHAR, or LONG VAR-CHAR
| DECIMAL8
| SMALL-INT
| INTEGER
| REAL12
| Double Precision13
| DATE, TIME, or TIMESTAMP
| DBCS, GRAPHIC, VAR-GRAPHIC, or LONG VAR-GRAPHIC
|
|---|
| CHAR
| Yes1
| Yes2,3
| Yes2,4
| Yes2,4
| Yes2,5
| Yes2,5
| Yes1
| Yes
|
| GRAPHIC (G)
| No
| No
| No
| No
| No
| No
| No
| Yes9
|
| 1-Byte FIXED
| No
| No
| Yes6
| Yes6
| No
| No
| No
| No
|
| 2-Byte FIXED
| No
| No
| Yes6
| Yes6
| No
| No
| No
| No
|
| 4-Byte FIXED
| No
| No
| Yes6
| Yes6
| No
| No
| No
| No
|
| 4-Byte FLOAT
| No
| No
| No
| No
| Yes
| Yes7
| No
| No
|
| 8-Byte FLOAT
| No
| No
| No
| No
| Yes11
| Yes
| No
| No
|
| DECIMAL
| No
| Yes14
| No
| No
| No
| No
| No
| No
|
| ZONED
| No
| Yes
| Yes6
| Yes6
| No
| No
| No
| No
|
| DATE, TIME, or TIMESTAMP
| No
| No
| No
| No
| No
| No
| Yes10
| No
|
Notes for Table 13:
- The CHAR, VARCHAR, and long field column data may be truncated if the
length of the source data is greater than the length of an output CHAR data
field. For TIME and DATE, an error occurs if the length of the source
data is greater than the length of an output CHAR data field. For
TIMESTAMP, if the output data field length is less than 19 bytes, an error
occurs; if the output data field length is less than 26 bytes but greater
than or equal to 19 bytes, trailing digits of the microseconds part of the
timestamp is truncated. If the length of the output record data field
is greater than the length of the source column data, all trailing (low-order)
positions of the data field are padded with a blank (hex 40) value.
Occurrences of null character
column data result in an all blank output record data field.
- If a CHAR output record data field is potentially too small to contain all
significant digits, the sign, and the decimal point for a value derived from a
column defined with a numeric (SMALLINT, INTEGER, DECIMAL, or FLOAT)
data type, then:
- An error message is written to the Database Services Utility message file
identifying the column name.
- The output record CHAR data field associated with a numeric column
contains asterisks (*) if a data overflow condition actually occurs.
- A CHAR output data field derived from a column with a DECIMAL data type
contains an EBCDIC plus sign (hex 4E) or minus sign (hex 60) in the leading
(high-order) position. The data value is right-justified in the
low-order positions of the output data field and represented using the values
hex F0 through hex F9 in each position except for the decimal point
position. A decimal point (hex 4B) precedes the low-order scale value
positions in the output data field. The leading (high-order) positions
of the output data field (except for the first position) contain zeros (hex
F0) if the number of significant positions of the data value is less than the
length of the output data field minus 2.
Occurrences of null column data result in an unsigned output data field
value of 0.
The leading position of the field contains a blank (hex 40) and the remainder
of the data field contains the value hex F0 (except for the decimal point
position).
For example, the hexadecimal values in each position of an eight-position
CHAR output record data field derived from a DECIMAL (5,2) column containing
the value +11.11 are:
| Hexadecimal Value
| 4E
| F0
| F0
| F1
| F1
| 4B
| F1
| F1
|
| EBCDIC Character
| +
| 0
| 0
| 1
| 1
| .
| 1
| 1
|
| Field Position
| 1
| 2
| 3
| 4
| 5
| 6
| 7
| 8
|
| Note: | The minimum length of a CHAR output data field derived from a DECIMAL
select-list column is the column scale length plus 2.
|
- A CHAR output data field derived from a column with a SMALLINT or INTEGER
data type contains a minus sign (hex 60) in the leading (high-order) position
for negative column values. If the column value is positive, the
leading (high-order) position of the data field contains a blank (hex
40). The data value is right-justified in the low-order positions of
the output data field and represented using the values hex F0 through hex F9
in each position. The leading (high-order) positions of the output data
field (except for the first position) contain zeros (hex F0) if the number of
significant positions of the data value is less than the length of the output
data field minus 1.
Occurrences of null column data result in an unsigned output data field
value of 0. The leading position of the field contains a
blank (hex 40) and the remainder of the data field positions contains the
value hex F0.
For example, the hexadecimal values contained in each position of an
eight-position CHAR output record data field derived from a SMALLINT column
containing the value +32767 are:
| Hexadecimal Value
| 40
| F0
| F0
| F3
| F2
| F7
| F6
| F7
|
| EBCDIC Character
|
| 0
| 0
| 3
| 2
| 7
| 6
| 7
|
| Field Position
| 1
| 2
| 3
| 4
| 5
| 6
| 7
| 8
|
| Note: | The minimum length of a CHAR output data field derived from a SMALLINT or
INTEGER column is 2.
|
- A CHAR output data field derived from a column with a FLOAT data type is
left-justified in the leading (high-order) positions of the output data
field. The format of the output data field value ranges from:
sn.nEsn to sn.nnnnnnnnnnnnnEsnn (for 8-byte float)
or
sn.nEsn to sn.nnnnnEsnn (for 4-byte float)
where:
s = EBCDIC sign: Plus (+) sign (hex 4E)
Minus (-) sign (hex 60)
n = EBCDIC numeric character (hex F0-F9)
. = EBCDIC decimal point (hex 4B)
E = EBCDIC character E (hex C5)
The trailing (low-order) positions of the output data field contain blanks
(hex 40) if the length of the field is greater than the EBCDIC
representation of the column value.
Occurrences of null column data result in an unsigned output data field
value ( 0.0E+0).
The leading position of the data field contains a blank (hex 40).
For example, the hexadecimal values contained in each position of a
10-position CHAR output record data field derived from an 8-byte FLOAT column
containing the value +1.11E+02 are:
| Hexadecimal Value
| 4E
| F1
| 4B
| F1
| F1
| C5
| 4E
| F0
| F2
| 40
|
| EBCDIC Character
| +
| 1
| .
| 1
| 1
| E
| +
| 0
| 2
|
|
| Field Position
| 1
| 2
| 3
| 4
| 5
| 6
| 7
| 8
| 9
| 10
|
| Note: | The minimum length of a CHAR output data field derived from a column with
FLOAT data type is 7.
|
- Leading (high-order) zero-value positions of column data are truncated if
the length of a numeric
(FIXED, DECIMAL, or ZONED) output data field is less than the length of the
numeric (SMALLINT, INTEGER, or DECIMAL) column value. A null column
value results in a numeric output record data field value of
0.
If a numeric output record data field is too small to contain all
significant digits of the data value from a numeric column:
- The message ARI0833E is written to the Database Services Utility message
file identifying the column name and the SQL SELECT row count.
- No data for the row is written to the output data file.
- A null column value results in a numeric output record data field value of
0.
An 8-byte float column unloaded into a 4-byte output record FLOAT data
field results in the loss of a number of digits of precision. The
fraction (mantissa) is reduced from 14 to 6 digits of precision. During
the conversion process, if the exponent value exceeds a value of +63, the
message ARI0833E is generated and no data for the row is unloaded.
- The minimum length of an output data field derived from a DECIMAL column
is the (scale value/2+1). The scale of output data derived from a
DECIMAL column is the same as that for the source column.
If the length of the output data field is greater than the value of (column
precision/2)+1, the column value is extended with high-order zeros in the
output data field.
If the length of the output data field is less than the value of (column
precision/2)+1, nonsignificant high-order zeros to the left of the implied
decimal point are not reflected in the data field value.
- Only fixed-length DBCS (data type of GRAPHIC) output data fields are
produced by DATAUNLOAD processing. The startpos of the output
data field contains a shift-out delimiter (hex 0E). The endpos
of the output data field contains a shift-in delimiter (hex 0F).
DBCS column data is truncated if the length of the column data plus 2 is
greater than the length of the output GRAPHIC data field. The length of
the column data is the number of DBCS characters times 2.
If the length of the output data field minus 2 is greater than the column
data length, the output data field is padded with trailing (low-order) DBCS
blank (hex 4040) characters in the unused low-order data field
positions. The last position of the data field always contains a
shift-in delimiter (hex 0F).
If the column data value is all blanks or null, startpos+1 to
endpos-1 of the output DBCS data field contains DBCS blank
characters (hex 4040).
- The datetime data type input and target type must match; for example,
the input data type of TIME is valid only for the target column data type of
TIME.
- A 4-byte float column unloaded into an 8-byte float data field is padded
with hex 0000 0000.
- REAL represents single precision floating point data and is synonymous
with FLOAT(n) where 1 is less than or equal to n is less
than or equal to 21.
- DOUBLE PRECISION represents double precision floating point data and is
synonymous with FLOAT or FLOAT(n) where 22 is less than or equal to
n is less than or equal to 53.
- If the length of the output record data field is greater than the length
of the source column data, all leading (high-order) positions of the data
field are padded with hex zeros.
VM Format:
>>-RELOAD DBSPACE----(dbspace_name)---+-NEW---+----------------->
'-PURGE-'
>----INFILE--(ddname)---+----------------------------+---------->
'-COMMITCOUNT--(--ccount--)--'
>-----+---------------------------------+----------------------->
'-RESTARTTABLE--(--table_name--)--'
>-----+-----------------------------+--------------------------><
'-RESTARTCOUNT--(--rcount--)--'
VSE Format:
>>-RELOAD DBSPACE----(dbspace_name)---+-NEW---+----------------->
'-PURGE-'
>----INFILE--(--ddname--+---------------------------+----------->
| .-2048--. |
'-BLKSZ--(--+-size--+---)---'
>-----+----------------------------------+--)------------------->
| .-REWIND---. |
| .-(TAPE)--+-NOREWIND-+--. |
'-PDEV--+-(DASD)----------------+--'
>-----+----------------------------+---------------------------->
'-COMMITCOUNT--(--ccount--)--'
>-----+---------------------------------+----------------------->
'-RESTARTTABLE--(--table_name--)--'
>-----+-----------------------------+--------------------------><
'-RESTARTCOUNT--(--rcount--)--'
Examples:
RELOAD DBSPACE(JOHNS.SPACE1) PURGE INFILE(TEMP)
RELOAD DBSPACE(PUBLIC.SPACE2) NEW INFILE(TEMP)
RELOAD DBSPACE(DBS1) PURGE INFILE(IFILE) COMMITCOUNT(300)
RESTARTTABLE(EMPLOYEE) RESTARTCOUNT(600)
Authorization:
You must have the INSERT privilege on the tables affected by the
command. Additional authority is required depending on the keywords
specified:
- RESOURCE-if NEW is specified.
- SELECT, DELETE, and INSERT- if
PURGE is specified.
- DBA-if PURGE is specified, and if any indexes defined on
an affected table are owned by someone else. DBA authority is also
required if NEW is specified, and any tables are to be created for another
user.
|
Before you reload tables into a dbspace, it must already exist.
| Note: | The RELOAD DBSPACE command is not supported if you are using DRDA
flow.
|
- DBSPACE (dbspace-name)
- identifies a RELOAD DBSPACE request and identifies the dbspace to be
loaded. The Database Services Utility loads the tables into the dbspace
in the order that they occur in the input data. If you do not own a
private dbspace with the dbspace-name identified, the data is
loaded into a public dbspace (if one having that name exists). The
owner of a public dbspace is PUBLIC.
- NEW
- instructs the Database Services Utility to create each table contained in
the input file before loading the data. Tables represented in the input
data file that already exist in the database are not processed. The
tables are created for the current Database Services Utility user. You
must have RESOURCE authorization to use this keyword.
If either the RESTARTCOUNT or RESTARTTABLE parameters appear on the RELOAD
DBSPACE command, the NEW parameter will not cause the restart table to be
created. The RESTARTCOUNT and RESTARTTABLE parameters indicate that the
RELOAD DBSPACE operation is being restarted, therefore, NEW processing must
have already occurred, so it is not required to create the restart table
again. Note that NEW processing is performed on all tables to be
reloaded before any rows are reloaded to any table. This means that NEW
processing will have already occurred for all tables to be reloaded.
- PURGE
- instructs the Database Services Utility that existing tables within the
dbspace are to be loaded. The rows for all dbspace tables to be
processed are deleted before the first table is loaded. The tables that
are processed are those that are in the input file; that is, if table
JONES.PROJECT exists in the dbspace, but the input file contains only
JONES.EMPLOYEE and JONES.DEPARTMENT, then JONES.PROJECT
is unaffected by RELOAD processing. Even if the input file contains
SMITH.PROJECT, JONES.PROJECT is unaffected. The Database
Services Utility uses fully qualified table names when determining the tables
to reload. You must have the DELETE privilege to use this keyword if
you do not own the affected tables. You must also have DBA authority if
any indexes defined on an affected table are owned by someone else.
If either the RESTARTCOUNT or RESTARTTABLE parameters appear on the RELOAD
DBSPACE command, the PURGE parameter will not cause all rows of the restart
table to be deleted. The RESTARTCOUNT and RESTARTTABLE parameters
indicate that the RELOAD DBSPACE operation is being restarted, therefore,
PURGE processing must have already occurred, so it is not required to delete
all rows from the restart table again. Note that PURGE processing is
performed on all tables to be reloaded before any rows are reloaded to any
table. This means that PURGE processing will have already occurred for
all tables to be reloaded.
| Note: | You must specify either NEW or PURGE in the RELOAD DBSPACE statement.
Because existing tables might be greatly affected by the choice of these
parameters, there is no default specification.
|
- INFILE (ddname)
- in DB2 Server for VSE, this identifies and describes the
sequential (SAM) file containing the input dbspace data. The default
record format in a DB2 Server for VSE system is variable-length blocked,
spanned (SB), with LRECL=(BLKSIZE-4) for variable and spanned records or
LRECL=BLKSIZE for fixed and undefined records.
The default record format in a DB2 Server for VM system is variable-length
blocked, spanned (VBS). Block size and record format information is
specified using a CMS FILEDEF command; the LRECL parameter is not
applicable.
- ddname
- In DB2 Server for VSE: this is the TLBL or DLBL job control
statement file name for the sequential input file.
In DB2 Server for VM: this is the name of the sequential input file
defined with a CMS FILEDEF command. Except for the ddname, use
the same CMS FILEDEF command information for RELOAD command processing that
you used when UNLOAD command processing created the file. Define the
CMS file used for RELOAD command input with the file-mode number
4. Do not specify SYSIN or SYSPRINT as the
ddname.
- COMMITCOUNT (ccount)
- identifies the frequency of COMMIT action during RELOAD processing.
ccount is a number from 1 to 2,147,483,647 indicating that a COMMIT
statement should be executed after the number of input table rows equal to
ccount are processed by RELOAD for each table. A COMMIT
statement will also be executed after the last row of each table has been
reloaded.
| Note: | Database Services Utility AUTOCOMMIT ON processing must be in effect when you
use RELOAD COMMITCOUNT processing. If AUTOCOMMIT is OFF and the
COMMITCOUNT parameter is used, an error message is written and RELOAD command
processing is not performed.
|
- RESTARTTABLE (table_name)
- identifies at which table the RELOAD DBSPACE processing will be
restarted. If a RELOAD DBSPACE operation ended normally, and the RELOAD
DBSPACE statement included the COMMITCOUNT parameter, the RELOAD DBSPACE
operation can be restarted by using the RESTARTTABLE and RESTARTCOUNT
parameters. table_name identifies the table where RELOAD
processing should begin. If RESTARTTABLE is omitted, RELOAD DBSPACE
processing will begin reloading the first table, and the RESTARTCOUNT
parameter, if specified, will apply to the first table.
| Note: | If the table does not exist in the database when RELOAD DBSPACE with
RESTARTCOUNT or RESTARTTABLE is issued, an error message is displayed.
|
- RESTARTCOUNT (rcount)
- identifies the restart point for RELOAD processing. rcount
is a number from 1 to 2,147,483,647 that identifies the number of input table
rows in the restart table to be skipped before RELOAD command processing
begins. If RESTARTCOUNT is omitted, no table rows are skipped and
RELOAD processing begins with the first table row of the restart table.
| Note: | If an end-of-table condition occurs before rcount rows of the
restart table are read, an error message is written before RELOAD processing
ends.
|
- BLKSZ (size) (DB2 Server for VSE Only)
- is an optional parameter that specifies the block size of the sequential
output file. The default block size is 2048 bytes per block.
- PDEV (TAPE or DASD)
- is an optional parameter that specifies the device type (DASD or TAPE) of
the sequential (SAM) input file. Specify PDEV(DASD) if the input file
resides on any device supported by the VSE DTFSD macro. An exception to
this is VSAM-managed SAM files. VSAM-managed SAM does not
support spanned records. Specify PDEV(TAPE) if the input file resides
on a device supported by the VSE DTFMT macro. The default is
PDEV(TAPE).
BLKSZ and PDEV can be specified in any order but must occur after the
ddname parameter.
- REWIND or NOREWIND
- controls tape file rewind processing performed during OPEN
processing. This parameter is valid only if you specify TAPE for
PDEV.
The default processing is REWIND.
- REWIND
- specifies that the tape file is rewound by OPEN processing.
- NOREWIND
- specifies that the tape file is not rewound by OPEN processing. If
NOREWIND is specified for input tape files referenced by a series of RELOAD
commands, you must ensure that the tape files being referenced are in
ascending sequence. For example, if NOREWIND is specified in a sequence
of two RELOAD commands and the first command reads tape file 2, then the
second command must reference tape file 3 or higher number. If it
references tape file 1, an OPEN error occurs.
|Changes were required in Version 7 Release 1 to handle file I/O
|correctly when using CMS 15 and later. These changes affect the format
|of data that is unloaded and reloaded by the UNLOAD and RELOAD commands of the
|DBS Utility. If you use the DBS Utility's UNLOAD and RELOAD
|commands with databases at different release levels, you must ensure that the
|code changes have been applied at all release levels. For releases
|prior to Version 7 Release 1, you must apply the following APARs:
|
| Release
| APAR
|
| 3.5
| PQ28584
|
| 5.1
| PQ28583
|
| 6.1
| PQ27957
|
VSE Format:
>>-RELOAD TABLE----(table_name)---+-PURGE---------------+------->
'-NEW--(dbspace_name)-'
>-----+------------------------+-------------------------------->
'-INTABLE--(table_name)--'
>----INFILE--(--ddname--+-------------------------------+------->
| .-2048------. |
'-BLKSZ--(--+-+------+--+---)---'
'-size-'
>-----+----------------------------------+--)------------------->
| .-REWIND---. |
| .-(TAPE)--+-NOREWIND-+--. |
'-PDEV--+-(DASD)----------------+--'
>-----+----------------------------+---------------------------->
'-COMMITCOUNT--(--ccount--)--'
>-----+-----------------------------+--------------------------><
'-RESTARTCOUNT--(--rcount--)--'
VM Format:
>>-RELOAD TABLE----(table_name)---+-PURGE---------------+------->
'-NEW--(dbspace_name)-'
>-----+------------------------+--INFILE--(ddname)-------------->
'-INTABLE--(table_name)--'
>-----+----------------------------+---------------------------->
'-COMMITCOUNT--(--ccount--)--'
>-----+-----------------------------+--------------------------><
'-RESTARTCOUNT--(--rcount--)--'
VSE Examples:
RELOAD TABLE(SALARY)
NEW(DBSPACE1)
INTABLE(SMITH.SALARY)
INFILE(CIPHER3 PDEV(TAPE))
RELOAD TABLE(SALARY)
NEW(DBSPACE1)
INTABLE(SMITH.SALARY)
INFILE(CIPHER3)
COMMITCOUNT(300)
RESTARTCOUNT(600)
VM Example:
RELOAD TABLE(SALARY)
NEW(DBSPACE1)
INTABLE(SMITH.SALARY)
INFILE(CIPHER3)
COMMITCOUNT(300)
RESTARTCOUNT(600)
Authorization:
You must have the INSERT privilege on the "target" table.
Additional authority is required depending on the keywords specified.
RESOURCE-if NEW is specified.
DELETE and INSERT-if PURGE is specified and the table is owned by another
user.
DBA-if PURGE is specified, and if any indexes defined on an affected table
are owned by someone else.
|
| Note: | The RELOAD TABLE command is not supported if you are using DRDA
flow.
|
- TABLE (table_name)
- identifies a RELOAD TABLE request and the table to be loaded. You
can further identify the table by specifying the owner of the table
(see Qualifying Object Names for details). You cannot use a synonym for a
table_name. If you specify the NEW option, a table called
table_name is created for that user. If you specify the
PURGE option, you can specify a view name instead of a table name if the view
meets the following requirements:
- The view is defined on a single table.
- The view definition includes all the NOT NULL columns in the table.
That is, all columns outside of the view definition must permit the insertion
of nulls.
- The view has no column definitions based on functions (virtual data
columns).
When reloading data into a view that was created using the WITH CHECK OPTION
clause, the database manager checks all inserts and updates to the view
against the view definition and rejects them if the row to be inserted or
updated does not conform to the view definition.
- NEW (dbspace_name)
- instructs the Database Services Utility that the table to
be loaded does not exist and must first be created. You can identify
the dbspace by the owner. If you do not specify the owner of
the dbspace (see Qualifying Object Names for information about owner), a private dbspace
that you own with dbspace_name specified is loaded. If no
such private dbspace exists, a public dbspace with dbspace_name is
loaded. The owner of a public dbspace is PUBLIC, for example, NEW
(PUBLIC.PRODUCTION). If owner is specified for the
table name and owner is not specified for the dbspace name, the
Database Services Utility does not use the owner specified for the
table name to identify the private dbspace.
If the RESTARTCOUNT parameter appears on the RELOAD TABLE command, the NEW
parameter will not cause the table to be created. The RESTARTCOUNT
parameter indicates that the RELOAD TABLE operation is being restarted,
therefore, NEW processing must have already occurred, so it is not required to
create the table again.
- PURGE
- identifies that the output table (table to be loaded)
exists and that all existing table rows should be deleted by RELOAD TABLE
processing before loading. You must have the DELETE privilege on the
output table. If you are not the owner of the output table, you require
DELETE and INSERT authority for the table. If any indexes for the table
are owned by another user, you require DBA authority.
If the RESTARTCOUNT parameter appears on the RELOAD TABLE command, the
PURGE parameter will not cause all row to be deleted. The RESTARTCOUNT
parameter indicates that the RELOAD TABLE operation is being restarted,
therefore, PURGE processing must have already occurred, so it is not required
to delete all rows from the table again.
| Note: | You must specify either NEW or PURGE in the RELOAD TABLE statement.
Because existing tables might be greatly affected by the choice of these
parameters, there is no default specification.
|
- INTABLE (table_name)
- is optional. If omitted, the Database Services Utility
loads data from the first table it finds in the input file. INTABLE
identifies data in the input file to be used for RELOAD TABLE
processing. Because the input file must be created by UNLOAD
processing, the data is organized by the tables from which it was
unloaded. Thus, the table_name that you specify here is the
name of a table that was unloaded at an earlier time. This parameter is
useful if your input file was created by an UNLOAD DBSPACE command. The
UNLOAD DBSPACE command can unload many tables into a sequential file.
The INTABLE parameter merely identifies which of those tables you now want to
reload.
You can use owner to specify the user ID of the person who created
the table in the input file. If you omit the owner (see Qualifying Object Names for more information about owner), the utility uses
the data of the first table encountered in the input file with the
table_name specified. In this instance, owner does
not default to the user ID of the current Database Services Utility
user.
- INFILE (ddname)
- identifies and describes the sequential
(SAM) input file containing the data to be loaded into the table. The
file must be created with UNLOAD processing.
The default record format in a DB2 Server for VSE system is variable-length
blocked, spanned (SB), with LRECL=(BLKSIZE-4) for variable and spanned
records or LRECL=BLKSIZE for fixed and undefined records.
The default record format in a DB2 Server for VM system is variable-length
blocked, spanned (VBS), with block size and record format information
specified by a CMS FILEDEF command; the LRECL parameter is not
applicable.
- COMMITCOUNT (ccount)
- identifies the frequency of COMMIT action during RELOAD processing.
ccount is a number from 1 to 2,147,483,647 indicating that a COMMIT
statement should be executed after the number of input table rows equal to
ccount are processed by RELOAD TABLE.
| Note: | Database Services Utility AUTOCOMMIT ON processing must be in effect when you
use RELOAD COMMITCOUNT processing. If AUTOCOMMIT is OFF and the
COMMITCOUNT parameter is used, an error message is written and RELOAD command
processing is not performed.
|
- RESTARTCOUNT (rcount)
- identifies the restart point for RELOAD processing. rcount
is a number from 1 to 2,147,483,647 that identifies the number of input table
rows to be skipped before RELOAD command processing begins. Row
rcount + 1 will be the first row to be reloaded. If
RESTARTCOUNT is omitted, no rows are skipped and RELOAD processing begins with
the first input row.
| Note: | If an end-of-table condition occurs before rcount rows are read from
the input UNLOAD file, an error message is written before RELOAD processing
ends.
|
- ddname
- in DB2 Server for VSE: this is the TLBL or DLBL job control
statement file name for the sequential input file.
In DB2 Server for VM: this is the name of the sequential input file
defined with a CMS FILEDEF command. Except for the ddname, CMS FILEDEF
command information for RELOAD command processing should be identical to the
information in the FILEDEF command used when the file was created by UNLOAD
command processing. You must define a CMS file used for RELOAD command
input with the file-mode number 4. Do not specify
SYSIN or SYSPRINT as the ddname.
- BLKSZ (size)
- is an optional parameter that specifies the block size of the sequential
output file. The default block size is 2048 bytes per block.
- PDEV (TAPE or DASD)
- is an optional parameter that specifies the device type (DASD or TAPE) of
the sequential (SAM) input file. Specify PDEV(DASD) if the input file
resides on any device supported by the VSE DTFSD macro. An exception to
this is VSAM-managed SAM files. VSAM-managed SAM does not
support spanned records. Specify PDEV(TAPE) if the input file resides
on a device supported by the VSE DTFMT macro. The default is
PDEV(TAPE).
BLKSZ and PDEV can be specified in any order but must occur after the
ddname parameter.
- REWIND or NOREWIND
- controls tape file rewind processing performed during OPEN
processing. This parameter is valid only if you specify TAPE for
PDEV. The default processing is REWIND.
- REWIND
- specifies that the tape file is rewound by OPEN processing.
- NOREWIND
- specifies that the tape file is not rewound by OPEN processing. If
NOREWIND is specified for input tape files referenced by a series of RELOAD
commands, you must ensure that the tape files being referenced are in
ascending sequence. For example, if NOREWIND is specified in a sequence
of two RELOAD commands and the first command reads tape file 2, then the
second command must reference tape file 3 or higher. If it references
tape file 1, an OPEN error occurs.
|Changes were required in Version 7 Release 1 to handle file I/O
|correctly when using CMS 15 and later. These changes affect the format
|of data that is unloaded and reloaded by the UNLOAD and RELOAD commands of the
|DBS Utility. If you use the DBS Utility's UNLOAD and RELOAD
|commands with databases at different release levels, you must ensure that the
|code changes have been applied at all release levels. For releases
|prior to Version 7 Release 1, you must apply the following APARs:
|
| Release
| APAR
|
| 3.5
| PQ28584
|
| 5.1
| PQ28583
|
| 6.1
| PQ27957
|
VM Format:
>>-UNLOAD DBSPACE----(dbspace_name)--OUTFILE----(ddname)-------><
VSE Format:
>>-UNLOAD DBSPACE----(dbspace_name)----------------------------->
>----OUTFILE--(--ddname--+---------------------------+---------->
| .-2048--. |
'-BLKSZ--(--+-size--+---)---'
>-----+-----------------------------------+--)-----------------><
| .-NOREWIND--. |
| .-(TAPE)--+-REWIND----+--. |
'-PDEV--+-(DASD)-----------------+--'
VM Example:
UNLOAD DBSPACE(THOMPSON.SPACE1) OUTFILE(HISTORY)
VSE Example:
UNLOAD DBSPACE(MIKE.SP2) OUTFILE(SAVE BLKSZ(2048))
Authorization:
You must have the SELECT privilege on the table(s) being
unloaded.
UNLOAD DBSPACE unloads all tables of the specified dbspace to a sequential
output file.
|
| Note: | The UNLOAD DBSPACE command is not supported if you are using DRDA
flow.
|
Following are the descriptions for each portion of the command:
- DBSPACE (dbspace_name)
- identifies an UNLOAD DBSPACE request and the dbspace to be
unloaded. The utility unloads the tables of the dbspace in an
unpredictable order. The dbspace_name is the name of the
dbspace to be unloaded. If you do not specify owner, the
utility unloads one of your dbspaces. See Names and Identifiers for more information about naming conventions for data
objects. If you do not own a dbspace called dbspace_name,
the utility unloads a public dbspace having that name. If there is no
public dbspace having that name, UNLOAD processing is unsuccessful, and an
error message is written to the Database Services Utility message file.
For example, suppose your user ID is GENE and you specify:
UNLOAD DBSPACE(SPACE1) ...
The Database Services Utility unloads the private dbspace named
GENE.SPACE1. If there is no such dbspace, the utility unloads
the public dbspace named PUBLIC.SPACE1. If there is no
PUBLIC.SPACE1, no dbspace is unloaded, and you receive an error message
in the message file.
If you own a private dbspace with the same name as a public dbspace, and
you want to unload the public dbspace, you must specify
PUBLIC.dbspace_name. If owner is omitted,
the private dbspace is unloaded.
- OUTFILE|OUTFILE (ddname)
- identifies and describes the sequential (SAM) output file that is to
contain the data unloaded from the dbspace. The default record format
is variable-length blocked, spanned (VBS). A minimum logical record
length (LRECL) of 8240 bytes is the default in DB2 Server for VSE. A
block size greater than 8244 is recommended for tape output files to improve
performance.
- ddname
- DB2 Server for VSE
This is the TLBL or DLBL job control statement file name for the sequential
output file.
| Note: | If the message ARI0868I generated during Database Services Utility UNLOAD
command processing identifies RECFM=VS for an output file defined with RECFM
VBS, the file can be read by Database Services Utility RELOAD command
processing using RECFM VBS.
|
DB2 Server for VM
This is the name of the sequential output file defined with a CMS FILEDEF
command. The FILEDEF command should contain the record format
specification RECFM VBS or a block size (BLOCK or BLKSIZE) value or
both. You must define a CMS file used for UNLOAD command output with
the file mode number 4.
If the row length (sum of defined column lengths) for any table in the
dbspace being unloaded exceeds 8 240 bytes, the largest row length value
is used as the minimum logical record length.
Notes:
- Always specify a record format (RECFM) of variable-length blocked, spanned
(VBS) for UNLOAD processing. UNLOAD processing changes the record
format to variable-length |spanned (VS).
- If the message ARI0868I generated during Database Services Utility UNLOAD
command processing identifies RECFM=VS for an output file defined with
RECFM=VBS, Database Services Utility RELOAD command processing can read the
file by using RECFM=VBS.
- If the message ARI0868I indicates RECFM=VS for a tape output file,
significant performance improvements can be obtained by increasing the block
size (BLOCK) value for the file. A block size greater than 8244 bytes
is recommended for tape output files created by UNLOAD processing.
Do not specify SYSIN or SYSPRINT as the ddname.
- BLKSZ (size) (DB2 Server for VSE Only)
- is an optional parameter that specifies the block size of the sequential
output file. The default block size is 2048 bytes per block.
- PDEV (TAPE or DASD) (DB2 Server for VSE Only)
- is an optional parameter that specifies the device type (DASD or TAPE) for
the sequential output file. Specify PDEV(DASD) for files that reside on
any device supported by the VSE DTFSD macro. An exception to this is
VSAM-managed SAM files. VSAM-managed SAM does not support
spanned records. Specify PDEV(TAPE) for files that reside on any device
supported by the VSE DTFMT macro. The default is PDEV(TAPE).
BLKSZ and PDEV can be specified in any order but must occur after the
ddname parameter.
- NOREWIND or REWIND (DB2 Server for VSE Only)
- controls tape file rewind processing performed during CLOSE
processing. This parameter is valid only if you specified TAPE for
PDEV. The default is NOREWIND.
- NOREWIND
- specifies that the tape file will not be rewound by CLOSE
processing.
- REWIND
- specifies that the tape file is rewound by CLOSE processing.
|Changes were required in Version 7 Release 1 to handle file I/O
|correctly when using CMS 15 and later. These changes affect the format
|of data that is unloaded and reloaded by the UNLOAD and RELOAD commands of the
|DBS Utility. If you use the DBS Utility's UNLOAD and RELOAD
|commands with databases at different release levels, you must ensure that the
|code changes have been applied at all release levels. For releases
|prior to Version 7 Release 1, you must apply the following APARs:
|
| Release
| APAR
|
| 3.5
| PQ28584
|
| 5.1
| PQ28583
|
| 6.1
| PQ27957
|
VSE Format:
>>-UNLOAD TABLE----(table_name)--------------------------------->
>----OUTFILE--(--ddname--+--------------------------+----------->
| .-2048--. |
'-BLKSZ--(--+-size--+---)--'
>-----+-----------------------------------+--)-----------------><
| .-NOREWIND--. |
| .-(TAPE)--+-REWIND----+--. |
'-PDEV--+-(DASD)-----------------+--'
VM Format:
>>-UNLOAD TABLE----(table_name)--OUTFILE----(ddname)-----------><
VSE Example:
UNLOAD TABLE (EMPLOYEE) OUTFILE(SAVE13 PDEV(DASD))
VM Example:
UNLOAD TABLE (EMPLOYEE) OUTFILE(SAVE13)
Authorization:
You must have the SELECT privilege on the table being unloaded.
The UNLOAD TABLE command unloads a specific table or view to an output
file.
|
| Note: | The UNLOAD TABLE command is not supported if you are using DRDA
flow.
|
Following are descriptions of each portion of the command:
- TABLE (table_name)
- identifies an UNLOAD TABLE request and the table to be processed.
You can UNLOAD a view merely by specifying a view name instead of a table
name. You can further identify the table or view by specifying the
owner of the table or view (see Qualifying Object Names for details). A synonym cannot be used for
table_name.
- OUTFILE|OUTFILE (ddname)
- identifies and describes the sequential (SAM) output file that is to
contain the data unloaded from the table. The default record format is
variable-length blocked, spanned (VBS). A minimum logical record length
(LRECL) of 8240 bytes is the default in a VSE system. To improve
performance, a block size greater than 8244 bytes is recommended for tape
output files.
- ddname
- in DB2 Server for VSE: this is the TLBL or DLBL job control
statement file name for the sequential output file.
| Note: | If the message ARI0868I generated during Database Services Utility UNLOAD
command processing identifies RECFM=VB for an output file defined with RECFM
VBS, the file can be read by Database Services Utility RELOAD command
processing using RECFM VBS.
|
In DB2 Server for VM: this is the name of the sequential output file
defined with a CMS FILEDEF command. The FILEDEF command should contain
the record format specification RECFM VBS or a block size (BLOCK or BLKSIZE)
value or both. You must define a CMS file used for UNLOAD command
output with the file mode number 4. UNLOAD processing writes
variable-length spanned records with a minimum logical record length (LRECL)
of 8240 bytes.
Notes:
- A record format (RECFM) of variable-length blocked, spanned (VBS) should
always be specified for UNLOAD processing. UNLOAD processing changes
the record format to variable-length |spanned (VS).
- If the message ARI0868I generated during Database Services Utility UNLOAD
command processing identifies RECFM=VS for an output file defined with
RECFM=VBS, the file can be read by Database Services Utility RELOAD command
processing using RECFM=VBS.
- If the message ARI0868I indicates RECFM=VS for a tape output file,
significant performance improvements can be obtained by increasing the block
size (BLOCK) value for the file. A block size greater than 8244 is
recommended for tape output files created by UNLOAD processing.
Do not specify SYSIN or SYSPRINT as the ddname.
- BLKSZ (size)
- is an optional parameter that specifies the block size of the sequential
output file. The default block size is 2048 bytes per block.
- PDEV (TAPE or DASD)
- is an optional parameter that specifies the device type (DASD or TAPE) for
the sequential output file. Specify PDEV(DASD) for files that reside on
any device supported by the VSE DTFSD macro. An exception to this is
VSAM-managed SAM files. VSAM-managed SAM does not support
spanned records. Specify PDEV(TAPE) for files that reside on any device
supported by the VSE DTFMT macro. The default is PDEV(TAPE).
BLKSZ and PDEV can be specified in any order but must occur after the
ddname parameter.
- NOREWIND or REWIND
- controls tape file rewind processing performed during CLOSE
processing. This parameter is valid only if you specify TAPE for
PDEV. The default is NOREWIND.
- NOREWIND
- specifies that the tape file is not rewound by CLOSE processing.
- REWIND
- specifies that the tape file is rewound by CLOSE processing.
|Changes were required in Version 7 Release 1 to handle file I/O
|correctly when using CMS 15 and later. These changes affect the format
|of data that is unloaded and reloaded by the UNLOAD and RELOAD commands of the
|DBS Utility. If you use the DBS Utility's UNLOAD and RELOAD
|commands with databases at different release levels, you must ensure that the
|code changes have been applied at all release levels. For releases
|prior to Version 7 Release 1, you must apply the following APARs:
|
| Release
| APAR
|
| 3.5
| PQ28584
|
| 5.1
| PQ28583
|
| 6.1
| PQ27957
|
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
