UNLOAD

The UNLOAD online utility copies data from one or more source objects to one or more BSAM sequential data sets in external formats. The output records that the UNLOAD utility writes are compatible as input to the LOAD utility. Therefore, you can use this output to reload the original table or different tables.

Although the function of the UNLOAD utility is often referred to as unloading data, the data is not deleted from the source object. The utility just makes a copy of the data. That copy includes the data only; it does not include all of the pages, such as the system pages and header pages, that are included in an image copy.

The source for UNLOAD can be DB2® table spaces or DB2 image copy data sets. Start of change The source cannot be a concurrent copy or a FlashCopy® image copy. End of change

You can unload rows from an entire table space or select specific partitions or tables to unload. You can also select columns by using the field specification list. If a table space is partitioned, you can unload all of the selected partitions into a single data set. Alternatively, you can unload each partition in parallel into physically distinct data sets.

UNLOAD must be run on the system where the definitions of the table space and the table exist.

Output

UNLOAD generates an unloaded table space or partition.

Authorization required

To execute this utility, you must use a privilege set that includes one of the following authorities:

Ownership of the tables
SELECT privilege on the tables
DBADM authority for the database. If the object on which the utility operates is in an implicitly created database, DBADM authority on DSNDB04 or the implicitly created database is sufficient.
DATAACCESS authority
SYSADM authority
SYSCTRL authority (catalog tables only)
SQLADM authority (catalog tables only)
System DBADM authority (catalog tables only)
ACCESSCTRL authority (catalog tables only)
SECADM authority (catalog tables only)

If you use RACF® access control with multilevel security and UNLOAD is to process a table space that contains a table that has multilevel security with row-level granularity, you must be identified to RACF and have an accessible valid security label. Each row is unloaded only if your security label dominates the data security label. If your security label does not dominate the data security label, the row is not unloaded, but DB2 does not issue an error message.

Execution phases of UNLOAD

The UNLOAD utility operates in these phases:

Phase: Description
UTILINIT: Performs initialization.
UNLOAD: Unloads records to sequential data sets. One pass through the input data set is made. If UNLOAD is processing a table space or partition, DB2 takes internal commits. These commits provide commit points at which the utility can be restarted if the utility stops in this phase.
UTILTERM: Performs cleanup.

Syntax and options of the UNLOAD control statement
The UNLOAD utility control statement, with its multiple options, defines the function that the utility job performs.
Before running UNLOAD
Certain activities might be required before you run the UNLOAD utility, depending on your situation.
Unloading partitions
You can unload partitions in one of two ways.
Unloading XML data
You can unload XML data in one of two ways.
Unloading LOB data
You can unload LOB data in one of two ways.
Unloading data in spanned record format
If you want to unload data from a table that has large LOB or XML fields, consider unloading the data in spanned record format to improve the performance of read/write operations.
Selecting tables and rows to unload
If a table space contains multiple tables, you can select specific tables to unload.
Selecting and ordering columns to unload
Use a field specification list in a FROM TABLE clause to unload specified columns in the listed order. If you omit a field specification list, all the columns in the row are unloaded in the order of the columns that are defined on the table.
Unloading data from image copy data sets
In addition to unloading data from table spaces and partitions, you can also unload data from one or more image copy data sets. The UNLOAD utility accepts full image copies, incremental image copies, and copies of pieces as valid input sources.
Data conversion with the UNLOAD utility
You can convert one data type to another compatible data type by using the UNLOAD utility. The source type is used for user-defined distinct types.
Output field types
An output field can have a different data type from the one that is defined on a source table column if the data types are compatible. The UNLOAD utility follows the general DB2 rules and conventions for the data type attributes and the compatibility among the data types.
Output field positioning and size
By default, output data is always placed in an output record in the order of the defined columns over the selected tables. You can choose to specify the order of the output fields by using a list of field specifications.
Layout of output fields
The layout of output fields vary based on a variety of factors, including whether the field is varying-length or fixed-length and whether the field is nullable.
Output for special values Infinity, sNaN, or NaN
When you run UNLOAD against a DECFLOAT column that contains the special values Infinity, sNaN, or NaN, the output is in uppercase.
Unloading delimited files
You can use the DELIMITED option to specify that UNLOAD is to produce an output file in delimited format. All fields in the output data set are either in character string or numeric external format. Each column is separated from the next by a column delimiter, and character strings are marked by character string delimiters.
Specifying TRUNCATE and STRIP options for output data
You can unload certain types of data into output fields that are shorter than the length of the output data. This data truncation occurs only when you explicitly specify the TRUNCATE option. Any CCSID conversion is applied first, and then truncation is applied to encoded data for output.
LOAD statements that are generated by UNLOAD
The UNLOAD utility can generate one or more LOAD utility statements that you can later use to load the unloaded data into either the original table or different tables. The generated LOAD statements are written to the data set that is specified by the PUNCHDDN option. By default, that data set is SYSPUNCH.
Unloading compressed data
You can unload compressed rows from an image copy data set only when the dictionary for decompression has been retrieved. If a row is compressed and the dictionary pages have not been read when the row is encountered, the UNLOAD utility ignores this row, issues a warning message, and increments the error count.
Field specification errors
If the UNLOAD utility detects any inconsistency relating to the field specification, DB2 issues an error message. For example, the UNLOAD utility might detect a data conversion problem or an encoding problem that occurs during the unloading of a row.
Termination or restart of UNLOAD
You can terminate and restart an UNLOAD utility job.
Sample UNLOAD control statements
Use the sample control statements as models for developing your own UNLOAD control statements.

Parent topic: DB2 online utilities

Related concepts:

Multilevel security