DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

Chapter 11. Application Server Support for VSE

Up to 36 DB2 Server for VSE application servers can be active at the same time in

your VSE system.

DBNAME Directory

The DBNAME directory is a user-definable directory of application server names,

contained in an A-type source member called ARISDIRD. Each entry in this

directory is an 80 byte record in the following format:

v Comment, column 1

v Transaction Program Name (TPN), columns 2 to 5

v Application Identifier (APPLID), columns 10 to 17

v System default marker (SYSDEFAULT), column 21

v DBNAME columns 22 to 39

v Partition name (PDEFAULT) columns 44 and 45

v Privileged (PRIVILEGE) column 50.

For more information about the DBNAME directory, refer to the DB2 Server for

VSE System Administration manual.

Chapter

12. SQL Reserved Words

Following is a list of SQL reserved words you should avoid using:

ACQUIRE

GRANT

RESOURCE

ADD

GRAPHIC

REVOKE

ALL

GROUP

ROLLBACK

ALTER

ROW

AND

HAVING

RUN

ANY

IDENTIFIED

SCHEDULE

ASC

SELECT

AVG

INDEX

SET

INSERT

BETWEEN

INTO

SOME

STATISTICS

STORPOOL

CALL

SUM

CHAR

LOCK

SYNONYM

CHARACTER

LONG

COLUMN

TABLE

COMMENT

MAX

COMMIT

MIN

CONCAT

MODE

UNION

CONNECT

UNIQUE

COUNT

NAMED

UPDATE

CREATE

NHEADER

USER

CURRENT

NOT

NULL

VALUES

DBA

VIEW

DBSPACE

DELETE

WHERE

DESC

OPTION

WITH

DISTINCT

WORK

DOUBLE

ORDER

DROP

PACKAGE

EXCLUSIVE

PAGE

EXECUTE

PAGES

EXISTS

PCTFREE

EXPLAIN

PCTINDEX

PRIVATE

FIELDPROC

PRIVILEGES

FOR

PROGRAM

FROM

PUBLIC

Notices

IBM may not offer the products, services, or features discussed in this document in

all countries. Consult your local IBM representative for information on the

products and services currently available in your area. Any reference to an IBM

product, program, or service is not intended to state or imply that only that IBM

product, program, or service may be used. Any functionally equivalent product,

program, or service that does not infringe any IBM intellectual property right may

be used instead. However, it is the user’s responsibility to evaluate and verify the

operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter

described in this document. The furnishing of this document does not give you

any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing

IBM Corporation

North Castle Drive

Armonk, NY 10594-1785

U.S.A.

For license inquiries regarding double-byte (DBCS) information, contact the IBM

Intellectual Property Department in your country or send inquiries, in writing, to:

IBM World Trade Asia Corporation

Licensing

2-31 Roppongi 3-chome, Minato-ku

Tokyo 106, Japan

The following paragraph does not apply to the United Kingdom or any other

country where such provisions are inconsistent with local law:

INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS

PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER

EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED

WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS

FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or

implied warranties in certain transactions, therefore, this statement may not apply

to you.

This information could include technical inaccuracies or typographical errors.

Changes are periodically made to the information herein; these changes will be

incorporated in new editions of the publication. IBM may make improvements

and/or changes in the product(s) and/or the program(s) described in this

publication at any time without notice.

Any references in this information to non-IBM Web sites are provided for

convenience only and do not in any manner serve as an endorsement of those Web

sites. The materials at those Web sites are not part of the materials for this IBM

product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it

believes appropriate without incurring any obligation to you.

103

Licensees of this program who wish to have information about it for the purpose

of enabling: (i) the exchange of information between independently created

programs and other programs (including this one) and (ii) the mutual use of the

information which has been exchanged, should contact:

IBM Corporation

Mail Station P300

522 South Road

Poughkeepsie, NY 12601-5400

U.S.A

Such information may be available, subject to appropriate terms and conditions,

including in some cases, payment of a fee.

The licensed program described in this information and all licensed material

available for it are provided by IBM under terms of the IBM Customer Agreement,

IBM International Program License Agreement, or any equivalent agreement

between us.

Any performance data contained herein was determined in a controlled

environment. Therefore, the results obtained in other operating environments may

vary significantly. Some measurements may have been made on development-level

systems and there is no guarantee that these measurements will be the same on

generally available systems. Furthermore, some measurement may have been

estimated through extrapolation. Actual results may vary. Users of this document

should verify the applicable data for their specific environment.

Information concerning non-IBM products was obtained from the suppliers of

those products, their published announcements, or other publicly available sources.

IBM has not tested those products and cannot confirm the accuracy of

performance, compatibility, or any other claims related to non-IBM products.

Questions on the capabilities of non-IBM products should be addressed to the

suppliers of those products.

All statements regarding IBM’s future direction or intent are subject to change or

withdrawal without notice, and represent goals and objectives only.

This information may contain examples of data and reports used in daily business

operations. To illustrate them as completely as possible, the examples include the

names of individuals, companies, brands, and products. All of these names are

fictitious and any similarity to the names and addresses used by an actual business

enterprise is entirely coincidental.

This information may contain sample application programs in source language,

which illustrates programming techniques on various operating platforms. You

may copy, modify, and distribute these sample programs in any form without

payment to IBM, for the purposes of developing, using, marketing, or distributing

application programs conforming to the application programming interface for the

operating platform for which the sample programs are written. These examples

have not been thoroughly tested under all conditions. IBM, therefore, cannot

guarantee or imply reliability, serviceability, or function of these programs.

104

Programming Interface Information

This book documents intended Programming Interfaces that allow the customer to

write programs to obtain services of DB2 Server for VSE & VM.

Trademarks

The following terms are trademarks of International Business Machines

Corporation in the United States, or other countries, or both:

CICS

CICS/VSE

DATABASE 2

DB2

DRDA

IBM

IBMLink

PROFS

SAA

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of

Microsoft Corporation in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks

of others.

Notices

105

Readers’ Comments — We’d Like to Hear from You

DB2 Server for VSE & VM

Quick Reference

Version 7 Release 5

Publication No. SC09-2988-01

We appreciate your comments about this publication. Please comment on specific errors or omissions, accuracy,

organization, subject matter, or completeness of this book. The comments you send should pertain to only the

information in this manual or product and the way in which the information is presented.

For technical questions and information about products and prices, please contact your IBM branch office, your

IBM business partner, or your authorized remarketer.

When you send comments to IBM, you grant IBM a nonexclusive right to use or distribute your comments in any

way it believes appropriate without incurring any obligation to you. IBM or any other organizations will only use

the personal information that you supply to contact you about the issues that you state on this form.

Comments:

Thank you for your support.

Submit your comments using one of these channels:

v Send your comments to the address on the reverse side of this form.

v Send a fax to the following number: 1-416-448-6161 (US and Canada) (Attention RCF Coordinator)

v Send your comments via e-mail to: torrcf@ca.ibm.com

If you would like a response from IBM, please fill in the following information:

Name

Address

Company or Organization

Phone No.

E-mail address

Contents

About This Manual

Option 3 - REORGANIZE DBSPACE

Who Should Use This Manual

Option 4 - RELOAD DBSPACE

Conventions Used in This Manual

DBSPACE Reorganization Submit Screen

Organization of This Manual

VSE/POWER Parameters

Prerequisite IBM Publications

Single User Mode (SUM) DBSPACE Reorganization

Before You Choose Single User Mode Execution

Single User Mode Parameters

Summary of Changes

vii

DBSPACE Reorganization Tape Support

Summary of Changes for DB2 Version 7 Release 3

vii

Unloading to Tape

Enhancements, New Functions, and New

Special Considerations

Capabilities

. vii

Repetitive Scheduling

Reliability, Availability, and Serviceability

Failure Restart

Improvements

viii

Problem Analysis

Chapter 1. Introduction

Chapter 5. DBSPACE Analysis Tools .

