The RELOAD commands, like their UNLOAD counterparts, do not support user-defined data formats. System-defined format is the only option. You must use the OUTFILE output of the UNLOAD DBSPACE or UNLOAD TABLE commands as input to the RELOAD DBSPACE and RELOAD TABLE commands.
| Note: | For a VM application requestor, the RELOAD DBSPACE and RELOAD TABLE commands can only be used if a DB2 Server for VM application server is used and the protocol is either SQLDS or AUTO. For a VSE application requestor, the RELOAD DBSPACE and RELOAD TABLE commands can only be used if a DB2 Server is running on the same VSE system as the Database Services Utility or if the DB2 Server for VM is on VM and is accessed through Guest Sharing. |
Assuming that you have output from UNLOAD processing, and you want to use it as input to a DB2 Server for VSE & VM database, you must now decide whether to use the RELOAD DBSPACE or the RELOAD TABLE command.
| Note: | Use the UNLOAD command, not the DATAUNLOAD command. The unit of output of the DATAUNLOAD command is the table row whereas the UNLOAD commands have the table as their unit of output. |
RELOAD DBSPACE is usually associated with UNLOAD DBSPACE, and RELOAD TABLE is associated with UNLOAD TABLE. In practice, this is the most frequent pairing, but all four commands use identical data formatting. Sometimes changing the object when you go from UNLOAD to RELOAD is appropriate. Briefly, the objects that each command manipulates are:
You might want to use RELOAD DBSPACE with UNLOAD TABLE if you unload a single table and want to reload it into a dbspace. If you do not want to specify where the table goes in the dbspace, the RELOAD DBSPACE command achieves the same result as the RELOAD TABLE command. RELOAD DBSPACE is even more convenient because it has fewer parameters to specify.
You might want to use RELOAD TABLE with UNLOAD DBSPACE if you unload an entire dbspace and want to reload just one of its tables into another dbspace. Using the RELOAD DBSPACE command reloads the entire UNLOAD output file, not just the desired table.
| Processing Multiple Tables or Multiple Files |
|---|
|
The Database Services Utility's RELOAD processing does not support the concurrent loading of multiple tables. Sequential loading, however, is supported, as long as the tables are in the same file. During one invocation of the Database Services Utility, you can reload data from a multiple-volume tape file or from a multiple-file tape volume. In DB2 Server for VSE, to reload data from a multiple-file tape volume, you must specify the correct file name and file sequence number on the TLBL statement for each tape file. Because the Database Services Utility rewinds the tape when each file is opened for input, this information is necessary to locate the correct file on the tape. |
RELOAD DBSPACE processing loads tables serially in the order that they appear in the input file. Use the RELOAD DBSPACE command to supply four pieces of information to the Database Services Utility:
Figure 44 shows the command flow.
Figure 44. Diagram of the RELOAD DBSPACE Command
|
This figure shows a RELOAD DBSPACE operation on the RESOURCES dbspace, and the creation of new tables from the input file called HISTORY.
The RELOAD TABLE command is more precise than the RELOAD DBSPACE command. RELOAD TABLE specifies that you want to reload only one table no matter how many exist in the input file. In the RELOAD TABLE command, you give the following five pieces of information to the Database Services Utility:
Figure 45 shows the command flow.
Figure 45. Diagram of the RELOAD TABLE Command
 |
