Control Center Operations Guide for VSE

Appendix B. DBSPACE and Table Reorganization Tool Related Files

A DBSPACE reorganization job involves several files. Depending on the options chosen, some or all of the following files are used:

Filename
File ID & Description

SQMPARM
"SQLMSTR.REORG.PARMS"

This is the DBSPACE Reorganization parameter file. The job submission programs write a record to this file whenever a job is submitted. The SQMPARM record contains REORG parameters and is read by the batch DDL generation and timekeeping programs. SQMPARM is a VSAM KSDS file whose key is composed of database name, owner, DBSPACE name, and option. SQMPARM is defined during installation and resides on the Control Center user catalog. |The record is deleted when the REORG completes.

SQMRDAT
"SQLMSTR.REORG.DATA"

This file holds statistical data relating to a DBSPACE unload/reload. At job end, the SQLMAINT table is updated with this data. SQMRDAT is used for Options 2, 3, and 4 of the reorganization tool. It is a VSAM KSDS file whose key is composed of database name, owner, and DBSPACENAME. The file is defined during installation and resides on the Control Center user catalog.

|SQMTPARM
|"SQLMSTR.TABLE.PARMS"

| |This is the Table Reorganization parameter file. The job submission |programs write a record to this file whenever a job is submitted. The |SQMTPARM record contains REORG parameters and is read by the batch DDL | | generation and timekeeping programs. SQMTPARM is a VSAM KSDS |file. It is defined during installation and resides in the Control |Center user catalog.

SQMDDL
File ID built dynamically during job submission.

SQMDDL is a VSAM-managed SAM file that is used to contain the DDL created by SQB01, the batch DDL generation program. The file id is composed of a concatenation of:

  1. "L" - (indicates DDL)
  2. Database name
  3. DBSPACE owner name
  4. DBSPACE name

File size is defined by the DDL STMTS parameter on the DBSPACE REORGANIZATION screen (defaults to 1000 80-byte records). This file resides on the Control Center user catalog and remains there until it is deleted by a successful REORGANIZE DBSPACE or deleted specifically by the user.

SQMDATn
"SQLMSTR.DATA.FILEn"

This SAM file is used to hold the output of the UNLOAD DBSPACE step when DISK is selected as the unload media. "n" corresponds to the FILE # parameter that appears on the DBSPACE REORGANIZATION UTILITY screen. Valid values are 1 to 3. This file is defined from the WORK FILE LABEL DEFINITION facility (Option 4 of the Main Menu). It is used in Options 2 and 3 of the reorganization tool.

SQMDDLn
"SQLMSTR.DDL.FILEn"

This SAM file is required to contain the DDL extracted by SQB01, the batch DDL generator. It is used as the DBSU command input file in the RELOAD step. Because DBSU expects commands to come from SYSIPT and a VSAM file cannot be assigned to SYSIPT, the VSAM SQMDDL file is REPRO'd to SQMDDLn. "n" corresponds to the FILE # parameter on the DBSPACE REORGANIZATION UTILITY screen. Valid values are 1 to 3. This file is defined from the WORK FILE LABEL DEFINITION facility and is used in Options 3 and 4 of the reorganization tool.

SQMDAT
Built dynamically by the submit program.

SQMDAT is a VSAM-managed SAM file that is used to contain the DBSPACE data unloaded from DBSU in the UNLOAD DBSPACE step. The file id is composed of a concatenation of:

  1. "D" - (indicates data)
  2. Database name
  3. DBSPACE owner name
  4. DBSPACE name

File size is computed from DBSPACE catalog information. This file is only created when a user selects UNLOAD DBSPACE (Option 2) and does not specify tape. This file is also created if an error occurs during the RELOAD step of a REORGANIZE DBSPACE from disk (Option 3). In effect, this file is a backup copy of the unloaded DBSPACE. It can be used as input to a RELOAD DBSPACE (Option 4). SQMDAT resides on the Control Center user catalog and remains there until the same DBSPACE is unloaded again or the file is specifically deleted.

SQMTAPE
Specified by the user.

SQMTAPE is used when the user selects tape media by entering a TAPE FILE NAME on the DBSPACE REORGANIZATION UTILITY screen. This file must be defined to Control Center from the WORK FILE LABEL DEFINITION facility (Option 4 of the Main Menu).