Product Benefits

About the DBSPACE Analysis Tools

Access Control

Before You Begin

Single User Mode Parameters

How the DBSPACE Analysis Tools Work . .

Multiple Database Support

SQLMAINT Table

Database Operator Command Interface

DBSPACE Analysis Utility Screen

DBSPACE Reorganization

Functions

DBSPACE Analysis

Selection Options

Group Authorization Tools

DBSPACE Reorganization Criteria (CRITERIA)

Monitor Tools

Update Statistics Analysis Tool

Package Tools

DBSPACE Reorganization Analysis Tool

Work File Label Definition

DBSPACE Analysis Submit Screen

CICS Report Controller Interface

VSE/POWER Job Parameters

Help Facility

DBSPACE Analysis Job Options

Job Scheduling Tool

Additional Topics

Query Management Facility

Initial Execution

Table Utility Tool

Reorganization Work Space

Prerequisite Programs

About Control Center

Control Center’s DBA ID

Chapter 6. Work File Label Definition

How to Invoke

Tool

Before You Use Control Center

About the Work File Label Definition Tool

Changing to a New Database

Work File Label Definition Screen

How the Work File Label Definition Tool Works

Chapter 2. Getting Started

Disk Work File Label Definition Screen

Disk Work File Label Definition Fields

Getting Started With Control Center . .

Tape Work File Definition Screen

Control Center Sign On

Special Considerations

Chapter 3. Using the Operator Command

Chapter 7. CICS Report Controller

Interface Tool

About the CICS Report Controller Interface Tool .

Chapter 4. DBSPACE Reorganization

A Sample CICS Report Controller Session

Tool

When To Reorganize

Chapter 8. Control Center Help Facility

Features

About the Help Facility

How the DBSPACE Reorganization Tool Works . . 14

Using the DBSPACE Reorganization Utility Screen

Chapter 9. Package Utility

Optional Parameters

Using the DBSPACE REORGANIZATION Tool . . 19

Introduction

Option 1 - GENERATE DDL

Package Utility Functions

Option 2 - UNLOAD DBSPACE

Package Function Descriptions

iii

Package Migration

Starting a Monitor

Invocation

Changing a Monitor

Package Utility Parameters

Viewing Monitor Data

Using the Package Utility

Stopping a Monitor

Chapter 10. Group Authorization Tool

Chapter 12. Table Utility

About the Group Authorization Tool

Invocation

Functions

LIST TABLES

ADD a Group

List Tables Processing Flow

DROP a Group

DROP TABLE

Manage Group Objects and Users

Drop Table Processing Flow

Manage Privileges

REORGANIZE TABLE

LIST Functions

Reorganize Table Processing Flow

Using the Group Authorization Tool

Table Reorganization Menu Required Parameters

Step 1: Define Application Groups

Table Reorganization Menu Optional Parameters

Step 2: Add (or Drop) Objects to the Application

Using the TABLE REORGANIZATION Option

Group

Table Reorganization Submit Screen . .

Step 3: Define User Groups

CREATE TABLE

Step 4: Add Users to the User Groups

Create Table Processing Flow

Step 5: Grant Authorities to the User Groups . . 66

Using the Create Table Function

Special Considerations

Create Table Parameters

UPDATE STATISTICS

Chapter 11. The Monitor Maintenance

Update Statistics Submit Screen Required

Parameters

Update Statistics Submit Screen Optional

How the Monitors Work

Parameters

Options and Monitors Available

Monitor Thresholds and VSE Console Messages

Chapter 13. Installing Stored

Description of Monitor Options

Start the Monitor Kernel

Procedures Support

101

Stop the Monitor Kernel

List Monitors

Appendix A. Reorganization Job

Add a Monitor

Streams

105

Modify a Monitor

Delete a Monitor

Appendix B. DBSPACE and Table

Display a Monitor

View Data

Reorganization Tool Related Files

127

Reset Data

Print Report

Notices

135

Types of Monitors

Trademarks

137

SHOW ACTIVE

SHOW LOCK

Glossary

139

SHOW DBEXTENT

SHOW LOG

Bibliography

141

SHOW CONNECT

SHOW DBSPACE

COUNTER *

Index

145

Invocation

How To Use the Monitors

Contacting IBM

149

Adding A Monitor

Product information

149

Using the Monitor Maintenance Menu

Specifying Monitor Thresholds

iv Control Center Operations Guide for VSE

About This Manual

Who Should Use This Manual

Control Center is a set of database administration and operation support tools for

IBM DB2 Server for VSE & VM Version 7.3 databases. This manual is intended for

people who want to learn about the product or who are involved in its evaluation,

installation, maintenance, administration, or use in a VSE/ESA^™ environment.

Conventions Used in This Manual

Throughout this document and in the Control Center screen interfaces, the terms

database, database manager, and database server, are used to refer to a DB2 Server for

VSE composed of a directory, log(s), and one or more dbextents.

Unless otherwise specified, the term Control Center refers to Control Center

Version 7 Release 3 Modification 0.

Organization of This Manual

“Summary of Changes” on page vii summarizes the changes made to DB2 Server

for VSE & VM for this release.

Chapter 1, “Introduction” on page 1 introduces the Control Center product and

tool set.

Chapter 2, “Getting Started” on page 7 introduces you to the Control Center Main

Menu.

Chapter 3, “Using the Operator Command Interface Tool” on page 9 describes

how to use the Operator Command interface to issue SHOW and COUNTER

commands to any connected DB2 Server in your VSE environment.

Chapter 4, “DBSPACE Reorganization Tool” on page 13 describes the automated

Control Center tools for reorganizing DBSPACES within a database.

Chapter 5, “DBSPACE Analysis Tools” on page 27 describes the Control Center

tools for analyzing database DBSPACEs and performing maintenance upon them

to improve performance. Maintenance includes DBSPACE reorganization and

UPDATE STATISTICS.

Chapter 6, “Work File Label Definition Tool” on page 39 describes the Work File

Label Definition tools that help you define the reorganization tool’s work files.

Chapter 7, “CICS Report Controller Interface Tool” on page 47 describes the

Control Center interface to the CICS^® Report Controller function.

Chapter 8, “Control Center Help Facility” on page 51 describes the Control Center

Help Facility.

Chapter 9, “Package Utility” on page 53 describes how you can automate tasks

associated with packages within a database.

Chapter 10, “Group Authorization Tool” on page 59 describes how this tool assists

DBAs in managing the access to database objects, simplifies the process of

authorization, and shortens the amount of time needed to grant or revoke

privileges to individual users or groups of individuals.

Chapter 11, “The Monitor Maintenance Menu” on page 69 describes how the

Database Monitoring tools are used to monitor database status and activities.

Chapter 12, “Table Utility” on page 81 describes how you can easily view a list of

tables (and some of their attributes) stored in a DB2 Server for VSE database and

do specific operations on it.

Chapter 13, “Installing Stored Procedures Support” on page 101 describes the

stored procedures that are used to do local processing.

Appendix A, “Reorganization Job Streams” on page 105 gives examples of the job

streams generated when a database or table reorganization is requested by the

user.

Appendix B, “DBSPACE and Table Reorganization Tool Related Files” on

page 127 lists the DBSPACE Reorganization Tool related files and explains their

use.

Prerequisite IBM Publications

This manual assumes you have reviewed and understand the IBM manuals for the

related products. You should be familiar with VSE systems, VSE job control

language, VSE/VSAM, the CICS system and have a working technical knowledge

of System Administration and Database Administration in a DB2 Server for VSE

environment.

vi Control Center Operations Guide for VSE

Summary of Changes

This is a summary of the technical changes to the DB2 Server for VSE & VM

database management system for this edition of the book. Several manuals are

affected by some or all of the changes discussed here. For your convenience, the

changes made in this edition are identified in the text by a vertical bar (|) in the

left margin. This edition may also include minor corrections and editorial changes

