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

Chapter 5. DBSPACE Analysis Tools

About the DBSPACE Analysis Tools

When an application or user requests information from a database or server, the

OPTIMIZER uses catalog statistics to choose the most efficient access path to the

data. These statistics are not automatically updated every time a row is inserted,

updated, or deleted because of the overhead that would be involved. However, the

more accurate the statistics, the better able the optimizer is to choose an efficient

method of retrieving data, thereby improving overall database performance. Every

DBA needs to ensure that statistics are updated on a regular basis to maximize

database performance.

Another common database problem that impacts performance is the gradual

fragmentation and disorganization of data over time. After many inserts, updates,

and deletes, the data in a database becomes fragmented - spread out over many

physical pages, with many gaps and with a physical sequence much different than

the logical sequence of data. Reorganization is required to reload the data into a

minimum number of physical pages in a physical sequence that optimizes logical

data retrieval.

The DBSPACE Analysis tools help you to analyze DBSPACEs and perform

maintenance on them to improve performance. These tools are designed to allow

you to specify all DBSPACEs or a subset of the DBSPACEs in the database. You

can choose what criteria are to be used to identify candidates for maintenance.

There are two basic DBSPACE Analysis tools: one for Update Statistics analysis

and another for DBSPACE Reorganization analysis.

Before You Begin

You can analyze DBSPACEs at any time since minimal calls are made to the

database catalog. Based on the results of the analysis, the actual maintenance jobs

can then be scheduled for off-hours, or as needed.

Whenever possible, schedule the actual maintenance during non-peak hours to

prevent locking contention with other database users. Extensive updating of the

database system catalogs occurs during UPDATE STATISTICS and DBSPACE

reorganizations. During periods of high database usage, this can lead to lock

contention.

How the DBSPACE Analysis Tools Work

The DBSPACE Analysis tools help keep DBSPACEs tuned by keeping track of the

UPDATE STATISTICS and reorganization activities at the DBSPACE level and by

executing these functions where and when required based on specified execution

parameters. This data is stored in a database table (SQLMSTR.SQLMAINT) that is

created during Control Center installation.

When you select Option 2 from the DBSPACE Analysis Utilties screen (Figure 10 on

page 30) to list UPDATE STATISTICS candidates or Option 3 to list REORG

candidates, a detailed analysis of each DBSPACE is conducted to determine the

DBSPACE Analysis Tools

degree of need for maintenance. The results of this analysis are displayed. The

DBA can then choose what DBSPACEs to reorganize or to run UPDATE

STATISTICS against.

The DBSPACE Analysis tool uses a database table (SQLMSTR.SQLMAINT) to

maintain information about DBSPACEs in the database. During execution, each

DBSPACE is considered for maintenance based on parameters you enter. After

execution, the information in the SQLMAINT table is updated to reflect the

changes that have occurred.

When you invoke the DBSPACE Analysis tool, it:

1. Displays an analysis selection screen.

2. Optionally refreshes the SQLMAINT table with data from

SYSTEM.SYSDBSPACES.

3. Selects DBSPACEs that match the selection parameters for UPDATE

STATISTICS or reorganization.

4. Displays a list of candidates chosen for maintenance.

5. Optionally submits maintenance jobs for the candidates you select.

6. Updates the SQLMAINT table for each DBSPACE that receives maintenance.

The selection process consists of one step for UPDATE STATISTICS analysis and

two steps for the reorganization analysis. The steps are:

1. Select DBSPACES from the SQLMAINT table using a first set of selection

parameters such as name, size, and time. (See “Selection Options” on page 31).

2. For reorganization, applies REORG CRITERIA to the list selected in step 1

above to produce a list of candidates that need reorganization. (See “DBSPACE

Reorganization Criteria (CRITERIA)” on page 31).

SQLMAINT Table

The Control Center installation process creates SQLMAINT in a public DBSPACE

in the target database as shown in Figure 9.

CREATE TABLE "SQLMSTR"."SQLMAINT"

( "OWNER"

CHAR(8),

"DBSPACENAME"

CHAR(18),

"DBSPACENO"

SMALLINT,

"FREEPCT"

SMALLINT,

"PCTINDX"

SMALLINT,

"UPSTAT_DATE"

DATE,

"UPSTAT_TIME"

TIME,

"UPSTAT_ELAPSED" TIME,

"REORG_DATE"

DATE,

"REORG_TIME"

TIME,

"REORG_ELAPSED"

TIME,

"REORG_FREEPCT"

SMALLINT,

"REORG_PCTINDX"

SMALLINT,

"REORG_STATUS"

CHAR(2),

"REORG_WEIGHT"

SMALLINT,

"NPAGES"

INTEGER )

IN "PUBLIC"."SQLMAINT";

Figure 9. SQLMAINT Table Definition

Control Center Operations Guide for VSE

DBSPACE Analysis Tools

SQLMAINT can be used as a basis for creating your own maintenance reports. You

can also update it manually to further control the maintenance process (such as

changing the reorganization date to prevent a large DBSPACE from being selected

as a candidate for reorganization).

The columns that make up SQLMSTR.SQLMAINT are:

Column Name

Description

OWNER

Owner of the DBSPACE (from

SYSTEM.SYSDBSPACES)

DBSPACENAME

Name of the DBSPACE (from

SYSTEM.SYSDBSPACES)

DBSPACENO

DBSPACE number (from SYSTEM.SYSDBSPACES)

FREEPCT

FREEPCT value (from SYSTEM.SYSDBSPACES)

PCTINDX

PCTINDX value (from SYSTEM.SYSDBSPACES)

UPSTAT_DATE

Date of the most recent UPDATE STATISTICS

UPSTAT_TIME

Time of the most recent UPDATE STATISTICS

UPSTAT_ELAPSED

Elapsed time of the most recent UPDATE

STATISTICS job for this DBSPACE

REORG_DATE

Date of the most recent reorganization

REORG_TIME

Time of the most recent reorganization

REORG_ELAPSED

Elapsed time of the most recent reorganization job

REORG_FREEPCT

FREEPCT value in the most recent reorganization

REORG_PCTINDX

PCTINDX value in the most recent reorganization

REORG_STATUS

A two-character reorganization status indicator

REORG_WEIGHT

An integer indicating the relative need for

reorganization. This is derived using a formula

based on other information.

NPAGES

Size of the DBSPACE

Function 1 of the DBSPACE Analysis Tool (Initialize Control Table) inserts rows for

all private and non-system owned public DBSPACEs into the SQLMAINT table. All

DATE columns are initialized to ″0001-01-01″. All TIME columns are set to

″00:00:00″. The table is then ready for use with the other DBSPACE Analysis tool

functions.

DBSPACE Analysis Utility Screen

Figure 10 on page 30 shows the DBSPACE Analysis Utilities screen.

Chapter 5. DBSPACE Analysis Tools

DBSPACE Analysis Tools

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*----------------------- DBSPACE ANALYSIS UTILITIES -------------------------*

| DATABASE => SQLDBA

| OPTION ===> 3

REFRESH

=> 1 (1=YES 2=NO)

| 1=INITIALIZE CONTROL TABLE

2=UPDATE STATISTICS CANDIDATES

| 3=LIST REORG CANDIDATES

| **************************** SELECTION OPTIONS

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

| OWNER

===> ALL

DBSPACENAME

=> ___________________ |

| MIN(PAGES)

==> 0

MAX(PAGES)

=> 9999999

| DAYS SINCE ======> 14

| CRITERIA

==> 1

| 1=CLUSTERRATIO < 9999

2=UNCLUSTERED INDEX

| 3=NOVERFLOW

1 %

4=APPLY ALL CRITERIA

*------------------------------------------------------------------ SQC08-----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT

Figure 10. DBSPACE Analysis Utilities Screen

Most fields are initialized to some value. The first two parameters (DATABASE

and OPTION) are required. They identify the function you want to perform and

what database the function will be performed upon. These fields are initialized to

Option 3 (List REORG Candidates) and the database to which Control Center is

currently connected.

Functions

The option field can have the following values:

