Control Center Operations Guide for VSE

Using the DBSPACE REORGANIZATION Tool

The DBSPACE Reorganization tool can be used in a variety of ways to achieve different goals. Each of the options is discussed, followed by a sample JCL stream produced by the program:

Option 1 - GENERATE DDL

By reading the catalogs, this option generates the DDL necessary to

re-create a DBSPACE and all of its associated objects. DDL is written to the VSE/POWER punch queue in the form of DBSU commands and can be used, as is, to redefine the DBSPACE. This option:

• Relieves DBAs from having to maintain large libraries of DDL
• Saves library disk space
• Solves the problem of who owns the "official" DDL
• Provides an easy way to determine table and index characteristics
• Provides authorization and "where-used" information

Figure 5 is an example of the jobstream produced by Control Center to generate DDL for the PUBLIC.SAMPLE DBSPACE.

Figure 5. DBSPACE Reorg Option 1 (Generate DDL) - Sample Jobstream

* $$ JOB JNM=GENDDL,CLASS=0,DISP=D,PRIO=3 * $$ LST PRI=3 * $$ PUN PRI=3 // JOB GENDDL MUM GENERATE DDL // OPTION LOG * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * STEP0001 UNLOAD DDL * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * // DLBL SQMPARM,'SQLMSTR.REORG.PARMS',,VSAM,CAT=SQMCAT // DLBL SQMDDL,'L.SQLDS710.PUBLIC.SAMPLE',0,VSAM, X RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT // ASSGN SYS005,SYSRDR // ASSGN SYS006,SYSPCH // ASSGN SYS011,SYSLST // EXEC SQB01,SIZE=AUTO %%SQLDBA PUBLIC SAMPLE 1 N /* /&

Option 2 - UNLOAD DBSPACE

This option writes the DDL necessary to recreate a DBSPACE to a VSAM file. It then unloads the DBSPACE to a SAM disk file (or a tape if that option was selected). The SAM disk data file is then REPRO'd to a VSAM-managed SAM file for more permanent retention. Data is unloaded in system-defined format; therefore, you must make sure that this data file is not altered prior to reloading the DBSPACE. This option is essentially a DBSPACE backup. Used in conjunction with a RELOAD DBSPACE (Option 4), it provides the capability to recover from application errors.

Figure 55 shows a jobstream that was generated by Control Center to unload the PUBLIC.SQMHELP DBSPACE. See Appendix B, DBSPACE and Table Reorganization Tool Related Files for information about the file.

Option 3 - REORGANIZE DBSPACE

This option schedules a full DBSPACE reorganization, including capturing all related DDL (DROP DBSPACE, ACQUIRE DBSPACE, CREATE TABLE, RELOAD DBSPACE) and executing it. In addition, depending on the optional parameters chosen, a DBSPACE can be migrated to another storage pool or another owner. A DBSPACE may also be changed from private to public, or vice versa. The DBSPACE can be moved to another database, as well as have its characteristics, number of pages, percent free space, and percent index changed. This is the most comprehensive option of the reorganization tool.

Figure 56 is an example of a jobstream that was generated by Control Center to reorganize the PUBLIC.SQMHELP DBSPACE.

Option 4 - RELOAD DBSPACE

This option submits a job to reload a DBSPACE previously UNLOADED or REORGANIZED using Control Center. The previously created DDL and data files are used to re-create the DBSPACE in its entirety. This option is the recovery counterpart to the UNLOAD DBSPACE option, (Option 2) and is the method of recovering from an error during a reorganization RELOAD step.

Figure 57 shows a sample jobstream that was generated by Control Center to reload the PUBLIC.SQMHELP DBSPACE.