that are not identified.

This summary does not list incompatibilities between releases of the DB2 Server

for VSE & VM product; see either the DB2 Server for VSE & VM SQL Reference, DB2

Server for VM System Administration, or the DB2 Server for VSE System

Administration manuals for a discussion of incompatibilities.

Summary of Changes for DB2 Version 7 Release 3

Version 7 Release 3 of the DB2 Server for VSE & VM database management

system is intended to run on the VM/ESA Version 2 Release 4 or later environment

and on the Virtual Storage Extended/Enterprise Systems Architecture (VSE/ESA^®)

Version 2 Release 5 Modification 1 or later environment.

Enhancements, New Functions, and New Capabilities

The following have been added to DB2 Version 7 Release 3:

Cancel TCP/IP Agent in VM

With this new functionality, if the client is connected to a DB2 for VM application

server via TCP/IP, and the client disconnects from the application server, the DB2

for VM application server will detect that the client has disconnected and will

immediately release the resources that it had held.

Force Inactive Users

This function introduces a new DB2 Server for VM operator command, FORCE

INACTIVE. The FORCE INACTIVE command will disconnect inactive users on

DB2 Server for VM. Also, when SQLEND is issued on DB2 Server for VM, inactive

users will be automatically disconnected so that they do not delay database

shutdown. This will make the behavior of SQLEND consistent on DB2 Server for

VM and on DB2 Server for VSE.

See the DB2 Server for VSE & VM Operation manual for further details.

CLI/JDBC V8 Redesign

If you would like to use DB2 Server for VSE & VM as a server with access by

CLI/ODBC/JDBC/OLE clients, you need the support of schema functions on the

DB2/VSE&VM database servers. These schema functions allow applications to get

catalog information in a way that is database vendor independent. The schema

functions return a standard result set to the user. In DB2 Universal Database V8.1,

these functions are implemented with views and stored procedures if the views

rely on SQL that is not supported on the VSE & VM platforms.

There is now a set of stored procedures that will generate the schema functions

results sets without views. The result sets returned by these stored procedures

correspond to the results obtained from other servers when SELECTing from the

schema views.

1997, 2003

vii

The schema stored procedures created on VM and VSE servers are invoked

internally by CLI/JDBC drivers.

See the DB2 Server for VSE & VM Database Administration manual for additional

information.

Control Center for VSE Enhancements

The Control Center for VSE restriction of a hard coded user name and password

for connecting to a database has been removed. When a Control Center for VSE

transaction is initiated in CICS, Control Center will request the user ID and the

password from the terminal user. A parameter card will be used for batch

programs to input the user ID and password.

Control Center for VM Enhancements

Control Center enhancements include support for new operator commands,

enhancements to existing operator functions, and enhancements to current

initialization parameters.

Reliability, Availability, and Serviceability Improvements

Alternate Logging

A new inactive log can now take over when the active log reaches the ARCHPCT

value and alternate logging is enabled, instead of immediately forcing a log archive

to be taken. A log archive can then be taken at a later time, as determined by the

operator. If an attempt to switch to the inactive log occurs and that inactive log has

not already been archived, the operator is forced to take an archive of the inactive

and active logs.

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VSE System Administration

v DB2 Server for VM System Administration

v DB2 Server for VSE & VM Diagnosis Guide and Reference

v DB2 Server for VSE & VM Operation

v DB2 Server for VSE Program Directory

v DB2 Server for VM Program Directory

Data Restore Changes

Data Restore now allows users to use the RELOAD function with

RECOVERY=YES, even if an event such as a COLDLOG has broken the logical

continuity of the log files. In this case, the log can now be applied until this event

is reached. Alternate logging is fully supported with Data Restore for VSE & VM

V7.3.

Release Empty Pages

You can now release empty pages in any dbspace on VM without having to issue

DROP DBSPACE, by using a new single user mode utility called SQLRELEP. A

new single user mode startup option, STARTUP=P, is introduced to enable you to

release empty pages on VSE. For more information, see the DB2 Server for VSE &

VM Database Administration manual.

More Information on TCP/IP Messages

In the event of a TCP/IP server-side error, new messages will now be displayed

with more information pertaining to the TCP/IP error. This information will

include the input parameters of the service causing the error.

viii Control Center Operations Guide for VSE

Chapter

1. Introduction

Control Center is an IBM licensed program that works with the DB2 Server for

VSE licensed program to automate many of the manual Database Administrator

(DBA) functions required to support databases. It automates functions such as

DBSPACE backup, migration, reorganization, and analysis. It generates the

complete set of Data Definition Language (DDL) required to redefine any

DBSPACE and its objects. It also keeps catalog statistics updated.

Through full support for VSE/POWER Job Scheduling, Control Center functions

can be initiated immediately or scheduled to execute at any later date or time.

Repetitive execution is also supported.

By automating the complex steps required to perform many DBA activities,

Control Center simplifies the task of supporting databases. DBA functions can be

scheduled and performed automatically during periods of low system use. This

improves the operational productivity of the entire system and allows these

functions to be performed in a consistent and repeatable manner with a high

degree of security and control.

Control Center is easily installed into DB2 Server for VSE databases. Its automated

control lessens the workload of managing many databases.

The product consists of a set of online and batch programs, VSAM and SAM files,

and database tables. It does not directly attach to any of your operating system or

database management system code. All interfaces used are standard and

documented.

Control Center helps you manage your databases using conventional COBOL CICS

and related programs. The Online Resource Adapter (ORA) provides the

connection to the database servers on your VSE system, while the CICS Report

Controller feature provides the interface to your VSE/POWER spool file. Using

these interfaces, applications can CONNECT to your databases and build and

submit batch jobstreams to maintain them.

Product Benefits

Access Control

Lets you control access to Control Center by assigning a special CICS transaction

security key to its transactions. You must include it in the CICS signon table (SNT)

entries for the users to whom you want to allow access. You can also control access

to the product by granting or withholding RUN authority on its packages.

Single User Mode Parameters

Such as LOGMODE, NDIRBUF, and NPAGEBUF give you the flexibility to choose

whether or not to LOG transactions. Using these parameters, for instance during

reorganizations, can improve performance by allowing the database to use more

buffers in single user mode than it would use in multiple user mode.

Introduction

Multiple Database Support

Exploits CICS Database Switching to CONNECT to, and manage, all of the

databases on a VSE/ESA system.

Database Operator Command Interface

Lets you issue SHOW and COUNTER operator commands easily without having

to use ISQL. It displays operator command output in a user-friendly format with

full scrolling and online help.

DBSPACE Reorganization

Offers four (4) main functions:

v DDL generation

v UNLOAD DBSPACE

v RELOAD DBSPACE

v Reorganize DBSPACE

Reorganizing at the DBSPACE level helps you improve performance and eliminate

wasted space. The DROP DBSPACE command is used to reduce logging and

return pages to the storage pool for use elsewhere in the database. TABLES are

RELOADed individually in the sequence of their clustering index.

Execution options let you move a DBSPACE to a larger DBSPACE, a different

storage pool, or a different database for migration or regeneration. You can store

data externally on tape or disk. You can also choose to REBIND all PACKAGES

that are dependent on an object in the DBSPACE and optionally UPDATE ALL

STATISTICS.

The Generate DDL option captures all of the DDL necessary to recreate a

DBSPACE. DDL is placed in the VSE/POWER punch queue. From there, you can

copy it into your editor and make whatever changes you want to it.

DBSPACE Analysis

Evaluates your databases using built-in DBA expertise. The DBSPACE Analysis

tools build a list of DBSPACES that require maintenance (reorganization or

UPDATE STATISTICS) and allow you to view it online. From the list you can select

what DBSPACEs you want to maintain. The DBSPACE Analysis tools then build

and submit the appropriate batch job.

Group Authorization Tools

Simplify the management of access to database tables, views, and packages. The

Group Authorization Tool allows DBAs to issue authorizations to groups of users