SQMMESG
"SQLMSTR.MESSAGES"

SQMMESG is a VSAM KSDS file that is used to contain Control Center error message text. The key is a 4-digit number. SQMMESG is defined during installation and resides on the Control Center User catalog.

SQMWORK
"SQLMSTR.WORK.FILES"

SQMWORK is a VSAM KSDS file that holds the ASSGN, DLBL, EXTENT, and TLBL statements that define your Control Center work files. The key is composed of a 17-character file ID and a sequence number. SQMWORK is defined during installation and resides on the Control Center user catalog.

Figure 63 is an example of the SQMDDL file created when a REORGANIZE DBSPACE job executes for the PUBLIC.SAMPLE DBSPACE. This SQMDDL command file contains all the 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 SQMDDL file may contain considerably more commands.

Figure 63. Sample Generated DDL File

COMMENT '**************** CONTROL CENTER DBSPACE REORG **************
COMMENT '*    Database: SQLDBA                                  *'
COMMENT '*     DBSPACE: "PUBLIC"."SAMPLE"                       *'
COMMENT '*        Date: 06/30/00 09:21:58                       *'
COMMENT '********************************************************'
CONNECT "SQLMSTR" IDENTIFIED BY ********;
COMMIT WORK;
GRANT DBA TO "SQLREORG" IDENTIFIED BY "PU24L5AR";
CONNECT "SQLREORG" IDENTIFIED BY "PU24L5AR";
COMMIT WORK;
COMMENT '*************            Drop DBSPACE            *************'
SET ERRORMODE (CONTINUE)
DROP DBSPACE "PUBLIC"."SAMPLE"              ;
COMMIT WORK;
SET ERRORMODE (OFF)
SET AUTOCOMMIT (ON)
COMMENT '*************          Acquire DBSPACE           *************'
ACQUIRE PUBLIC DBSPACE NAMED "PUBLIC"."SAMPLE"
 (PAGES =     512, PCTINDEX = 33,
  PCTFREE = 10, NHEADER = 8,
  STORPOOL =   1, LOCK = PAGE   );
COMMENT '*************           Create Tables            *************'
CREATE TABLE "SQLDBA"."ACTIVITY" (
  "ACTNO" SMALLINT         NOT NULL
 ,"ACTKWD" CHAR(6)          NOT NULL
 ,"ACTDESC" VARCHAR(20)      NOT NULL
 ) IN "PUBLIC"."SAMPLE"              ;
CREATE TABLE "SQLDBA"."DEPARTMENT" (
  "DEPTNO" CHAR(3)          NOT NULL
 ,"DEPTNAME" VARCHAR(36)      NOT NULL
 ,"MGRNO" CHAR(6)
 ,"ADMRDEPT" CHAR(3)          NOT NULL
 ) IN "PUBLIC"."SAMPLE"              ;
CREATE TABLE "SQLDBA"."EMP_ACT" (
  "EMPNO" CHAR(6)          NOT NULL
 ,"PROJNO" CHAR(6)          NOT NULL
 ,"ACTNO" SMALLINT         NOT NULL
 ,"EMPTIME" DECIMAL(5,2)
 ,"EMSTDATE" DATE
 ,"EMENDATE" DATE
 ) IN "PUBLIC"."SAMPLE"              ;
CREATE TABLE "SQLDBA"."EMPLOYEE" (
  "EMPNO" CHAR(6)          NOT NULL
 ,"FIRSTNME" VARCHAR(12)      NOT NULL
 ,"MIDINIT" CHAR(1)          NOT NULL
 ,"LASTNAME" VARCHAR(15)      NOT NULL
 ,"WORKDEPT" CHAR(3)
 ,"PHONENO" CHAR(4)
 ,"HIREDATE" DATE
 ,"JOB" CHAR(8)
 ,"EDLEVEL" SMALLINT         NOT NULL
 ,"SEX" CHAR(1)
 ,"BIRTHDATE" DATE
 ,"SALARY" DECIMAL(9,2)
 ,"BONUS" DECIMAL(9,2)
 ,"COMM" DECIMAL(9,2)
 ) IN "PUBLIC"."SAMPLE"              ;
