The fundamental purpose of the DBSPACE Reorganization tools is to provide a means to reorganize DBSPACES within databases. This function is necessary to provide optimal database performance and to allow control and flexibility during database growth.
The DBSPACE Reorganization tools consist of SQLREORG for multiple user mode (MUM) DBSPACE reorganizations, a single user mode (SUM) DBSPACE reload tool, and a full single user mode DBSPACE reorganization tool. The SUM tools use the DDL generator portion of the SQLREORG tool as part of their processing. The remarks about the SQLREORG tool can also be generally applied to the SUM tools as far as the actual reorganization part goes. The SUM tools also encompass other processing in addition to the reorganization function.
Prior to using the Control Center DBSPACE Reorganization tools, review the DB2 Server for VSE & VM Database Administration manual for an understanding of the DBSPACE reorganization process.
When the Multiple User Mode DBSPACE Reorganization tool (SQLREORG) is invoked, these steps will be performed:
All grants are issued in the same chronological order in which they were originally issued, in order to retain hierarchical dependencies.
In order to grant authority to an object, SQLREORG must first connect as the user who originally issued the grant. Therefore, Control Center must gather database connect passwords for all grantors. If a grantor does not have a connect password, SQLREORG will give the user a temporary one and will later remove it.
The database does not remove grant information from the system catalogs when a user is removed from the SYSTEM.SYSUSERAUTH table. Therefore, the possibility exists that SQLREORG needs to connect as a nonexistent user in order to re-establish a grant. If this situation occurs, SQLREORG will temporarily grant connect authority to the user and will later revoke it.
| Operational Note: | In some cases (such as reload failure), temporarily granted IDs will not be revoked from the database. The administrator should revoke these IDs at some point in time. The IDs are identified by the starting letters REOnnnnn (where nnnnn is some random number). |
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.
If the ALTERNATE FILENAME parameter is used for a MUM reorganization, then the filename designated below as DBSPACEname will be that chosen by the submittor.
The basic files created will be:
The disposition of these files depends on whether the reorganization is successful or fails 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 access modules (packages), if present, will always be available on either your A-disk or on the permanent disk specified, and in the reader of the user running the reorganization if temp disk was used. The other files will be on the A-disk or on the permanent disk, if used.
Figure 174 is an example of the exec that will be created when SQLREORG executes, providing recoverability for the reorganization process. This exec will connect to the database and complete the second phase of the reorganization process (Create/Reload), regardless of when the failure occurred in the original reorganization. This exec is also used to complete a reorganization when the PAUSE option is specified.
Figure 174. Example Reorganization Recovery Exec
/************************************************************/
/* SQLREORG RECOVERY EXEC */
/* */
/* This exec is used to recover from a failed SQLREORG. */
/* It will attempt to recreate and reload a DBSPACE from */
/* files saved on disk by SQLREORG. */
/************************************************************/
/* The files required for recovery are: */
/* 1) DBSPNAME DBSPACE C4 */
/* 2) DBSPNAME SQLDBSU A */
/* 3) DBSPNAME PROGnnnn A (for any packages) */
/************************************************************/
trace 'o'
say 'Reorganizing DBSPACE MSTRUSR.DBSPNAME on database SQLDBA...'
say 'Using: MSTRUSR DBSPACE C'
'ESTATE MSTRUSR DBSPACE C'
estrc = RC
if estrc ¬= 0 then do
say 'ERROR! ==> UNLOADED DBSPACE NOT FOUND!'
EXIT -5
end
conn = '
'QUERY' userid() '(STACK'
if queued() > 0 then pull . '-' conn
if conn ¬= 'DSC'
then do
say ' Do you wish to continue (Y/N)?'
pull ans
if substr(ans,1,1) ¬= 'Y' then exit
end
starttime = time('E')
'DBINIT SQLDBA'
if RC ¬= 0 then do
say ' ERROR during DBINIT to SQLDBA'
exit RC
end
'SET CMSTYPE HT'
'FILEDEF DBSFILE CLEAR'
'FILEDEF ARISQLLD CLEAR'
'SET CMSTYPE RT'
'FILEDEF DBSFILE DISK MSTRUSR DBSPACE C4 (RECFM VBS BLKSIZE 4097'
'SQLDBSU IN(MSTRUSR SQLDBSU A) SYSPRINT(USERA LISTING A)'
erc = RC
if erc ¬= 0 then do
say 'ERROR running SQLDBSU, RC=' RC
say 'Check MSTRUSR LISTING for error details.'
end
else do
stoptime = time('E')
sttime = trunc(starttime,2)
sptime = trunc(stoptime,2)
elaptime = trunc((sptime - sttime) / 60,2)
tlaptime = elaptime + 2.04
tlaptime = strip(format(tlaptime,5,0))
if tlaptime = 0 then tlaptime = 1
sqlmaint_cmd = 'UPDATE SQLMAINT.SQLMAINT_TABLE ',
"SET REORG_STATUS = '00', REORG_DATE = '"||date('J')"',",
"REORG_TIME = '"time()"', REORG_ELAPSED = "tlaptime",",
"UPSTAT_TIME = '"time()"', UPSTAT_DATE = '"date('J')"'",
"WHERE DBSPACENAME = '"M760595"' AND OWNER = '"M760595"'"
SET CMSTYPE HT
ERASE $SQLREO$ $OUTPUT$ A
FILEDEF SYSPRINT CLEAR
fdef = 'FILEDEF SYSPRINT DISK $SQLREO$ $OUTPUT$ A'
fdef = fdef || ' (RECFM V LRECL 120'
ADDRESS CMS fdef
'SET CMSTYPE RT'
'EXEC ARISQLLD'
'SET CMSTYPE HT'
'NUCXDROP ARIDBS'
'SET CMSTYPE RT'
'NUCXLOAD ARIDBS ARIDBS ARISQLLD'
dcmd = 'ARIDBS PROMPTS(NO) MESSAGES(SQLONLY)'
dcmd = dcmd || ' FORMAT(LO) PAGECTL(NO)' sqlmaint_cmd
ADDRESS CMS dcmd
dbsurc = RC
'NUCXDROP ARIDBS'
if dbsurc ¬= 0
then do
say ' ERROR during update to SQLMAINT table'
exit dbsurc
end
end
'SET CMSTYPE HT'
'ERASE $SQLREO$ $OUTPUT$ A'
'FILEDEF SYSPRINT CLEAR'
'FILEDEF SYSPRINT DISK $SQLREO$ $OUTPUT$ A (RECFM V LRECL 255'
'SET CMSTYPE RT'
'EXEC ARISQLLD'
'SET CMSTYPE HT'
'NUCXDROP RXSQL'
'NUCXDROP ARISRRM'
'NUCXLOAD ARISRRM ARISRRM ARISQLLD'
'ARISRRM ENDSBCMD'
'NUCXDROP ARISRRM'
'NUCXDROP ARIDBS'
'SET CMSTYPE RT'
'NUCXLOAD ARIDBS ARIDBS ARISQLLD'
'ARIDBS PROMPTS(NO) MESSAGES(SQLONLY) FORMAT(LO) PAGECTL(NO)
REVOKE CONNECT FROM REO55553;'
'NUCXDROP ARIDBS'
'SET CMSTYPE HT'
'ERASE $SQLREO$ $OUTPUT$ A'
'SET CMSTYPE RT'
EXIT erc
Figure 175 is a simple example of the SQLDBSU file created when SQLREORG executes. This SQLDBSU command file contains all DDL statements associated with the reorganized DBSPACE and the RELOAD statements required to perform the reorganization.
For DBSPACES with very complex data structures (many tables, referential integrity, views, grants), the SQLDBSU file will contain considerably more commands, which would be very difficult to build manually without a comprehensive reorganization tool similar to SQLREORG.
Figure 175. Example SQLDBSU Command File
COMMENT '******************** SQLREORG DBSU ********************'
COMMENT '* DBSPACE: MSTRUSR.DBSPNAME (SQLDBA) *'
COMMENT '* Date: 03/14/97 09:52:23 *'
COMMENT '*******************************************************'
GRANT DBA TO REO35543 IDENTIFIED BY ********;
COMMIT WORK;
CONNECT REO35543 IDENTIFIED BY ********;
COMMIT WORK;
COMMENT '******************** Drop DB Space ********************'
SET ERRORMODE (CONTINUE)
DROP DBSPACE "MSTRUSR"."DBSPNAME";
COMMIT WORK;
SET ERRORMODE (OFF)
SET AUTOCOMMIT (ON)
COMMENT '********************* Acquire DB Space ********************'
ACQUIRE PRIVATE DBSPACE NAMED "MSTRUSR"."DBSPNAME"
(PAGES = 2048, PCTINDEX = 15,
PCTFREE = 0, NHEADER = 8,
LOCK = DBSPACE, STORPOOL = 9);
COMMENT '********************* Create Tables **********************'
CREATE TABLE "MSTRUSR"."CPRCNTRL" (
"OWNER" CHAR(8)
,"PROJECT" CHAR(8)
,"TBLID" CHAR(1)
,"PROJECT_NAME" CHAR(20)
,"XFER_USERID" CHAR(8)
) IN "MSTRUSR"."DBSPNAME";
COMMENT '************* Deactivated Primary Keys *************'
COMMENT '************* Inactive Unique Constraints *************'
COMMENT '********************* Reload Tables **********************'
RELOAD TABLE("MSTRUSR"."CPRCNTRL")
PURGE
INTABLE("MSTRUSR"."CPRCNTRL")
INFILE(DBSFILE);
CONNECT REO35543 IDENTIFIED BY ********;
COMMENT '************* Comment Tables *************'
COMMENT '************* Comment Columns *************'
COMMENT '************* Primary Cluster Keys *************'
COMMENT '************* Clustering Unique Constraints *************'
COMMENT '********************* Create Indexes **********************'
CREATE INDEX "MSTRUSR"."CPR1110X"
ON "MSTRUSR"."CPRCNTRL"
("OWNER")
PCTFREE = 0;
COMMENT '************* Primary Keys *************'
COMMENT '************* Remaining Foreign Keys *************'
COMMENT '************* Active Unique Constraints *************'
COMMENT '************* Table Grants *************'
CONNECT MSTRUSR IDENTIFIED BY ********;
GRANT SELECT ON "MSTRUSR"."CPRCNTRL" TO "PUBLIC";
GRANT UPDATE ON "MSTRUSR"."CPRCNTRL" TO "APCSDEV";
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX ON
"MSTRUSR"."CPRCNTRL" TO "APCSDEV";
CONNECT REO35543 IDENTIFIED BY ********;
COMMENT '************* Column Grants *************'
COMMENT '************* Views with Grants *************'
COMMENT '******************** PACKAGE REBIND ********************'
SET AUTOCOMMIT (ON)
Use of the DBSPACE Reorganization tools requires Control Center Database administration-level or greater authorization.
Although SQLREORG can be selected from the Control Center panel interface, it is actually a product that is designed to execute from the CMS command level. It can be invoked in panel mode by either entering SQLREORG at the CMS READY prompt, or by using Option U (Database Utilities) on the Control Center Main Menu and then entering Option R (SQLREORG) or RS (REORG SINGLE USER). Additionally, SQLREORG can be invoked through Control Center's Search List tool, see Figure 213. If invoked through SL, the PCTFREE, ALTER PCTFREE, and PCTINDEX reflect values used from the last reorganization.
SQLREORG can also be invoked in command mode (in BATCH) by supplying all parameters required to define the desired reorganization. This chapter only addresses the Control Center panel interface option. The other method of invoking SQLREORG is discussed in Appendix G, Command Mode Interface.
SQLREORG executes as a database application rather than as a function within the database virtual machine. SQLREORG therefore, normally executes on your virtual machine (or a support machine) and thus requires that you (or the support machine) have Database Administrator authority within the target database. SQLREORG is also invoked (used) by the SQLMAINT tool when performing reorganizations.
These comments apply to a full reorganization of a DBSPACE and not to the PAUSE option.
SQLREORG should be used during non-peak hours to prevent locking contention with other users of the database. Due to the intensive updating of the database system catalogs, use of this utility is prone to lock contention during heavy multi-user sessions. Running more than one SQLREORG against different DBSPACES simultaneously within a single database should also not be done due to catalog contention.
SQLREORG should be used whenever database statistics indicate that a DBSPACE is in need of reorganization. This need is usually indicated when indexes are no longer clustered or when a considerable amount of delete activity has occurred, leaving holes of deleted data. The SQLMAINT tool can help determine when DBSPACES need reorganization; see DBSPACE Reorganization Maintenance Tool.
SQLREORG should also be used when a larger DBSPACE is needed due to a growth in the volume of data within a DBSPACE, or when a performance problem indicates that heavily used DBSPACES should be moved to storage pools with less usage. This movement can also solve a storage pool short-on-storage problem without adding a new dbextent to the database.
Installations that want to schedule reorganization and maintenance jobs should implement a separate Control Center support machine to provide the scheduling capability without impacting the database operation control functions of the Control Center service machine. SQLREORG jobs should not be scheduled to execute on the service machine, since all automated operations functions with all databases will be disabled while the maintenance functions execute. Although SQLREORG can be executed in batch mode with any scheduling product, a separate Control Center support machine will allow these jobs to be scheduled easily from the Control Center panel interface.
The SQLREORG tool has these features:
Single user mode usage of the SQLREORG tool is a special topic which is covered later. Many components of Control Center are involved in a Single User Mode reorganization and the entire process is explained in detail.
Shown in Figure 176 is the entry panel of the Multiple User Mode DBSPACE Reorganization tool.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *------------------- SQLREORG DBSPACE Reorganization Utility ----------------* | | | DATABASE ==> SQLDBA (example: SQLDBA) | | | | -------- | | | | OWNER.DBSPACENAME ==> M760595.M760595A (example: PUBLIC.DBSP) | | | | --------------------------- | | | | ========================== OPTIONAL PARAMETERS ========================== | | | | NEW DATABASE ==> SENDTO ==> | | | | -------- -------------------- | | | | NEW OWNER.DBSPACE ==> ALTFNAME ==> | | | | -------------------------- -------- | | | | PAGES ==> NHEADER ==> STORPOOL ==> | | | | -------- - ---- | | | | PCTFREE ==> ALTER PCTFREE ==> PCTINDEX ==> | | | | --- --- --- | | | | LOCKMODE ==> REPREP PKGS ==> YES ALLSTATS ==> NO (Y/N) | | | | -------- --- --- | | | | PAUSE ==> NO UNLOAD ==> YES TAPE ==> NO (Y/N) | | | | --- --- --- | | | | FILEMODE ==> TDISK size ==> COMMITCOUNT ==> | | | | - --- --------- | | | *---------------------------------------------------------------SQLREORX-----* | | PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU 6 SCHEDULE 10 PROCESS NOW | | | +--------------------------------------------------------------------------------+
The first two parameters (DATABASE and OWNER.DBSPACENAME) are required to identify the DBSPACE that is to be reorganized. The database specified must match either the database parameter or the nickname parameter of some entry in the DBINIT CONTROL file. The OWNER.DBSPACENAME parameter must identify a valid DBSPACE within the targeted Database.
The parameters below the line labeled "Optional Parameters" do not require an entry.
After entering all desired SQLREORG parameters, PF10 can be used to perform the DBSPACE reorganization immediately.
| Usage Consideration: | When used in this manner, the tool will run on your virtual machine, thus tying up your ID for the duration of the reorganization job. |
Optionally, PF6 can be used to schedule the reorganization activity using the Job Scheduling tool. Refer to Chapter 10, Job Scheduling Tool.
SQLREORG can be used to do a complete database reorganization of a DBSPACE for purposes of maintenance and performance improvement.
Another usage of this tool is for providing a backup (snapshot) of a DBSPACE. This is done by running the tool with the PAUSE option. This option creates all the DDL needed to restore a DBSPACE and unloads the data to a designated medium. In addition, a recover exec is provided.
Running the tool with the PAUSE option and the UNLOAD=NO option provides all the DDL associated with a given DBSPACE. This can be helpful in analyzing grants and indexes. This file can also be modified and used as input to a subsequent SQLDBSU run.
When using the TAPE option, you must issue the appropriate FILEDEF, LABELDEF, and TAPE MOUNT commands prior to invoking SQLREORG. The DDNAME for the unloaded data must be DBSFILE. SQLREORG executes the UNLOAD utility. For UNLOAD, always specify a record format (RECFM) of variable-length blocked spanned (VBS). For instance, an example of a FILEDEF and LABELDEF commands would be:
FILEDEF DBSFILE TAP1 SL (RECFM VBS BLKSIZE 28672 LABELDEF DBSFILE VOLID SCRATCH |
If the unloaded DBSPACE will fit on a single tape, then SQLREORG can be executed without the PAUSE option, there is no need to rewind the tape. It will automatically be rewound between the UNLOAD and RELOAD steps.
If the unloaded DBSPACE will span multiple tapes, then the proper LABELDEF command must be issued to handle the multi-volume tape. The PAUSE option must be specified so that the appropriate sequence of DETACH and MOUNT commands can be issued between the UNLOAD and RELOAD processing. After the PAUSE, the last tape must be detached and the first tape must again be mounted before the reload process can begin.
SQLREORG will display status information at your terminal to indicate what step is being performed. If SQLREORG fails at any time prior to the "Reloading..." message, the DBSPACE has not been changed and SQLREORG should be restarted from the beginning. If the failure occurs during the "Unloading..." step and you are using a temporary disk, you can detach the temporary disk by issuing the CMS RELEASE command with the DET option.
If SQLREORG fails at any time after the "Reloading..." message has been displayed, the DBSPACE must be recovered using the created exec. This exec will begin the reload process by using the SQLREORG SQLDBSU file and the unloaded data file. If the data was unloaded to TAPE, then the proper FILEDEF, LABELDEF, and tape MOUNT commands must be used before the created exec can be invoked.
| NOTE |
|---|
|
If SQLREORG unloads data to a temporary disk and SQLREORG fails, you must be aware that the temporary disk (and unloaded data) will be purged if you log off. Therefore, the created exec must be run successfully before logging off. |
As a special precaution to prevent loss of data, SQLREORG sends the unloaded data file to your reader. If a temporary disk is lost, the reader copy can be received onto a new disk and the created exec can then be executed. Under this scenario, you should verify that all file mode references in the created exec correctly point to the new location of the data file.
If you used the COMMITCOUNT option, the RELOAD TABLE statements generated by the tool in the SQLDBSU file, will have to be inspected and removed for the failing table prior to using the RESTARTCOUNT option. In general, care must be used in restarting a failed RELOAD. The entire SQLDBSU file must be examined closely and altered accordingly if you wish to restart at any specific point rather than to rerun the entire RELOAD process (which option is always available).
The same cautions discussed in "Failure Restart" also apply to the use of the SQLREORG PAUSE option. Each time that SQLREORG is run, a new SQLDBSU and exec file will be created. Any previous copy of these files will be renamed by reversing the filename and filetype and reversing all letters in each. This implies that if a second SQLREORG is executed prior to completion of the first SQLREORG, the first one can be used again if the files are named back again.
The PAUSE option can be very useful to an experienced user of SQLREORG and very dangerous to an inexperienced user. If you have a good working knowledge of the Database Services Utility and of SQLREORG, the PAUSE option will allow a great deal of flexibility and control in the reorganization process. The SQLDBSU file can be examined and modified prior to running the created exec in order to change or remove certain characteristics of the DBSPACE. Once again, extreme care should be exercised to prevent loss or contamination within the database.
SQLREORG will unload the database data within a DBSPACE to an OS format file on a CMS minidisk or tape using the DBS Utility UNLOAD command. This unload format is defined internally by the unloaded data file, therefore, it can not be altered in any way. Extreme caution should be exercised to ensure that this data file is not altered prior to successful completion of SQLREORG. The file should not be edited (through XEDIT or ISPF, for example). The filemode must always be defined with a filemode number of 4 (for OS simulated dataset).
During the capture portion of SQLREORG processing, SQL statements are used to capture information from the database system catalogs. If any SQL statement fails, SQLREORG will terminate and display the SQLCODE associated with the failure.
SQLREORG uses a DBS Utility command file to execute the data UNLOAD portion of the reorganization. Detailed output from the UNLOAD portion is captured in a file on your 191 disk with a file type of UNLIST. This file can be examined to determine the reason for failure.
During the "Reloading..." portion of SQLREORG processing, the SQLDBSU command file is executed using the DBS Utility (SQLDBSU). Detailed output of this processing is captured within a file on the target disk with a filetype of LISTING and a filename equal to the first eight alphanumeric characters of the DBSPACE name. If a failure occurs during this step of processing, this file can be examined to determine the cause of failure.
One common problem to be aware of is the possible LOG FULL condition that may occur during the RELOAD processing. The DBS Utility RELOAD TABLE command executes as a single LUW, which means that the entire reload of all rows is capable of being rolled back if an error occurs. The database must therefore record the entire LUW within the database LOG.
If the target table is very large, or the database LOG file was nearly full when the reorganization began, the possibility for a LOG FULL condition exists. Depending on the logmode of the database, it will either attempt to perform a full archive, a log archive, or a checkpoint within the LOG.
If the RELOAD process continues until the LOG file completely fills, then the database will begin to ROLLBACK the entire RELOAD. Since the DROP DBSPACE has already completed and has been COMMITTED to the database, the target DBSPACE will be in an incomplete state if this occurs. There are several possible solutions to this problem.
If the RELOAD failed because the log file was nearly full prior to the reorganization, then you can perform a log archive, full archive, or coldlog (depending on whether you are using logmode L, A, or Y respectively). After this completes, you can run the DBSPACE exec created by SQLREORG to restart the process.
| Note: | If you are using a temporary disk, be careful not to lose it by logging off. |
If the RELOAD cannot fit into the LOG file, even if empty, you have three options:
The Single User Mode DBSPACE Reload tool provides a method of reloading a database DBSPACE in SUM without logging onto the database. This tool reloads the data from a DBSPACE previously unloaded by the MUM SQLREORG tool.
Review the DB2 Server for VSE & VM Database Administration manual for an understanding of the database running in single user mode. For the SUM reload, the database machine ID must have Database Administrator authority in the DB2 Server for VM database. In addition, it must be enabled for link and access authority to the disks where the files it uses reside.
The following three figures show the panels which you will be presented with when invoking the Single User Mode DBSPACE Reorganization tool.
Figure 177. Single User Mode DBSPACE Reload Usage Information
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------- Single User Mode DBSPACE Reload ----------------------* | | | Command ==> CTRLID: MSTRSRV1 | | | | **** I M P O R T A N T **** NODE: VMSYSTM1 | | | | | | | | In order to Reorganize a DBSPACE in Single User Mode, you must first | | | | use the SQLREORG package in multi-user mode, with the PAUSE option, | | | | and with output placed on a PERMANENT disk that can be linked to | | | | by the database machine (DIRMAINT or RACF authorized). | | | | | | | | You must assure that no updates have occurred within the DBSPACE | | | | after the SQLREORG PAUSE step and prior to the CTRLCTR REORG step . | | | | | | | | This function will switch the database from multi-user mode to | | | | single user mode with a specified logmode. If logmode N is requested, | | | | no logging of the updates will be done, and CTRLCTR will automatically | | | | handle all database requirements to switch the logmode to N and back, | | | | including all archives and coldlogs. | | | | | | | | Press ENTER to continue or press PF3 to CANCEL the reload | | | *---------------------------------------------------------------SQMSUMR1----* | | PF: 1 Help 3 End (Cancel) | | | | | +--------------------------------------------------------------------------------+
Figure 178. Single User Mode DBSPACE Reorganization Entry Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------- Single User Mode DBSPACE Reload ----------------------* | | | Command ===> CTRLID: MSTRSRV1 | | | | Database ===> SQLDBA NODE: VMSYSTM1 | | | | | | | | Userid ===> MSTRUSR Userid of SQLREORG file output | | | | Disk Address ===> 191 Disk address of SQLREORG output | | | | File name ===> SQLREORG output file name | | | | Logmode ===> Logmode for s.u.m. operation, | | | | defaults to current logmode | | | | | | | | Enter the Userid and Disk Address where the SQLREORG output files | | | | (SQLDBSU and DBSPACE) are located. Also enter the File Name of | | | | the output files and what logmode you desire (remember that using | | | | logmode N will require archives/coldlogs/log archives). | | | | | | | | | | | | Press ENTER to select IMMEDIATE or SCHEDULED execution, or | | | | press PF3 to CANCEL the Single User Mode reload | | | | | | | | | | | *---------------------------------------------------------------SQMSUMR2----* | | PF: 1 Help 3 End | | | +--------------------------------------------------------------------------------+
Figure 179. Single User Mode DBSPACE Reorganization Options Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------- Single User Mode DBSPACE Reload ----------------------* | | | Option ===> CTRLID: MSTRSRV1 | | | | Database ===> SQLDBA NODE: VMSYSTM1 | | | | Reorg ===> MSTRUSR 191 DBSPNAME | | | | | | | | I INITIATE RREORG Immediate Single User Mode reload | | | | | | | | S SCHEDULE RREORG Schedule later reload | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Enter OPTION and press ENTER to process, or | | | | press PF3 to CANCEL the Single User Mode reload | | | | | | | *---------------------------------------------------------------SQMSUMR3----* | | PF: 1 Help 3 End | | | +--------------------------------------------------------------------------------+
SUM DBSPACE reload process will require you to perform two separate processing steps: first, unloading the data in multiple user mode and then reloading the data in single user mode.
Prior to invoking the panel interface, you should use the SQLREORG utility in MUM with two special options: PAUSE and DISK. The PAUSE option will cause SQLREORG to complete the capture and unload of the DBSPACE but stop the process prior to the drop and reload function. The DISK option must be used to place the unloaded DBSPACE on a permanent linked disk. The default within SQLREORG is to use a temp disk, which will not allow the single user reload to be completed.
The database machine must have link access to the disk that is used for the unloaded DBSPACE. After the unload portion of SQLREORG has completed, then the panel interface can be used to complete the SUM reorganization. This function will ask for the unloaded file name (given by SQLREORG), the user ID which owns the disk where the DBSPACE was unloaded, and the address of the disk.
This process will cause the database to be brought down, the minidisk to be linked, and the reload to be executed in SUM with logmode N (no logging). After the reorganization completes, the database will be brought back up with whatever logmode the database was running under previously. Switching from logmode N to logmode Y, A, or L will require some combination of coldlogs, archives, or log archives. These activities will be performed automatically by Control Center once the SUM activity completes.
If you wish to perform multiple SUM reloads and only a single archive, you will need to issue the SQLEND command to the database before invoking the SUM Reorganization function. Control Center will always return the database to the same state that it was in prior to the invoked function. Therefore, if the database is down when the SUM process is performed, the archive will be delayed until the STARTUP command is issued. You can, therefore, perform as many SUM activities as desired prior to bringing the database back up and causing the archive process to begin.
The SQLREORG utility provides an optional full Single User Mode DBSPACE Reorganization tool that will process both the unload and reload reorganization steps in Single User Mode execution.
Review the DB2 Server for VSE & VM Database Administration manual for an understanding of the database running in single user mode. For the SUM tool, the database machine ID must have Database Administrator authority in the database.
The SQLREORG utility is executed in Single User Mode on the database virtual machine to reorganize a specified DBSPACE. By performing the reorganization in Single User Mode, contention with interactive users can be avoided. Also, reorganizations for very large DBSPACES can execute with logmode N (No Logging) to avoid the logging overhead of Multiple User Mode, and also enable reorganizations of DBSPACES that cannot execute in multiple user mode because the data reload completely fills the database log disk, causing the reorganization to fail.
The Single User Mode functions of SQLREORG are identical to those of Multiple User Mode, with a few specific exceptions. When SQLREORG is invoked in single user mode by using the "REORG SINGLE USER" (SU) option on the UTILITIES menu of the Control Center panel interface, a special "Single User Mode Reorganization Utility" panel will be presented. This SUM panel has two new parameters and eliminates two other parameters available in multiple user mode.
The first new parameter available in single user mode is LOGMODE. The value presented on the panel is the current logmode of the database. It can be changed to "N" if you wish to perform the reorganization without logging (logmode N). Choosing logmode N will require a logmode change for the database, with an accompanying set of archives, log archives, and/or coldlogs (depending on the current logmode before switching to logmode N). Control Center will handle these requirements automatically, but they may impose a considerable amount of additional time, especially if your database is quite large, and a full database archive is necessary.
Another parameter is STAYDOWN, which defaults to NO (N) but can be changed to YES (Y). Normally, when functions execute on the database virtual machine, the database will be returned to Multiple User Mode operation (available to all users) when the function completes. The STAYDOWN option can be used to force the database to remain DOWN after the reorganization completes, rather than starting the database back up. This can be useful when multiple reorganizations are done in single user mode on the database machine. A number of reorganizations can be scheduled at the same time, dependent on successful completion of the previous job, with all but the last one specifying STAYDOWN=YES. Each SUM reorganization will then execute in turn, with the database returning to normal Multiple User Mode operation only after the last one completes.
The two eliminated parameters are PAUSE and UNLOAD since they are not applicable to this tool.
If the TAPE parameter is specified as Y, an additional panel is presented. This is discussed in detail below.
To invoke SQLREORG in single user mode, Control Center Database Administrator (Level 3) or greater authority is required. Additionally, the database virtual machine must have Database Administrator authority within the database.
The SQLREORG SUM Tool has these features:
The following two figures show the panels which you will be presented with when invoking the Single User Mode DBSPACE Reorganization tool.
Figure 180. Single User Mode DBSPACE Reorganization Options Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *------------------- Single User Mode Reorganization Utility ----------------* | | | DATABASE ==> CPMICDBA (example: SQLDBA) | | | | | | | | OWNER.DBSPACENAME ==> M760595.M760595A (example: PUBLIC.DBSP) | | | | | | | | ========================== OPTIONAL PARAMETERS ========================== | | | | LOGMODE ==> L (defaults to current ) SENDTO ==> | | | | - -------------------- | | | | STAYDOWN ==> NO (Y or N) ALTFNAME ==> | | | | --- -------- | | | | PAGES ==> NHEADER ==> STORPOOL ==> | | | | -------- - ---- | | | | PCTFREE ==> ALTER PCTFREE ==> PCTINDEX ==> | | | | --- --- --- | | | | LOCKMODE ==> REPREP PKGS ==> YES ALLSTATS ==> NO (Y/N) | | | | -------- --- --- | | | | TAPE ==> NO (Y/N) COMCOUNT ==> | | | | --- --- | | | | FILEMODE ==> ( Linked R/W disk, TDISK size ==> (Defaults to | | | | - defaults to TEMP DISK ) --- calculation) | | | *---------------------------------------------------------------SQLSUMR$-----* | | PF1 HELP PF3 QUIT PF4 EXIT PF6 Schedule PF10 Process immediately | | | +--------------------------------------------------------------------------------+
The first two parameters (DATABASE and OWNER.DBSPACENAME) are required to identify the DBSPACE that is to be reorganized.
The parameters below the line labeled "Optional Parameters" do not require an entry. The optional parameters not described just below are explained in the parameter descriptions for Figure 176. The two different parameters for the SUM Reorganization screen are:
Tape processing is fully supported in single user mode if you currently have automated archiving to tape using one of the supported tape managers (VMTAPE, EPIC, DYNAM/T, and CMS).
The SUM reorg tape manager is assumed to be the same as the tape manager used for archive activity; therefore the TAPE option is only valid if you have tape archives automated without any customization to the SQMOUNT EXEC. Any changes made to the SQMOUNT EXEC must also be made to the SQMSTAPE EXEC used by SQLREORG before the TAPE=YES option can be utilized.
An additional panel will be displayed if the TAPE=YES option is selected on the primary single user mode DBSPACE Reorganization panel. This panel will allow the user to enter tape parameters that will be used when the reorganization is performed, automatically mounting and using any tapes required for the reorganization.
Figure 181. Single User Mode DBSPACE Reorganization SQMREORG Tape Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *------------------------- SQMREORG Tape Screen -----------------------------* | | | DATABASE ==> SQLDBA CTRLID: MSTRSRV1 | | | | NODE: WMAVM1 | | | | Tape-manager ==> EPIC (EPIC, VMTAPE, CMS, Other) | | | | Tape-user-id ==> EPIC (ID of the Tape Server Machine) | | | | Tape-code-disk ==> 19E (Virtual address (CUU) of tape code disk) | | | | Tape-code-owner ==> $MAINT (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 REORG (BLANK for SCRATCH) ======= | | | | ______ ______ ______ ______ ______ ______ ______ ______ | | | | | | | | ===== OPTIONAL INFORMATION: CATALOG DATASET NAME / TAPE SCRATCH POOL ===== | | | | Catl-dataset-name ==> __________________________________________________ | | | | Tape-scratch_pool ==> __________________________________________________ | | | | | | | *---------------------------------------------------------------SQMREOD5-----* | | PF1 HELP PF3 PREVIOUS SCREEN ENTER CONTINUE | | | | | +--------------------------------------------------------------------------------+
The menu above contains information SQLREORG 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 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.
For multi-volume processing, only standard label tapes can be used. If the scratch option is specified, the unloaded DBSPACE must fit on 1 tape, since multi-scratch tape handling is not supported.
The result of processing this screen is a file with the name dbmachine DBREOSUF which is sent to the Control Center service machine. It is later requested by the SUM Reorganization execs and read to obtain the information needed to support tape processing. Figure 182 is an example of the file created from the sample Tape Screen shown above:
Figure 182. Example dbmachine DBREOSUF Tape File
*TAPE: TAPE_MGR VMTAPE *TAPE: TAPE_UID VMTAPE *TAPE: TAPE_OWNER NERTOOLS *TAPE: TAPE_DISK 19E *TAPE: DEN 38K *TAPE: PREMOUNT N *TAPE: BLKSIZE 28672 *TAPE: LABEL SL *TAPE: RETPD 30 |
All tapes used must be standard label due to SQLDBSU's handling of non-labelled tapes (it forces a label to be written over the tape). Therefore non-labelled tapes are not supported.
A special mount/detach exec is included. It is called SQMSTAPE EXEC. The same file is also supplied as SQMSTAPE $EXEC. The Database Administrator should not have to modify this $EXEC file. It is there simply to remain consistent with our modifiable code supplied. The SQMSTAPE EXEC is supplied to ensure that the exec resides on the code disk and is accessed by the database machine during SUM reorgs. The Database Administrator 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 should work for EPIC and CMS. Depending on the type of tape drive, if VMTAPE, DYNAM/T, or another tape management product is used, the SQMOUNT EXEC may need to be modified. See the DB2 Server for VM Control Center Program Directory.
For tape processing, the options below apply:
The files created by the SQLREORG exec have the dispositions described below.
The output of a single user mode SQLREORG job is available on the database's A-disk. As a result, the output is not available from the normal VJ (View Job) option of the panels. You must link and access the database machine's 191 disk and/or view the log of the Control Center machine and the file dbmachine SQMREORG for results.
The Single User Mode DBSPACE Reorganization Tool 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 HELP REORGF from the CMS command line to view the help available.
An example of invoking single user mode SQLREORG from command mode, with parameters for unloading to a permanent disk and logmode=L, follows:
| Command Mode SUM Reorg |
|---|
|
SQM dbname REORGF dbname dbspaceowner.dbspacename ( DISK=C LOG=L |
If you wish to use a tape and execute the tool in command mode, you must have created the dbmachine DBREOSUF file on the Control Center service machine prior to invoking the tool. This can be accomplished by using the panels only through the job schedule step (then cancelling the scheduled job) or by simply creating a file and sending it to the service machine.
The failures from SQLREORG can be analyzed using the information provided in Failure Analysis. The new functions of SQLREORG (tape control temp-disk handling) can provide additional error detection (missed tape mounts, more than one scratch tape). These errors will be noted in the SQLREORG console. Use this console to further analyze the problem.