on groups of objects rather than one by one. Control Center stores group

information in database tables and provides reports designed to make

authorization administration easier.

Monitor Tools

Record database activity such as locking or log percent full and provide

notification when the threshold you define has been exceeded. Monitor information

is stored in tables that you can view on-line or print in a batch report.

Control Center Operations Guide for VSE

Introduction

Package Tools

Allow you to unload, reload, rebind, or view any of the packages stored in your

databases. You can also use the package tools to migrate packages from one

application server to another.

Work File Label Definition

Allows you to define your tape and SAM work files easily. For compatibility with

tape management systems, all TLBL parameters are supported. For SAM work

files, you define a set of small, medium, and large files that are used for all the

DBSPACEs in your database.

CICS Report Controller Interface

Provides quick and easy access to the jobs and reports you have submitted. Using

the CICS Report Controller Interface, you can hold, or delete batch jobs, and

browse, print, or delete reports. You can also view or change job and report

characteristics.

Help Facility

Provides comprehensive Help and ″How-To″ information on all aspects of Control

Center. A scrollable menu of Help topics is presented allowing you to select more

specific Help information.

Job Scheduling Tool

Utilizes the full power and capabilities of VSE/POWER Time Event Scheduling as

it builds batch jobs. For your jobs, you may specify the day or date and time a job

is to be scheduled for processing. If you want to schedule a repetitive job, you can

choose:

v Daily

v Weekly (for example, each Monday)

v Specific day of the month (for example, each first day)

v Specific day of specific months (for example, January 1, July 1).

Query Management Facility

Provides quick and easy access to QMF from the Control Center Main Menu.

Table Utility Tool

Provides quick and easy ways to list, reorganize (including unloading and

reloading), create, drop, and update the statistics for tables stored in a DB2 Server

for VSE database.

Prerequisite Programs

This section summarizes the required program products. Unless otherwise stated,

Control Center works with all subsequent versions, releases, and modification

levels of the products listed in this section as well as with equivalent non-IBM

products.

These are prerequisite products:

v VSE/Enterprise Systems Architecture Version 2 Release 3 or later

v DB2 Server for VSE Version 7 Release 3 (5697-F42)

Chapter 1. Introduction

Introduction

v VSE/REXX (part of VSE/Central Functions, Program Number 5686-066, and all

subsequent releases)

Control Center Version 7.3 can work with previous version databases of DB2

and SQLDS. However, since the Control Center Version 7.3 packages must reside

in those databases, installing Version 7.3 will overlay any earlier packages and

will thereby preclude using earlier versions of Control Center with those

databases. What this means is that a database can be used with one and only

one version of Control Center.

v LE for VSE/ESA Version 1 Release 4 (5686-094)

About Control Center

The product executes as a set of CICS transactions and VSE batch jobstreams. Its

transactions share the same CICS partition as your other applications. Its batch jobs

can be run in any open partition.

Control Center applications use static, pre-planned SQL. The database optimizer

determines the most efficient access path at compile-time, and stores it in the

database as a package. This results in better performance at run-time. Control

Center programs are pseudo-conversational which allow more transactions to run

concurrently.

The CICS Report Controller is utilized to submit batch jobs from CICS. This way,

potentially long-running tasks such as DBSPACE reorganizations do not adversely

affect online users. A menu interface is also provided to allow users to manage

jobs and reports in the VSE/POWER spool file and browse reports online.

Additional capabilities include:

v Control Center takes advantage of CICS Database Switching; this lets online

users dynamically connect to different servers.

v Its batch jobs are intelligent, using return codes and conditional JCL to alter

execution in the event of failure; this gives the DBA application recovery

capability.

v It temporarily stores unloaded data and DDL statements in SAM datasets. For

permanent storage, you may select VSAM or tape (for data). This allows you to

reload data and DDL from prior DBSU unload operations.

Control Center’s DBA ID

Control Center requires an ID with DBA authority to connect to every database it

manages. Be sure you have completed the installation step that grants DBA

authority to this ID.

How to Invoke

Using the Screens

You can enter the transaction ID ″SQM″ from a blank CICS screen to access

Control Center tools using the panel interface. From there, you can navigate

through the product quickly and easily using ENTER and the function keys.

Using the Transaction ID (TRANSID)

If you know the transaction ID of the function you want to execute and if it

supports direct invocation, you may simply enter its TRANSID. When you exit

from that function, you return to a blank CICS screen. The functions that support

direct invocation are:

Control Center Operations Guide for VSE

Introduction

Main Menu

(SQM)

Group Authorization Tool

(SQGA)

Operator Commands Menu

(SQOM)

DBSPACE Reorganization

(SQDR)

DBSPACE Analysis

(SQMM)

Package Utility Tool

(SQPM)

Work File Label Definition

(SQFM)

CICS Report Controller

(CEMS)

Help Facility

(SQHM)

Table Utility

(SQTU)

Before You Use Control Center

Distribution Library:

The default delivery library for Control Center is PRD2.CCF730. The installation

procedures in the DB2 Server for VSE Control Center Program Directory describe how

you can specify a different library. If you are using DB2 Server for VSE Version 7.1,

the default installation library is PRD2.DB2710; for DB2 Server for VSE Version 7.2,

it is PRD2.DB2720; and for DB2 Server for VSE Version 7.3, it is PRD2.DB2730.

Preventive Service Planning:

Read the Control Center Program Directory provided with the distribution tape and

check for any program temporary fixes (PTFs) that you may need to install. If you

obtained Control Center individually from IBM Software Distribution, you should

contact the IBM Support Center, or use either Information/Access, or the IBMLink

system (ServiceLink) for additional preventive service planning (PSP) information.

This program release will be maintained through the use of program temporary

fixes (PTFs). An updated version or release replaces the entire program code. A

PTF replaces the changed program code only.

Changing to a New Database

The Control Center installation process is defined in the DB2 Server for VSE Control

Center Program Directory. Its instructions include intializing CICS and DB2

databases for use with Control Center. If you want to add a new database to those

already initialized for Control Center, you must do the following steps.

STEP TITLE

v Ensure that the Control Center DBA ID has proper DBA Authority

v Define and Load the Help Table

v Define the Maintenance Tracking Table

v Define the Monitor Tables

v Define the Group Authorization Tables

v Load Packages into Servers

v Installing Stored Procedure Support (Optional)

Chapter 1. Introduction

Chapter 2. Getting Started

This chapter explains how to start using the Control Center feature and introduces

you to the main menu. The following chapters describe how to use the Control

Center tools.

Getting Started With Control Center

You can type SQM on a blank CICS screen to reach the main menu.

mm/dd/yyyy

CONTROL CENTER V7.3

hh:mm:ss

*------------------------------- MAIN MENU

--------------------------------*

| OPTION

=> __ A

USER ID: B

| DATABASE

=> __________________ C

CICS ID: D

| E

************************** DBA FUNCTIONS

************************** |

1 OPERATOR COMMANDS

2 DBSPACE REORGANIZATION

3 DBSPACE ANALYSIS

4 WORK FILE LABEL DEFINITION

5 CICS REPORT CONTROLLER

6 HELP FACILITY

7 PACKAGE UTILITY

8 GROUP AUTHORIZATION

9 MONITOR UTILITY

| 10 TABLE UTILITY

| 11 QMF

*------------------------------------------------------------------ SQC01 ----*

G PRESS ENTER TO SELECT FUNCTION

H ENTER F1=HELP F3=EXIT

Figure 1. Control Center Main Menu

Some things you should know about typical Control Center menus are:

A OPTION—Selects the function you want to use. Enter the number of the

tool you want to use in this field. The option number is the highlighted

identifier displayed to the left of the option description.

B USER ID—The 8-character USER ID of the signed on user.

C DATABASE—Identifies the database you are working with. When you sign

on, this field displays the name of the default database. To work with a

different database you can type the name of the new database in this field.

