The fundamental purpose of the DBSPACE Reorganization Driver tool is to provide a capability to schedule a single job that will process multiple DBSPACE reorganizations. A variety of selection options allows the DBA to choose subsets of DBSPACES to be reorganized. For example, VMDSS users can select DBSPACES by storage pool to take advantage of Data Spaces Support and striping.
This tool executes in multiple user mode as a database application and should be scheduled to run on a Control Center support machine. The tool consists of 2 parts: a panel driven candidate selection process and job scheduling function; and the actual execution of the DBSPACE reorganizations under the control of the DBSPACE Reorganization Driver program (SQLREODR). The SQLREODR program normally executes on a support machine, and requires that the support machine have DBA authority within the target database.
The SQLREODR Program is primarily intended to be run as a scheduled job rather than from command mode. It can be invoked in panel mode by either entering SQLREODR at the CMS READY prompt, or by using U (Database Utilities) on the Control Center Main Menu, and then entering Option RD. Command mode execution of SQLREODR will be described later.
Review the DB2 Server for VSE & VM Database Administration manual for an understanding of the DBSPACE reorganization process.
The DBSPACE Reorganization Driver Tool (SQLREODR) consists of a two-step process. The first step is the creation of an input file of DBSPACE names to be reorganized and the setting of the parameters which will drive the second step, namely the execution of SQLREODR (the actual DBSPACE reorganizations). The first step can be done manually as explained in Running SQLREODR in Command Mode. However, the simpler way of preparing the file and parameters is by using the panel which is invoked either through the RD function of the Utilities panel, or by invoking SQLREODR without any parameters (which will cause a panel to be displayed). The first step results in a list of DBSPACEs to be reorganized. The list is created in a file with a filename of the database name and a filetype of REODRIPK. The file is sent to the Control Center service machine which controls the support machine where the job will run.
The second step of the driver function is invoking the SQLREODR exec. When the SQLREODR job is run on the support machine, it asks for the list file from the Control Center service machine. The status flags are all set to a blank. As the driver job is run, the status flag is set to R for those DBSPACEs which were reorganized. If all DBSPACEs were reorganized, then when the job completes, all flags are set back to blanks. The file is then returned to the service machine for reuse in the event the job is rescheduled. Note that if all DBSPACEs were not reorganized in one run of the job, the remaining ones (those with a blank status flag) would be chosen when the job was run again (those whose flag contains an R are bypassed). When all DBSPACEs are set to R, the list is reset to blanks in preparation for another cycle. Prior to execution or re-execution, the DBA can modify the list in any fashion by manipulating the status flag, modifying the order of the DBSPACES, adding an entry, deleting entries, as well as changing the values of the columns PCTFREE, ALTFREE, NPAGES, and PCTINDX (used by SQLREORG in the ACQUIRE DBSPACE command).
During the driver run, the remaining time left in the job is checked at several points. Before a new reorganization starts, time left is checked to determine whether time remains for it to be done. The last elapsed time from the SQLMAINT table is used to determine this. If enough time does not remain, the next DBSPACE is tried. During the reorganization, after the DBSPACE unload step (just prior to starting the actual DBSU to drop and reload), the time left is checked again. If there is insufficient time to complete the remaining reorg, the DBSPACE is NOT reorganized.
If a log archive is requested upon job completion, the log archive will be initiated by SQLREODR only if at least one DBSPACE was successfully reorganized.
If a reorganization should encounter an error, the run will stop if any of these conditions are met:
Note that if you wish to run in command mode, the input file of DBSPACE names must be set up properly before invoking SQLREODR (Processing Flow). For more details, refer to Running SQLREODR in Command Mode.
A report is produced with each run of SQLREODR, as shown in Figure 183. The report contains information about the options chosen, start/stop times of various activities, and other informative messages.
Figure 183. SQLREORG DRIVER REPORT
SQLREORG DRIVER REPORT
Database: WMAVM1.CPMICDBA 04/09/97 13:34:16
13:34:16 This run of SQLREODR had the following options chosen:
TIME=20 SOF=Y TAPE LPOOL LMODE=L LTHRESH=69 TACT=ARC LBA=N/Y
TIME STAMP ACTIVITY
*************************************************************
13:34:16 SQLREODR started
13:34:24 REORG of M262264.M262264A started
13:39:02 REORG of M262264.M262264A completed Elapsed Time = 4.63
13:39:02 REORG of M356959.M356959A started
13:39:46 REORG of M356959.M356959A completed Elapsed Time = 0.73
13:39:47 REORG of M661995.M661995A started
13:42:08 REORG of M661995.M661995A completed Elapsed Time = 2.35
13:42:08 REORG of M668047.M668047A started
13:46:05 REORG of M668047.M668047A completed Elapsed Time = 3.97
13:46:05 REORG of M760595.M760595A started
13:47:01 REORG of M760595.M760595A completed Elapsed Time = 0.92
13:47:01 LOG ARCHIVE after reorgs starting
13:50:25 LOG ARCHIVE completed Elapsed Time = 16.15
13:50:35 Job complete. SQLREODR terminating.
Number of DBSPACEs successfully reorged = 5
Total elapsed time for entire run = 16.15 minutes
End of Report for CPMICDBA SQLREODR JOB
|
The file created in the first step of the DBSPACE Reorganization Driver tool process contains entries for each DBSPACE to be reorganized. Each record is a fixed length 128-byte entry consisting of the following fields:
Figure 184. REODRIPK List File layout
Columns Length Description
001 - 001 1 Status flag indicating whether the DBSPACE
has been reorganized (R) or not (blank or ' ').
003 - 010 8 DBSPACE owner
012 - 029 18 DBSPACE name
031 - 034 4 Pool
036 - 039 4 DBSPACE number
041 - 048 8 Size of DBSPACE (NPAGES)
050 - 057 8 Number of active pages (NACTIVE)
059 - 061 3 Percent Index (PCTINDEX)
063 - 065 3 Current PCTFREE of DBSPACE
067 - 069 3 PCTFREE to be used in ACQUIRE DBSPACE
071 - 073 3 ALTFREE to be used after loading the DBSPACE
075 - 081 7 Last REORG date from SQLMAINT table (or blank)
083 - 090 8 Last REORG time from SQLMAINT table (or blank)
092 - 095 4 Last elapsed time of REORG from SQLMAINT table
097 - 103 7 Last Update statistics date from SQLMAINT table
105 - 112 8 Last Update statistics time from SQLMAINT table
114 - 114 1 Sort control character (sort by pool number, '-'
for descending order, '>' for ascending)
116 - 119 4 Absolute pool number (used for sorting)
121 - 121 1 Sort control character (as above, but for sort
by number of active pages)
123 - 130 8 Absolute active pages (used for sorting)
132 - 141 10 COMMITCOUNT value
This file contains one record for each DBSPACE selected. If the unload is to be done to tape, the information needed to control the tape processes is added to the end of the file. See Unloading to Tape for a more detailed discussion of tape use.
Files created during the reorganization will have a filename that is similar to the DBSPACE name that is being reorganized. If the dbspace name has any special characters, they will be removed. If the DBSPACE name is longer than eight characters, then it will be truncated to eight. The basic files created are:
The disposition of these files depends on whether the reorganization is successful and on whether a temporary disk is used or a permanent disk is available during the reorganization. In all cases, the files needed for recovery from a failed reorganization (the EXEC, DBSPACE, SQLDBSU, and those pertaining to packages), if present, will always be available on either the Support machine's A-disk or on the permanent disk specified, and/or in the reader of the user id running the reorganization. The other files will be on the A-disk or on the permanent disk, if used.
Use of the DBSPACE Reorganization Driver tool requires Control Center Database administration-level or greater authorization.
The SQLREODR tool has these features:
Shown in Figure 185 is the entry panel of the DBSPACE Reorganization Driver tool.
Figure 185. SQLREODR Screen
+--------------------------------------------------------------------------------+
| mm/dd/yyyy CONTROL CENTER hh:mm:ss |
|*------------------------- SQLREORG Driver Utility ---------------------------* |
|| CTRLID: SQLMOD1 | |
|| Database ==> SQLDBA NODE: VMSYSTM1 | |
|| | |
|| DBSPACE OWNER ==> ________ (blank for ALL, PUBLIC, PRIVATE, ownername) | |
|| | |
|| DBSPACE NAME ==> __________________ (blank for ALL, use % for wildcard) | |
|| | |
|| STORPOOL ==> ____ (blank for ALL Pools or specific | |
|| including minus sign for non-logging pool)| |
|| REPREP PKGS ==> YES (YES/NO dflt) ALLSTATS ==> NO (YES/NO dflt) | |
|| | |
|| DBSPACE PAGES: MINIMUM ==> 1 COMMITCOUNT ==> 0 | |
|| MAXIMUM ==> 9999999 | |
|| DBSPACE NACTIVE PAGES ==> P (A=All, P=positive, N=negative | |
|| values 1 - 9999999 ) | |
|| The % SQL wildcard character can be used when specifying the | |
|| Dbspace Owner or Dbspace Name values for selection. | |
|| | |
|*---------------------------------------------------------------SQMREODR------* |
| PF1 HELP PF3 QUIT PF4 EXIT PF5 Main Menu Enter Retrieve List |
| |
| |
+--------------------------------------------------------------------------------+
From the menu displayed, the DBA can select the criteria to be used to obtain a list of candidate DBSPACES for reorganization. DBSPACES can be selected by storage pool, by owner (ALL, PUBLIC, PRIVATE, or an owner can be specified which may include the database wildcard character), by DBSPACE name (which may include the wildcard character), or by any combination of these. In addition, the choice of DBSPACES can be further limited by specifying a number or range of DBSPACE pages or active pages.
The DBA can also specify three additional options on this menu; whether to rebind packages, update all statistics, and a COMMITCOUNT value (valid in DB2 Server for VM Version 5.1 and above). These options apply to all selected DBSPACEs when the reorganization portion of the job is executed.
The display panel below shows the DBSPACE candidates meeting the search criteria (in this example, DBSPACE OWNER = PUBLIC; DBSPACE NAME = %PMP%). From this screen, you have the ability to execute commands, for example, to update statistics, to issue show DBSPACE commands, or to exclude DBSPACES from reorganization, by using the OMIT selection option.
Certain DBSPACE attributes can be changed in the panel's modifiable fields. Using the provided fields, the amount of free space, the percentage of space reserved for indexes, and the size of the DBSPACE can be altered. By changing the value in the POOL field, DBSPACES can be migrated from one storage pool to another.
Sort function - The DBSPACES will be reorganized in the order they appear in this list. The list can be sorted by name, number of active pages, number of pages (descending), or storage pool, through the use of the appropriate PF key.
Shown in Figure 186 is an example of the Selection List panel:
Figure 186. SQLREODR Selection List Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*------------------------- SQLREORG Driver Utility --------------------------* | || Database => SQLDBA | | || PCTFREE PCT | | || SEL OWNER NAME ACQ ALT INDX POOL NPAGES NACTIVE | | || --- -------- ------------------ ------- ---- ---- ------- ------- | | || _ PUBLIC PMPACM_DBS_T 10 0 20 -21 4096 334 | | || _ PUBLIC PMPACM_DEV 10 0 20 15 4096 270 | | || _ PUBLIC PMPACM_TRN_DBS_T 10 0 10 18 2560 1123 | | || _ PUBLIC PMPACP_DBS_T 10 0 20 -8 1024 1 | | || _ PUBLIC PMPACP_DEV 10 0 20 -12 1024 1 | | || _ PUBLIC PMPACP_TRN_DBS_T 10 0 10 -20 2048 40 | | || _ PUBLIC PMPBPM_DBS_T 10 0 20 -11 2048 119 | | || _ PUBLIC PMPBPM_DEV 10 0 20 -12 2048 98 | | || _ PUBLIC PMPBPM_TRN_DBS_T 10 0 25 18 2048 384 | | || _ PUBLIC PMPBPP_DBS_T 10 0 20 -9 1024 1 | | || | | || The fields in red are modifiable for SQLREORG use | | || Select: O = Omit DBSPACE, S = Show Dbspace, U = Update Statistics | | || | | || Page 1 of 3 | | |*---------------------------------------------------------------SQMREOD2-----* | |PF: 1 Help 3 QUIT 4 StatHistory 7 Bkwd 8 Fwd | | 9 Sort/Name 10 Sort/Nact 11 Sort/Pages 12 Sort/Pool ENTER CONTINUE | +--------------------------------------------------------------------------------+
A description of the fields on the selection list panel follows:
The select options may be invoked immediately against the listed DBSPACES.
The PF Keys offer these options:
After selection has been made for reorganization, the DBA is shown a menu which specifies how SQLREODR is to control the reorganization step of the job. It contains required parameters and, if any DBSPACES chosen are in logging pools (recoverable storage pools), a logging pool section that includes fields for setting thresholds should archive activity be necessary before or after reorganization. To assist the DBA in archive management, relevant database logging parameters are highlighted in the lower portion of the panel.
Shown in Figure 187 is the job control panel of the DBSPACE Reorganization Driver tool.
Figure 187. SQLREODR Job Control Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:s| |*------------------------- SQLREORG Driver Utility --------------------------* | || CTRLID: SQLMOD1 | | || DATABASE ==> SQLDBA NODE: VMSYSTM1 | | || DBSPACE OWNER ==> PUBLIC DBSPACE NAME ==> %PMP% | | || | | || NOTE: THERE ARE DBSPACES IN LOGGING POOLS CHOSEN FOR THIS RUN. ***** | | || | | || ========================= 'REQUIRED PARAMETERS' ========================== | | || TIME LIMIT ==> 120 (Minutes; dflt 120) STOP ON FAILURE ==> Y (Y or N) | | || ---- - | | || FILEMODE ====> _ (Linked R/W disk; dflt TEMP DISK) TAPE ==> Y (Y or N) | | || - - | | || ============== FOLLOWING OPTIONS ARE FOR LOGGING POOLS ONLY: ============= | | || | | || LOG THRESHOLD ==> 70 LOG ARCHIVE BEFORE/AFTER JOB: ==> N/N (logmode L) | | || ACTION WHEN THRESHOLD REACHED: ==> ARCH (ARCH or STOP) | | || | | || ==FYI: CURRENT DB SETTINGS: LOGMODE ==> L SLOGCUSH ==> 90 ARCHPCT ==> 79 | | || | | |*---------------------------------------------------------------SQMREOD4-----* | |PF1 HELP PF3 QUIT PF5 REVIEW/MODIFY LIST ENTER CONTINUE | | | | | | | +--------------------------------------------------------------------------------+
If there are DBSPACEs chosen which are in a logging pool, this will be indicated on the panel.
The required job control parameters used with the DBSPACE Reorganization Driver tool are:
These parameters are present only if any of the DBSPACES selected are in logging pools:
If the DBA specifies that the reorganization job is to unload to tape, a Tape Control panel is presented. The DBSPACE Reorganization Driver tool will issue the required filedef and labeldef commands for one or more volumes. Multiple volume standard label tapes, single volume scratch tapes, catalogued tapes and scratch pool processing will be managed.
Shown in Figure 188 is an example of the Tape Control panel:
Figure 188. SQREODR Tape Control Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*------------------------- SQLREORG Driver Utility --------------------------* | || DATABASE ==> SQLDBA CTRLID: MSTRSRV | | || NODE: WMAVM1 | | || Tape-manager ==> EPIC (EPIC, VMTAPE, CMS, Other) | | || Tape-user-id ==> EPIC (ID of the Tape Server Machine) | | || Tape-code-disk ==> 195 (Virtual address (CUU) of tape code disk) | | || Tape-code-owner ==> EPIC (ID that owns the tape code disk) | | || Tape-density ==> 38K (4K, 38K) | | || Tape-blksize ==> 28672 (28672) | | || Tape-premount ==> N (Y or N, premount tape with TVI) | | || Tape-retention ==> 30 (Tape retention (days)) | | || | | || ===== LIST WORK TAPES TO BE USED DURING REORGS (BLANK for SCRATCH) ======= | | || ______ ______ ______ ______ ______ ______ ______ ______ | | || | | || ===== OPTIONAL INFORMATION: CATALOG DATASET NAME / TAPE SCRATCH POOL ===== | | || Catl-dataset-name ==> __________________________________________________ | | || Tape-scratch_pool ==> __________________________________________________ | | || | | |*---------------------------------------------------------------SQMREOD5-----* | | PF1 HELP PF3 QUIT PF7 PREVIOUS SCREEN ENTER CONTINUE | | | | | +--------------------------------------------------------------------------------+
The Tape Control panel is used to contain information the DBSPACE Reorganization Driver tool uses to control the use of tape for the unload/reload data portion of the DBSPACE reorganization.
The parameters displayed are the current settings of either the last values the program used, or the values from various Control Center control files. Note, these values are modifiable and will be saved in the user's LASTING GLOBALV file.
The list of work tapes to be used is optional. Complete it with the number of standard label tapes you think your job will need to hold the largest DBSPACE to be unloaded. All filedef, labeldef, mounting, rewinding, detaching, and reuse involved will be managed for you. If this parameter is left blank, the default will be one scratch tape.
NOTE: For multiple volume processing, only standard label tapes can be used. If the scratch option is specified, the largest DBSPACE unloaded must fit on 1 tape, since multiple scratch tape handling is not supported.
SQLREODR may be used to do a complete database reorganization of one or more DBSPACEs for purposes of maintenance and performance improvement.
Another purpose for this tool is to allow VMDSS users to take advantage of Data Spaces Support and striping by providing reorganization at the storage pool level.
The following is a description of the services, options, and restrictions which the DBSPACE Reorganization Driver tool provides when the unload to tape option is chosen. A tape use option panel is presented when the Tape option is specified. The tape parameters are written to the REODRIPK file following the DBSPACE entries. The use of tape is for unloading/reloading only and not for storage of unloaded DBSPACES. A tape (or tapes) will be reused by SQLREORG during the course of the job. The DBA must know how many tapes will be needed to hold the data from the largest DBSPACE unloaded. After each reorganization, the tape (or tapes) will be rewound and reused for the next reorganization.
First, all aspects of tape processing are automated. SQLREORG will take care of the FILEDEF and LABELDEF needed for the DBSU unload/reload steps. In addition, all mounting and rewinding between unloads/reloads and between reorganizations will be handled automatically.
Second, all tapes used must be standard label due to SQLDBSU's handling of labeled tapes (it forces a label to be written over the tape). SQLREODR does not support non-labeled tapes.
Third, a special mount/detach exec is supplied with SQLREODR. It is called SQMSTAPE EXEC. The same file is also supplied as SQMSTAPE $EXEC. The DBA should not have to modify this $EXEC file. It is there simply to remain consistent with our sample, reference code supplied. The SQMSTAPE EXEC is supplied to ensure that the exec resides on the code disk and is accessed by the support machine during a SQLREODR run. The DBA should try the supplied exec. It is possible that modifications might have to be made depending on one's tape management system, but the exec as supplied will work for VMTAPE, CA-DYNAM/T, EPIC, and CMS.
These options are available and an explanation of how they work is provided:
When using TEMP disk to hold the unloaded data, these considerations apply.
The use of the TEMP disk by SQLREORG is changed somewhat when SQLREORG is invoked by SQLREODR. Temp disk will be handled thus: SQLREORG determines whether a TEMP disk is currently attached (the label used by SQLREORG when defining and formatting a TEMP disk is SQLTMP). If SQLREORG does not have a TEMP disk attached (which would normally be the case upon the first invocation of SQLREORG by SQLREODR), it will determine the size required and acquire one, as usual. When the reorganization is completed, SQLREORG will NOT release the TEMP disk if it has been invoked by SQLREODR (in anticipation of more invocations). If the reorganization is successful, we will erase the DBSPACE file on that TEMP disk before returning to SQLREODR. When invoked again, it checks whether there is a TEMP disk available (yes); it then checks whether it will be large enough to hold the current DBSPACE to be reorganized. If so, it uses the current TEMP disk. If not, it will release the old TEMP disk and acquire a new one. If a reorganization fails, the temp disk will be relabeled to SQLBAD. This way the temp disk will not be reused for other reorganizations and the unloaded dbspace is saved. When the last DBSPACE has been reorganized, SQLREODR will release and detach the final TEMP disk. Therefore, the DBA can see that it would be more efficient to order the reorganizations in descending order of DBSPACE size since only one TEMP disk would be acquired by SQLREORG for the entire job. This option is available in the selection screen by using the sort on pages option.
The files created by the SQLREORG exec have the following dispositions:
SQLREODR is best used as a scheduled job from the Control Center panels. The utility can be run, however, from the command mode. The HELP file guides you as to the parameters needed when invoking the utility. Just enter SQLREODR HELP to view the help available.
The most important item to remember is that the dbname REODRIPK file must be established on the Control Center service machine's A-disk. Previous sections explain the layout of this file. The file can be generated from the panels (by going all the way through to the schedule step). You can also copy and edit the file and send it to the Control Center service machine.
Note these points concerning the support machine on which the tool runs:
If using tape for the unload, the proper tape parameters MUST be present in the REODRIPK file. The best way to determine these is to run a sample retrieval and invoke the tapes panel, schedule the job with I (to mark it inactive), then look at the tape parameters generated on the REODRIPK file sent to the Control Center service machine.
A sample of the tape parms appended to a REODRIPK file is:
Figure 189. SQREODR Tape Parameters in List File
*TAPE: TAPE_MGR VMTAPE *TAPE: TAPE_UID VMTAPE *TAPE: TAPE_OWNER $MAINT *TAPE: TAPE_DISK 19E *TAPE: DEN 38K *TAPE: PREMOUNT Y *TAPE: BLKSIZE 28676 *TAPE: RETPD 30 *TAPE: DSN REODRIVR.TEST1 *TAPE: VOLID QU2221 *TAPE: VOLID QU2222 |
These parms start in column one. The VOLID parm is present for EACH tape volume used when using pre-defined volumes. When using scratch tape, the VOLID parameter is not used.
Finally, a sample of the invocation of the Driver utility from command mode, with parameters, follows:
Figure 190. SQREODR Command Mode Example
SQLREODR dbname (TIME=60 SOF=Y LPOOL NORE LMODE=L TAPE
LTHRESH=70 TACT=STOP LBA=Y/N
|
A brief explanation is:
The failures from SQLREORG can be analyzed using the information provided in the section on that utility. The new functions of SQLREORG (tape control, temp-disk handling) may provide additional error detection (missed tape mounts, more than one scratch tape). These errors will be noted in the SQLREORG console as well as referenced in the RUNDRIVR report file created by SQLREODR. Use this file to determine the nature of the problem. Use the console which is created with each job to further analyze the problem. Restart can be done after adjustments are made to the REODRIPK file if necessary.