This figure shows a RELOAD TABLE operation on Smith's DEPARTMENT table, which must be purged first. The SAVE file is used as input.
If the UPDATE STATISTICS setting is ON, the RELOAD command automatically causes table statistics to be generated while the data is being reloaded.
| Indexing Notes |
|---|
|
With the PURGE Parameter: When a table is purged, the default clustering rules are used because all indexes for the purged table are dropped. With the NEW Parameter: When a new table is created, the column definitions are identical to the definitions of the table contained in the input file, except the keys and unique constraints are not reproduced. The new table also does not have any indexes defined for it. You must construct them yourself by issuing subsequent SQL CREATE INDEX statements. Using the NEW Parameter with Field Procedures: When a new table is created, the field procedures are not reproduced. Thus, using the reload 'NEW' parameter is not recommended for tables with field procedures. To reload tables with field procedures, use the 'PURGE' parameter. |
When reloading a dbspace or a table, you must either create new tables for the RELOAD input or purge old tables before reloading them. A particular table in the input file replaces the like-named table in the target dbspace if the PURGE parameter is specified, but it remains unloaded if the NEW parameter is in effect. Similarly, under the NEW option, a particular table in the input file remains unloaded when a like-named table with that name exists in any dbspace of the entire database; if no table with that name exists, however, the Database Services Utility creates a table into which the given input is loaded.
| Referential Integrity and the RELOAD Commands |
|---|
|
Referential integrity might affect the RELOAD commands. Specifically, if you create an unloaded file when there is no primary key, and there is a primary key on the target table at the time of RELOAD PURGE, the primary key becomes active after the RELOAD PURGE operation. If you do not want the primary key active, you must drop the primary key manually by using the ALTER TABLE statement. |
If the table being reloaded has an active primary key, the Database Services Utility records this fact and issues an ALTER TABLE table-name DEACTIVATE PRIMARY KEY command. The Database Services Utility also saves the active foreign key names, unique constraints, and their owner's name, before issuing an ALTER TABLE table-name DEACTIVATE FOREIGN KEY or DEACTIVATE UNIQUE KEY command. After the data has been loaded, the Database Services Utility reactivates the keys.
For more information about referential integrity, see the DB2 Server for VSE & VM Database Administration manual.
The Database Services Utility also preserves the tags for character and graphic data (single byte, double byte, and mixed) that identify the data format, such as US or Kanji. The tags are reloaded with the data when you use the RELOAD command.
A user is allowed to use a DBSU module from one release to connect to a database server containing a DBSU package at a different release. Specifically, in an UNLOAD TABLE/DBSPACE and RELOAD TABLE/DBSPACE scenario, there are 4 objects being used that may all be at different releases. For Data Capture, it is only necessary to consider whether the R710 release of the object is being used or a pre-R510 release is being used. In the chart below, "Unload Module" refers to the release of the DBSU module which the user is accessing when performing the unload operation. This may not be the same as the release of the database server which the user is connecting to. Similarly, "Reload Module" refers to the release of the DBSU module which the user is accessing when performing the reload operation. This may not be the same as the release of the database server which the user is connecting to. "Unload P/S" refers to the release of the database server and the release of the DBSU package contained in the database server in which the table is being unloaded. Similarly, "Reload P/S" refers to the release of the database server and the release of the DBSU package contained in the database server in which the table is being reloaded. The release of the DBSU package must be equal to the release of the database server where it is contained. Mixed releases are not supported.
In the most general case, a user can use a DBSU module at release A to
unload a table from a database server which is at release B. Then, the
user can use a DBSU module at release C to reload the table to another
database server at release D. The chart below can be used to determine
whether the DATA CAPTURE setting for the table will be restored.
Table 3. DATA CAPTURE settings and DBSU RELOAD and UNLOAD
| Unload P/S | Unload Module | Reload P/S | Reload Module | Comments |
|---|---|---|---|---|
| pre-R510 | n/a | n/a | n/a | Tables in a pre-R510 server do not contain a Data Capture setting. |
| R710 | pre-R510 | n/a | n/a | The pre-R510 unload module does not save the Data Capture setting so the setting will not be restored on the reload. |
| R710 | R710 | pre-R510 | n/a | Data Capture setting will be saved in the unload file but pre-R510 servers do not allow a Data Capture setting for tables so the setting will not be restored on the reload. |
| R710 | R710 | R710 | pre-R510 | Data Capture setting will be saved in the unload file but the pre-R510 reload module does not restore the Data Capture setting so the setting will not be restored on the reload. |
| R710 | R710 | R710 | R710 | Data Capture setting will be saved in the unload file and will be restored by the reload module. |
The PURGE keyword tells the Database Services Utility that the target table exists, and that all rows must be deleted from it before RELOAD TABLE processing begins. (If the target table does not exist, you receive an error message.) Of course, the column definitions of the target table must be identical to those of the source table.
The Database Services Utility, as part of PURGE processing, drops the clustering index, deactivates any active primary keys, active foreign keys, and active unique keys, and deletes all indexes on the target table before deleting and reloading the data. Therefore, you must have DBA authority to do a RELOAD with the PURGE option if the target table or any of its indexes are not yours. After all tables have been reloaded, the Database Services Utility reactivates the clustering index, primary key and unique keys, and re-creates the remaining indexes. It ensures that the first index that was created for the table (as recorded at PURGE time) is also the first index re-created. After all the tables are processed, the Database Services Utility reactivates all the foreign keys that it deactivated. DB2 Server for VSE & VM packages are invalidated because of table index deletions, but are automatically preprocessed the next time someone attempts to execute the package.
The following example illustrates the PURGE parameter:
RELOAD DBSPACE (RESOURCES) PURGE INFILE(HISTORY)
PURGE tells the Database Services Utility to delete all the rows of the table before loading the data. The table must, however, exist in the specified dbspace. Note also that fully qualified table names are always used internally for RELOAD DBSPACE. That is, if you unload JONES.EMP_ACT and use RELOAD DBSPACE with a PURGE option, JONES.EMP_ACT is the only table affected by the reload.
The specified dbspace must already exist before you can reload tables into it. The NEW parameter causes the utility to create tables, not dbspaces. If you are using UNLOAD and RELOAD processing to duplicate an existing dbspace (as for testing application programs), first acquire an appropriate dbspace. The SQL ACQUIRE DBSPACE statement is described in the DB2 Server for VSE & VM SQL Reference. If the table you are reloading does not replace a table already in the dbspace, the Database Services Utility can create the target table for you. In the following example, the source table EMPTABLE is not in the target dbspace:
RELOAD TABLE(EMPTABLE) NEW(PRODUCTION)
INTABLE(EMPLOYEE)
INFILE(SAVE)
The NEW parameter in the above command tells the Database Services Utility that the table (EMPTABLE) to be loaded does not exist and must be created. It also identifies the dbspace (PRODUCTION) where you want the table created. The Database Services Utility creates the EMPTABLE, finds the EMPLOYEE table on the input file (SAVE), and loads the data. The new table is created in a private dbspace, PRODUCTION, that the current user owns. If the current user does not own a private dbspace with the specified name, the table is created in a public dbspace with this name. If you want to have the new table created in a particular dbspace, specify:
NEW (dbspace-name)
where dbspace-name is the name of the dbspace.
In another example, suppose that user ID BOB is the current Database Services Utility user. BOB issues this command:
RELOAD DBSPACE (RESOURCES) NEW INFILE(HISTORY)
Suppose, also, that one of the tables in the HISTORY file is called BOB.EMPLOYEE. If BOB already owns a table called BOB.EMPLOYEE in any other dbspace, the table cannot be created and loaded in the RESOURCES dbspace. The user ID concatenated to the table name uniquely identifies a table within the database. Thus, if BOB.EMPLOYEE already exists, it is impossible for the utility to create another BOB.EMPLOYEE anywhere else in the database.
During RELOAD processing, the current percent free value for the dbspace being loaded, or for the dbspace where the table being loaded resides, can be critical. Before RELOAD processing begins, increase the percent free space value to reserve free space for additional rows inserted after the RELOAD process is completed. Immediately after RELOAD processing is completed, reduce the percent free value to allow the reserved free space to be used for the new rows. Refer to the DB2 Server for VSE & VM Database Administration for more information on the dbspace percent free specification.
To reload multiple tables into a dbspace where they already exist, proceed as follows:
In VSE
RELOAD DBSPACE (dbspace-name)
where dbspace-name is the name of the dbspace.
PURGE
INFILE(ddname)
where ddname identifies the input file. Use the same ddname in a TLBL or DLBL statement, depending on whether the data is stored on tape or in a DASD file.
In VM
RELOAD DBSPACE (dbspace-name)
where dbspace-name is the name of the dbspace.
PURGE
INFILE(ddname)
where ddname identifies the input data file. You need to use the same ddname when you specify the FILEDEF statement for the input file.
To reload multiple tables into a database where they do not exist, follow the procedure given in Reloading Several Tables into a Dbspace Where They Are Already Defined, but substitute NEW for PURGE. NEW indicates the replacement method for new tables.
If you have just one table to load into a dbspace (that was unloaded with an UNLOAD TABLE command), use the RELOAD DBSPACE command. Follow the procedure in Reloading Several Tables into a Dbspace Where They Are Already Defined, and use the appropriate replacement method (PURGE or NEW) for the table to be loaded.
| Note: | If your input file contains multiple tables but you do not want to reload all of them, use the RELOAD TABLE command. |
The reason for using the RELOAD TABLE command rather than the RELOAD DBSPACE command is to reload one particular table into a dbspace. The RELOAD DBSPACE command loads an entire input file of table data into a dbspace (subject to the constraints imposed by the NEW or PURGE parameters). Although RELOAD processing follows the input order of the data, UNLOAD output is unpredictable: you have no way of knowing the sequence of tables in the UNLOAD DBSPACE output file. In general, if you use output from an UNLOAD DBSPACE as input to RELOAD TABLE processing (meaning that you want to reload a specific table), use the INTABLE parameter with the RELOAD TABLE command.
To reload a single table into a dbspace where it already exists, proceed as follows:
In VSE
RELOAD TABLE (table-name)
where table-name is the name of the table.
PURGE
INFILE (ddname)
where ddname identifies the input file. Use the same ddname in a TLBL or DLBL statement, depending on whether the data is stored on tape or in a DASD file.
In VM
RELOAD TABLE (table-name)
where table-name is the name of the table.
PURGE
INFILE(ddname)
where ddname identifies the input data file. You need to use the same ddname when you specify the FILEDEF statement for the input file.
You can also reload views if the view meets the restrictions defined under the RELOAD TABLE command description (see RELOAD TABLE). Use the PURGE parameter to reload a view that was previously unloaded. Using PURGE makes use of an existing view definition and does not violate the rule that a view is a virtual table. The only difference between reloading a table and reloading a view is that statistics are not collected for a view.
To reload a single table into a dbspace where it is not defined, follow the procedure in Reloading a Single Table into a Dbspace Where It Is Already Defined, but use the following replacement method instead of PURGE:
NEW (dbspace)
where dbspace is the name of the dbspace where you want to create a new table.
The multitable input source referred to in this section is the output file from an UNLOAD DBSPACE command. If you do regular backups of a dbspace, and the data in one table is lost or modified incorrectly, reload the one table with the RELOAD TABLE command. Use the procedure in Reloading a Single Table into a Dbspace Where It Is Already Defined, but with the following differences:
INTABLE(table)
where table is the name of the source table.
INFILE (ddname)
where ddname identifies the input data file.
If you are reloading DB2 Server for VSE data from magnetic tape, identify each file of a multiple-file tape volume with the correct file name and file sequence number on the TLBL statement for each tape file. Because the Database Services Utility rewinds the tape when each file is opened for input, this information is necessary to locate the correct file on the tape.
| Notification of Records Reloaded |
|---|
|
If you are reloading more than 15,000 data records, messages (ARI8995I) are written to your terminal after every 15,000 records to inform you that a multiple of 15,000 records has been loaded. |
Suppose that a dbspace was unloaded and the dbspace contained two tables named EMPLOYEE. One of these EMPLOYEE tables was originally created by SCOTT, the other by MIKE. If you want to reload the EMPLOYEE table that was created by SCOTT, you should identify the table by prefixing the table name EMPLOYEE with the owner SCOTT in the INTABLE parameter:
RELOAD TABLE(EMPTABLE) NEW(PRODUCTION)
INTABLE(SCOTT.EMPLOYEE)
INFILE(SAVE)
If you do not, the Database Services Utility reloads the data of the first table it finds in the input file that has the same name. If you omit the INTABLE parameter completely, the utility uses the data of the first table it finds in the input file, regardless of the table name and owner.
RELOAD processing requires a control file and a data file for input, and a message file for output.
| Use the Same File Definition for RELOAD As for UNLOAD |
|---|
|
CMS FILEDEF command information for RELOAD command processing should be identical to the information in the FILEDEF command you used when UNLOAD command processing created the file. |
Figure 46 shows the relationship of the RELOAD files and the appropriate definition facility (FILEDEF or SQLDBSU) for each.
Figure 46. RELOAD DBSPACE and RELOAD TABLE Files
 |