D CICS ID—Shows the 8-character APPLID of the CICS system owning the

transaction.

E DBA FUNCTIONS—Lists the functions available from the current menu.

F Error Message Line—Error messages, if any, are displayed on this line.

G Instruction Line—Instructions are displayed on this line.

H Function (F) Key Line—Displays active function keys and control keys.

Each of the DBA Functions shown in Figure 1 is described in detail in the chapters

that follow, except for the QMF function. Selecting option 11, QMF, directly invokes

QMF. If it completes normally, control returns to Control Center, otherwise QMF

handles the error.

1997, 2003

Getting Started

Control Center Sign On

When you enter the main Control Center transaction ID (SQM), or one of the

transactions that directly invoke a Control Center function, you will be presented

with the Sign On Panel. Enter the DBA ID and the password, and press the Enter

key to continue. You will remain signed on until you exit the transaction.

mm/dd/yyyy

CONTROL CENTER V7.3

hh:mm:ss

*-----------------------------

-------------------------------*

SIGN ON PANEL

=============

USER ID: SQLDBA

| DATABASE

=> VSEMCHMB

CICS ID: DBDCCICS |

| PLEASE ENTER CONTROL CENTER DBA ID AND PASSWORD

| CC DBA ID =>

PASSWORD =>

*------------------------------------------------------------------ SQC00 ----*

ENTER F1=HELP F3=EXIT

Figure 2. Control Center DBA Sign On

Control Center Operations Guide for VSE

Chapter 3. Using the Operator Command Interface Tool

The Operator Command Interface tool provides an interface between you and the

database to perform operator SHOW and COUNTER commands. With this tool,

you do not need to directly enter ISQL commands, nor do you need to issue the

commands from the VSE Operator Console. Multiple screens are used to display

the set of operator commands that you can select. You can scroll between them

using the Forward and Backward function keys.

This tool can be invoked by selecting option 1 from the main menu or by using the

CICS transaction ID “SQOM”.

The Operator Commands menus use this syntactical notation:

v Words in uppercase must be typed exactly as shown

v Words in lowercase should be replaced by your particular values in uppercase or

lowercase

v Sets of phrases or words from which you must choose one are enclosed in

parentheses and separated by vertical bars

v Default values are shown in a non-green (usually turquoise) color and are

underlined.

Using the Operator Command Interface Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*----------------------------- OPERATOR COMMANDS -----------------------------*

| OPTION ===>

USER ID: MSTRSRV1 |

| PARMS ====> _________________________________________

CICS ID: SYSTVM1

| DATABASE => SQLDBA_____________

1 SHOW ACTIVE

2 SHOW ADDRESS

module name

3 SHOW BUFFERS

4 SHOW CONNECT

( ALL | user-id | AGENT n | LUWID id

| ACTIVE | WAITING | INACTIVE )

5 SHOW DBCONFIG

6 SHOW DBEXTENT

7 SHOW DBSPACE

dbspace number

8 SHOW INDOUBT

9 SHOW INITPARM

| 10 SHOW INVALID

| 11 SHOW LOCK ACTIVE

| 12 SHOW LOCK DBSPACE

( ALL | dbspace number)

*------------------------------------------------------------------ SQC11A ---*

Default values are shown in turquoise.

ENTER OPTION, PARMS, AND DATABASE NAME AND PRESS ENTER

ENTER F1=HELP F3=EXIT F8=FWD

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*-------------------------

OPERATOR COMMANDS

-------------------------*

| OPTION

=> __

USER ID: MSTRSRV1 |

| PARMS

=> _________________________________________ CICS ID: SYSTVM1

| DATABASE

=> SQLDBA__________

| 13 SHOW LOCK GRAPH

( auth-id | AGENT n )

| 14 SHOW LOCK MATRIX

| 15 SHOW LOCK USER

( ALL | auth-id | AGENT n )

| 16 SHOW LOCK WANTLOCK

( ALL | auth-id | AGENT n )

| 17 SHOW LOG

| 18 SHOW LOGHIST

( ALL | n ) ( SERVICE )

| 19 SHOW POOL

( ALL | SUMMARY | DELETED | n )

| 20 SHOW PROC

( proc-name | proc-name AUTHID auth-id

| * | * AUTHID auth-id )

| 21 SHOW PSERVER

( * | procsvr-name | GROUP * | GROUP svr-group-name )|

| 22 SHOW STORAGE

| 23 SHOW SYSTEM

| 30 COUNTER

( * | counter-name )

*------------------------------------------------------------------ SQC11B ---*

Default values are shown in turquoise.

ENTER OPTION, PARMS, AND DATABASE NAME AND PRESS ENTER

ENTER F1=HELP F3=EXIT F7=BWD

Figure 3. DBA Operator Commands Screens

The DBA Operator Commands menus are shown in Figure 3.

The database server you are currently working with is identified in the

DATABASE field shown near the top of the screen.

To execute an operator command:

v Enter the 1 or 2 digit OPTION number that precedes each command. The option

number can be entered even if the option and syntax information are not on the

screen currently being shown.

Control Center Operations Guide for VSE

Using the Operator Command Interface Tool

v Enter any required parameters. (The parameters shown in uppercase are to be

entered as shown. Those shown in lowercase are for you to supply.) Default

values are shown in a different color (usually turquoise) and are underlined.

v Enter the database name in the DATABASE field.

For example, if you want to issue a SHOW LOCK USER on AGENT1 command

against the DB2PROD database, enter:

OPTION ===> 16

PARMS ====> AGENT 1

DATABASE => DB2PROD

The result of the command will be displayed. For example, if you issued the

following SHOW ACTIVE command against server SQLDBA:

OPTION ===> 1

PARMS ====>

DATABASE => SQLDBA

you would see:

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*---------------------- OPERATOR COMMAND DISPLAY SCREEN ----------------------*

| COMMAND

=> SHOW ACTIVE

| DATABASE => SQLDBA

| Status of agents:

Checkpoint agent is not active.

User Agent: 3 User ID: SVSCVSAX is NIW SUBS

Agent is not processing and is in communication wait.

User Agent: 4 User ID: SQLMSTR is R/O SUBS BDFF

Agent is processing an operator command.

User Agent: 5 User ID: SVSCVSAX is NIW SUBS

Agent is not processing and is in communication wait.

2 agent(s) not connected to an APPL or SUBSYS.

| ARI0065I Operator command processing is complete.

*------------------------------------------------------------------ SQC12 ----*

F1=HELP,F3=EXIT,F4=TOP,F5=BOT,F7=BWD,F8=FWD,F12=CANCEL

Figure 4. Show Active Command Display Screen

Note: When the Operator Command Selection Menu (Figure 3 on page 10) is being

displayed, F1=HELP will display an explanation of the individual

commands. When the Operator Command Display Screen (Figure 4) is being

displayed, F1=HELP displays help information for the screen, not for the

operator command results.

For a complete description of each operator command and appropriate parameters,

refer to the DB2 Server for VSE & VM Operation manual.

Chapter 3. Using the Operator Command Interface Tool

Chapter

4. DBSPACE Reorganization Tool

The DBSPACE Reorganization Tool tool makes it easy to manage your database

servers. Databases are composed of many DBSPACEs that are logical allocations of

space. DBSPACEs can contain one or more tables and their indexes. DBSPACE

reorganizations are critical for providing optimum database performance because

when you DROP and re-ACQUIRE a DBSPACE, all unused DBSPACE pages are

returned to the storage pool for use elsewhere.

Control Center’s ability to backup, copy, move, and migrate DBSPACEs gives you

control and flexibility in managing database growth. It also allows you to extract

all of the Data Definition Language (DDL) statements needed to re-create a

DBSPACE and everything in it. DBAs no longer have to manage huge libraries of

DDL or struggle to produce ″where-used″ information because Control Center does

it for them.

The DBSPACE Reorganization Tool tool operates in Multiple User Mode (MUM) or

