|
|
Group Authorization Tool
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*--------------------------
ADD USERS
------------------------*
| DATABASE => SQLDBA
|
| GROUP
=> UGROUP1
|
| OPTION ===> 1
|
|
|
| 1= ADD USER(S)
|
|
|
|
USER
|
|
User1
|
|
User2
|
|
User3
|
|
User4
|
|
User5
|
|
User6
|
|
|
|
|
|
|
|
|
|
|
*-------------------------------------------------------------- SQC29
----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=RETURN
Figure 30. Adding Users to UGROUP1
Step
5: Grant Authorities to the User Groups
In this step, you grant object authority to the User Group. Use the Authorization
Menu to issue grants or revokes to the User Groups. Figure 31 shows how to grant
Select and Update on AGROUP1 to UGROUP1.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*-------------------------- AUTHORIZATIONS MENU
--------------------------*
| DATABASE => SQLDBA
|
|
|
| OPTION
=> 1
(
1=GRANT
2=REVOKE
)
|
|
|
| PRIVILEGES
=> 1 4
(
1=SELECT
2=INSERT
3=DELETE
|
|
4=UPDATE
5=ALL
6=EXECUTE
|
|
ON
|
| OBJECT OWNER
=>
|
| OBJECT NAME
=>
|
|
OR
|
| APPLICATION GROUP NAME => AGROUP1
|
|
|
|
|
|
TO OR FROM
|
| USER GROUP
=> UGROUP1
|
|
|
| WITH GRANT OPTION
=>
(
1=YES
)
|
|
|
*----------------------------------------------------------------- SQC27 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=RETURN
Figure 31. Authorization Menu
The choices made in the example show in Figure 31, grant Select and Update
privileges on tables PERSTAB1, PERSTAB2, PERSTAB3, and PERSVIEW1 to user
IDs USER1 through USER6. To grant multiple privileges at the same time, enter the
number corresponding to each privilege separated by a space.
66
Control Center Operations Guide for VSE
Group Authorization Tool
Let’s assume you have also granted Select and Update privileges on tables
PERSTAB1, PERSTAB2, PERSTAB3, and PERSVIEW1 to UGROUP2 (that contains
user IDs USER1, and USER7 through USER10). Note that you have given USER1
Select and Update privileges on the same tables in AGROUP1. If you revoked the
Update privilege from UGROUP1, all members of UGROUP1 would lose their
Update privilege except USER1. USER1 would keep the UPDATE privilege because
it is also a member of UGROUP2 that still has the UPDATE privilege.
Special Considerations
Using Control Center you grant authorizations to Groups, not to individual user
ID’s.
1.
If you want to grant authorizations to an individual user ID, define a User
Group with only one user ID in it. You can then grant authorizations to that
user group which, in effect, let’s you grant authorizations to a single user ID.
2.
Authorizations can be granted to an individual table that is not a member of an
Application Group. For example:
Individual Tables
GRANT Select on SQLDBA.ACTIVITY to UGROUP1
This will grant the privilege to all users defined in UGROUP1. However, since
the grant is to an individual database entity and not to an Application Group,
the Group Authorization tool does not record the authorizations in its tables.
If any user ID in UGROUP1 were to later have its privilege to
SQLDBA.ACTIVITY revoked due to the table being defined in an Application
Group, the privilege would be deleted from SYSTEM.SYSTABAUTH since the
Group Authorization tool does not know about the individually granted
authority. If you want the tool to keep a record of the individual authorization,
define the table in its own group. Just as you can define a single user in a User
Group, you can also define a single object in its own Application Group.
3.
You can grant or revoke the following authorizations:
v SELECT, INSERT, UPDATE, DELETE and ALL on Tables and Views. The
ALL authorization is equivalent to the SELECT, INSERT, UPDATE, and
DELETE privileges. It does NOT include ALTER, INDEX, or REFERENCES.
v EXECUTE on Packages
v Use the GRANT option on all the above authorizations.
|
4.
All grants and revokes are executed with the Control Center DBA connect ID.
|
5.
When granting EXECUTE on packages, the Control Center DBA ID must have
|
the RUN privilege with GRANT option in order to do the GRANT.
6.
You cannot grant referential constraint privileges.
|
7.
The tool will not affect any current authorizations recorded in
|
SYSTEM.SYSTABAUTH and SYSTEM.SYSPROGAUTH, since the Control
|
Center DBA ID grants all privileges. As you build your authorization scheme
|
and issue grants using the tool, remove old SQLMSTR grants (if you are
|
migrating your Control Center to Version 7.3 or later), or non-Control Center
|
DBA grants as appropriate.
Chapter 10. Group Authorization Tool
67
Group Authorization Tool
8. The following is an example of a query you can use to obtain information from
the Group Authorization tables. For example, if you want a list of those users
(and their group IDs) that have SELECT authorization to all the objects in a
particular Application Group, use:
SELECT A.APPL_GROUP_NAME, O.OBJECT_OWNER, O.OBJECT_NAME, G.S_AUTH,
U.USERID, UG.GROUP_NAME
FROM SQLMSTR.USERID_GROUP_TAB UG, SQLMSTR.USERID_TAB U,
SQLMSTR.OBJECT_TAB O, SQLMSTR.GROUP_AUTH_TAB G,
SQLMSTR.APPL_GROUP_TAB A
WHERE A.APPL_GROUP_NAME = ’AGROUP5’
AND
O.APPL_GROUP_ID
= G.APPL_GROUP_ID AND
U.GROUP_ID
= G.USERID_GROUP_ID AND
A.APPL_GROUP_ID
= G.APPL_GROUP_ID AND
UG.GROUP_ID
= U.GROUP_ID
AND
G.S_AUTH > ’ ’
ORDER BY 1,2,3,5
Figure 32. Query Using Join Statement
APPL
GROUP
OBJECT
OBJECT
GROUP
NAME
OWNER
NAME
S AUTH
USERID
NAME
----
------
--------
------
------
------
AGROUP5
M760595
TABERROR
G
TESTGP3
TESTGP3
AGROUP5
M760595
TABERROR
Y
TESTGP4
TESTGP4
AGROUP5
M760595
TABERROR
G
USER1
TESTGP3
AGROUP5
M760595
TABERROR
Y
USER1
TESTGP4
AGROUP5
M760595
TABERROR
G
USER2
TESTGP3
AGROUP5
M760595
TABERROR
G
USER3
TESTGP3
AGROUP5
M760595
TABERROR
Y
USER4
TESTGP4
AGROUP5
M760595
TABERROR
Y
USER5
TESTGP4
AGROUP5
SQLDBA
ACTIVITY
G
TESTGP3
TESTGP3
AGROUP5
SQLDBA
ACTIVITY
Y
TESTGP4
TESTGP4
AGROUP5
SQLDBA
ACTIVITY
G
USER1
TESTGP3
AGROUP5
SQLDBA
ACTIVITY
Y
USER1
TESTGP4
Figure 33. Results of Join Query
Or, if you want to modify a group’s description, you can simply update the
GROUP_DESC field for the USERID_GROUP_TAB or the APPL_DESC field of
the APPL_GROUP_TAB.
68
Control Center Operations Guide for VSE
Chapter
11. The Monitor Maintenance Menu
The Control Center Monitor Utilities are a DBA’s best friend. They keep watch
over the database while you are busy doing something else. They notify you when
exceptional conditions occur and they capture key information that you need to
track performance problems and resource consumption.
Whether you are interested in tracking user activity, locking, physical and logical
space use, or the database itself, there are monitors designed to give you that
information.
The SHOW ACTIVE and SHOW CONNECT monitors collect information that tells
you if users have been waiting for database resources. You can choose to set the
Monitor Utility to alert you immediately, on the operator’s console, if it detects a
user in wait state.
The SHOW LOCK monitor records the number of lock buffers in use and who is
locking whom. You can also set this monitor to alert you immediately, on the
operator’s console, if it detects any kind of user locking.
The SHOW LOG, SHOW DBEXTENT, and SHOW DBSPACE monitors record the
use of these vital resources. You can direct the SHOW LOG and SHOW
DBEXTENT monitors to notify you immediately if their use exceeds the percentage
you specify. The COUNTER * monitor records the occurrence of key events in the
database and stores this information in the database. You are then free to develop
your own custom queries to track statistics; such as, buffer hit ratio or LPAGBUFF
and PAGEREAD.
How the Monitors Work
The monitor ″kernel″ is the heart of the Monitor Utility. It is a COBOL/CICS
program that runs unattached to any terminal. For each database for which you
have started a kernel, there will be a separate kernel task. Each time a monitor
kernel is activated, it determines what monitors should be scheduled and starts a
task for each. At midnight, the kernel schedules the Reset Monitor task, that
optionally deletes the monitor data and optionally produces a report for all
monitors scheduled to be reset.
Each individual monitor:
v Issues a database operator command,
v Captures the output,
v Writes information to the appropriate monitor table,
v Optionally provides notification of exceptional conditions where applicable.
This monitor kernel reactivates itself every minute to check if it needs to dispatch
any monitors.
69
The Monitor Maintenance Menu
Options and Monitors Available
As shown in Figure 38 on page 75, the Monitor Utilities include options that allow
you to:
v Start and stop monitor kernels
v List defined monitors
v Add, modify, delete, and display monitors
v View monitor data online
v Reset monitor data
v Print monitor reports
These options use seven monitors:
v SHOW ACTIVE
v SHOW LOCK
v SHOW DBEXTENT
v SHOW LOG
v SHOW CONNECT
v SHOW DBSPACE
v COUNTER *
Monitor Thresholds and VSE Console Messages
All the monitors listed, except SHOW DBSPACE and COUNTER *, let you specify
a threshold at which Control Center will generate a VSE console message.
For instance, if the SHOW ACTIVE monitor finds a CHECKPOINT or USER WAIT
situation when it executes, or if SHOW LOG or SHOW DBEXTENT reaches a
PERCENT USED value, Control Center sends a message to the VSE console.
Similarly, if SHOW CONNECT finds AGENTS WAITING, NOT PROCESSING or
INACTIVE, or if the SHOW LOCK monitors meet the locking indicators, Control
Center will issue the appropriate message.
Figure 34 is an example of the messages sent to the VSE console when a SHOW
ACTIVE monitor threshold is reached.
SYSTEM: VSE/ESA
V2.1.0
-- SVSCVSA
USER: VS22
TIME: hh:mm:ss
F2 0002 SQM0632 SQLMSTR IS IN COMMUNICATION WAIT
F2 0002 SQM0318 DATABASE:
SQLDBA
F2 0002 SQM0632 CMORGAN IS IN COMMUNICATION WAIT
F2 0002 SQM0318 DATABASE:
SQLDBA
==>
1=HLP 2=CPY 3=END 4=RTN 5=DEL 6=DELS 7=RED 8=CONT 9=EXPL 10=HLD
12=RTRV
ACT_MSG: HOLD
PAUSE: 01
SCROLL: 1
MODE: CONSOLE
Figure 34. VSE Console - Monitor Threshold Notification
In this figure, Database SQLDBA shows two agents, SQLMSTR and CMORGAN, in
communication wait state. Although neither situation is necessarily a problem, you
may want to investigate continued wait states.
70
Control Center Operations Guide for VSE
The Monitor Maintenance Menu
Description of Monitor Options
Start the Monitor Kernel
You start the monitor kernel from the Monitor Menu. The Monitor Menu is
invoked by selecting Option 09 Monitor Utilities from the Control Center Main
Menu, and then Option 1. It is shown in Figure 35. You must have defined (“Add a
Monitor” on page 72) at least one monitor in order to use this function. Once
started, the monitor kernel runs until you execute the Stop Monitor command.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*---------------------------
MONITOR UTILITY
---------------------------*
|
|
| DATABASE
=> SQLDBA
|
|
|
| *************************************************************************** |
|
|
| OPTION
=>
01 START KERNEL
02 STOP KERNEL
|
|
03 LIST MONITORS
04 ADD MONITOR
|
|
05 MODIFY MONITOR
06 DELETE MONITOR
|
|
07 DISPLAY MONITOR
08 VIEW DATA
|
|
09 RESET DATA
10 PRINT REPORT
|
|
|
| *************************************************************************** |
|
|
| MONITOR
=>
01 SHOW ACTIVE
02 SHOW LOCK
|
|
03 SHOW DBEXTENT
04 SHOW LOG
|
|
05 SHOW CONNECT
06 SHOW DBSPACE 00001
|
|
07 COUNTER *
|
|
|
*------------------------------------------------------------------ SQC40 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT
Figure 35. Monitor Menu
Stop the Monitor Kernel
Lets you end the execution of all active monitors, once monitors currently
executing have been completed. When you stop the Monitor Kernel, you stop all
the monitors.
List Monitors
Lists all the monitors defined for the specified database on your terminal. Figure 36
on page 72 shows the monitors belonging to the SQLDBA database.
Chapter 11. The Monitor Maintenance Menu
71
The Monitor Maintenance Menu
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*---------------------------
MONITOR LIST
----------------------------*
| DATABASE
=> SQLDBA
|
|
|
| MONITOR_____
DESCRIPTION_______________________________________
|
| ACTIVITY
SHOW ACTIVE
|
| LOCKING
SHOW LOCK ACTIVE
|
| COUNTERS
COUNTER *
|
| LOG
SHOW LOG
|
| POOL
SHOW POOL
|
| DBEXTENT
SHOW DBEXTENT
|
| DBSPACE00001
SHOW DBSPACE
1
|
| DBSPACE00002
SHOW DBSPACE
2
|
| DBSPACE00003
SHOW DBSPACE
3
|
| DBSPACE00004
SHOW DBSPACE
4
|
| DBSPACE00005
SHOW DBSPACE
5
|
| DBSPACE00006
SHOW DBSPACE
6
|
| DBSPACE00007
SHOW DBSPACE
7
|
| DBSPACE00010
SHOW DBSPACE
10
|
| DBSPACE00022
SHOW DBSPACE
22
|
*------------------------------------------------------------------ SQC43 ----*
F1=HELP F3=EXIT F4=TOP F5=BOT F7=BWD F8=FWD F12=CANCEL
Figure 36. Monitor List
Add a Monitor
Lets you define a monitor, the first step in using one. You specify the database you
want to monitor, the type of monitor, the frequency, and the start and stop times.
You also define the threshold at which notification will occur.
Modify a Monitor
Lets you view the monitor definition information. You can choose to update the
frequency, start and stop times, and the monitor thresholds, and to activate or
deactivate a monitor.
Delete a Monitor
Lets you delete a monitor. To delete the SHOW LOG monitor from the SQLDBA
database, for example, select Option 06. Next select Monitor 04 and press ENTER.
Note that you can press PF3 to cancel deleting the monitor.
Display a Monitor
Lets you view the definition of a monitor.
View Data
Lets you view the data the monitor has collected. With this option, you can
analyze the output and watch for significant database use or trends.
Reset Data
Deletes all rows of data collected by the monitor from the associated monitor table.
Data keeps accumulating until the monitor is reset.
72
Control Center Operations Guide for VSE
The Monitor Maintenance Menu
Print Report
Generates a monitor report and places it directly on the VSE/POWER List queue.
The monitor report includes all the data that the monitor has captured. To Print a
Monitor Report, select Option 10 from the main Monitor Menu, together with the
desired monitor ID.
The figure that follows is where you include appropriate job submission
parameters. The RESET DATA? option keeps the data or deletes it from the related
monitor table when the job executes. Specify ″1″ to refresh the table, or ″2″ to show
cumulative data.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*----------------------------
MONITOR REPORT
---------------------------*
|
|
| DATABASE
=> SQLDBA____________
MONITOR
=> DBSPACE00001 |
|
|
| ***********************
REPORT PARAMETERS
*********************** |
|
|
| REPORT NAME
=> ________
|
|
|
| CLASS
=> A
|
|
|
| PRI
=> 3
|
|
|
| DISP
=> D (D,H,L,K)
|
|
|
| *************************************************************************** |
|
|
| RESET DATA?
=> 1 (1=YES/2=NO)
|
|
|
|
|
*------------------------------------------------------------------ SQC26 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 37. Monitor Report Option
Types of Monitors
SHOW ACTIVE
Monitors active database users. You can set this monitor to send a message to the
VSE console when it detects an active checkpoint agent or a user agent in
checkpoint, communication, or lock wait.
SHOW LOCK
Monitors database lock contention. You can set this monitor to send a message to
the VSE console whenever it detects a lock holder not processing situation, due to
a checkpoint or any lock contention at all.
SHOW DBEXTENT
Monitors page use for storage pools. You can set this monitor to send a message to
the VSE console if the percentage of the pages in use for any storage pool reaches
the specified threshold.
SHOW LOG
Monitors database log use. You can set this monitor to send a message to the VSE
console if the log reaches or exceeds the percentage used that you specify.
Chapter 11. The Monitor Maintenance Menu
73
The Monitor Maintenance Menu
SHOW CONNECT
Monitors users connected to the database. You can set this monitor to send a
message to the VSE console whenever it detects an active user not processing or
inactive users.
SHOW DBSPACE
Monitors dbspace use. You must specify a dbspace number.
COUNTER *
Monitors the occurrence of key events in the database.
For more detailed information about the DB2 Server for VSE commands, refer to
the DB2 Server for VSE & VM Operation manual.
Invocation
You start monitors from the Monitor Utility Menu. To reach this menu, choose
Option 9 Monitor Utility, from Control Center’s main menu.
How To Use the Monitors
To use the monitors, you:
1. Add a monitor. As part of adding the monitor, you define the conditions you
want to set for the monitor and the database name.
2. Start a kernel for the database to which the monitor has been defined.
3. Work with the information that the monitors give you.
Adding A Monitor
To add a monitor, choose:
1. The name of the database you want to monitor,
2. Option 4 (Add Monitor), and
3. The monitor identifier (number) that you want to add.
Figure 38 on page 75 shows an example of adding the SHOW ACTIVE monitor for
the SQLDBA database.
74
Control Center Operations Guide for VSE
The Monitor Maintenance Menu
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*---------------------------
MONITOR UTILITY
----------------------------*
|
|
| DATABASE
=> SQLDBA
|
|
|
| *************************************************************************** |
|
|
| OPTION
=> 04 01 START KERNEL
02 STOP KERNEL
|
|
03 LIST MONITORS
04 ADD MONITOR
|
|
05 MODIFY MONITOR
06 DELETE MONITOR
|
|
07 DISPLAY MONITOR
08 VIEW DATA
|
|
09 RESET DATA
10 PRINT REPORT
|
|
|
| *************************************************************************** |
|
|
| MONITOR
=> 01 01 SHOW ACTIVE
02 SHOW LOCK
|
|
03 SHOW DBEXTENT
04 SHOW LOG
|
|
05 SHOW CONNECT
06 SHOW DBSPACE 00001
|
|
07 COUNTER *
|
|
|
*------------------------------------------------------------------ SQC40 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT
Figure 38. How to Add a SHOW ACTIVE Monitor
When you press ENTER to process the choices you have made on the Monitor
Menu, you see the Monitor Maintenance menu. The Monitor Maintenance menu
shows you the status of the monitor in the current database. You use this menu to
make a specific monitor active or not. You also can schedule when you want the
monitor to run. You can choose to reset the data the monitor has collected as well
as define and print reports from here.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*-------------------------
MONITOR MAINTENANCE
-------------------------*
|
|
| DATABASE
=> SQLDBA
MONITOR
=> ACTIVE
|
|
|
| DESCRIPTION
=> monitor our active users
|
| ACTIVE?
=> 1
(1=YES/2=NO)
|
|
1 2 3 4 5 6 7
|
| RUN DAYS
=> 2 1 1 1 1 1 2
(1=YES/2=NO)
|
| START TIME
=> 1400
(HHMM)
|
| STOP TIME
=> 1600
(HHMM)
|
| INTERVAL
=> 0005
(HHMM)
|
| RESET DATA?
=> 2
(1=YES/2=NO)
|
| RESET DAY
=> 1
(1-7)
|
| PRINT REPORT?
=> 1
(1=YES/2=NO)
|
|
|
| ************************
REPORT PARAMETERS
************************ |
|
|
| REPORT NAME
=> ACTIVE
CLASS
=> A
|
| PRI
=> 3
DISP
=> D (D,H,L,K)
|
*------------------------------------------------------------------ SQC42 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 39. Adding a Monitor on the Monitor Maintenance menu
The monitor shown above is active and scheduled to run Monday-Friday (days
2-6), every five minutes, from 14:00 to 16:00. Additional options include printing
the report and controlling the resetting of the data collected in the monitor table.
Using the Monitor Maintenance Menu
You can specify:
Chapter 11. The Monitor Maintenance Menu
75
The Monitor Maintenance Menu
Field
Description
DATABASE
Displays the name of the database you specified on
the Monitor Menu.
MONITOR
Shows the status of the monitor.
DESCRIPTION
Enter a brief description of the monitor. (It will be
converted to all uppercase letters.)
ACTIVE?
Deactivates a monitor without having to delete the
monitor definition. Use 1 to activate the monitor
and 2 to deactivate it.
RUN DAYS
Indicates the days of the week on which you want
the monitor to run. You can specify all days,
specific days, or a range of days. Use 1 for YES
and 2 for NO under the number for each day
where 1=Sunday, 2=Monday, 3=Tuesday, and so on.
The figure above shows monitor execution from
Monday through Friday (days 2-6 are set to ″1″).
START TIME
Indicates when the monitor is to start for the day(s)
specified. This is specified as HHMM, for example,
0001 for one minute after midnight. This value
must be earlier than the STOP TIME value.
STOP TIME
Indicates when the monitor is to stop for the day(s)
specified. This is specified as HHMM, for example,
2359 for one minute before midnight. This value
must be later than the START TIME value.
INTERVAL
Indicates approximately how often the monitor
will run during the time period between the start
and stop times. You can schedule the monitors to
run with frequencies from 2 minutes to several
hours. The actual interval may vary, depending
upon the system loading and the time needed for
the task to begin executing.
RESET DATA?
Deletes all rows of data collected by the monitor
from the associated monitor table. Use 1 to delete
all the rows and 2 to keep them.
RESET DAY
Used to indicate on what day to RESET DATA. The
reset takes place at about 23:59. This value is
ignored unless RESET DATA is 1.
PRINT REPORT?
Generates a monitor report and places it directly
on the VSE/POWER List queue.
REPORT NAME
Specifies the name for the monitor report job and
its associated queue entries.
CLASS
Specifies the class (partitions) in which you want
this job to run. The 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 VSE
reader queue. Disposition may be specified as:
76
Control Center Operations Guide for VSE
The Monitor Maintenance Menu
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.
Specifying Monitor Thresholds
When Adding a SHOW ACTIVE, SHOW CONNECT, SHOW DBEXTENT, SHOW
LOCK or a SHOW LOG monitor, you can also specify a threshold for the monitor.
Figure 40 shows how to set the CHECKPOINT WAIT and USER WAIT thresholds
for the SHOW ACTIVE monitor that belongs to database SQLDBA.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------
MONITOR THRESHOLDS
--------------------------*
|
|
| DATABASE
=> SQLDBA
MONITOR
=> ACTIVITY
|
|
|
| ***************************
SHOW ACTIVE
*************************** |
| CHECKPOINT WAIT
=> 1 (1=YES/2=NO)
|
| USER WAIT
=> 1 (1=YES/2=NO)
|
|
|
| ***************************
SHOW CONNECT
*************************** |
| AGENT NOT PROCESSING
=> 2 (1=YES/2=NO)
|
| INACTIVE
=> 2 (1=YES/2=NO)
|
|
|
| *****************************
SHOW LOCK
***************************** |
| CHECKPOINT
=> 2 (1=YES/2=NO)
|
| ANY LOCKING
=> 2 (1=YES/2=NO)
|
|
|
| ***************
SHOW LOG AND SHOW DBEXTENT
*************** |
| PERCENT USED
=> __ %
|
|
|
*------------------------------------------------------------------ SQC42 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 40. SHOW ACTIVE Threshold Specification
To activate threshold checking, for the SHOW ACTIVE, SHOW CONNECT, or
SHOW LOCK monitors, specify ″1″ (YES). To deactivate threshold checking,
specify ″2″ (NO).
To initiate SHOW LOG or SHOW DBEXTENT threshold checking, specify
PERCENT USED as a number between 1 and 99. Figure 41 is a partial screen
image showing how to set the SHOW LOG monitor to issue a VSE console
message if the monitor detects a value of 75% or greater.
Chapter 11. The Monitor Maintenance Menu
77
The Monitor Maintenance Menu
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------
MONITOR THRESHOLDS
--------------------------*
| DATABASE
=> SQLDBA
MONITOR
=> LOG
|
|
|
|
|
| ***************
SHOW LOG AND SHOW DBEXTENT
*************** |
| PERCENT USED
=> 75%
|
*------------------------------------------------------------------ SQC42 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 41. SHOW LOG Threshold Specification
The fields displayed on the Monitor Threshold screen set thresholds for:
Entry Field
Description
CHECKPOINT WAIT
The checkpoint agent is active and is in ″wait″
state.
USER WAIT
User is in a wait state caused by locking,
checkpoint or communication wait.
AGENT NOT PROCESSING Active agent that is not processing.
INACTIVE
User connected but inactive.
CHECKPOINT
Users locked because the database is taking a
checkpoint.
ANY LOCKING
Users locked for any reason.
PERCENT USED
For the SHOW LOG monitor, log use has reached
the percent specified. For the SHOW DBEXTENT
monitor, log use has reached the percent specified.
Starting a Monitor
Once you have defined a monitor, you need to enable it. Choose Option 1, Start
Kernel from the Monitor Utility menu. A kernel must be "started" in each database
for which any monitor is defined before that monitoring activity can take place.
Changing a Monitor
Once you are using a monitor, you may decide you want to change some or all of
its settings. For example, you may decide you want the monitor to execute more
frequently. If you want to modify a monitor, choose Option 5 on the Monitor
Menu. You will see the Monitor Maintenance menu where you can make your
changes to the monitor. Figure 42 on page 79 shows how to enter 2 in the ACTIVE?
field to deactivate the SHOW ACTIVE monitor.
78
Control Center Operations Guide for VSE
The Monitor Maintenance Menu
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*-------------------------
MONITOR MAINTENANCE
-------------------------*
|
|
| DATABASE
=> SQLDBA
MONITOR
=> ACTIVE
|
|
|
| DESCRIPTION
=> MONITOR OUR ACTIVE USERS
|
| ACTIVE?
=> 2
(1=YES/2=NO)
|
|
1 2 3 4 5 6 7
|
| RUN DAYS
=> 2 1 1 1 1 1 2
(1=YES/2=NO)
|
| START TIME
=> 1400
(HHMM)
|
| STOP TIME
=> 1600
(HHMM)
|
| INTERVAL
=> 0005
(HHMM)
|
| RESET DATA?
=> 2
(1=YES/2=NO)
|
| RESET DAY
=> 1
(1-7)
|
| PRINT REPORT?
=> 1
(1=YES/2=NO)
|
|
|
| ************************
REPORT PARAMETERS
************************ |
|
|
| REPORT NAME
=> ACTIVE
CLASS
=> A
|
| PRI
=> 3
DISP
=> D (D,H,L,K)
|
*------------------------------------------------------------------ SQC42 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 42. Updating (Deactivating) a Monitor
Figure 43 shows how to enter 2 to execute the SHOW ACTIVE monitor every two
minutes instead of the five minutes originally defined in Figure 42.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*-------------------------
MONITOR MAINTENANCE
-------------------------*
|
|
| DATABASE
=> SQLDBA
MONITOR
=> ACTIVE
|
|
|
| DESCRIPTION
=> MONITOR OUR ACTIVE USERS
|
| ACTIVE?
=> 2
(1=YES/2=NO)
|
|
1 2 3 4 5 6 7
|
| RUN DAYS
=> 2 1 1 1 1 1 2
(1=YES/2=NO)
|
| START TIME
=> 1400
(HHMM)
|
| STOP TIME
=> 1600
(HHMM)
|
| INTERVAL
=> 2
(HHMM)
|
| RESET DATA?
=> 2
(1=YES/2=NO)
|
| RESET DAY
=> 1
(1-7)
|
| PRINT REPORT?
=> 1
(1=YES/2=NO)
|
|
|
| ************************
REPORT PARAMETERS
************************ |
|
|
| REPORT NAME
=> ACTIVE
CLASS
=> A
|
| PRI
=> 3
DISP
=> D (D,H,L,K)
|
*------------------------------------------------------------------ SQC42 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 43. Updating (Deactivating) a Monitor
Viewing Monitor Data
The View Data Option displays the selected monitor report at the user’s terminal.
An example of the View Data Option for the SHOW ACTIVE monitor is below.
This information is useful in monitoring trends or resource consumption of your
database.
Chapter 11. The Monitor Maintenance Menu
79
The Monitor Maintenance Menu
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*----------------------------
MONITOR DATA
---------------------------*
| DATABASE
=> SQLDBA
MONITOR
=> ACTIVE
|
|
|
|
|
|
COMM LOCK CHKPT PAGE BLOCK I/O
|
|
DATE
TIME NACT NIW R/O R/W NEW WAIT WAIT WAIT WAIT WAIT WAIT
|
| ---------- -------- ---- --- --- --- --- ---- ---- ----- ---- ----- ----
|
| 2000-01-18 16.25.10
3
2
1
0
0
0
0
0
0
0
0
|
| 2000-01-18 17.06.56
3
2
1
0
0
0
0
0
0
0
0
|
| 2000-01-19 16.16.37
3
2
1
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.17.05
3
2
1
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.20.01
3
2
1
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.22.01
3
0
3
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.24.01
3
0
3
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.26.01
3
1
2
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.28.01
3
1
2
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.49.18
3
2
1
0
0
2
0
0
0
0
0
|
| 2000-01-19 16.55.18
3
1
2
0
0
2
0
0
0
0
0
|
| 2000-01-19 17.01.18
3
1
2
0
0
2
0
0
0
0
0
|
*------------------------------------------------------------------ SQC44 ----*
F1=HELP F3=EXIT F4=TOP F5=BOT F7=BWD F8=FWD F10=LEFT F11=RIGHT F12=CANCEL
Figure 44. Monitor Report Data
To understand the monitor measurements, refer to the appropriate sections of the
DB2 Server for VSE & VM Operation manual.
Stopping a Monitor
There are two methods that can be used to stop a monitor. You can disable ALL
monitors in a specific database by selecting the STOP KERNEL option from the
Monitor Utility menu, or you can disable a single monitor by deactivating it from
the Monitor Maintenance menu. To restart all monitors, stopped due to the kernel
being stopped, select the START KERNEL from the Monitor Utility menu, or to
restart a single monitor, reactivate it from the Monitor Maintenance menu. For a
monitor to be active, a kernel must be running in its database.
80
Control Center Operations Guide for VSE
Chapter
12. Table Utility
The Table Utility provides an easy way for you to create a table or view a list of
tables stored in a DB2 Server for VSE database, and select one (or to directly
specify a single table) and then do these DBA-oriented operations on it:
v Drop (delete) a table.
v Reorganize a table (recreating all associated DDL).
- Generate DDL.
- Unload a table to tape or disk in a DBSU internal format.
- Perform a full reorganization on a table.
- Reload a table from tape or disk generated by an Unload.
v Update statistics.
The Table Utility consists of a set of programs, a batch DDL generator program,
and batch jobstreams that execute Database Services Utility (DBSU) commands.
Based on your selections, the Table Utility will:
v
generate the DDL required to recreate a table and the objects related to it,
including:
- Table Definition
- Data
- Referential Integrity constraints
- Unique column definitions
- Indexes
- Views
- Grants
- Table and Column Comments
- Table and Column Labels
- Packages
v
load data in clustering index sequence
v
update statistics
v
reprep invalidated packages
v
support:
- Commitcount use
- table backup (image copy)
- copying a table to a different database
- copying a table to a different owner and/or name
- moving a table to a different DBSPACE or Server
- reloading data into a different table
v
drop a table
v
provide a screen interface to define (create) a table.
Invocation
There are two ways to invoke the Table Utility:
1. on the Control Center Main Menu - select option 10
(TABLE UTILITY) and
press ENTER.
2. directly from CICS, by typing the Table Utility transaction ID ″SQTU″ and
pressing ENTER.
1997, 2003
81
Table Utility
When either of these is done, the Table Utility main menu is displayed.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------------- TABLE UTILITY -------------------------------*
|
|
| DATABASE
=> SQLDBA
|
|
|
| ***********************************************************************
|
|
|
| OPTION
=> _ 1 LIST TABLES
|
|
2 DROP TABLE
|
|
3 REORGANIZE TABLE
|
|
4 CREATE TABLE
|
|
5 UPDATE STATISTICS
|
|
|
| ***********************************************************************
|
|
|
|
|
| CREATOR
=> ________
|
|
|
| TABLE
=> __________________
|
|
|
*------------------------------------------------------------------ SQC60 ----*
PRESS ENTER TO SELECT FUNCTION
ENTER F1=HELP F3=EXIT
Figure 45. Table Utility Main Menu
There are 5 major functions you can select from this menu. They are described in:
“LIST TABLES” on page 83
“DROP TABLE” on page 84
“REORGANIZE TABLE” on page 85
“CREATE TABLE” on page 93
“UPDATE STATISTICS” on page 98
If Option 1 is selected, several choices exist:
v If CREATOR and TABLE are left blank, then all tables in the database will be
listed.
v If one of the CREATOR or TABLE fields is blank or specified as ″ALL″, all tables
with the matching other field will be listed.
Both, or each of the CREATOR and TABLE field values can have a trailing
percent sign (%) to represent a string of zero or more characters in the same way
you can use the LIKE predicate in an SQL statement.
If Option 2, 3, or 5, is selected, an existing database table name (TABLE) and
creator (CREATOR) must be entered (or have been selected by using Option 1, List
TABLES).
If Option 4 is selected, then a CREATOR and non-existing TABLE name must be
entered.
Pressing PF12, RETURN, when available, returns control to the invoking screen.
Pressing PF3, EXIT, returns control to the point from where the Table Utility was
started, the Control Center main menu or CICS.
Functions 2 through 5 produce a job that is submitted to VSE/POWER for
execution. If you code DISP=H on the job submission screens, then you can view,
and if necessary, alter the job for your particular environment.
82
Control Center Operations Guide for VSE
Table Utility
LIST TABLES
The LIST TABLES function is used to select a table to be operated on by the other
functions of the Table Utility.
Using the List Tables function you can search a database for a specific table,
several tables with common NAMEs or CREATORs (using “wildcard”
specification) or all tables. Tables meeting the search criteria are displayed in the
TABLE LIST menu. A specific table can then be selected and its NAME and
CREATOR will be returned to the Table Utility main menu and displayed on it.
The Table List menu is displayed when the Table List Search criteria you specify on
the Table Utility main menu are satisfied. If no tables match the search criteria, a
message is displayed indicating that no such tables can be found.
Additional information (described below) is displayed about the tables. Note that
these statistics may be correct only as of the last statistics update or table
reorganization.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*-------------------------------- TABLE LIST ---------------------------------*
| DATABASE => SQLDBA
|
|
|
| CREATOR TNAME
AVGROWLEN ROWCOUNT NPAGES PCTPAGES NOVERFLOW |
|
|
| _ SQLMSTR APPL_GROUP_TAB
39
1
1
100
|
| _ SQLMSTR COUNTER
0
0
0
0
|
| _ SQLMSTR GROUP_AUTH_TAB
18
1
1
100
|
| _ SQLMSTR MONITOR_CONTROL
142
1
1
100
|
| _ SQLMSTR OBJECT_TAB
36
1
1
100
|
| _ SQLMSTR RAY_TEST
13
1
1
100
|
| _ SQLMSTR RAY_TEST2
13
1
1
100
|
| _ SQLMSTR SHOW_ACTIVE
0
0
0
0
|
| _ SQLMSTR SHOW_CONNECT
0
0
0
0
|
| _ SQLMSTR SHOW_DBEXTENT
0
0
0
0
|
| _ SQLMSTR SHOW_DBSPACE
0
0
0
0
|
| _ SQLMSTR SHOW_LOCK
0
0
0
0
|
| _ SQLMSTR SHOW_LOG
0
0
0
0
|
| _ SQLMSTR SQLMAINT
85
16
1
100
|
*------------------------------------------------------------------ SQC63 ----*
SELECT A TABLE AND PRESS ENTER TO RETURN
F1=HELP F3=EXIT F4=TOP F5=BOT F7=BWD F8=FWD F12=CANCEL
Figure 46. Table List Screen
From this menu you can scroll forward or backward, and to the top or bottom, if
there are multiple menus. You can choose one table by placing an ″X″ (or any
non-blank character) next to the table name and then pressing ENTER to return to
the Table Utility main menu. The chosen table NAME and CREATOR will be
placed in the respective fields of the main menu for use with Table Utility options
2, 3, and 5. For option 4, Create Table, you can use the List function to check that
the planned new table’s name does not exist.
If you press F3, control returns to the Control Center main menu. If you press F12,
control returns to the Table Utility main menu, without changing anything that
was originally on that menu.
The Table List menu (Figure 46) displays these table attributes from
SYSTEM.SYSCATALOG:
Column Label
Content
Chapter 12. Table Utility
83
Table Utility
AVGROWLEN
the average length of the rows in this table
ROWCOUNT
the number of rows in this table as of the last
statistics update
NPAGES
the number of pages in the DBSPACE in which
rows of this table appear
PCTPAGES
the approximate percentage of the active pages in
the DBSPACE that have rows from this table in
them
NOVERFLOW
the number of rows that have overflowed from
their original page in storage to another page
List Tables Processing Flow
When you choose the LIST TABLES function, Control Center reads the system
catalogs to build a list of tables. Values you specify as CREATOR and TABLE name
are passed to SQL to use as filters.
DROP TABLE
This option provides you with an easy method of dropping (deleting) a table.
On the Table Utility main menu, you can directly specify the table to be dropped
or you can use the Table List function, option 1, to identify the table. Then, select
option 2, Drop Table. You will be prompted with a message asking for
confirmation of the DROP TABLE request as shown in Figure 47. You can then
cancel the request (F12) or proceed with the drop (F10).
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------------- TABLE UTILITY -------------------------------*
|
|
| DATABASE
=> SQLDBA
|
|
|
| ***********************************************************************
|
|
|
| OPTION
=> 2 1 LIST TABLES
|
|
2 DROP TABLE
|
|
3 REORGANIZE TABLE
|
|
4 CREATE TABLE
|
|
5 UPDATE STATISTICS
|
|
|
| ***********************************************************************
|
|
|
|
|
| CREATOR
=> SQLMSTR
|
|
|
| TABLE
=> COUNTER
|
|
|
*------------------------------------------------------------------ SQC60 ----*
PRESS F10 TO VERIFY DROP OR F12 TO CANCEL
ENTER F1=HELP F3=EXIT
Figure 47. Table Utility Main Menu with Prompt for Verifying a Drop Request
Drop Table Processing Flow
84
Control Center Operations Guide for VSE
Table Utility
When you choose the drop table option and have entered a table’s CREATOR and
NAME, Control Center issues a SQL DROP TABLE statement for immediate
execution. If the drop is successful, message REQUEST SUCCESSFULLY
PROCESSED, will be displayed; if not, an SQL error message will be displayed.
REORGANIZE TABLE
The Reorganize Table function is used to simplify your work for backup and
restore, table reorganization, and saving table structure information. The function
provides four options; see Figure 48 on page 86.
You can choose one of the following options:
1 - GENERATE DDL
Generates from the database all of the DDL required to recreate the table
and the objects it contains, including indexes, views, and grants. The DDL
is placed in the punch queue for inspection, alteration, or later use.See
Figure 50 on page 91.
2 - UNLOAD TABLE
Generates DDL (as in Option 1) and writes it to a VSAM file. Then, a
DBSU UNLOAD TABLE step is executed that writes the table 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 generated DDL can
be used as the basis for a subsequent RELOAD TABLE (Option 4) job. The
table is not dropped. An example of an UNLOAD TABLE job created to do
this is in Figure 61 on page 120.
3 - REORGANIZE TABLE
Results in a full table reorganization. A jobstream is created that generates
the DDL, UNLOADS the table, DROPs, recreates, and RELOADS the
TABLE. Error recovery logic is included. An example of a REORGANIZE
TABLE job is in Figure 62 on page 121.
4 - RELOAD TABLE
Submits a job to recreate and reload a table that has been unloaded by
Option 2. This is basically a table recovery facility. An example of the job
created to do this is in Figure 63 on page 124.
Each of the options is discussed in detail below and is accompanied by a sample
JCL stream created by the Table Utility.
Chapter 12. Table Utility
85
Table Utility
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*----------------------- TABLE REORGANIZATION UTILITY ------------------------*
| DATABASE
=> SQLDBA____________
|
| CREATOR
=> SQLDBA__
|
| TABLE
=> ACTIVITY__________
|
| FILE
=> 1 (1-3)
|
| OPTION
=> 3 (1 GENERATE DDL
2 UNLOAD TABLE)
|
|
(3 REORGANIZE TABLE
4 RELOAD TABLE)
|
| ***********************
OPTIONAL PARAMETERS
*******************
|
| DATABASE
=> __________________
|
| OWNER
=> ________
|
| DBSPACE
=> __________________
|
| CREATOR
=> ________
|
| TABLE
=> __________________
|
|
|
|
|
| REBIND PACKAGE => 1 (1=YES/2=NO)
UPDATE ALL STATISTICS => : 2 (1/2) |
| COMMITCOUNT
=> __________
DATA CAPTURE
=> : none
|
|
|
| TLBL FILE-ID
=> _________________
|
*------------------------------------------------------------------ SQC61 ----*
PRESS ENTER TO PROCESS
F1=HELP F3=EXIT
Figure 48. Table Reorganization Screen
The following is a summary of the various processing options:
1.
Generate DDL
If the Generate DDL option is specified, the utility will generate the table create
statement and all associated database objects, including indexes, views, and
grants.
2.
Unload Table
This option creates a backup copy of a table using the UNLOAD TABLE DBSU
command. The DDL will also be generated for backup purposes. The table will
not be dropped.
3.
Table Reorganization
If only the source database and table (at the top of the menu) are specified
(meaning that this is not a migration), the utility will perform a table
reorganization by generating a job to:
a. generate the DDL for recreating the table and its objects,
b. unload the data to a VSAM file,
c. copy the VSAM file to a SAM file,
d. drop the table,
e. recreate the table,
f. reload the table,
g. and generate the table objects, such as indexes, etc.
When data and DDL are both generated for a reorganization, the program has
DBA authority for copying all dependent objects within the database (including
those of other users). Control Center will copy the data using UNLOAD
TABLE, will DROP and CREATE the table, RELOAD TABLE to reload the data,
and then recreate all dependent objects (indexes, views, and grants).
The presence of optional parameters affects the ultimate type of reorganization
selected. These are:
v Table Migration
The utility will migrate data or table DDL between databases if the
“optional” DATABASE parameter is specified. The source table will not be
dropped or modified in any way.
86
Control Center Operations Guide for VSE
Table Utility
v
Table Copy
The utility will copy the DDL and data if the “optional” CREATOR and/or
TABLE parameter are specified. The target table will be dropped before being
defined. The source table will not be dropped or modified in any way. It is
important to note that if the target CREATOR is the same as the source table
CREATOR, the INDEX and VIEW create names will be identical to the
existing ones in the old table; the create statements will fail. A warning
message will be displayed to this effect and then you can confirm whether to
proceed or cancel processing.
v
Table Move
The utility will move a table to a new DBSPACE if the optional OWNER
and/or DBSPACE parameters are specified. Note that the old table will be
dropped only if the new CREATOR and TABLE name remain the same as
the old CREATOR and TABLE.
If you are moving to a database created prior to DB2 Version 5.1:
- Control Center cannot access the passwords on that database. This means
that for any grants for which a password is needed, the password from
the old database (at the top of the menu) will be used if it exists. This may
cause the connect in the new data base to fail if the password is different.
If there is no password on the old database, one is generated for you. This
would be executed on the new database, thereby changing your password
if it existed on the new database.
- If the TABLE NAME or OWNER change, any view text referencing the old
table will not change. This means that the view create may fail if the table
name is used in the view and it does not exist in the new database.
4.
Reload Table
This option reloads a table from a previous backup made with the UNLOAD
TABLE function. The unloaded table resulting from a reorganization function
that failed can also be used as input to this option.
Reorganize Table Processing Flow
When you submit a table reorganization job, Control Center:
1. Links and establishes communication to the target server.
2. Connects as user SQLREORG.
3. Verifies the availability of the new DBSPACE (if specified).
4. Gathers system catalog information about the specified table and creates
corresponding DDL statements in the DBSU command file:
a. Table create statements
b. Table comments
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. Unloads the table data to the specified disk or tape.
6. Executes the SQLDBSU command file from the Database Services Utility to
reorganize the table and rebind any dependent packages.
Chapter 12. Table Utility
87
Table Utility
Special Processing Considerations:
1.
In order to retain hierarchical dependencies, Control Center issues all grants in
the same chronological order in which they were originally issued.
2.
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.
3.
The database server does not remove grant information from the system
catalogs when a user is removed from the SYSTEM.SYSUSERAUTH table.
Consequently, the REORGANIZATION 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 you and later revokes it.
|
4.
Referential Integrity Considerations:
v If the table being reorganized has a deactivated primary key, any dependent
tables (that is, those with foreign keys) will not be able to have their foreign
keys created.
v If the table being reorganized has an implicitly deactivated foreign key, it
cannot be created.
v If the table being reorganized has an explicitly deactivated foreign key, it can
be created, then deactivated, IF the parent table’s primary key is active.
v If the table being reorganized has an active primary key, any dependent table
with an explicitly deactivated foreign key may be able to have its foreign key
created AFTER the primary key table is loaded. The create will fail if the
dependent table’s data is not correct. If the create is successful, the key will
then be deactivated.
Table Reorganization Menu Required Parameters
To reach the Table Reorganization menu shown in Figure 48 on page 86, choose
Option 3 on the Table Utility main menu.
When the menu is displayed, the DATABASE, CREATOR and TABLE fields at the
top of the menu will be filled in from the Table Utility main menu.
When you installed Control Center, you defined one to three SAM DDL files to
hold generated DDL. Specify the number of the file you want to use in the FILE
field. (1 selects the small file, 2 selects the medium file, 3 selects the large file.) The
number also indicates what SAM data file to use if you have not entered a Tape
File Name. You do not need to specify the file number if you choose Option 1 or 2,
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.
Table Reorganization Menu Optional Parameters
Parameters below the ″OPTIONAL PARAMETERS″ line are not required.
DATABASE
The name of a different database into which the table is reloaded. Use of
this parameter lets you migrate a table from one database to another. For
example, you can migrate a table from a development database to a
production database. Before you migrate the table, you may want to ensure
that the two databases are compatible so that all reload statements execute
successfully. When you use the optional DATABASE parameter, the table in
the old database remains unchanged.
88
Control Center Operations Guide for VSE
Table Utility
OWNER/DBSPACE
If used, these two parameters must be used as a pair; however, if you omit
one of the two, Control Center provides a default value. They indicate that
you want to specify a new DBSPACE for the table.
If the new CREATOR/TABLE option is used, a copy will be performed. If
a new CREATOR/TABLE is not specified, the table will be moved from the
current DBSPACE to the new DBSPACE.
CREATOR/TABLE
If used, these two parameters must be used as a pair; however, if you omit
one of the two, Control Center provides a default value. They indicate that
you want to specify a new TABLE name (and CREATOR) for the DDL
and/or data from the old table. Use of this option pair will cause the
source table to be copied, (when using options other than 1 - Generate
DDL) with the source table remaining unchanged.
Note: The table view names and index names will not be changed, causing
failure of the DDL CREATE statements if the original table
CREATOR is the same as the new CREATOR.
REBIND PACKAGE
Once a table has been reloaded, the Table Utility rebinds all PACKAGES
that are dependent on that table. To bypass package rebind processing,
specify 2 (NO). The default value is 1 (YES). This option is not valid when
the OWNER name is changed.
UPDATE ALL STATISTICS
By default, UPDATE STATISTICS is done for a table during reload and
during index create. Statistics (including the number of rows and number
of unique values in a column) are, by default, gathered only for columns
that appear as the first column in an index. To update the statistics for all
the columns in all of your indexes, specify 1 (YES) for this parameter.
Note: To perform periodic statistics updates, use the Table Utility main
menu Option 5, Update Statistics.
When this parameter is set to NO, the statistics are reset only for the first
column in each index. When set to YES, the statistics are reset for all
columns in all indexes. The statistics include such things as the number of
rows in a table and the number of unique values in an index column. They
are used by SQL to optimize retrievals. Since this is can be a lengthy and
resource consuming process, it is recommended that you do not reset all of
the statistics during normal work hours. If you are reloading a table, this is
an optimal time to reset ALL of the statistics.
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.
TAPE FILE NAME (TLBL FILE-ID)
Used to specify that data should be unloaded to tape instead of disk. The
tape file must have been defined by the WORK FILE LABEL DEFINITION
tool Chapter 6, “Work File Label Definition Tool” on page 39. This field
does not apply to DDL because DDL is always unloaded to disk.
Chapter 12. Table Utility
89
Table Utility
DATA CAPTURE
Allows changing or adding the attribute to a table for DB2 Release 5.1 (or
later) databases. The default is to use what is currently in the catalog for
the table. The value “CHANGES” can be used to change the current value
held in the catalog. This affects how much information is retained in the
log when a table is changed.
After entering the desired parameters, press ENTER to proceed to the Table
Reorganization Submit Screen.
Using the TABLE REORGANIZATION Option
The Table Reorganization option can be used in a variety of ways to achieve
different goals. Each of the options is discussed in more detail.
Option 1 - GENERATE DDL
By reading the catalogs, this option generates the DDL necessary to recreate a table
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 recreate the table. 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 49 is an example of the jobstream produced by Control Center to generate
DDL for the SQLDBA.ACTIVITY table.
|
* $$ JOB JNM=GENDDL,CLASS=0,DISP=D,PRI=9
|
* $$ LST CLASS=A,PRI=3
|
* $$ PUN PRI=3
|
// JOB TRAY2 GENERATE DDL FOR TABLE "SQLDBA"."ACTIVITY"
|
// OPTION LOG
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
* STEP0001 UNLOAD DDL
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
// DLBL SQMTPRM,’SQLMSTR.TABLE.PARMS’,,VSAM,CAT=SQMCAT
|
// DLBL SQMDDL,’L.DB2730.EXAMPLE.ROUTINE’,0,VSAM,
X
|
RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT
|
// ASSGN SYS005,SYSRDR
|
// ASSGN SYS006,SYSPCH
|
// ASSGN SYS011,SYSLST
|
// EXEC SQB60,SIZE=AUTO
|
%%SQLDBA
SQLDBA ACTIVITY
1 N SQLDBA SQLDBAPW
|
/*
|
/&
Figure 49. Table Reorg Option 1 (Generate DDL) - Sample Jobstream
Option 2 - UNLOAD TABLE
This option generates the DDL necessary to recreate a table and writes it to a
VSAM file. It then unloads the table to a SAM disk file (or a tape if a tape label
was specified). The SAM data file is then REPRO’d to a VSAM-managed SAM file
for more permanent retention. Data is unloaded in system-defined format; you
must make sure that this data file is not altered prior to reloading the table. This
option is essentially a table backup. Used in conjunction with a RELOAD TABLE
(Option 4), it provides the capability to recover from application errors.
90
Control Center Operations Guide for VSE
Table Utility
Figure 61 on page 120 shows a jobstream generated by Control Center to unload
the SQLDBA.ACTIVITY TABLE.
Option 3 - REORGANIZE TABLE
This is the most comprehensive option of the reorganization tool. It schedules a
full table reorganization, including generating all related DDL and executing it.
Depending on the optional parameters chosen, a table can be migrated to another
DBSPACE or another owner, the table name can be changed, and the table can be
moved to another database.
Figure 62 on page 121 shows a jobstream generated by Control Center to reorganize
the SQLDBA.ACTIVITY table.
Option 4 - RELOAD TABLE
This option generates a job to reload a table previously unloaded or reorganized
using Control Center. The previously created DDL and data files are used to
recreate the table in its entirety. This option is the recovery counterpart to the
UNLOAD TABLE option, (Option 2), and is the method of recovering from an
error during a reorganization reload step.
Figure 63 on page 124 shows a jobstream generated by Control Center to reload the
SQLDBA.ACTIVITY table.
Table Reorganization Submit Screen
Figure 50 shows the Table Reorganization Submit Screen.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*-------------------- TABLE REORGANIZATION SUBMIT SCREEN ---------------------*
|
|
| JOBNAME
=> ________
CLASS
=> A
|
|
|
| PRI
=> 3
DISP
=> D (D,H,K,L)
|
|
|
| FROM
=> ________
LST CLASS => A
|
|
|
| DUETIME
=> ____ (HHMM)
DUEDATE
=> ______
(AABBYY)
|
|
|
| DUEDAY
=> __________________________________________
|
|
|
|
|
| OTHER
=> ____________________________________________________________
|
|
|
|
|
|
|
|
|
|
|
*------------------------------------------------------------------ SQC62 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 50. Table Reorganization Submit Screen
To reach this menu, press ENTER from the Control Center Table Reorganization
menu.
Table Reorganization Job Submission Screen Parameters
On each of the job submission screens (Table Reorganization, Create Table, and
Update Statistics), there are parameters that are required, that are “required” but
have defaults, and some that are optional. The following parameter descriptions
apply to ALL of the Table Utility job submission screens.
Chapter 12. Table Utility
91
Table Utility
JOBNAME
Specifies the job name for the Table Reorganization job and its associated
queue entries. The JOBNAME parameter is the only parameter that you
must enter because there are default values for the other parameters that
are required.
CLASS
Specifies the class or partition in which you want this job to run. This
parameter is required; its default value is A.
PRI
Specifies the priority to be assigned to the job. Specify a number from 0 to
9 where 9 is the highest priority. This parameter is required; its default
value 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
This parameter is required; its default value is D.
Note: If you enter data for any of the Table Utility functions that cause job
submission and the job fails, you can easily resubmit the job.
However, this is not completely true when creating a table because
you may have entered a lot of data to define the table. There is no
way to “reload” that data entry menu, but, there is a way to recover.
If you use DISP=K, then the job is kept in the VSE/POWER queue
after the job ends, whether it is successful or not. If it is not
successful, you can edit the job, fix the problem, and resubmit the
job. If you use DISP=K, be sure to delete the job from the queue
when you are done.
FROM
Specifies the ID of the user being allowed to manipulate or retrieve the job.
This parameter is required. It defaults to the CICS User ID, even though
that is not displayed on the screen.
LST CLASS
Defines the class to be assigned to printed output. The value of this
parameter is used on a VSE/POWER $$ LST card. Specify any letter of the
alphabet, or any number 0 through 9. This parameter is required; its
default value is A.
DUETIME
Specifies the job processing start time using HH for hour and MM for
minute in 24-hour clock time (OPTIONAL).
DUEDATE
Specifies the date on which the job is to be run, using YY for the year.
Depending on the format defined for your system, AA is the month and
BB is the day, or AA is the day and BB is the month (OPTIONAL).
DUEDAY
Specifies the day(s) the job is to be scheduled. You can enter a day name or
abbreviation such as MON for Monday, or a list of these separated by
commas and enclosed in parentheses. You can enter the number of the
month or a list of day numbers separated by commas and enclosed in
92
Control Center Operations Guide for VSE
Table Utility
parentheses, and can also specify DAILY to schedule the job every day of
the year. Certain combinations of the date and time parameters specify
repeated processing. The VSE/POWER Installation and Operations Guide
manual explains the logic used to control repeated processing
(OPTIONAL).
If you schedule repetitive processing, you can cancel the job whenever
necessary by displaying the POWER READer queue and deleting the job.
OTHER
The VSE/POWER * $$ JOB card offers many parameters that do not
appear on the Table Utility job submission screens. 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, see the VSE/POWER
Installation and Operations Guide. After submitting any Table Utility job, control
returns to the screen from which job submission was selected.
CREATE TABLE
The Create Table function provides you with an easy, interactive, interface for
defining DB2 tables and having them created by the Table Utility. You are
presented with a Create Table screen (see Figure 51).
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------------- CREATE TABLE -------------------------------*
| DATABASE
=> SQLDBA
|
| CREATOR
=> SQLDBA
OWNER
=> ________
|
| TABLE
=> ACTIVITY2
DBSPACE
=> _________________
|
|
|
| COL CNAME
COLTYPE
LENGTH NULLS (1/2)
|
|
001 __________________ _______________ _____
_
|
|
002 __________________ _______________ _____
_
|
|
003 __________________ _______________ _____
_
|
|
004 __________________ _______________ _____
_
|
|
005 __________________ _______________ _____
_
|
|
006 __________________ _______________ _____
_
|
|
007 __________________ _______________ _____
_
|
|
008 __________________ _______________ _____
_
|
|
009 __________________ _______________ _____
_
|
|
010 __________________ _______________ _____
_
|
| ***********************
OPTIONAL PARAMETERS
*******************
|
| GRANT SELECT TO PUBLIC => 2 (1=YES/2=NO)
DATA CAPTURE => NONE___ |
| COMMENT ON TABLE => _____________________________________________________
|
*------------------------------------------------------------------ SQC65 ----*
ENTER DATA AND A VALID FKEY TO CONTINUE PROCESSING
ENTER F1=HELP F3=EXIT F8=FWD F12=CANCEL
Figure 51. Create Table Data Entry Screen
Create Table Processing Flow
Screen rows are scanned from the top to the bottom (including the DATABASE,
CREATOR, OWNER, TABLE, and DBSPACE). If any errors are detected, an error
message is issued; when processing resumes, the top-to-bottom scan restarts at the
top of the screen.
A data column must have a name (CNAME), column type (COLTYPE), and if
required by the column type, a length (default lengths are described just below).
Chapter 12. Table Utility
93
Table Utility
An embedded blank row (column definition) is not allowed. When you press
ENTER, the SQL statements corresponding to the table structure are generated and
made ready for job submission.
Using the Create Table Function
Entering a Column’s Data
Note: In the remainder of the Table Create discussion, the term row refers to a row
on the screen; this is synonymous with table column.
Each data column to be defined for the table is specified on a row on the CREATE
TABLE screen. Each row on the screen must be completed (at a minimum, have a
column name, type, and length if required) before you can scroll down to the next
screen. No intermediate, empty, rows are allowed. Each CREATE TABLE entry
screen has space to enter data for 10 data columns; when 10 entries are made, you
can scroll down to make the next 10 entries. Before you can scroll down, the
entries you have made must be correct for the fields you have entered. When you
scroll away from a screen on which you have entered data, Control Center, if you
have not entered a length, will fill in the length field with the SQL default length
length for the specific data type, such as, character fields default to length 1.
Note: SQL has a limitation on the length of a row in a database table. The
CREATE TABLE statement syntax (in the DB2 Server for VSE & VM SQL
Reference manual) describes the limitation. The limit is approximately 4K
bytes, however, variable length, and large character strings, each require
only a few bytes of the 4K, so this limit is usually not significant.
There are many infrequently used SQL options that you may want to specify when
defining a table column, such as UNIQUE. Control Center cannot directly support
these due to their number and complexity; however, there is a way for you to
specify them. In the description of the “job submission DISP parameter” on
page 92, note that you can specify that after a job is submitted, it should be held
rather than run. When you create a table and you need to include specifications
that Control Center does not directly support, use DISP=HOLD. Then, you can
copy the job from the VSE/POWER queue to the ICCF library, edit it, add the
additional specifications, and then resubmit the job.
Control Center does not directly support specifying a primary key, although you
can do this by using DISP=HOLD as described immediately above. The designers
of Control Center assume that if you are creating a table, you will usually follow
that with a bulk load. Existence of a primary key when a table is initially loaded
can cause serious performance degradation. It is much more efficient to do the
load first and then specify the primary key.
When You Think You’re Done
DO NOT PRESS ENTER UNTIL YOU HAVE ENTERED ALL OF THE COLUMN
DATA YOU PLAN TO ENTER FOR ALL OF THE ROWS.
Pressing the ENTER key indicates that you have completed entering the table’s
definition. If no errors are found, the CREATE TABLE job submission screen (see
Figure 53 on page 97) is displayed. If you accidentally press ENTER, when the
CREATE TABLE job submission screen is displayed, press PF12 to cancel the job
submission and return to the CREATE TABLE screen.
94
Control Center Operations Guide for VSE
Table Utility
Inserting and Deleting Data Columns
If you have not yet submitted the table create job, you can change, insert, and
delete rows. Suppose that you have specified columns 1 through 10, used PF8 to
scroll to a second screen, and have specified additional columns. At this time, you
decide to make changes to rows specified on the first screen. Press PF7 to return to
the previous screen. If there are no errors in the specification of the rows on the
current screen, the previous screen will be displayed. If there are errors, they must
be fixed before you can scroll.
You will now notice that a new field, with no heading, has been added to the
screen. Located to the left of the column number (COLNO) field, this field is used
for indicating that you want to insert or delete a column. The field has no
meaningful use when an empty CREATE TABLE screen is first presented, so it
does not appear the first time a screen is displayed for column data entry. You
must scroll away from a screen and then back to it for the new field to be
displayed.
You can use this field to insert or delete a row. To delete a row, type a 2 in the new
field at the left end of the row. To insert a new row ahead of another, type a 1 in
the new field and then overtype the row’s data with the new information; the
original row’s data will not be changed. In addition to inserts and deletes, you can
also overtype existing fields that you want to change, and, finish any partially
complete entries; over-type changes are automatically recognized by Control
Center.
Pressing ENTER after INS or DEL displays the changed screen. You must press
ENTER again if you are finished with the table’s definition.
Note: When you do an insert, under some conditions, a row of data that is pushed
down onto the next set of ten entries may be marked internally as not being
processed; if this happens, a message is displayed identifying the
unprocessed row (data column). To clear the message and proceed, scroll
down until the unprocessed row is visible on the screen. Then continue
what you were doing. If the message was displayed because you pressed
ENTER, press it again. If there is another unprocessed row, the message will
be displayed again. Scroll down until it is displayed and continue what you
were doing. Scrolling off a screen with such a row or pressing ENTER will
cause it to be processed.
When you have completed all of the changes on a screen, press a scroll key, or if
you are finished with the table definition, press ENTER to actuate the inserts,
deletes, and changes. Control Center will process the new information on the
screen, inserting and deleting rows, and changing any other data you have entered.
Error checking is performed as usual and the screen is re-displayed with deleted
rows removed, inserted rows added, and any other changes made. Inserts and
deletes cause automatic renumbering of the rows. Make these types of changes
carefully; there is no way to undo a change other than retyping the original data,
or using the DISP=HOLD technique.
OK, When You Really Are Done
When you have completed all of the column definitions, press ENTER to display
the CREATE TABLE job submission screen. When the job is submitted, Control
Center creates the JCL and DDL statements to define the table, and submits the job
to the VSE/POWER queue for execution.
Chapter 12. Table Utility
95
Table Utility
Create Table Parameters
The DATABASE, CREATOR and TABLE fields are required and are automatically
filled in from the Table Utility main menu. The COLNO parameter is initialized to
column numbers 1 - 10 on the first screen displayed. When the screen is scrolled
forward, columns 11 - 20 will be displayed (and so on with any further screen
entries). A maximum of 255 column entries is allowed.
The OWNER and DBSPACE parameters are required because they are needed in
the CREATE TABLE DDL statement generated by Control Center.
Parameters below the ″OPTIONAL PARAMETERS″ line are not required.
These fields are used to define the table to be created:
CNAME
This is the column name.
COLTYPE
The datatype of the column. Valid types are those defined in the CREATE
TABLE statement in the DB2 Server for VSE & VM SQL Reference manual.
Abbreviations allowed by SQL are also allowed here. Note that the DB2
Server for VSE & VM SQL Reference manual does not show that DEC is a
valid abbreviation for DECIMAL, although Control Center accepts it.
LENGTH
A LENGTH is required for VARCHAR and VARGRAPHIC data types only.
All other data types have a default length (or precision) specified in the
DB2 Server for VSE & VM SQL Reference manual. Unless you specify a
LENGTH value, the SQL defaults will be used.
You can add additional column specification parameters if necessary. To do
this, when you submit the Create Table job, use DISP=H as the disposition
parameter on the Create Table Submit Screen. Then, you can remove the
job from the READER queue, edit it, add the additional parameters, and
resubmit the job for execution.
NULL Specify ″1″ if the column allows NULLS, ″2″ if the column may not be
NULL. This field defaults to ″1″.
These are the ″OPTIONAL PARAMETERS″:
GRANT SELECT TO PUBLIC
Specifying a 1 (YES) for this parameter will cause
the statement to be generated in the DDL following
the CREATE TABLE (OPTIONAL).
DATA CAPTURE
This field causes the table to be defined with
DATA CAPTURE NONE (the default), or DATA
CAPTURE CHANGES if CHANGES is specified. It
affects how much information is retained in the log
when a table is changed.
COMMENT ON TABLE
This field allows you to add a comment to the
generated table. The COMMENT ON TABLE
statement will be generated in the DDL. The
comment may not contain apostrophes or
quotation marks. It is converted to all uppercase
letters.
96
Control Center Operations Guide for VSE
Table Utility
Figure 52 is an example of a filled in Create Table screen.
After entering the desired parameters and column descriptions, press F8 to scroll
forward for entry of more column definitions or press ENTER to proceed to the
Create Table Submit screen.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------------- CREATE TABLE --------------------------------*
| DATABASE
=> SQLDBA
|
| CREATOR
=> SQLDBA
OWNER
=> owner
|
| TABLE
=> ACTIVITY2
DBSPACE
=> dbspacename
|
|
|
| COL CNAME
COLTYPE
LENGTH NULLS (1/2)
|
|
001 name-last
char
20
|
|
002 name-first
char
25
|
|
003 name-middle
char
25
1
|
|
004 title
char
10
|
|
005 address 1
char
25
|
|
006 address 2
char
25
1
|
|
007 address 3
char
25
1
|
|
008 city
char
25
|
|
009 state
char
2
|
|
010 zip-5
char
5
|
| ***********************
OPTIONAL PARAMETERS
*******************
|
| GRANT SELECT TO PUBLIC => 2 (1=YES/2=NO)
DATA CAPTURE => NONE
|
| COMMENT ON TABLE =>
|
*------------------------------------------------------------------ SQC65 ----*
ENTER DATA AND A VALID FKEY TO CONTINUE PROCESSING
ENTER F1=HELP F3=EXIT F4=TOP F5=BOT F7=BWD F8=FWD F12=CANCEL
Figure 52. Create Table Data Entry Screen
When you press ENTER, the Create Table Submit screen is displayed; see
Figure 53.
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*------------------------ CREATE TABLE SUBMIT SCREEN -------------------------*
|
|
| JOBNAME
=> ________
CLASS
=> A
|
|
|
| PRI
=> 3
DISP
=> D (D,H,K,L)
|
|
|
| FROM
=> ________
LST CLASS => A
|
|
|
| DUETIME
=> ____ (HHMM)
DUEDATE
=> ______
|
|
|
| DUEDAY
=> __________________________________________
|
|
|
|
|
| OTHER
=> ______________________________________________________________ |
|
|
|
|
|
|
|
|
|
|
*------------------------------------------------------------------ SQC66 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 53. Create Table Submit screen
The first parameter, JOBNAME, is the only one that is required because the other
parameters have default values.
Chapter 12. Table Utility
97
Table Utility
For a detailed description of the job submission parameters, see the description of
the job submission parameters on page 91.
Note: In the discussion of the “job submission DISP parameter” on page 92, be
sure to note that using DISP=K as a means of recovering from some of the
errors you might make when creating a table.
A job return code of 6 is normal.
UPDATE STATISTICS
This function provides you with the capability of issuing the UPDATE STATISTICS
or UPDATE ALL STATISTICS commands in a background DBSU job.
When you select Option 5, Update Statistics, on the Table Utility main menu, the
Update Statistics Submit screen is displayed (Figure 54).
mm/dd/yyyy
CONTROL CENTER
hh:mm:ss
*---------------------- UPDATE STATISTICS SUBMIT SCREEN ----------------------*
|
|
|********************
VSE/POWER JOB PARAMETERS
*******************
|
|
|
| JOBNAME
=> ________ CLASS
=> A PRI => 3
DISP
=> D (D,H,L,K)
|
|
|
| FROM
=> VS52____ DUETIME => ____ (HHMM)
DUEDATE => ______
|
|
|
| DUEDAY
=> __________________________________________
|
|
|
| LST CLASS => A
|
|
|
| OTHER
=> ______________________________________________________________ |
|
|
| **************************
JOB OPTIONS
************************** |
|
|
| UPDATE ALL STATISTICS => 2 (1=YES/2=NO)
|
|
|
|
|
*------------------------------------------------------------------ SQC64 ----*
PRESS ENTER TO PROCESS
ENTER F1=HELP F3=EXIT F12=CANCEL
Figure 54. Update Statistics Submit Screen
Update Statistics Submit Screen Required Parameters
The first parameter, JOBNAME, is the only one that is required. For a detailed
description of the job submission parameters, see “job submission parameters” on
page 91.
Update Statistics Submit Screen Optional Parameters
UPDATE ALL STATISTICS Specifies whether ALL should be added to the
UPDATE STATISTICS command. 1 indicates YES, 2
indicates NO. When this parameter is set to NO,
the statistics are reset for the first column in each
index. When set to YES, the statistics are reset for
all columns in all indexes. The statistics include
such things as the number of rows in a table and
the number of unique values in an index column.
They are used by SQL to optimize retrievals. Since
this is can be a lengthy and resource consuming
98
Control Center Operations Guide for VSE
Table Utility
process, it is recommended that you do not reset
all of the statistics during normal work hours.
Note: You should update all statistics on a periodic
basis for tables that are subject to frequent
change.
After entering the desired submit parameters, press ENTER to submit the job to
VSE/POWER.
Chapter 12. Table Utility
99
100
Control Center Operations Guide for VSE
Chapter
13. Installing Stored Procedures Support
DB2 UDB’s Web Control Center, running on your workstation, can be used to
perform certain tasks using a DB2 Server for VSE & VM database running on a
"host" computer. Control Center for VSE and VM provides stored procedures that
are used to do that type of processing. The stored procedures are invoked by
UDB’s Web Control Center to process DB2 commands such as Reorganize Index
and Rebind Package.
Web Control Center stored procedures support requires Version 7 or greater of DB2
UDB, DB2 for VSE, and Control Center for VSE. If you are planning to use DB2
Web Control Center, then perform the process described below. If you will not be
installing DB2 Web Control Center, then the following process is not needed.
This optional procedure is used to install two IBM-supplied stored procedures.
One can be used to Reorganize an Index and to Rebind a Package; the other is
used as part of the Visual Explain services.
To use stored procedures, you must define a stored procedure server (part of your
DB2 installation and tailoring process) and then inform DB2 about the stored
procedures you want to execute. The stored procedures must be accessible to the
stored procedure server at execution time. Stored procedures are explained in
detail in the DB2 Server for VSE & VM Database Administration manual.
In this step, you will:
1. Define the stored procedures to DB2. (Step 1 in SQMSPRC1)
2. Linkedit the procedures into the Control Center library. A linkedit is necessary
because the Language Environment (LE) used by the Control Center
development team may be different than that you are using. (Step 2 in
SQMSPRC1)
3. Load the stored procedure packages into the databases that will use them. (Step
3 in SQMSPRC1)
4. Make the Control Center library accessible to the stored procedure server.
To install the support, punch SQMSPRC1.Z from your Control Center installation
library, tailor it for your installation and submit it for execution.
Note: The SQMSPRC1 job must be re-run for each database that will use these
stored procedures. Each time you re-run it, the stored procedures’ phases
will be linkedited into the Control Center library again although the rest of
the job will involve a different database each time. Setting up the job in this
manner involves a little extra CPU time, but simplifies your installation
process.
Executing this process does not START the stored procedure server nor any stored
procedures; it only enables those actions.
There are a number of stored procedure server initialization procedures that are
required, including previously defining a stored procedure server. See the DB2
Server for VSE & VM Database Administration manual for information about these.
To complete this step:
1997, 2003
101
Stored Procedures Support
1. Punch SQMSPRC1.Z and edit.
2. Modify EACH procedure used to start a stored procedure server so that it can
refer to your Control Center library. The sample procedure is in the DB2 Server
for VSE & VM Database Administration manual which discusses Stored
Procedures.
$ $$ JOB JNM=SQMSPRC1,CLASS=0,DISP=D,PRI=9
$ $$ LST CLASS=Q
// JOB SQMSPRC1
INSTALL THE STORED PROCEDURES
04/22/2000
// LIBDEF *,SEARCH=PRD2.DB2710
* ********************************************
* NOTE: YOU MUST HAVE AT LEAST ONE PSERVER DEFINED TO DB2 BEFORE
RUNNING THIS PROCESS!
* ********************************************
* CHANGE ALL PARAMETERS ENCLOSED IN <> AND REMOVE THE <>.
* THE PARAMETERS ARE:
* DATABASE NAME
(4X)
* SQLDBA PASSWORD
(2X)
* CONTROL CENTER LIBRARY: WHERE CONTROL CENTER IS INSTALLED (2X)
* STORED PROCEDURE LIBRARY: WHERE THE EXECUTABLE MODULES FOR
(1X)
THE STORED PROCEDURES WILL RESIDE
Figure 55. Install Stored Procedures (SQMSPRC1.Z) (Part 1 of 3)
102
Control Center Operations Guide for VSE
Stored Procedures Support
* ********************************************
* STEP 1: DEFINE THE STORED PROCEDURES
* ********************************************
// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(<DATABASE
NAME>)’
CONNECT SQLDBA IDENTIFIED BY <SQLDBA PASSWORD>
TO
<DATABASE
NAME>
CREATE PROCEDURE ARISCCF
(IN FUNCODE INT,
IN OWNER CHAR(8),
IN PARM1 CHAR(18),
IN PARM1LN INT,
IN PARM2 INT,
OUT RETCODE INT )
LANGUAGE PLI,
EXTERNAL,
SERVER GROUP,
GENERAL,
RESULT SET 0;
CREATE PROCEDURE ARISVEF
(IN CREATOR CHAR(8),
IN PKNAME CHAR(8),
OUT RETCODE INT )
LANGUAGE PLI,
EXTERNAL,
SERVER GROUP,
GENERAL,
RESULT SET 0;
#*
// IF $RC > 0 THEN
// GOTO $EOJ
* ********************************************
* STEP 2. LINK EDIT THE STORED PROCEDURES
* ********************************************
// OPTION CATAL
// LIBDEF *,SEARCH=(<CONTROL CENTER LIBRARY>,PRD2.DB2710)
// LIBDEF PHASE,CATALOG=<STORED PROCEDURE LIBRARY>
PHASE ARISCCF,*
INCLUDE ARISCCF
// EXEC LNKEDT,PARM=’MSHP’
// IF $RC > 4 THEN
// GOTO $EOJ
PHASE ARISVEF,*
INCLUDE ARISLKSV
// EXEC LNKEDT,PARM=’MSHP’
// IF $RC > 4 THEN
// GOTO $EOJ
Figure 55. Install Stored Procedures (SQMSPRC1.Z) (Part 2 of 3)
Chapter 13. Installing Stored Procedures Support
103
* ********************************************
* STEP 3. LOAD THE STORED PROCEDURE PACKAGE INTO THE DATABASE
* ********************************************
// LIBDEF *,SEARCH=(<CONTROL CENTER LIBRARY>,PRD2.DB2710)
// OPTION LOG
// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(<DATABASE NAME>)’
CONNECT SQLDBA IDENTIFIED BY <SQLDBA PASSWORD> TO <DATABASE NAME>
COMMENT ’***** RELOAD PACKAGE SQLDBA.ARIS7VE *****’
RELOAD PACKAGE (SQLDBA.ARIS7VE)
REPLACE
KEEP
INFILE(SYSIPT BLKSZ(80) PDEV(DASD));
READ MEMBER ARIS7VE.Q (NOCONT
#*
#&
$ $$ EOJ
Figure 55. Install Stored Procedures (SQMSPRC1.Z) (Part 3 of 3)
104
Control Center Operations Guide for VSE
Appendix A. Reorganization Job Streams
|
* $$ JOB JNM=DBUNLOAD,CLASS=0,DISP=D
|
* $$ LST PRI=3
|
// JOB DBUNLOAD MUM UNLOAD DBSPACE TO DISK
|
// OPTION LOG
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
* STEP0001 RECORD TIME BEFORE DDL GENERATION
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT
|
// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT
|
// ASSGN SYS005,SYSRDR
|
// ASSGN SYS011,SYSLST
|
// EXEC SQB02,SIZE=AUTO
|
%%SQLDS730
PUBLIC SQMHELP
2NN1 SQLDBA SQLDBAPW
|
/*
|
// IF $RC > 0000 THEN
|
// GOTO $EOJ
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
* STEP0002 UNLOAD DDL
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT
|
// DLBL SQMDDL,’L.SQLDS730.PUBLIC.SQMHELP’,0,VSAM,
X
|
RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT
|
// ASSGN SYS005,SYSRDR
|
// ASSGN SYS011,SYSLST
|
// EXEC SQB01,SIZE=AUTO
|
%%SQLDS730
PUBLIC SQMHELP
2 N SQLDBA SQLDBAPW
|
/*
|
// IF $RC > 0000 THEN
|
// GOTO $EOJ
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
* STEP0003 UNLOAD DBSPACE
|
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
|
// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD
|
// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000
|
// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR
|
// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(SQLDS730)’
|
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW
|
COMMENT ’***** UNLOAD DBSPACE "PUBLIC"."SQMHELP" *****’
|
SET UPDATE STATISTICS OFF;
|
UNLOAD DBSPACE ("PUBLIC"."SQMHELP")
|
OUTFILE(SQMDAT1 BLKSZ(02048) PDEV(DASD))
|
/*
|
// IF $RC > 0000 THEN
|
// GOTO $EOJ
Figure 56. DBSPACE Reorg Option 2 (Unload DBSPACE) - Sample Jobstream (Part
1
of
2)
105
|
||
|
|
|