This section describes the UNLOAD commands provided by the Database Services Utility.
Database Services Utility UNLOAD TABLE and UNLOAD DBSPACE processing allows you to unload tables and views to a sequential file. You can later use this file as input to Database Services Utility RELOAD TABLE and RELOAD DBSPACE processing (described in Chapter 4, "Reloading Data with the Database Services Utility"). With the UNLOAD commands, you cannot unload data in a user-defined format.
| Note: | You cannot use the UNLOAD DBSPACE and UNLOAD TABLE commands if you are using DRDA flow. |
The following is a brief description of the two UNLOAD commands:
| Unloading a Dbspace or Table That You Do Not Own |
|---|
|
To unload dbspaces and tables that you do not own, concatenate the owner's user ID to the dbspace name (for example, SMITH.PERSONNEL). You must have the SELECT privilege on all tables in the dbspace you want to unload. |
Figure 41 shows how the parts of an UNLOAD DBSPACE command relate to the dbspace and output file. In the figure, the command:
UNLOAD DBSPACE (SMITH.PERSONNEL) OUTFILE (SAVE)
unloads the dbspace called SMITH.PERSONNEL to the file SAVE.
Figure 41. Diagram of the UNLOAD DBSPACE Command
|
The format of the UNLOAD TABLE command is the same as UNLOAD DBSPACE, but instead of a dbspace, specify a table name. In Figure 42, the UNLOAD TABLE command unloads a single table called SMITH.DEPARTMENT to the output file SAVE.
Figure 42. Diagram of the UNLOAD TABLE Command
 |