Single User Mode (SUM). You can choose the mode to run in. (MUM jobs run in

one partition while the database is up and running in another, available for other

users and applications. A SUM application starts the database. As soon as the

database is up, the application program takes control, so both are running in the

same partition. Other users cannot access the database until the SUM job ends and

the database is restarted in MUM).

Reorganization jobs run in batch and consist of several job steps. Each job step is

assigned a step number and description. DLBLs are generated for each step and

are included in the JCL so that you know what files are being accessed.

The Control Center screen collects parameters needed by the batch programs. The

DBSPACE Reorganization Submit screen, displayed when you press ENTER from

the Reorganization screen, allows you to schedule jobs for execution immediately

or at a later date and time, and on a one-time or repetitive basis.

When To Reorganize

Schedule DBSPACE reorganizations and RELOADS during non-peak hours to

avoid locking contention with other database users. If you schedule these kinds of

jobs during peak hours, against heavy multiple user sessions, you may encounter

lock contention when the system catalogs are updated. Running more than one

DBSPACE reorganization or RELOAD simultaneously against a single database can

also result in catalog contention.

Schedule a DBSPACE reorganization whenever the database statistics indicate that

the DBSPACE needs it. For example, when indexes are no longer clustered or

when considerable delete activity has occurred, leaving holes of deleted data on

DBSPACE pages.

You can also use the DBSPACE Reorganization tool when you need a larger

DBSPACE due to growth in the volume of data in the DBSPACE. In addition, use

the tool to move DBSPACEs to less heavily occupied storage pools. Spreading the

distribution of DBSPACEs across storage pools helps improve performance.

Moving a DBSPACE can solve a short-on-storage problem and also eliminate the

need to add a new dbextent to the database.

DBSPACE Reorganization Tool

When you want to know the characteristics of the columns in a table, use the

DBSPACE Reorganization tool’s GENERATE DDL option. The generated DDL will

show you how all of the objects in the DBSPACE are defined, what indexes exist,

who has what authorizations, and what programs access what tables.

Features

The DBSPACE Reorganization tool allows you to:

Extract and create all DDL required to re-create the DBSPACE and the objects it

contains, including:

- Tables

- Data

- Referential Integrity constraints

- Unique column definitions

- Indexes

- Views

- Grants

- Table and Column Comments

- Table and Column Labels

- Packages (Access Modules)

Unload DBSPACE data to tape or disk

Free unused pages by dropping and re-acquiring the DBSPACE

Load data in clustering index sequence

Load data with freespace for future inserts

Rebuild clustered indexes (where possible)

Update Statistics

Reprep invalidated access modules

Reload a DBSPACE to a different database

Reload a DBSPACE with a different owner

Reload a DBSPACE with a different DBSPACE name

Acquire a DBSPACE in a new storage pool

Acquire a different size DBSPACE

Change the number of DBSPACE header pages

Change the free space percent

Change the index percent

Change the lock mode

Run in Multiple or Single User Mode

How the DBSPACE Reorganization Tool Works

When you choose full DBSPACE reorganization, Control Center generates and

submits a job to:

1. Link and establish communication with the target server.

2. Connect as user SQLREORG.

3. Verify the availability of the new DBSPACE (if specified).

4. Gather system catalog information about the specified DBSPACE and create

corresponding DDL statements in the Control Center Database Services Utility

(DBSU) command file:

a. Table create statements

b. Table comments

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool

c. Column comments

d. Table reload statements

e. Referential integrity constraints

f. Unique column definitions

g. Index create statements

h. Table column grants

i. Table grants

j. View creates/grants/comments/labels

k. Package rebind statements

5. Unload the DBSPACE data to the specified disk or tape.

6. Execute the SQLDBSU command file from the Database Services Utility to

reorganize the DBSPACE and rebind any dependent packages.

7. Update the SQLMAINT table with the date, time, and duration of the

reorganization (see Chapter 5, “DBSPACE Analysis Tools” on page 27).

In order to retain hierarchical dependencies, Control Center issues all grants in the

same chronological order in which they were originally issued.

In order to grant authority to an object, the grantor must first connect as the user

who originally issued the grant. Therefore, the program must gather database

connect passwords for all grantors. If a grantor does not have a connect password,

a temporary password is assigned and later removed.

The database server does not remove grant information from the system catalogs

when a user is removed from the SYSTEM.SYSUSERAUTH table. Consequently,

the REORG job may need to connect as a nonexistent user in order to re-establish a

grant. If this situation occurs, Control Center temporarily grants connect authority

to the user and later revokes it.

Operational Note: In some cases (such as a reload failure), temporarily granted

IDs will not be revoked from the database. You should revoke

these IDs at some point in time. The IDs are identified by the

starting letters REOnnnnn (where nnnnn is a random number).

Using the DBSPACE Reorganization Utility Screen

To display the DBSPACE Reorganization Utility menu shown in Figure 5 on

page 16, choose Option 2 on the Control Center Main Menu or enter the

transaction ID SQDR on a CICS screen.

Chapter 4. DBSPACE Reorganization Tool

DBSPACE Reorganization Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*----------------

DBSPACE REORGANIZATION UTILITY

---------------*

| DATABASE

=> SQLDBA____________

| OWNER

=> ________

| DBSPACE

=> __________________

| FILE

=> 1 (1-3)

| OPTION

=> 3 (1=GENERATE DDL

3=REORGANIZE DBSPACE

) |

(2=UNLOAD DBSPACE

4=RELOAD DBSPACE

) |

| ***********************

OPTIONAL PARAMETERS

*********************** |

| DATABASE => __________________

| OWNER

=> ________

| DBSPACE

=> __________________

| PAGES

=> ____________ NHEADER

=> _ (1-8)

STORPOOL => ___

| PCTFREE

=> __

ALTER PCTFREE

=> __

PCTINDEX => __

| LOCK

=> _______

| REBIND PACKAGE => 1 (1=YES/2=NO)

UPDATE ALL STATISTICS => 2 (1/2) |

| COMMITCOUNT

=> __________

| TLBL FILE-ID

=> _________________

DDL STATEMENTS => 1000__

*------------------------------------------------------------------ SQC05 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT

Figure 5. DBSPACE Reorganization Utility Screen

You must enter the first three fields (DATABASE, OWNER, DBSPACE name) to

identify the DBSPACE that you want to reorganize. The database specified must be

either the default CICS region database or one to which the program may

CONNECT. The OWNER and DBSPACE name parameters must identify a valid

DBSPACE in the target database.

When you installed Control Center, you defined three SAM DDL files to hold

extracted DDL. Type the number of the file (1=small, 2=medium, 3=large) you

want to use in the FILE field.) The FILE number determines what SAM data file to

use if you do not enter a Tape File Name. You do not need to specify the file

number if you choose Option 1, 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. You can

choose to:

Option

Description

1 GENERATE DDL

This option extracts from the database all of the

DDL required to re-create a DBSPACE and the

objects it contains. The DDL is saved in the punch

queue for inspection, alteration, or backup.

2 UNLOAD DBSPACE

This option extracts all DDL (as in Option 1) and

writes it to a VSAM file. Then, a DBSU UNLOAD

DBSPACE step is executed that writes the

DBSPACE data to a SAM or tape file. If SAM is

selected, the file is REPRO’d to a VSAM file for

more permanent retention. The unloaded data and

extracted DDL can be used as the basis for a

RELOAD DBSPACE (Option 4) job. An example of

an UNLOAD DBSPACE job created to do this is

shown in Figure 56 on page 105.

3 REORGANIZE DBSPACE This option results in a full DBSPACE

reorganization. A jobstream is created that captures

the DDL, unloads the DBSPACE, drops, acquires,

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool

recreates, and reloads the DBSPACE. Error recovery

logic is also included. An example of a

REORGANIZE DBSPACE job is shown in Figure 57

on page 107.

4 RELOAD DBSPACE

This option submits a job to recreate and reload a