The default record format for RELOAD processing is variable-length blocked spanned (VBS). If you specify a RECFM value other than VBS or a LRECL value with the CMS FILEDEF command, it is ignored and overridden. The message ARI0868I identifies the file characteristics used by Database Services Utility processing. A maximum length of BLKSIZE-4 is used for the spanned record segments.
| Isolation Level for RELOAD Operations |
|---|
|
Set the isolation level to repeatable read when you reload data to ensure a consistent state of the database during backup or migration. |
Identify each file of a multiple-file tape volume with the ddname and label specifications in the CMS FILEDEF command that you issue for each tape file. The Database Services Utility does not perform any tape rewind processing. Tape rewind processing is controlled by FILEDEF command specifications and performed by CMS OS/QSAM.
Use the UNLOAD and RELOAD commands (RELOAD with the PURGE option) to reorder the data records to match the indexes. Use the FILEDEF to specify a DISK file for quick and easy unloading and reloading. This reordering improves the efficiency of queries performed on your tables.
The FILEDEF command defining the Database Services Utility RELOAD output data file identifies a CMS file with 4 appended to the file mode letter (for example, A4) or a sequential tape file supported by CMS OS/QSAM. Always specify a record format of VBS or a block size value (or both) in the FILEDEF command defining the data file.
A sample of a FILEDEF command defining a CMS file for RELOAD processing is:
FILEDEF DBSFILE DISK DBSFILE DATA A4 (RECFM VBS BLOCK 2048
where DBSFILE is the ddname used in your RELOAD command and DBSFILE DATA A4 is the name of the input file.
For more information on FILEDEF parameters and options, see Appendix B, FILEDEF Command Syntax and Notes.