Function

Description

1 INITIALIZE CONTROL TABLE

Rebuilds the SQLMAINT control table in real time;

not as a scheduled job. All previous maintenance

data will be erased and a row for each non-system

owned DBSPACE will be inserted into the table.

2 UPDATE STATISTICS CANDIDATES

Lists the DBSPACEs that need their statistics

updated. The candidate list is displayed based

upon the other selection options chosen.

3 LIST REORG CANDIDATES

This function lists candidate DBSPACEs that

require reorganization based on the specified

criteria. It lists each candidate and advises the DBA

of the need for reorganization. This is the default

option when the screen is first displayed.

The REFRESH parameter (defaults to YES) updates the SQLMAINT table:

1. Adding rows for any DBSPACEs that are not in the SQLMAINT table.

2. Deleting rows for any DBSPACEs that are not in SYSTEM.SYSDBSPACES.

This is done in real time PRIOR to performing the analysis selected. Other

DBSPACE entries are not affected and their maintenance data remains intact.

Control Center Operations Guide for VSE

DBSPACE Analysis Tools

Selection Options

All parameters below the line labeled ″SELECTION OPTIONS″ allow you to

control the maintenance activity by restricting the number and type of DBSPACEs

that will be selected for analysis. In the case of the UPDATE STATISTICS tool, the

selected DBSPACEs will all be candidates. In the REORG tool, candidates retrieved

from the SQLMAINT table then have the reorganization CRITERIA applied against

them and a final status is presented that states whether the DBSPACE is a REORG

candidate.

Parameter

Description

OWNER

Is used to specify whether PUBLIC, PRIVATE, ALL,

or specific DBSPACE owner(s) will be selected for

analysis. The DB2 Server for VSE wildcard

character (%) can be used at the beginning and/or

end of a specified DBSPACE OWNER to select

″like″ DBSPACE OWNER names.

DBSPACENAME

Is used to specify a single DBSPACE name or a

group of similar DBSPACE names that should be

selected for analysis. The DB2 Server for VSE

wildcard character (%) can be used at the

beginning and/or end of the specified DBSPACE

name to select ″like″ DBSPACE names. This

parameter defaults to choosing all DBSPACEs

owned by the specified DBSPACE OWNER.

PAGES

Is used to specify the MINIMUM and MAXIMUM

DBSPACE sizes (expressed in pages) to be selected

for analysis.

DAYS SINCE

Specifies the number of days that must have

passed since the last UPDATE STATISTICS or

reorganization before the DBSPACE can be

considered for candidate analysis. 1 indicates

yesterday.

DBSPACE Reorganization Criteria (CRITERIA)

This parameter applies an additional test to selected DBSPACEs to determine

whether they are candidates for reorganization. After the selection options above

have been used to select DBSPACE names from the SQLMAINT table, the

reorganization CRITERIA chosen are checked against those DBSPACEs and the

results are displayed on a list screen (see Figure 12 on page 34). Those DBSPACEs

meeting the criteria should be considered candidates for reorganization. You can

select them from the REORG CANDIDATES LIST screen for job submission and

scheduling.

You can select one of four different methods of analyzing the need for

reorganization in the list of retrieved DBSPACEs. They are:

Criteria

Description

1 CLUSTERRATIO < nnnn If the cluster ratio of any index in a DBSPACE is

less than the stated value, the DBSPACE is

considered a candidate for reorganization,

regardless of the value of the CLUSTER field. You

may specify a value from 1 to 9999.

Chapter 5. DBSPACE Analysis Tools

DBSPACE Analysis Tools

2 UNCLUSTERED INDEX If there are any unclustered indexes in the tables in

the DBSPACE, the DBSPACE is identified as a

candidate.

3 NOVERFLOW ROWS > n%

If the number of overflow rows for any of the

tables in the DBSPACE exceeds the value you

entered, the DBSPACE is selected as a candidate.

4 APPLY ALL CRITERIA

If any of the above three criteria apply, the

DBSPACE is chosen as a candidate for

reorganization.

CLUSTER RATIO < 9999 is the default criterion.

Update Statistics Analysis Tool

The Update Statistics Analysis tool can be selected from the DBSPACE ANALYSIS

UTILITIES screen by choosing Option 2 (Update Statistics Candidates).

The purpose of this tool is to list the DBSPACEs that need to have their statistics

updated and to optionally submit batch jobs to update them.

An example of the list generated by the Update Statistics Analysis Tool is shown in

Figure 11. The list was created by specifying:

OPTION ===> 2

OWNER ===> PUBLIC

PAGES: MINIMUM ==> 0

MAXIMUM ========> 9999999

DAYS SINCE ======> 4

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*------------------------ UPDATE STATISTICS LIST --------------------------*

| DATABASE => SQLDBA

| OWNER

DBSPACENAME

LAST DATE ELAPSED NPAGES

| _ PUBLIC ANDY

1996-11-19

00:01:00

128

| _ PUBLIC CONNIE

1996-11-18

00:02:30

128

| _ PUBLIC FRED

1996-10-30

00:01:05

128

| _ PUBLIC MARTIN

1996-11-19

00:00:50

128

| _ PUBLIC SMITH

1996-11-17

00:03:14

256

| _ PUBLIC RAY

1996-11-17

00:02:45

512

| _ PUBLIC TAYLOR

1996-11-18

00:01:30

512

| _ PUBLIC SQLMSTR

1996-11-18

00:00:45

128

| _ PUBLIC SQMHELP

1996-10-29

00:01:20

256

*----------------------------------------------------------------- SQC09 ----*

USE ’X’ TO SELECT DBSPACE(S); PRESS ENTER TO SUBMIT

ENTER F1=HELP F3=EXIT F12=CANCEL

Figure 11. Update Statistics Analysis List Screen

Note that on this screen the following are displayed:

Control Center Operations Guide for VSE

DBSPACE Analysis Tools

1. The DBSPACE OWNER and NAME.

2. The DATE of the last Update Statistics for this DBSPACE.

3. The ELAPSED time (in hours:minutes:seconds) of the most recent Update

Statistics job for this DBSPACE.

4. The size of the DBSPACE.

From this screen, you can enter an ″X″ next to the DBSPACE(s) for which an

UPDATE STATISTICS job is desired. Then, by pressing ENTER, the DBSPACE

ANALYSIS SUBMIT screen is displayed and a job can be scheduled for execution.

When multiple DBSPACEs are selected, multiple jobs are submitted, each having

the same job name and parameters. (See “DBSPACE Analysis Submit Screen” on

page 35).

Pressing F12 will take you back to the previous screen (DBSPACE Analysis

Utilities). Pressing F3 will take you back to the Control Center main menu.

DBSPACE Reorganization Analysis Tool

This function lists the DBSPACEs that need to be reorganized. The selection

process is conducted by evaluating information in the database catalogs and

comparing it to the criteria selected by you.

There are three criteria that are evaluated by the program, any of which may cause

a DBSPACE to be selected for reorganization. You can choose any one or all three

to be used by the Analyzer tool:

1. The existence of unclustered primary indexes on tables within the DBSPACE.

This will typically occur when many inserts and deletes have been performed.

It greatly reduces the performance of the database.

2. Overflow pages greater than n% for any table in the DBSPACE. This condition

indicates a great deal of fragmentation, caused by updates that have increased

the row length. The number of I/O operations required to return selected rows

is greatly increased.

3. A CLUSTER RATIO value below the threshold specified by the user.

4. All of the criteria above.

An example of the list generated by the Reorganization Analysis Tool is shown in

Figure 12 on page 34. The list was created by specifying:

OPTION ===> 3

OWNER ===> PRIVATE

PAGES: MINIMUM ==>

MAXIMUM =========> 8192

DAYS SINCE ======> 14

REORG CRITERIA ==> 4

Chapter 5. DBSPACE Analysis Tools

DBSPACE Analysis Tools

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*--------------------------- REORG CANDIDATES LIST ---------------------------*

| DATABASE => SQLDBA

| OWNER

DBSPACENAME

LAST DATE ELAPSED NPAGES STATUS