DBSPACE that has been unloaded by Option 2.

This is basically a DBSPACE recovery facility. An

example of the job created to do this is shown in

Figure 58 on page 110.

Each of the options is discussed in more detail below and is accompanied by a

sample JCL stream created by the DBSPACE Reorganization tool.

Optional Parameters

All parameters below the ″Optional Parameters″ line on the screen do not require

entry.

Parameter

Description

DATABASE

Reloads the DBSPACE to a different database. Lets

you migrate a DBSPACE from one database to

another. For example, you can migrate a DBSPACE

from a development database to a production

database. Before you migrate the DBSPACE, you

may want to ensure that the two databases are

compatible so that all reload statements execute

successfully. When you use the DATABASE

parameter, the DBSPACE in the old database

remains unchanged.

OWNER/DBSPACE

Specifies a new owner and/or a new

DBSPACEname for the reloaded DBSPACE. When

the source DBSPACE and the target DBSPACE are

both PRIVATE, the old DBSPACE remains

unchanged. If either or both DBSPACEs are

PUBLIC, the old DBSPACE is dropped prior to

creation of the new DBSPACE.

PAGES

Defines a new DBSPACE page size for the

reorganized DBSPACE. An empty (unacquired)

DBSPACE of the indicated number of pages must

be available in the database. If PAGES is not

specified, a DBSPACE equal in size to the current

DBSPACE is acquired.

NHEADER

Specifies the number of pages in a DBSPACE

reserved for DBSPACE header information. The

value entered must be a number between 1 and 8.

If the number chosen is smaller than what is

required for all header information the reload may

fail. If you subscribe to the standard of one table

per DBSPACE, one header page is sufficient.

STORPOOL

Specifies a new storage pool for the acquired

DBSPACE. This allows you to balance database

I/O by spreading the most actively used

DBSPACEs over multiple DASD volumes.

Chapter 4. DBSPACE Reorganization Tool

DBSPACE Reorganization Tool

PCTFREE

Indicates the percentage of each DBSPACE page to

be reserved for INSERTS or UPDATES that increase

a table’s row length. PCTFREE defaults to 10

percent. After the data is reloaded into the

DBSPACE, PCTFREE can be altered to zero to

make the freespace available.

ALTER PCTFREE

Indicates the value to which PCTFREE is to be

altered, once the data has been reloaded into the

DBSPACE. This value must be lower than the

PCTFREE parameter value to have any positive

effect.

PCTINDEX

Specifies the ratio of index pages to total DBSPACE

pages. Use this parameter to maintain a balance

between the number of occupied data and index

pages. If not specified, the same ratio as the

original DBSPACE will be used.

LOCK

Changes the lock mode of a DBSPACE. Valid

values for PUBLIC DBSPACEs are DBSPACE,

PAGE, and ROW. Private DBSPACEs are always

locked at the DBSPACE level.

REBIND PACKAGE

Once a DBSPACE has been reloaded, DBSPACE

Reorganization rebinds all access modules that are

dependent on objects in the DBSPACE. To bypass

package rebind processing, specify NO (2). The

default value is YES (1).

UPDATE ALL STATISTICS

By default, UPDATE STATISTICS is issued for a

DBSPACE once it has been successfully reloaded.

UPDATE STATISTICS updates catalog statistics

only for columns that appear as the first column in

an index. To update catalog statistics for all

columns, specify YES (1) for the UPDATE ALL

STATISTICS parameter.

COMMITCOUNT

Used to specify the frequency of COMMITS during

reload processing. Enter a number in the range 1

through 2,147,483,647 (without the commas) to

cause a COMMIT WORK to be executed after that

number of input rows has been reloaded.

TLBL FILE-ID

Used to specify that data should be unloaded to

tape instead of disk. The tape file must have been

defined using the WORK FILE LABEL

DEFINITION tool. This does not apply to DDL;

DDL is ALWAYS unloaded to disk.

DDL STATEMENTS

Allows handling DBSPACEs that contain an

unusually large amount of DDL (lots of tables,

indexes, views, comments). This parameter defaults

to 1,000 records; that should be sufficient to handle

the vast majority of DBSPACEs.

After entering the desired REORG parameters, press ENTER to proceed to the

DBSPACE Reorganization Submit screen.

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool

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:

v Relieves DBAs from having to maintain large libraries of DDL

v Saves library disk space

v Solves the problem of who owns the ″official″ DDL

v Provides an easy way to determine table and index characteristics

v Provides authorization and ″where-used″ information

Figure 6 is an example of the jobstream produced by Control Center to generate

DDL for the PUBLIC.SAMPLE DBSPACE.

* $$ 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.SQLDS730.PUBLIC.SAMPLE’,0,VSAM,

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 SQLDBA SQLDBAPW

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

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 56 on page 105 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” on page 127 for information about the file.

Chapter 4. DBSPACE Reorganization Tool

DBSPACE Reorganization Tool

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 57 on page 107 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 58 on page 110 shows a sample jobstream that was generated by Control

Center to reload the PUBLIC.SQMHELP DBSPACE.

DBSPACE Reorganization Submit Screen

Figure 7 shows the DBSPACE Reorganization Submit screen.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*------------------------

DBSPACE REORG SUBMIT

-------------------------*

| *******************

VSE/POWER JOB PARAMETERS

****************** |

| JOBNAME

=> ________ CLASS ===> A PRI => 3

DISP ====> D

(D,H,L,K)

| FROM

=> ________ DUETIME => ____ (HHMM)

DUEDATE => ______ (AABBYY) |

| DUEDAY

=> __________________________________________ (DAY NAMES/NUMBERS) |

| OTHER

=> ______________________________________________________________ |

| ----------------

SINGLE USER MODE PARAMETERS

----------------- |

| SUM?

=> 2 (1=YES/2=NO)

DATABASE DEFINITION PROC => ________

| LOGMODE => _ (L,A,Y,N)

NDIRBUF => ______

NPAGBUF => ______

*------------------------------------------------------------------ SQC06 ----*

PRESS ENTER TO SUBMIT

ENTER F1=HELP F3=EXIT F12=CANCEL

Figure 7. DBSPACE REORGANIZATION SUBMIT screen

To reach this screen, press ENTER from the Control Center DBSPACE

REORGANIZATION screen.

The first parameter, JOBNAME, is the only one that is required.

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool

VSE/POWER Parameters

Parameter

Description

JOBNAME

Specifies the name by which the DBSPACE

REORGANIZATION job and its associated

VSE/POWER queue entries is to be known.

CLASS

Specifies the class or partition in which you want

this job to run. Class defaults to A.

PRI

Specifies the priority that is to be assigned to the

job. Specify a number from 0 to 9 where 9 is the

highest priority. Default priority is 3.

DISP

Specifies how the job is to be handled in the reader

queue. Disposition may be specified as:

v D - Delete after processing

v H - Hold until released

v K - Keep after processing

v L - Leave in the queue

Disposition defaults to D.

FROM

Specifies the ID of the user being allowed to

manipulate or retrieve the job. Defaults to the CICS

user ID.

DUETIME

Specifies the processing start time using hh for

hour and mm for minute in 24-hour clock time

(OPTIONAL). This can only be 0001 through 2359.

DUEDATE

Specifies the processing date using YY for year.

Depending on the format defined for your system,

AA is month and BB is day, or AA is day and BB is

month (OPTIONAL). No check is made for invalid

leap days nor passed dates.

DUEDAY

Specifies the day(s) the job is to be scheduled. You

may enter a day name abbreviation such as MON

for Monday, or a list separated by commas and

enclosed in single quotes (apostrophes). You may

also enter the day of the month or a list of day

numbers separated by commas and enclosed in

quotes. You may also specify DAILY to schedule

the job every day of the year (OPTIONAL).

OTHER

The VSE/POWER * $$ JOB card offers many

parameters that do not appear on the DBSPACE

REORGANIZATION SUBMIT screen. Use this field