The Database Services Utility UNLOAD processing writes all rows from a table or all rows from all tables in a dbspace as individual records to the sequential output file. Before producing these records, the utility writes records that contain information supporting the RELOAD function. The Database Services Utility RELOAD processing with the NEW parameter uses this information for creating the table(s).
Attention:
In RELOAD processing with the parameter NEW, the Database Services Utility must create the table. The table is not created if the CREATE TABLE statement used by the Database Services Utility is greater than 8192 bytes. If the statement is greater than 8192 bytes, you can use the RELOAD command only with the PURGE parameter and only if the table already exists.
The CREATE TABLE statement used by the Database Services Utility will be longer than the statement that you initially issue if:
The UNLOAD command does not unload any indexes, primary or foreign keys, or constraint definitions. Additionally, a package (preprocessed program) that depends on the unique constraint indexes is invalidated when unique constraints are dropped. All packages dependent on the table are invalidated when a unique constraint is added to the table because they might have UPDATE statements that cause multiple-row updates. Packages are also invalidated when unique constraints are activated or deactivated. When using the Database Services Utility RELOAD command with PURGE OPTION, however, you should ACTIVATE the unique constraints (in the ALTER TABLE statement) rather than re-create them because it is more efficient.
Furthermore, the Database Services Utility is sensitive to the tagging of character and graphic data (single byte, double byte, and mixed), which identify the format of the data, such as US or Kanji. The tags are maintained when you use the UNLOAD and RELOAD commands.
Attention:
Operate under isolation level repeatable read (the default Database Services Utility processing mode) when you use the UNLOAD command to ensure a consistent state of the database during backup or migration.
The command statements UNLOAD DBSPACE and UNLOAD TABLE are much simpler than the DATAUNLOAD statement because the UNLOAD commands do not specify the output fields. The lack of specifications leads to very long output records.
Such long output records make it difficult to locate specific data or to edit the output file. The UNLOAD commands are designed to provide output files for backup or subsequent reloading at remote sites (or locally, for purposes of reorganizing DB2 Server for VSE & VM data structures). Consequently, unloaded data should be used only for reloading purposes. The following UNLOAD DBSPACE command unloads all tables in a dbspace named PERSONNEL. The tables are placed in an output file called SAVE:
UNLOAD DBSPACE (PERSONNEL) OUTFILE (SAVE)
The syntax is simple, requiring only:
To unload an entire dbspace for backup or subsequent reloading, follow the procedure in Unloading Data in System-Defined Format, but use the UNLOAD DBSPACE command instead of the DATAUNLOAD command. The UNLOAD DBSPACE command uses the following structure:
UNLOAD DBSPACE (dbspace-name)
where dbspace-name is the name of the dbspace.
You can use UNLOAD TABLE to specify the table you want to unload. UNLOAD DBSPACE unloads all the tables in a dbspace.
For example, if you make regular backups of the inventory table of a small company, you unload the table frequently. The table is in a dbspace with other tables that you do not need to backup as often. Use the UNLOAD TABLE command to backup only the inventory table. If your system fails, you can reload the table by using the RELOAD TABLE command, which is discussed in the next chapter.
The syntax of the UNLOAD TABLE command is similar to that of the UNLOAD DBSPACE command, requiring only:
To unload a table for backup or subsequent reloading, use the same procedure given in Unloading Data in System-Defined Format, but use the UNLOAD TABLE command instead of the DATAUNLOAD command. The UNLOAD TABLE command has the following structure:
UNLOAD TABLE (table-name)
where table-name is the name of the table.
| Unloading Views |
|---|
|
You can unload a view (virtual table) with UNLOAD TABLE processing. The Database Services Utility processes views in the same manner as a table without indexes. |
In DB2 Server for VSE
Each file of a multiple-file tape volume must be identified with the correct file name and file sequence number of the TLBL statement for each tape file. Tape rewind processing is controlled by the VSE job control statements.
In DB2 Server for VM
If you want to do a simple Database Services Utility command, such as unload one table to a DASD file, and you are not going to repeat this command regularly, use the utility interactively to avoid creating a control file and specifying a message file.
If you have to execute several UNLOAD TABLE commands or use the same command again, use message and control files. For example, to unload the organization tables that are in the same dbspace as personnel tables, you need several UNLOAD TABLE commands to specify each organization table. To unload project activity tables for managers who have different dbspaces, you again need several UNLOAD TABLE commands. Putting the Database Services Utility commands in a control file enables you to use the commands again. Also, if any command fails, you can refer to the message file repeatedly to deal with each error message and correct the mistake in the control file without retyping all of the UNLOAD commands.
The UNLOAD DBSPACE and UNLOAD TABLE commands use at least three files: the control file, the message file, and one or more data output files.
| Multiple Output-File Possibilities |
|---|
|
You can have more than one UNLOAD TABLE or UNLOAD DBSPACE command within a single invocation of the utility; each command must unload data to a separate file, or the data is lost. You can unload data to a multiple-volume tape file. You can also unload data to multiple files on a single tape volume in one execution of the Database Services Utility. Each UNLOAD operation must unload to a separate file. The Database Services Utility does not rewind the tape when each file is opened for output. |
Figure 43 shows the relationship between the UNLOAD files and the appropriate definition facility (FILEDEF or SQLDBSU) for each.
 |
Each file of a multiple-file tape volume must be identified with the correct ddname and label specification in the CMS FILEDEF command issued for it. Tape rewind processing is controlled by FILEDEF command specifications and performed by CMS OS/QSAM.
| Specify VBS Record Format in the UNLOAD FILEDEFs |
|---|
|
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). |
A block size (BLOCK) greater than 8244 is recommended for tape output files created by UNLOAD processing.
An example of a FILEDEF statement defining a tape output file is:
FILEDEF SAVE2 TAP1 (RECFM VBS BLOCK 8244
where SAVE2 is the ddname that you used in the UNLOAD command.
The FILEDEF command defining the UNLOAD output data file can identify a CMS file with 4 appended to the file mode letter (for example, A4) or a sequential tape file supported by CMS OS/OSAM. Always specify a record format of VBS or a block size value (or both) in the FILEDEF command defining the data file.
| Note: | The message ARI0868I identifies the file characteristics used in the Database Services Utility's processing. If this message indicates RECFM=VS for a tape output file, you can obtain significant performance improvements by increasing the block size value specified in the FILEDEF command that defines the ddname. |
An example of a FILEDEF command defining a CMS file for UNLOAD processing is:
FILEDEF DBSFILE DISK DBSFILE DATA A4 (RECFM VBS BLOCK 2048
where DBSFILE is the ddname used in your UNLOAD command, and DBSFILE refers to the output file DBSFILE DATA A4.
For more information on FILEDEF parameters and options, see Appendix B, "FILEDEF Command Syntax and Notes".
|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 |