| _ ANDY

TAYLOR

0001-01-01

00:00:00

128

| _ CONNIE

SMITH

1996-11-18

00:01:35

128 NO REORG NEEDED

| _ FRED

TAYLOR

1996-11-10

00:01:38

128 OVERFLOW ROWS

| _ MARTIN

MARTIN

1996-11-17

00:02:40

128 NO REORG NEEDED

| _ MASALI

MASALI

1996-11-17

00:03:00

5120 CLUSTER RATIO

| _ RAY

MARTIN

1996-11-17

00:02:15

1024 UNCLUSTERED

| _ TAYLORA

TAYLOR

1996-11-12

00:01:50

128 NO REORG NEEDED

*------------------------------------------------------------------ SQC09 ----*

USE ’X’ TO SELECT DBSPACE(S); PRESS ENTER TO SUBMIT

ENTER F1=HELP F3=EXIT F12=CANCEL

Figure 12. DBSPACE Reorganization Analysis List Screen

Figure 12 is an example of the Reorganization Analysis List created by the tool.

Note that on this screen the following are displayed:

v the DBSPACE OWNER and NAME.

v the DATE of the last reorganization run for the DBSPACE.

v the ELAPSED time (in hours:minutes:seconds) of the most recent reorganization

job for this DBSPACE.

v the size of the DBSPACE.

v the result of the analysis.

From this screen, you can place an ″X″ next to the DBSPACE(s) for which

reorganization is desired. Then, by pressing ENTER, the DBSPACE ANALYSIS

SUBMIT screen is displayed and a reorganization job can be scheduled for

execution. Only one DBSPACE is reorganized in a single batch job. (See Figure 7 on

page 20 for details concerning the job submission screen).

If more than one DBSPACE is selected, the SUBMIT screen is displayed for the first

DBSPACE selected. Once a job is submitted, the LIST screen is re-displayed, with

that DBSPACE identified with an asterisk ″*″ in the select field. If ENTER is

pressed again, the SUBMIT screen is displayed for the next DBSPACE with an ″X″

in the select field. This process is repeated until a reorganization job has been

submitted for every DBSPACE selected (unless, of course, F3, F12, or a scroll

function key is chosen by you).

In the sample display, seven DBSPACEs were analyzed and three were selected for

reorganization. Four DBSPACEs were discounted because they did not meet the

criteria selected. Note that one DBSPACE has never been reorganized. Its date and

elapsed time fields have initial values. ANDY.TAYLOR is a new entry in the

SQLMAINT table.

By pressing F12, you are returned to the previous screen (DBSPACE Analysis

Utilities). F3 returns you to the main menu.

Control Center Operations Guide for VSE

DBSPACE Analysis Tools

DBSPACE Analysis Submit Screen

Figure 13 shows the DBSPACE Analysis SUBMIT screen.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*-------------------------- DBSPACE ANALYSIS SUBMIT --------------------------*

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

VSE/POWER JOB PARAMETERS

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

| JOBNAME

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

DISP ====> D

(D,H,L,K)

| FROM

=> VS02

DUETIME => ____ (HHMM)

DUEDATE => ______ (AABBYY) |

| DUEDAY

=> __________________________________________

| OTHER

=> ______________________________________________________________ |

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

JOB OPTIONS

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

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

REBIND PACKAGES => 1 (1/2) |

| COMMITCOUNT

=> __________

| TLBL FILE-ID

=> _________________

FILE #

=> 2 (1-3) |

*------------------------------------------------------------------ SQC10 ----*

PRESS ENTER TO SUBMIT

ENTER F1=HELP F3=EXIT F12=CANCEL

Figure 13. DBSPACE Analysis Submit Screen

Refer to the IBM VSE/POWER Installation and Operations Guide for use of the job

submit parameters. The parameters in the JOB OPTIONS section are for use by the

individual Update Statistics or reorganization jobs. These are explained below.

Note that only the UPDATE ALL STATISTICS parameter is applicable to the

Update Statistics function.

Chapter 5. DBSPACE Analysis Tools

DBSPACE Analysis Tools

Parameter

Description

JOBNAME

Specifies the name by which the DBSPACE

ANALYSIS job and its associated 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.

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.

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.

OTHER

The VSE/POWER * $$ JOB card offers many

parameters that do not appear on the DBSPACE

ANALYSIS SUBMIT screen. Use this field to have

Control Center include those parameters when the

job is submitted.

VSE/POWER Job Parameters

DBSPACE Analysis Job Options

Parameter

Description

UPDATE ALL STATISTICS Specifies whether an UPDATE ALL STATISTICS

command should be executed instead of merely an

UPDATE STATISTICS. It applies to Update

Statistics jobs as well as the reorganization jobs.

The default value is 2 (NO).

Control Center Operations Guide for VSE

DBSPACE Analysis Tools

REBIND PACKAGES

Specifies by 2 (NO) that the DBSPACE

Reorganization tool should not rebind packages as

part of a DBSPACE reorganization. The default is 1

(YES), which means rebinding will occur.

TAPE FILE NAME

Identifies a tape file name that is to receive the

output of the UNLOAD, rather than a disk file.

The file name should have been defined to Control

Center using the Work File Label Definition

function. See Chapter 6, “Work File Label

Definition Tool” on page 39.

FILE #

Defines which of the previously defined data and

DDL files (See “How the Work File Label

Definition Tool Works” on page 39 ) is to be used

for the unloaded data and DDL. Valid values are 1,

2, or 3.

Additional Topics

Initial Execution

Since the SQLMAINT table initially contains no maintenance history, all DBSPACEs

will be selected when analysis is done the first time. If you want to limit the

number of DBSPACEs returned in the Analysis Display List, the last date field for

either Update Statistics or REORG (or both) can be modified to inhibit selection of

those DBSPACEs for candidate processing. This can be done manually using ISQL

or DBSU.

For example, the REORG_DATE and UPSTAT_DATE columns in the SQLMAINT

table can initially be set to different values so that a single Analysis run will only

consider a portion of the DBSPACEs. You can execute a database command such

as:

UPDATE SQLMSTR.SQLMAINT SET UPSTAT_DATE = ’2000-04-15’, REORG_DATE

= ’2000-04-15’ WHERE OWNER < ’N’

This would change about half of the DBSPACEs to a different maintenance date.

You could then use the List Reorg Candidates function (option 3) with an

appropriate DAYS SINCE parameter so that only those DBSPACEs whose names

start with N through Z would be selected.

Reorganization Work Space

The same considerations for TAPE FILE NAME and FILE the reorganization is

accomplished using the DBSPACE Reorganization or the Analysis Tool.

Chapter 5. DBSPACE Analysis Tools

Chapter 6. Work File Label Definition Tool

About the Work File Label Definition Tool

The Work File Label Definition tool is an easy-to-use interface for DBAs to use to

set up the Job Control Language (JCL) label definition statements needed by

Control Center for the DBSPACE Reorganization and Analysis utilities. The JCL is

stored in the SQMWORK file and is used during job submission to create the JCL

for the batch job.

Note that work files are not actually defined until the first time they are used in a

Control Center job. If another application uses an area first, the Control Center job

will be cancelled.

Work File Label Definition Screen

When Option 4 is chosen from the Control Center main menu, the screen shown in

Figure 14 is displayed. This is also the screen that is presented when the CICS

transaction SQFM is entered directly.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*----------------------- WORK FILE LABEL DEFINITION

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

| OPTION

=> 1 (DEFINE WORK FILE

)

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

| FILE TYPE

(1=DATA

2=DDL

) |

(3=PACKAGES

4=MESSAGES

) |

| FILE/PARTITION

FILE TYPE 1 - 2 : FILE SIZE NUMBER

| NUMBER

FILE TYPE 3

: PARTITION NUMBER

FILE TYPE 4

: NOT USED

| TLBL FILE-ID

*----------------------------------------------------------------- SQC03 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT

Figure 14. Work File Label Definition Screen

This screen allows you to enter the parameters that identify the work file labels

you want to define or update. There is one option available - Define Work File.