to have Control Center include those parameters

when the job is submitted (OPTIONAL).

After entering the desired submit parameters, press ENTER to submit the job to

VSE/POWER. For more information on VSE/POWER jobs, refer to the

VSE/POWER Installation and Operations Guide.

Figure 8 on page 22 is a sample of the DBREORG Report.

Chapter 4. DBSPACE Reorganization Tool

DBSPACE Reorganization Tool

SQB02

CONTROL CENTER FOR VSE

hh:mm:ss

DBSPACE REORGANIZATION REPORT

mm/dd/yyyy

DBSPACE 1

DBSPACE 2

------------------

DATABASE:

SQLDBA

OWNER:

PUBLIC

DBSPACENAME:

SQMHELP

BEFORE REORG STATISTICS

AFTER REORG STATISTICS

-----------------------------------

DBSPACENO:

POOL:

NPAGES:

128

NRHEADER:

PCTINDX:

FREEPCT:

LOCKMODE:

PAGE

NACTIVE:

NTABS:

ELAPSED TIMES IN MINUTES

------------------------------------

UNLOAD DBSPACE:

00:00:07

RELOAD DBSPACE:

00:00:08

TOTAL ELAPSED TIME:

00:00:15

SQLMAINT TABLE HAS BEEN SUCCESSFULLY UPDATED.

Figure 8. DBSPACE Reorganization Report

Single User Mode (SUM) DBSPACE Reorganization

You can choose to run a DBSPACE reorganization in Multiple User Mode (MUM)

or in Single User Mode (SUM). In SUM, contention with other applications and

users is eliminated. Storage used to support those users can be used to define

additional directory or page buffers, resulting in better performance.

In SUM, you can bypass logging by specifying LOGMODE N. However, switching

to logmode N will probably require an archive and a coldlog before the switch and

another archive before switching back.

Before You Choose Single User Mode Execution

Review the DB2 Server for VSE & VM Database Administration manual to

understand Single User Mode database execution. Also, review the topics on

choosing a logmode and switching logmodes. Control Center Single User Mode

parameters are listed below:

Single User Mode Parameters

Parameter

Description

SUM?

Specify ″1″ (YES) to cause a Single User Mode job

to be submitted. This parameter defaults to ″2″

(Multiple User Mode).

DATABASE DEFINITION PROC

Specifies the name of the procedure that contains

the job control statements (DLBLs) required to

access the database. This parameter need not be

entered if the job control statements have been

loaded into standard labels.

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool

LOGMODE

Specifies the logmode you want Control Center to

use during Single User Mode processing. You must

enter a value. Valid values are:

v A - All database changes are logged and regular

database archives are maintained.

v L - All database changes are logged and regular

log archives are maintained.

v N - No database changes are logged.

v Y - All database changes are logged but no

archives are maintained.

NDIRBUF

The number of 512-byte directory pages to be kept

in storage. The bigger this value is, the better your

database will perform until you run out of storage

or cause excessive paging. If specified, the value

must be in the range 10—400000. This parameter is

not required.

NPAGBUF

The number of 4096-byte data pages to be kept in

storage. Again, bigger is better, within reason. If

specified, the value must be in the range

10—400000. Entry of this parameter is not required.

SUM processing requires that the database be ended prior to execution. If you

submit a SUM job for immediate execution, it will fail. This is because the database

server must be stopped when a SUM job is run but has to be up when you run the

Control Center function that creates the job. So, you must delay the execution of

the job from the reader manually or by using the scheduling controls of DUETIME,

DUEDATE, or DUEDAY.

When a job step requires access to a database, the database is started and the

application program is executed. When the application program ends, control

returns to the database server and the database is ended. The database remains

down until it is restarted. Remember that changing the logmode will probably

force some combination of coldlogs, and log or database archives.

Figure 59 on page 112 is an example of a Single User Mode REORGANIZE

DBSPACE (Option 3).These parameters were used when choosing the DBspace

Reorganization utility’s option 3 (REORGANIZE DBSPACE): owner = PUBLIC,

DBspace = SAMPLE, REBIND PACKAGE = 1 (YES), UPDATE ALL STATISTICS =

(YES), DDL STATEMENTS = 500. On the submit screen, these options were used:

SUM = 1 (YES), LOGMODE = N, NDIRBUF = 10, NPAGBUF = 10.

DBSPACE Reorganization Tape Support

Unloading to Tape

When you specify a TLBL FILE-ID on the DBSPACE REORGANIZATION UTILITY

screen, tape is used as the data unload media. As a result, the jobstream that

Control Center builds and submits is quite different. Figure 60 on page 116 is an

example of a REORGANIZE DBSPACE (Option 3) using tape.

Chapter 4. DBSPACE Reorganization Tool

DBSPACE Reorganization Tool

Special Considerations

Repetitive Scheduling

If a DBSPACE reorganization job is scheduled to be run on a repetitive basis (such

as each week on Thursday night), be aware that an SQMPARM file record is

created when the REORG job is scheduled. This record contains parameters used

by the REORG process. The same record will be used each time the DBSPACE is

reorganized. If an intervening REORG job for the same DBSPACE is scheduled

from Control Center, a new SQMPARM record will be generated based upon the

parameters chosen at that time. These may be different from the ones previously

chosen for the scheduled job. This means that the new SQMPARM record will be

used for all subsequent executions of the scheduled job. If this is not what you

want, delete the scheduled job from the VSE/POWER reader queue and schedule a

new one.

Failure Restart

The job listing from your Control Center jobs will indicate whether the job ended

successfully. Return code checking and conditional JCL are used to support failure

restart. If a DBSPACE reorganization fails prior to the reload step, the DBSPACE

has not been changed and the job can be restarted from the beginning. If the

failure occurs during the reload step, the function can be restarted using RELOAD

DBSPACE (Option 4).

In all cases, view the output job listing to determine the cause of the error and

whether it requires fixing. In many cases, minor errors occur but the job is able to

complete successfully.

Problem Analysis

During DDL generation, SQL statements are used to capture information from the

database manager system catalogs. If a serious database error is encountered, a

descriptive error message and all pertinent information from the SQL

Communication Area is displayed on the job listing.

The DBSPACE REORGANIZATION tools use a DBSU command file to execute the

UNLOAD DBSPACE portion of the job. Detailed output from the UNLOAD

portion is displayed on the job listing. Examine the listing to determine the reason

for failure.

During RELOAD processing, DBSPACE REORGANIZATION jobs invoke a DBSU

RELOAD. Detailed output of this process is displayed in the job listing. If a failure

occurs during the RELOAD, the listing can be examined to determine the cause of

failure.

One common problem to be aware of is a possible LOG FULL condition that may

occur during RELOAD processing. The DBSU RELOAD TABLE command executes

as a single LUW, meaning that the entire RELOAD could be rolled back if an error

occurs. The database server would then have to record the LUW in the LOG. If the

target table is large, or the database LOG file was nearly full when the reload

began, the possibility of a LOG FULL condition exists. Depending on logmode, the

database server will attempt to perform a database archive, a log archive, or a

checkpoint in the LOG. If the RELOAD process continues until the LOG is

completely full, the database server will begin to ROLLBACK the entire RELOAD.

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool

Since the DROP DBSPACE has already been COMMITTED, the target DBSPACE

will be in an incomplete state if this occurs. There are several possible solutions to

this problem.

v If the RELOAD failed because the LOG was nearly full prior to the reload, you

could perform a database archive, a log archive, or a coldlog (depending on

whether you are using logmode A, L, or Y respectively). After this completes,

you can complete the reload by initiating a RELOAD DBSPACE (Option 4).

v If the RELOAD LUW exceeds the LOG size, even when empty, you have two

options:

1. Increase the size of the LOG file, then complete the reorganization.

2. Run the RELOAD in SUM with logmode N (no logging).

Chapter 4. DBSPACE Reorganization Tool

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 12