The Reorganize Table function is used to simplify your work for backup and restore, table reorganization, and saving table structure information. The function provides four options; see Figure 47.
You can choose one of the following options:
Each of the options is discussed in detail below and is accompanied by a sample JCL stream created by the Table Utility.
Figure 47. Table Reorganization Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*----------------------- TABLE REORGANIZATION UTILITY ------------------------* | || DATABASE => SQLDBA____________ | | || CREATOR => SQLDBA__ | | || TABLE => ACTIVITY__________ | | || FILE => 1 (1-3) | | || OPTION => 3 (1 GENERATE DDL 2 UNLOAD TABLE) | | || (3 REORGANIZE TABLE 4 RELOAD TABLE) | | || *********************** OPTIONAL PARAMETERS ******************* | | || DATABASE => __________________ | | || OWNER => ________ | | || DBSPACE => __________________ | | || CREATOR => ________ | | || TABLE => __________________ | | || | | || | | || REBIND PACKAGE => 1 (1=YES/2=NO) UPDATE ALL STATISTICS => : 2 (1/2) | | || COMMITCOUNT => __________ DATA CAPTURE => : none | | || | | || TLBL FILE-ID => _________________ | | |*------------------------------------------------------------------ SQC61 ----* | | | |PRESS ENTER TO PROCESS | |F1=HELP F3=EXIT | +--------------------------------------------------------------------------------+
The following is a summary of the various processing options:
If the Generate DDL option is specified, the utility will generate the table create statement and all associated database objects, including indexes, views, and grants.
This option creates a backup copy of a table using the UNLOAD TABLE DBSU command. The DDL will also be generated for backup purposes. The table will not be dropped.
If only the source database and table (at the top of the menu) are specified (meaning that this is not a migration), the utility will perform a table reorganization by generating a job to:
When data and DDL are both generated for a reorganization, the program has DBA authority for copying all dependent objects within the database (including those of other users). Control Center will copy the data using UNLOAD TABLE, will DROP and CREATE the table, RELOAD TABLE to reload the data, and then recreate all dependent objects (indexes, views, and grants).
The presence of optional parameters affects the ultimate type of reorganization selected. These are:
The utility will migrate data or table DDL between databases if the "optional" DATABASE parameter is specified. The source table will not be dropped or modified in any way.
The utility will copy the DDL and data if the "optional" CREATOR and/or TABLE parameter are specified. The target table will be dropped before being defined. The source table will not be dropped or modified in any way. It is important to note that if the target CREATOR is the same as the source table CREATOR, the INDEX and VIEW create names will be identical to the existing ones in the old table; the create statements will fail. A warning message will be displayed to this effect and then you can confirm whether to proceed or cancel processing.
The utility will move a table to a new DBSPACE if the optional OWNER and/or DBSPACE parameters are specified. Note that the old table will be dropped only if the new CREATOR and TABLE name remain the same as the old CREATOR and TABLE.
If you are moving to a database created prior to DB2 Version 5.1:
This option reloads a table from a previous backup made with the UNLOAD TABLE function. The unloaded table resulting from a reorganization function that failed can also be used as input to this option.
When you submit a table reorganization job, Control Center:
Special Processing Considerations:
To reach the Table Reorganization menu shown in Figure 47, choose Option 3 on the Table Utility main menu.
When the menu is displayed, the DATABASE, CREATOR and TABLE fields at the top of the menu will be filled in from the Table Utility main menu.
When you installed Control Center, you defined one to three SAM DDL files to hold generated DDL. Specify the number of the file you want to use in the FILE field. (1 selects the small file, 2 selects the medium file, 3 selects the large file.) The number also indicates what SAM data file to use if you have not entered a Tape File Name. You do not need to specify the file number if you choose Option 1 or 2, because the DDL is written to the punch queue instead of to a file.
Enter the number of the option you want to execute in the Option field.
Parameters below the "OPTIONAL PARAMETERS" line are not required.
If the new CREATOR/TABLE option is used, a copy will be performed. If a new CREATOR/TABLE is not specified, the table will be moved from the current DBSPACE to the new DBSPACE.
| Note: | The table view names and index names will not be changed, causing failure of the DDL CREATE statements if the original table CREATOR is the same as the new CREATOR. |
PACKAGES that are dependent on that table. To bypass package rebind processing, specify 2 (NO). The default value is 1 (YES). This option is not valid when the OWNER name is changed.
| Note: | To perform periodic statistics updates, use the Table Utility main menu Option 5, Update Statistics. |
When this parameter is set to NO, the statistics are reset only for the first column in each index. When set to YES, the statistics are reset for all columns in all indexes. The statistics include such things as the number of rows in a table and the number of unique values in an index column. They are used by SQL to optimize retrievals. Since this is can be a lengthy and resource consuming process, it is recommended that you do not reset all of the statistics during normal work hours. If you are reloading a table, this is an optimal time to reset ALL of the statistics.
After entering the desired parameters, press ENTER to proceed to the Table Reorganization Submit Screen.
The Table Reorganization option can be used in a variety of ways to achieve different goals. Each of the options is discussed in more detail.
By reading the catalogs, this option generates the DDL necessary to recreate a table 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 recreate the table. This option:
Figure 48 is an example of the jobstream produced by Control Center to generate DDL for the SQLDBA.ACTIVITY table.
Figure 48. Table Reorg Option 1 (Generate DDL) - Sample Jobstream
* $$ JOB JNM=GENDDL,CLASS=0,DISP=D,PRI=9
* $$ LST CLASS=A,PRI=3
* $$ PUN PRI=3
// JOB TRAY2 GENERATE DDL FOR TABLE "SQLDBA"."ACTIVITY"
// OPTION LOG
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* STEP0001 UNLOAD DDL
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
// DLBL SQMTPRM,'SQLMSTR.TABLE.PARMS',,VSAM,CAT=SQMCAT
// DLBL SQMDDL,'L.DB2710.EXAMPLE.ROUTINE',0,VSAM, X
RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT
// ASSGN SYS005,SYSRDR
// ASSGN SYS006,SYSPCH
// ASSGN SYS011,SYSLST
// EXEC SQB60,SIZE=AUTO
%%SQLDBA SQLDBA ACTIVITY 1 N
/*
/&
This option generates the DDL necessary to recreate a table and writes it to a VSAM file. It then unloads the table to a SAM disk file (or a tape if a tape label was specified). The SAM data file is then REPRO'd to a VSAM-managed SAM file for more permanent retention. Data is unloaded in system-defined format; you must make sure that this data file is not altered prior to reloading the table. This option is essentially a table backup. Used in conjunction with a RELOAD TABLE (Option 4), it provides the capability to recover from application errors.
Figure 60 shows a jobstream generated by Control Center to unload the SQLDBA.ACTIVITY TABLE.
This is the most comprehensive option of the reorganization tool. It schedules a full table reorganization, including generating all related DDL and executing it. Depending on the optional parameters chosen, a table can be migrated to another DBSPACE or another owner, the table name can be changed, and the table can be moved to another database.
Figure 61 shows a jobstream generated by Control Center to reorganize the SQLDBA.ACTIVITY table.
This option generates a job to reload a table previously unloaded or reorganized using Control Center. The previously created DDL and data files are used to recreate the table in its entirety. This option is the recovery counterpart to the UNLOAD TABLE option, (Option 2), and is the method of recovering from an error during a reorganization reload step.
Figure 62 shows a jobstream generated by Control Center to reload the SQLDBA.ACTIVITY table.
Figure 49 shows the Table Reorganization Submit Screen.
Figure 49. Table Reorganization Submit Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*-------------------- TABLE REORGANIZATION SUBMIT SCREEN ---------------------* | || | | || JOBNAME => ________ CLASS => A | | || | | || PRI => 3 DISP => D (D,H,K,L) | | || | | || FROM => ________ LST CLASS => A | | || | | || DUETIME => ____ (HHMM) DUEDATE => ______ (AABBYY) | | || | | || DUEDAY => __________________________________________ | | || | | || | | || OTHER => ____________________________________________________________ | | || | | || | | || | | || | | || | | |*------------------------------------------------------------------ SQC62 ----* | | | |PRESS ENTER TO PROCESS | |ENTER F1=HELP F3=EXIT F12=CANCEL | +--------------------------------------------------------------------------------+
To reach this menu, press ENTER from the Control Center Table Reorganization menu.
On each of the job submission screens (Table Reorganization, Create Table, and Update Statistics), there are parameters that are required, that are "required" but have defaults, and some that are optional. The following parameter descriptions apply to ALL of the Table Utility job submission screens.
This parameter is required; its default value is D.
| Note: | If you enter data for any of the Table Utility functions that cause job
submission and the job fails, you can easily resubmit the job. However,
this is not completely true when creating a table because you may have entered
a lot of data to define the table. There is no way to "reload"
that data entry menu, but, there is a way to recover.
If you use DISP=K, then the job is kept in the VSE/POWER queue after the job ends, whether it is successful or not. If it is not successful, you can edit the job, fix the problem, and resubmit the job. If you use DISP=K, be sure to delete the job from the queue when you are done. |
|If you schedule repetitive processing, you can cancel the job |whenever necessary by displaying the POWER READer queue and deleting the |job.
After entering the desired submit parameters, press ENTER to submit the job to VSE/POWER. For more information on VSE/POWER jobs, see the VSE/POWER Installation and Operations Guide. After submitting any Table Utility job, control returns to the screen from which job submission was selected.