How the Work File Label Definition Tool Works

There are 2 types of work files that may be defined:

1. Data, and

2. DDL

Work File Label Definition Tool

Data work files are used by the DBSPACE Reorganization, DBSPACE Analysis, and

Table Reorganization tools to hold data unloaded from the DBSPACE using the

DBSU UNLOAD DBSPACE command. Data work files may be defined as either:

1. Disk, or

2. Tape

DDL work files are used by the DBSPACE Reorganization and DBSPACE Analysis

tools to hold DDL generated from the database by SQB01, the Control Center batch

DDL generation program. DDL work files are always stored on disk.

To define a tape work file label, leave FILE TYPE and FILE NUMBER BLANK and

only enter a 1 to 17 character FILE ID and press ENTER. The TAPE WORK FILE

LABEL DEFINITION will be displayed; see Figure 14 on page 39. Remember that

tape may only be used for data work files.

Defining disk files is more involved. Because disks do not have the same capacity

as tapes, file size becomes important. The FILE/PARTITION NUMBER field is

used to identify small (1), medium (2), and large (3) DDL and DATA work files. If

you define only one set of disk work files, we suggest you choose file number 2

because that is the default value on the screens.

When defining a package work file, the field is used to enter a partition number

(job class) in which the View Package job stream will run; valid values are 0-9 and

A-Z. These two types of values share the same inut field on the screen.

As an example, if the user wants to define a medium-size disk DATA work file, the

FILE TYPE would be 1 (DATA) and the FILE NUMBER would be 2 (medium). The

file name (and JCL) will be generated as file name SQMDAT2 with file identifier as

SQLMSTR.DATA.FILE2. The actual size of the files defined is entered on the next

screen and depends on the DBA’s estimates of the needed space. (See Chapter 4,

“DBSPACE Reorganization Tool” on page 13 for an explanation of how you can

combine a disk dataset for the DDL and a tape dataset for the DATA.)

Disk Work File Label Definition Screen

Enter a FILE TYPE and FILE NUMBER and press ENTER to display the DISK

WORK FILE LABEL DEFINITION screen as shown in Figure 15 on page 41.

Control Center Operations Guide for VSE

Work File Label Definition Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*---------------------- DISK WORK FILE LABEL DEFINITION ---------------------*

| FILE TYPE

(1=DATA

2=DDL

) |

(3=PACKAGES

4=MESSAGES

) |

| FILE NUMBER

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

DISK OPERANDS

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

| FILENAME

| FILE-ID

| SERIAL-NUMBER

| RELATIVE-TRACK/BLOCK

| NUMBER-OF-TRACKS/BLOCKS

*----------------------------------------------------------------- SQC04 ----*

ENTER F1=HELP F3=EXIT F12=CANCEL

Figure 15. Disk Work File Label Definition Screen

This screen contains the parameters required to define the location and size of a

disk work file. The values entered are used to create DLBL, ASSGN, and EXTENT

cards that define the file. When you press ENTER, Control Center writes the JCL

statements to the SQMWORK file, from which they are available for the job

submission routines. Each of the parameters is discussed below.

Disk Work File Label Definition Fields

DISK FILE TYPE and DISK FILE NUMBER are displayed as they were entered on

the previous screen. FILENAME and FILE-ID are displayed as generated by

Control Center. The following required parameters are explained below. They are

used in building the EXTENT JCL statement.

Parameter

Description

VOLUME SERIAL NUMBER This is the serial number parameter on the

EXTENT JCL card. It is a 1 to 6 character field

indicating the serial number of the volume on

which this file is to be located.

RELATIVE TRACK/BLOCK This is the starting location of the file. For CKD

(Count-Key-Data) devices, specify the 1 to 5 digit

sequential track number where the file is to begin.

For FBA devices, specify the physical block

number, from 2 to 2,147,483,645 (without the

commas), where the file is to begin.

NUMBER OF TRACKS/BLOCKS

This specifies the size of the work file. For CKD

devices, specify the 1 to 5 digit number of tracks to

be allocated to the file. For FBA devices, specify the

number of physical blocks that should be allocated

to the file. Valid values are 1 to 2,147,483,645

(without the commas).

Chapter 6. Work File Label Definition Tool

Work File Label Definition Tool

The actual size of each file depends on the size of the DBSPACEs in your database.

Refer to the DB2 Server for VSE Control Center Program Directory for sample

allocations. Then, make sure the allocations you have made can accommodate your

data and DDL.

After entering all parameters, press ENTER to complete processing. Control Center

will write DLBL, ASSGN, and EXTENT statements to the SQMWORK file.

F12 will return you to the main WORK FILE LABEL DEFINITION screen. F3 will

return you to the Control Center Main Menu.

Note that if the labels for the chosen file are already defined, the currently defined

values stored in the SQMWORK file will be displayed on the DISK WORK FILE

DEFINITION screen. When the ENTER key is pressed, the records in the

SQMWORK file will be updated with any new values entered.

Tape Work File Definition Screen

When defining a tape file, the screen shown in Figure 16 is presented. This screen

contains the parameters used to create TLBL and ASSGN statements for the

specified file. When you press ENTER, Control Center writes the JCL to the

SQMWORK file from which they are accessed by the job submission routines.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*---------------------- TAPE WORK FILE LABEL DEFINITION ---------------------*

| FILENAME

=> SQMTAPE

FILE-ID

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

TAPE OPERANDS

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

| FILE-SERIAL-NUMBER

| VOLUME-SEQUENCE-NUMBER

| FILE-SEQUENCE-NUMBER

| GENERATION-NUMBER

| VERSION-NUMBER

| DATE

(YYYY/DDD OR 0-9999)

| DEVICE-CLASS

=> 1

(1=CARTRIDGE/2=TAPE)

| MODE

*----------------------------------------------------------------- SQC04 ----*

ENTER F1=HELP F3=EXIT F12=CANCEL

Figure 16. Tape Work File Label Definition Screen

Each of the parameters is discussed in more detail below.

Tape Work File Label Definition Fields

FILENAME is pre-filled and is always set to ″SQMTAPE″. TAPE FILE-ID is

displayed as it was entered on the WORK FILE LABEL DEFINITION screen.

These parameters are all optional except DEVICE CLASS. They are used in

building the TLBL and ASSGN JCL statements that define the file.

Parameter

Description

VOLUME SERIAL NUMBER This is the file serial number parameter on the

Control Center Operations Guide for VSE

Work File Label Definition Tool

TLBL statement. It is a 1 to 6 character field

indicating the volume serial number of the first (or

only) reel of the file.

VOLUME SEQUENCE NUMBER

This is a 1 to 4-digit number specifying the volume

of a multi-volume file at which you wish to start

processing.

FILE SEQUENCE NUMBER This is a 1 to 4-digit number specifying the file of a

multi-file volume at which you wish to start

processing.

GENERATION NUMBER This is a 1 to 4-digit number specifying the

generation number of the file to be processed.

VERSION NUMBER

This is a 1 or 2-digit number specifying the version

of the file to be processed.

DATE

This is the expiration date of the output file

expressed either in YYYY/DDD format (absolute

expiration date as a Julian date) or as a retention

period in days expressed as a 1 to 5-digit number

from 0 to 99999.

DEVICE CLASS

This is used in building the ASSGN statement.

Valid values are ″1″ for CARTRIDGE or ″2″ for

TAPE. This is the only required parameter on this

screen.

MODE

This specifies density when a device class supports

more than 1.

After entering all required parameters, press ENTER to complete processing.

Control Center will write a TLBL and an ASSGN card to the SQMWORK file.

F12 returns you to the main WORK FILE LABEL DEFINITION screen. F3 returns

you to the Control Center Main Menu.

Note that if the file has already been defined, the current values from the

SQMWORK file are displayed on the TAPE WORK FILE LABEL DEFINITION

screen. When you press ENTER, the records in the SQMWORK file will be

updated with any new values entered.

JCL Default Values

If any of the above optional parameters are not entered, default values in

accordance with the VSE/ESA System Control Statements manual will be used.