CREATE TABLE "SQLDBA"."PROJ_ACT" (
  "PROJNO" CHAR(6)          NOT NULL
 ,"ACTNO" SMALLINT         NOT NULL
 ,"ACSTAFF" DECIMAL(5,2)
 ,"ACSTDATE" DATE             NOT NULL

 ,"ACENDATE" DATE
 ) IN "PUBLIC"."SAMPLE"              ;
CREATE TABLE "SQLDBA"."PROJECT" (
  "PROJNO" CHAR(6)          NOT NULL
 ,"PROJNAME" VARCHAR(24)      NOT NULL
 ,"DEPTNO" CHAR(3)
 ,"RESPEMP" CHAR(6)
 ,"PRSTAFF" DECIMAL(5,2)
 ,"PRSTDATE" DATE
 ,"PRENDATE" DATE
 ,"MAJPROJ" CHAR(6)
 ) IN "PUBLIC"."SAMPLE"              ;
COMMENT '*************      Deactivated Primary Keys      *************'
COMMENT '*************    Inactive Unique Constraints     *************'
COMMENT '*************          Reload Tables             *************'
RELOAD TABLE("SQLDBA"."ACTIVITY")
 PURGE
 INTABLE("SQLDBA"."ACTIVITY")
 INFILE(DBSFILE);
RELOAD TABLE("SQLDBA"."DEPARTMENT")
 PURGE
 INTABLE("SQLDBA"."DEPARTMENT")
 INFILE(DBSFILE);
RELOAD TABLE("SQLDBA"."EMP_ACT")
 PURGE
 INTABLE("SQLDBA"."EMP_ACT")
 INFILE(DBSFILE);
RELOAD TABLE("SQLDBA"."EMPLOYEE")
 PURGE
 INTABLE("SQLDBA"."EMPLOYEE")
 INFILE(DBSFILE);
RELOAD TABLE("SQLDBA"."PROJ_ACT")
 PURGE
 INTABLE("SQLDBA"."PROJ_ACT")
 INFILE(DBSFILE);
RELOAD TABLE("SQLDBA"."PROJECT")
 PURGE
 INTABLE("SQLDBA"."PROJECT")
 INFILE(DBSFILE);
ALTER DBSPACE "PUBLIC"."SAMPLE"               (PCTFREE =    0);
COMMENT '*************           Comment Tables           *************'
COMMENT '*************          Comment Columns           *************'
COMMENT '*************        Primary Cluster Keys        *************'
ALTER TABLE "SQLDBA"."ACTIVITY"
 ADD PRIMARY KEY
 ("ACTNO" )
 PCTFREE =  10;
ALTER TABLE "SQLDBA"."DEPARTMENT"
 ADD PRIMARY KEY
 ("DEPTNO" )
 PCTFREE =  10;
ALTER TABLE "SQLDBA"."EMPLOYEE"
 ADD PRIMARY KEY
 ("EMPNO" )
 PCTFREE =  10;
ALTER TABLE "SQLDBA"."PROJ_ACT"

 ADD PRIMARY KEY
 ("PROJNO" ,
  "ACTNO" ,
  "ACSTDATE" )
 PCTFREE =  10;
ALTER TABLE "SQLDBA"."PROJECT"
 ADD PRIMARY KEY
 ("PROJNO" )
 PCTFREE =  10;
COMMENT '*************  Clustering Unique Constraints     *************'
COMMENT '*************           Create Indexes           *************'
CREATE INDEX "SQLDBA"."PROJNOIN"
 ON "SQLDBA"."EMP_ACT"
 ("PROJNO" )
 PCTFREE =  10;
CREATE INDEX "SQLDBA"."DEPTNOI"
 ON "SQLDBA"."PROJECT"
 ("DEPTNO" )
 PCTFREE =  10;
CREATE INDEX "SQLDBA"."EMPNOIN"
 ON "SQLDBA"."EMP_ACT"
 ("EMPNO" )
 PCTFREE =  10;
CREATE INDEX "SQLDBA"."MGRNOI"
 ON "SQLDBA"."DEPARTMENT"
 ("MGRNO" )
 PCTFREE =  10;
CREATE INDEX "SQLDBA"."RESPEMPI"
 ON "SQLDBA"."PROJECT"
 ("RESPEMP" )
 PCTFREE =  10;
CREATE INDEX "SQLDBA"."WORKDEPTI"
 ON "SQLDBA"."EMPLOYEE"
 ("WORKDEPT" )
 PCTFREE =  10;