Package Work File

The View Package Utility uses a SAM work file (SQLPKGx) to hold the unloaded

package used to produce the Package Report. You must allocate a package work

file for each partition that might use the View Package tool. (The partition number

is used as the "x" in the name SQLPKGx.) Therefore, you may have to run this step

more than once. Control Center unloads the package into a package work file and

processes it to produce a package report. Note, only static partition numbers are

allowed.

Chapter 6. Work File Label Definition Tool

Work File Label Definition Tool

For example, if you want to run View Package jobs in the background partition

(CLASS 0), you would specify FILE TYPE 3 (PACKAGES) and FILE/PARTITION

NUMBER 0 (CLASS 0 = BG) as:

FILE TYPE

=> 3 (1=DATA

2=DDL

)

(3=PACKAGES

4=MESSAGES

)

FILE/PARTITION

=> 0

FILE TYPE 1 - 2 : FILE SIZE NUMBER

NUMBER

FILE TYPE 3

: PARTITION NUMBER

FILE TYPE 4

: NOT USED

Press ENTER to display the Disk Work File Label Definition screen. Then enter

SERIAL-NUMBER, RELATIVE-TRACK/BLOCK, and NUMBER-OF-

TRACKS/BLOCKS. Note that as of Control Center Version 7.1, the suggested size

of the package work file is 10K bytes; this is adequate for a report on a very big

package. If necessary, you can redefine the file’s size at any later time. If your

existing file is larger, you can redefine it with this step, making it smaller.

SERIAL-NUMBER

=> SYSWK1

RELATIVE-TRACK/BLACK

=> 03695_____

NUMBER-OF-TRACKS/BLOCKS

=> 20________

Press ENTER to return to the Work File Label Definition Menu and look for the

message WORKFILE UPDATED SUCCESSFULLY!

Repeat this process for each partition into which you want to submit batch View

Package jobstreams.

Package Messages Work File

You must also define a single package messages file that is used by all partitions.

The (batch) Package Report job obtains report headings from this file. To define the

file, specify FILE TYPE 4 (MESSAGES). When the FILE TYPE is 4, the

FILE/PARTITION NUMBER is not used.

FILE TYPE

=> 4 (1=DATA

2=DDL

)

(3=PACKAGES

4=MESSAGES

)

FILE/PARTITION

=> _

FILE TYPE 1 - 2 : FILE SIZE NUMBER

NUMBER

FILE TYPE 3

: PARTITION NUMBER

FILE TYPE 4

: NOT USED

Press ENTER to display the Disk Work File Label Definition screen. Then enter

SERIAL-NUMBER, RELATIVE-TRACK/BLOCK, and NUMBER-OF-

TRACKS/BLOCKS. Note that as of Control Center Version 7.1, the suggested size

of the package work file is 10K bytes; this is adequate for a report on a very big

package. If necessary, you can redefine the file’s size at any later time. If your

existing file is larger, you can redefine it with this step, making it smaller. The new

size value includes an allowance for expansion.

SERIAL-NUMBER

=> SYSWK1

RELATIVE-TRACK/BLACK

=> 03683_____

NUMBER-OF-TRACKS/BLOCKS

=> 12________

Control Center Operations Guide for VSE

Work File Label Definition Tool

Press ENTER to return to the Work File Label Definition Menu and look for the

message WORKFILE UPDATED SUCCESSFULLY!

Special Considerations

Size of Defined Files

Depending on the size and type of DBSPACEs in your database, definition of the

DDL and DATA files need not follow the examples. For instance, a database may

have some large DBSPACEs (many rows). This would mean a large DATA file

would have to be defined. However, the corresponding DDL may in fact be rather

small (say a PUBLIC DBSPACE with SELECT granted to all users, only one table, a

few indexes, and no referential integrity). The DDL could fit in a small file. In this

case, you might define the number 3 DATA file to be much larger and the

associated number 3 DDL file to be much smaller.

Chapter 6. Work File Label Definition Tool

Chapter 7. CICS Report Controller Interface Tool

About the CICS Report Controller Interface Tool

The CICS Report Controller Interface tool provides a means of transferring from

Control Center to the CICS Report Controller so that you can manage the

VSE/POWER queue entries associated with the Control Center jobs you have

submitted. The CICS Report Controller allows you to release, delete, change, and

browse queue entries. When you exit the CICS Report Controller facility, you are

returned to the Control Center main menu.

Option 5 from the main menu causes Control Center to start the CEOS CICS

transaction that displays the initial Report Controller screen. From there, you

navigate through the screens and are presented with different options depending

on whether you are working with report listings or jobs in the reader queue. To

return to Control Center, repeatedly press F3 until the Control Center main menu

is displayed.

A Sample CICS Report Controller Session

In this sample session, assume that you have submitted a Multiple User mode job

to reorganize the PUBLIC.SQMHELP DBSPACE.

You enter Option 5 from the main menu and are presented with the CICS Report

Controller main menu as shown in Figure 17.

CEMS:

CICS REPORT CONTROLLER

Select one of the following options:

Report selection.

Printer selection.

JCL report (job) selection.

Transient data queue selection.

Selection ==>

PF1=Help PF3=End ENTER=Continue.

Figure 17. CICS Report Controller Main Menu Screen

You enter selection 1 (Report selection) and are presented with the Report Selection

screen as shown in Figure 18 on page 48.

CICS Report Controller Interface Tool

CEMS: 11

REPORT SELECTION

You may list all reports or only those reports that match

your selection criteria.

Type your selection criteria:

Report number

==>

Report name

==> M*

Destination

==>

Forms

==>

Class

==>

A-Z / 0-9

From date

==>

mm/dd/yy

To date

==>

mm/dd/yy

To select Status type a Y against one or more of the following:

In use

==>

Held (L)

==>

Resume

==>

Held (H)

==>

Error creating ==>

Ready (K)

==>

Error printing ==>

Ready (D)

==>

PF1=Help PF3=End ENTER=Continue.

Figure 18. CICS Report Controller Report Selection Screen

You tab down to the Report name field, enter ″M*″ (show all reports whose name

starts with ″M″), and are presented with the Report List screen as shown in

Figure 19.

CEMS: 111

REPORT LIST

1 TO

1 OF

Enter Options (1=See/Change Characteristics 3=Print 5=Delete 6=Hold 8=Browse)

OPT NAME

USERDATA

NUMBER STATUS FORMS CLS PAGES CPY PRI DEST

MUMREORG

17471 READY(D)

SYSCICSA

PF1=Help PF3=End PF6=Bulk Change ENTER=Continue.

Figure 19. CICS Report Controller Report List Screen

You enter Option 8 (Browse) and are presented with the Report Browse screen. By

pressing F8 (Scr Fwd) and F11 (Page+1), you can view the output of the

MUMREORG job, including the portion shown in Figure 20 on page 49.

Control Center Operations Guide for VSE

CICS Report Controller Interface Tool

CEMS: 1114

Browsing: MUMREORG

PAGE 2

Search for ==>

- +

09:14:50 Gathering table create DDL...

09:14:51 Gathering deactive primary key DDL...

09:14:51 Gathering inactive unique constraints ..

09:14:51 Gathering table comment DDL...

09:14:51 Gathering column comment DDL...

09:14:51 Gathering primary clustering keys DDL...

09:14:51 Gathering clustering unique constraints DDL...

09:14:51 Gathering index create DDL...

09:14:51 Gathering primary keys DDL...

09:14:51 Gathering foreign keys DDL...

09:14:51 Gathering active unique constraints DDL...

09:14:51 Gathering table grants DDL...

09:14:51 Gathering column grants DDL...

09:14:51 Gathering views and grants DDL...

09:14:51 Gathering package rebind/reload DDL...

09:14:51 Gathering temporary password revokes DDL...

DDL CREATE SUCCESSFUL!

PF1=Help

PF2=Scr Cursor PF3=Quit PF4=Scr Left PF5=Scr Right PF6=Home

PF7=Scr Back

PF8=Scr Fwd

PF9=Repeat PF10=Page-1

PF11=Page+1

PF12=Scal

Figure 20. CICS Report Controller Browse Screen

For detailed instruction on using the CICS Report Controller, refer to the IBM

CICS/VSE^® Report Controller User’s Guide.

Chapter 7. CICS Report Controller Interface Tool

Chapter 8. Control Center Help Facility

About the Help Facility

The Help Facility tool provides information on Control Center menus, functions,

and other topics that you may find useful.

You can reach the Help Menu shown in Figure 21 by selecting Option 6 from

Control Center’s Main Menu.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

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

HELP FACILITY

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

| DATABASE

=> SQLDBA

| SUBJECT

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

HELP SUBJECTS

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

1 APPLICATION GROUP OBJECTS MENU

2 APPLICATION GROUPS LIST

3 CICS REPORT CONTROLLER

4 DBSPACE ANALYSIS SUBMIT SCREEN

5 DBSPACE ANALYSIS UTILITY SCREEN

6 DBSPACE REORGANIZATION SCREEN

7 DBSPACE REORGANIZATION SUBMIT SCREEN

8 DISK WORK FILE LABEL DEFINITION SCREEN

9 GROUP AUTHORIZATION - USER GROUP FUNCTION SCREEN

| 10 GROUP AUTHORIZATION ADD USERS SCREEN

| 11 GROUP AUTHORIZATION APPLICATION GROUP MENU

| 12 GROUP AUTHORIZATION GRANT/REVOKE MENU

| 13 GROUP AUTHORIZATION LIST

| 14 GROUP AUTHORIZATION OBJECT LIST

| 15 GROUP AUTHORIZATION USER LIST

*------------------------------------------------------------------ SQC07 ----*

ENTER F1=HELP F3=EXIT F5=BOT F8=FWD

Figure 21. Sample Control Center Help Menu

The name of the server you are currently working with is displayed in the

DATABASE field in the upper left corner of the screen.

An alphabetically sorted scrollable list of Help Subjects available is displayed in

the body of the screen. By typing the associated subject number in the TOPIC field

and pressing ENTER, a display of information on that subject is presented for

viewing. Backward and forward as well as top and bottom scrolling are available

where applicable.

Text for the Help Facility is located in the database in the PUBLIC.SQMHELP

DBSPACE in the SQLMSTR.SQMHELP table. The list of topics and their order may

change from one release of Control Center to another due to the addition of new

topics.

In most cases, the text provided is identical to that displayed when F1 (HELP) is

entered from many of the Control Center screens.

You can find additional information about Control Center in the DB2 Server for

VSE technical library on the World Wide Web at:

http://www.software.ibm.com/data/db2/vse-vm/.Click on ″More Info on DB Server

...″ and then on ″Control Center″.

1997, 2003

Chapter 9. Package Utility

Introduction

The Control Center Package Utility assists DBAs by automating four tasks very

often associated with managing database packages. A package is a control structure

containing SQL statements in executable form. It is produced as part of the

preparation process before a program is compiled. Packages are stored in a

database.

When a program is migrated from test to production, very often it is not

recompiled. Instead, the executable PHASE is simply copied from the test to the

production library. If the program contains SQL statements, the associated package

must also be copied to the production database.

The Control Center Package Utility helps in this effort by allowing you to unload a

package from one database and reload it into another. The unload/reload utilities

are also useful for migrating packages in a distributed processing environment.

Whenever an object such as a DBSPACE, table, or index is dropped, all packages

dependent upon that object are marked invalid. Before an invalid package can be

executed, the database must first rebind it. This can result in poor response time

for the first person attempting to execute the program. The Control Center Package

Utility allows DBAs to rebind packages ahead of time so that their users are not

impacted.

Many times, DBAs are called upon to help application developers identify poor

performing SQL statements in their programs and suggest ways the SQL can be

recoded to improve performance. The Control Center Package Utility helps in this

area by producing a package report which, among other things, lists every SQL

statement in the program. The Control Center package report can be used to tune

the SQL in the program and becomes an important part of the documentation

about the program.

Package Utility Functions

The Package Utility offers four main functions:

v Unload Package

v Reload Package

v Rebind Package

v View Package

The Unload, Reload, Rebind, and View Package Utilities all use DBSU to

accomplish the desired process. For each function, Control Center generates a batch

job, the appropriate DBSU commands, and includes the JCL to manage their

execution. The Unload and Reload functions use LIBR, the VSE librarian, for

storage and retrieval of unloaded packages. The View Package Utility uses a

REXX/VSE program to produce a printed package report.

For each of these functions, using information you supply on the Package Utility

menu (Figure 22 on page 55), Control Center builds and submits a DBSU job.

Package Utility

Package Function Descriptions

Control Center stores packages for this tool in a Librarian library,

SQLMSTR.PACKAGE. Periodically, you may want to delete packages no longer

needed in this library. Ask your System Programmer to do the delete for you.

Unload Package

The job executes DBSU to unload the package to the VSE punch queue. It then

catalogs the punch file containing the package into SQLMSTR.PACKAGE, where it

can be accessed for reloading.

Reload Package

The job punches the package to the VSE reader queue from SQLMSTR.PACKAGE.

It then executes DBSU to reload the package from the VSE reader queue. Options

such as whether an existing package is to be replaced and whether to keep or

revoke existing run privileges are supported. Optionally, you may specify a server

name other than the one that is displayed.

Note: This function can only be used to reload packages that Control Center has

unloaded.

Rebind Package

Optionally, you may specify that the package is only to be rebound if it is already

marked as invalid. The job executes DBSU to rebind the package.

View Package

You identify the package to be viewed by specifying an owner and package name.

You may specify a server name other than the one that is displayed.

In the job, the package messages are copied to a SAM file where they are accessible

to the REXX/VSE package report program. Next, the package is unloaded to a

SAM file, followed by the execution of a REXX/VSE program to produce the

package report.

Package Migration

For package migration, both the Unload and Reload Package functions previously

described are used. Be sure to specify the Server name field (Figure 22 on page 55)

when invoking the Reload function. During the job submission step, the parameter

DEST must be specified. See “Package Utility Parameters” on page 55 and

“Package Utility Job Submit Parameters” on page 56.

Invocation

There are two ways to invoke the Package Utility:

v From the main menu, by selecting Option 7 (PACKAGE UTILITY), or

v Directly from CICS, by typing the Package Utility transaction ID, SQPM.

Control Center Operations Guide for VSE

Package Utility

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

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

PACKAGE UTILITY

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

| DATABASE

=> SQLDBA

| OWNER

=> ________

PACKAGE-NAME => ________

| OPTION

=> _ (1=UNLOAD PACKAGE

2=RELOAD PACKAGE)

(3=REBIND PACKAGE

4=VIEW PACKAGE)

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

OPTIONAL PARAMETERS

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

| INVALID ONLY?

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

| TO SERVER-NAME

=> __________________

| REPLACE/NEW

=> 1 (1=REPLACE/2=NEW)

| KEEP/REVOKE

=> 1 (1=KEEP/2=REVOKE)

*------------------------------------------------------------------ SQC16 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT

Figure 22. Package Utility Screen

Package Utility Parameters

The Package Utility parameters are:

Entry Field

Description

DATABASE

the name of the default or last used application

server. If you enter the name of another server, you

will be connected to it before proceeding.

OWNER

the ID of the person who created this package or

preprocessed the program associated with it.

PACKAGE-NAME

the name of the package.

OPTION

specifies the function to be executed

INVALID ONLY?

You may want to not rebind a package that is

already valid. If so, specify YES. The Package

Utility will check to make sure the package is

invalid before allowing a rebind job to be

submitted. The default value is NO (2).

TO SERVER-NAME

Use this parameter to reload a package to a

different application server. DBSU will connect to

the application server and reload the package. This

parameter applies only to RELOAD PACKAGE.

REPLACE/NEW

Specify REPLACE if an existing package is to be

replaced by the reload. If the package does not

exist, a new package will be created. NEW causes

the reload to fail if a package already exists.

REPLACE is the default.