COMMENT '*************           Primary Keys             *************'
COMMENT '*************       Remaining Foreign Keys       *************'
ALTER TABLE "SQLDBA"."PROJ_ACT"
  ADD FOREIGN KEY "R_ACTIV"
 ("ACTNO"
 ) REFERENCES "SQLDBA"."ACTIVITY"
  ON DELETE RESTRICT;
ALTER TABLE "SQLDBA"."EMPLOYEE"
  ADD FOREIGN KEY "R_DEPT1"
 ("WORKDEPT"
 ) REFERENCES "SQLDBA"."DEPARTMENT"
  ON DELETE SET NULL;
ALTER TABLE "SQLDBA"."PROJECT"
  ADD FOREIGN KEY "R_DEPT2"
 ("DEPTNO"
 ) REFERENCES "SQLDBA"."DEPARTMENT"
  ON DELETE RESTRICT;
ALTER TABLE "SQLDBA"."DEPARTMENT"
  ADD FOREIGN KEY "R_EMPLY1"
 ("MGRNO"
 ) REFERENCES "SQLDBA"."EMPLOYEE"
  ON DELETE SET NULL;

 ALTER TABLE "SQLDBA"."EMP_ACT"
  ADD FOREIGN KEY "R_EMPLY3"
 ("EMPNO"
 ) REFERENCES "SQLDBA"."EMPLOYEE"
  ON DELETE CASCADE;
ALTER TABLE "SQLDBA"."PROJECT"
  ADD FOREIGN KEY "R_EMPLY2"
 ("RESPEMP"
 ) REFERENCES "SQLDBA"."EMPLOYEE"
  ON DELETE SET NULL;
ALTER TABLE "SQLDBA"."EMP_ACT"
  ADD FOREIGN KEY "R_PROACT"
 ("PROJNO"
 , "ACTNO"
 , "EMSTDATE"
 ) REFERENCES "SQLDBA"."PROJ_ACT"
  ON DELETE RESTRICT;
ALTER TABLE "SQLDBA"."PROJ_ACT"
  ADD FOREIGN KEY "R_PROJ2"
 ("PROJNO"
 ) REFERENCES "SQLDBA"."PROJECT"
  ON DELETE RESTRICT;
COMMENT '*************      Active Unique Constraints     *************'
COMMENT '*************           Table Grants             *************'
CONNECT "SQLDBA" IDENTIFIED BY "BOOMER";
GRANT SELECT
 ON "SQLDBA"."ACTIVITY"
 TO "PUBLIC";
GRANT SELECT
 ON "SQLDBA"."DEPARTMENT"
 TO "PUBLIC";
GRANT SELECT
 ON "SQLDBA"."EMP_ACT"
 TO "PUBLIC";
GRANT SELECT
 ON "SQLDBA"."EMPLOYEE"
 TO "PUBLIC";
GRANT SELECT
 ON "SQLDBA"."PROJ_ACT"
 TO "PUBLIC";
GRANT SELECT
 ON "SQLDBA"."PROJECT"
 TO "PUBLIC";
COMMENT '*************          Column Grants             *************'
COMMENT '*************       Views with Grants            *************'
   COMMENT '******** SQLDBA.VPHONE                 ********'
CREATE VIEW VPHONE (LASTNAME, FIRSTNAME, MIDINITL, PHNUMBER, EMNUMBER,
 DPNUMBER, DEPTNAME) AS SELECT LASTNAME, FIRSTNME, MIDINIT, PHONENO,
 EMPNO, DEPTNO, DEPTNAME FROM EMPLOYEE,
 DEPARTMENT WHERE WORKDEPT=DEPTNO;
GRANT SELECT
 ON "SQLDBA"."VPHONE"
 TO "PUBLIC";
   COMMENT '******** SQLDBA.VPROJ                  ********'
CREATE VIEW VPROJ (EMP_NO, AC_STAFF, DEPT_NO) AS SELECT EMPNO, ACSTAFF,
 DEPTNO FROM EMP_ACT, PROJ_ACT,
 PROJECT WHERE EMP_ACT.PROJNO = PROJ_ACT.PROJNO AND PROJ_ACT.PROJNO =
 PROJECT.PROJNO;
COMMENT '********************  PACKAGE REBIND ********************'

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]