KEEP/REVOKE

KEEP specifies that run authorizations are to

remain in effect when the package is reloaded.

REVOKE causes all existing run authorizations to

Chapter 9. Package Utility

Package Utility

be revoked. KEEP/REVOKE are only allowed with

REPLACE. KEEP is the default.

Using the Package Utility

The examples below illustrate how to use the Unload and View Package functions.

How to Unload a Package

To Unload a package, select Option 1 on the Package Utility screen (Figure 22 on

page 55). Identify the package to be unloaded by specifying the owner and

package name. Optionally, you may specify a server name other than the one that

is displayed. If the package does not exist, you will receive a message in the lower

left hand corner of menu.

After you press ENTER while viewing the Package Utility screen, the Package Job

Submit screen, Figure 23, is displayed. This is where you specify the parameters for

job submission to the VSE/POWER queue.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*------------------------- PACKAGE JOB SUBMIT SCREEN -------------------------*

| JOBNAME

=> ________

CLASS

=> A

| PRI

=> 3

DISP

=> D (D,H,L,K)

| FROM

=> ________

| DUETIME

=> ____ (HHMM)

DUEDATE => ______ (AABBYY)

| DUEDAY

=> __________________________________________

| OTHER

=> ______________________________________________________________ |

| LST CLASS => A

| DEST

=> ________

*------------------------------------------------------------------ SQC17 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT

Figure 23. Package Job Submit Screen

Package Utility Job Submit Parameters

The job submission parameters are:

Parameter

Description

JOBNAME

Specifies the name by which the DBSPACE

REORGANIZATION job and its associated 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

Control Center Operations Guide for VSE

Package Utility

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).

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).

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).

LST CLASS

defines the class to be assigned to printed output.

Specify any letter of the alphabet, or any number 0

through 9.

DEST

specifies the node-id where VSE/POWER is to

route the RELOAD PACKAGE job for processing.

Specify the name of an applicable node as defined

to VSE/POWER with the PNODE macro. This

parameter allows you to migrate a package from a

local application server to an application server

running on a remote system.

How to View a Package

To view a package, specify the OWNER and PACKAGE_NAME fields on the

Package Utility screen (Figure 22 on page 55), and select Option 4. Provide

appropriate parameters on the Package Job Submit screen. The View Package tool

executes DBSU to unload the desired package to a SAM file. It then generates a

package report that lets you view the package contents which include:

v Preprocessing Information

v Each SQL statement used in the associated program

v Information about its corresponding host variables

This data is quite helpful and can be used to analyze performance problems.

Chapter 9. Package Utility

Package Utility

Figure 24 shows a report generated from the View Package option.

Date: dd Mmm yyyy

Page:

Control Center Package Report

_____________________________

Package: SQLMSTR.SQC05

Database: SQLDBA

First Create Release: 7.1

Last Create Release: 7.1

Charname: INTERNATIONAL

Sections: 6

Preprocessing Characteristics:

_______________________________

NOGRAPHIC, BLOCK, NOMODIFY, NODESCRIBE

Options Specified at Prep time:

________________________________

BLock

ISOLation(RR)

DEFAULT Options at Prep time:

______________________________

RELease(COMMIT), EXPLAIN(NO), KEEP, REPLACE, NOEXIST

NOCHECK, PERiod, APOST

PREPname=SQC05, CTOKEN(NO), LABEL( )

Static SQL Statements in Package:

__________________________________

SELECT NPAGES, POOL INTO :H, :H FROM SYSTEM.SYSDBSPACES WHERE

OWNER

:H AND DBSPACENAME = :H

SELECT OWNER FROM SYSTEM.SYSDBSPACES WHERE OWNER = :H AND DBSPACETYPE

:H AND NPAGES = :H AND POOL IN (:H,:H)

SELECT OWNER INTO :H FROM SYSTEM.SYSDBSPACES WHERE OWNER = :H AND

DBSPACENAME = :H

SELECT VALUE INTO :H FROM SYSTEM.SYSOPTIONS WHERE SQLOPTION = ’RELEASE’

SELECT CURRENT SERVER INTO :H FROM SYSTEM.SYSOPTIONS WHERE SQLOPTION =

’RELEASE’

Figure 24. Example Output from View Package Option

Control Center Operations Guide for VSE

Chapter 10. Group Authorization Tool

The Group Authorization tool helps DBAs manage access to database objects,

simplifies the authorization process, and shortens the time needed to grant or

revoke privileges. It lets DBAs issue authorizations to groups of users on groups of

objects rather than one by one. You can associate individual users with defined

User Groups, and you can associate database objects (such as tables, views, and

packages) with defined Application Groups. Then you can use the Group

Authorization menus to issue GRANTs and REVOKEs specifying a User Group

(grantee) and an Application Group (on objects).

About the Group Authorization Tool

The Group Authorization Tool is a series of CICS transactions that operate under

the SQM main transaction. The Control Center DBA ID grants all authorizations.

The tool records and maintains all authorizations.

You can use the “LIST Functions” on page 61 to view the various reports that are

available to help manage database access. These reports show Application Groups

and the objects they contain, such as tables, views and packages; User Groups and

user IDs associated with specific User Groups; and authorities granted to User

Groups.

The Group Authorization tool keeps all data about User and Application Groups,

as well as authorization information, in database tables. You can query these tables

to obtain authorization information. See “Special Considerations” on page 67 for an

example.

The Control Center DBA ID owns five authorization tables that contain its

information about User, Application Groups and authorizations. These tables are:

USERID_GROUP_TAB

User Group Table. This table is used to hold the

name, internal ID, and description of a User group.

USERID_TAB

User ID Table. This table has one row for each

unique combination of Userid and User Group ID.

APPL_GROUP_TAB

Application Group Table. This table is used to hold

the name, internal ID, and description of an

Application Group.

OBJECT_TAB

Object ID Table. This table has an entry for each

unique combination of object (object owner, object

name) and Application Group ID.

GROUP_AUTH_TAB

Group Authorization Table. This table records each

group authorization made by the Control Center

DBA. It records the User Group ID, the Application

Group ID and the specific privileges granted using

the Group Authorization tool.

Users can belong to more than one User Group and can have the same privilege

granted to an object through multiple User Groups. If you drop the user from one

User Group, the user does not automatically lose the privilege to the object since

the user still has authority through the second User Group.

1997, 2003

Group Authorization Tool

The same is true of an object that is in more than one Application Group: if that

object is dropped from one group, but privileges on it exist through another

application group, those privileges will not be automatically revoked from users

who have authority on it through the second group.

You can choose to:

v ADD a group

v DROP a group

v Manage group objects and users

v Manage privileges

v LIST privileges

Functions

ADD a Group

Lets you create both User and Application Groups. The data created will be stored

in the USER_GROUP_TAB and APPL_GROUP_TAB tables respectively.

DROP a Group

Deletes a group entry from the applicable tables. In addition, if any privileges have

been granted to a User Group which is being dropped, all privileges will be

revoked from all users in that group. If an Application Group is dropped, all

privileges that were granted on that group will be revoked from all users who

were granted those privileges. There are two exceptions to this rule:

1. When dropping a User Group to which some group authorizations have been

made, a check is made before revoking each user’s privileges to determine if

the user has been granted the same privileges through another group. If they

do belong to a User Group with the same privileges, the users will not lose

their privileges.

2. When dropping an Application Group that has had privileges granted on it, a

check is made before revoking the privileges from each user to whom they

were granted. If the privileges on the object have been granted to that user

through another Application Group, the users will not lose their privileges.

Manage Group Objects and Users

Permits the DBA to populate a group with user IDs (in the case of User Groups)

or, tables and views, or packages (in the case of Application Groups). Application

Groups are defined as either a table group (consisting of tables and views only) or

a package group (consisting of package names only).

A user can belong to more than one User Group. An object can belong to more

than one Application Group. Each group type must have at least one member.

When you add an object to an Application Group, any privileges of existing User

Groups will be GRANTED on the newly added object to all users in the User

Groups authorized to that Application Group. When an object is dropped from an

Application Group, all privileges to that object will be REVOKED for all users

unless the user is a member of another group with similar privileges. (See the

exceptions in “DROP a Group”.)

Control Center Operations Guide for VSE

Group Authorization Tool

When you add a user to a User Group, the user is granted all authorities that the

group currently holds. When you drop a user from a group, the user loses all

authorities which the group has unless the user is a member of another group with

the same authorities. (See exceptions in “DROP a Group” on page 60.)

Manage Privileges

Lets you grant and revoke privileges to User Groups on individual database

objects or on Application Groups (that is, on all objects defined in the group). You

cannot grant column update privileges; however, you can create a view with the

column updates and then grant update privilege on the view.

Use the Authorizations Menu to enter an individual object or a group of objects on

which you want to grant or revoke privileges. An individual object is identified by

its owner and object name. A group object is identified by its Application Group

name. You use the Control Center DBA connect ID authorizations.

An extra grant is done for the User Group name to facilitate the implementation of

the tool. If you give SELECT privilege to User Group UGROUP1 on Application

Group AGROUP1, the Group Authorization tool generates one extra GRANT

SELECT to UGROUP1 on each object in AGROUP1. Likewise, when you use the

REVOKE function, the SELECT privilege is revoked from the User Group.

LIST Functions

The Group Authorization tool provides the following on-line reports:

v All User Groups

v All Application Groups

v All Users/Objects within a specific group

v All Application Groups on which a given User Group has been granted

privileges, and what those privileges are

v All User Groups to which privileges have been granted on a given Application

Group, and what those privileges are

Using the Group Authorization Tool

To use the Group Authorization tool, you need to:

1. Define Application Groups

2. Add (or Drop) objects to the Application Groups

3. Define User Groups

4. Add Users to the User Groups

5. Grant Authorities to the User Groups

In addition, you need to be able to:

6. Drop a User

7. Drop a Group

To reach the Group Authorization Menu shown in Figure 25:

v Select Option 8 from the Control Center main menu, or

v Enter the SQGA transaction ID from a CICS screen.

Chapter 10. Group Authorization Tool

Group Authorization Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

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

GROUP AUTHORIZATION MENU

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

| DATABASE

=> SQLDBA

| OPTION

1 USER GROUP FUNCTION

2 USER FUNCTION

3 APPLICATION GROUP FUNCTION

4 GROUP AUTHORIZATIONS

*----------------------------------------------------------------- SQC19 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT

Figure 25. Control Center Group Authorization Menu

Before using the Group Authorization tool, you need to analyze your current

database authorization structure. Start by grouping tables and views according to

some common function or element(s). For instance, perhaps you want all payroll,

personnel, or accounting tables in their own specific groups. Once you have

decided this, you can define Application Groups for these various functions.

Let’s say you have 5 tables and 3 views that belong in the personnel organization

and you want to define three Application Groups for this organization. The

Application Groups will contain the tables and views from the personnel

organization.

The following steps show how to use the tool to define the application groups.

Before starting, decide what tables will be in which group as shown in Application

Group Definitions:

Application Group Definitions

AGROUP1 contains PERSTAB1, PERSTAB2, PERSTAB3, PERSVIEW1

AGROUP2 contains PERSTAB1, PERSTAB4, PERSTAB5, PERSVIEW2

AGROUP3 contains PERSVIEW3

Note: PERSTAB1 is common to both AGROUP1 and AGROUP2, and AGROUP3

has only one view defined to it (PERSVIEW3).

Step

1: Define Application Groups

To define an Application Group, enter Option 3 from the Group Authorization

Menu to get to the Application Group Menu.

Control Center Operations Guide for VSE

Group Authorization Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

*-------------------------- APPLICATION GROUP MENU ---------------------------*

| DATABASE

=> SQLDBA

| GROUP

=> AGROUP1

| OPTION

=> 1

1 ADD GROUP

2 DROP GROUP

3 ADD/DROP OBJECTS IN A GROUP

4 LIST GROUPS

5 LIST OBJECTS IN A GROUP

6 LIST GROUP AUTHORIZATIONS

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

| DESCRIPTION

=> Personnel tables

| GROUP TYPE

=> T

(T=TABLES AND VIEWS, P=PACKAGES)

*------------------------------------------------------------------ SQC26 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT F12=RETURN

Figure 26. Control Center Application Group Menu

Enter the name of the Application Group you are adding in the Group field,

choose Option 1, Add Group, and enter the Group Type (use T for tables or views

or P for packages). You can also choose to add a description of the group in the

Group Description field. If you enter a Group Description, it is stored in the

database record and you can view it in the Application Group list report. The

database you are working with is shown at the top of the screen in the Database

field. If you want to work with a different database, enter the name of the new

database in the DATABASE field.

The example shown in Figure 26 shows how to add the group AGROUP1. The

Application Group type is T for tables or views, and "Personnel tables" has been

added as the Group DESCRIPTION.

Step

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

Once you have defined the Application Group, you add the objects (that is, tables

and views, or packages) to the group. On the Application Group Menu Figure 26,

choose Option 3 to add the objects to the Application Group. You add tables and

views, or packages to the Application Group on the Object Functions menu.

Figure 27 on page 64 shows how to add the three personnel tables and a view to

the AGROUP1 Application Group.

If you later want to delete an object from an application group, use this function

(accessed by Group Authorization, main menu option 3) to delete the object.

Chapter 10. Group Authorization Tool

Group Authorization Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

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

OBJECT FUNCTIONS

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

| DATABASE => SQLDBA

| GROUP

=> AGROUP1

| OPTION ===> 1

| 1= ADD OBJECTS

2= DROP OBJECTS

OWNER

OBJECT NAME

PERS

PERSTAB1

PERS

PERSTAB2

PERS

PERSTAB3

PERS

PERSVIEW1

*----------------------------------------------------------------- SQC28 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT F12=RETURN

Figure 27. Adding Objects to Your Application Group

Step 3: Define User Groups

After you have established the Application Groups, you define the users of your

database to whom you want to grant privileges. For example, let’s assume you

have ten users whose IDs are USER1, USER2, and so on through USER10. You can

now group these users by the criteria you use to determine authorization. Our

examples use the following three User Groups:

User Group Definitions

UGROUP1 consists of User1, User2, User3, User4, User5, User6

UGROUP2 consists of User1, User7, User8, User9, User10

UGROUP3 consists of User1 through User10

Note: User1 is in all 3 groups, all ten users are in UGROUP3.

Use the User Group Menu to add or delete a User Group, to define the members

of a User Group, and to list User Groups. To reach the User Group Menu: From

the Group Authorization Menu, select Option 1 to reach the User Group Menu.

Figure 28 on page 65 show how to add the UGROUP1 User Group.

Control Center Operations Guide for VSE

Group Authorization Tool

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

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

USER GROUP MENU

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

| DATABASE

=> SQLDBA

| GROUP

=> UGROUP1

| OPTION

=> 1

1 ADD GROUP

2 DROP GROUP

3 ADD USERS TO A GROUP

4 LIST GROUPS

5 LIST USERS IN A GROUP

6 LIST GROUP AUTHORIZATIONS

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

| DESCRIPTION

=> Executives

*----------------------------------------------------------------- SQC20 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT F12=RETURN

Figure 28. Adding a User Group

Figure 29 shows the result of using option 4 to see what groups already exist.

mm/dd/yyyy

CONTROL CENTER

hh:mm:ss

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

USER GROUP LIST

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

| DATABASE

=> SQLDBA

GROUP

DESCRIPTION

TESTGRP TEST GROUP 1

*----------------------------------------------------------------- SQC21 ----*

PRESS ENTER TO PROCESS

ENTER F1=HELP F3=EXIT F12=RETURN

Figure 29. Checking for Existing User Group

Step

4: Add Users to the User Groups

Now choose Option 3 to add users to the User Group you have defined in Step

You enter the User Group member on the Add Users screen shown in Figure 30 on

page 66, or Option 2 from the Group Authorization Menu.

Chapter 10. Group Authorization Tool

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