Updating the Db2 catalog by using BMCSTATS
Use this procedure to create a utility job that updates the Db2 catalog with statistics from the DASD MANAGER PLUS database.
With this procedure, you create a BMCSTATS utility job that collects statistics and updates the Db2 catalog at the same time. If you have recently run the BMCSTATS utility with the UPDATEDB2 option set to No, you can update the Db2 catalog statistics without collecting statistics again. (See Updating the Db2 catalog by using BMCUPRS.)
To update the Db2 catalog by using BMCSTATS
Create an action as described in Creating-a-Service-Action.A user-defined Service Action is a group of services (utilities) within a DASD MANAGER PLUS job. This Service Action creates a worklist to run the BMCSTATS utility. For more information about actions, see Maintaining-and-generating-Service-Actions.
After you specify the action and press Enter, the Action List appears.
Add the BMCSTATS service to the Service Action, as follows:
- Type I in the Act field next to the services that should precede the BMCSTATS service and press Enter to add a blank line.
- In the blank line, type E (or S) in the Act field.
- In the Object Name field, type the fully qualified object name.You can use wildcards. For example, QZUD40.% specifies all table spaces in the QZUD40 database.
In the Type field, type the two-letter abbreviation for the specified object type and press Enter.Valid entries are:
- TS (table space)
- IX (index)
- IS (index space)
- TT (table space set)
- SG (storage group)
- VL (volume)
As an alternative method, specify the object name and object type by using an object set. For more information, see the link below to the relevant topic.
- To insert a service, type I in the Act field, type % in the Service field, and press Enter. The Service List panel displays a list of the services that you can select.
- Select the BMCSTATS utility as a service in your new Service Action by typing S in the Act field beside the service name and pressing Enter.
- Press END to return to the Edit Action Services panel.
- On the Edit Action Services panel, type E in the Act field beside the BMCSTATS service to edit the service syntax.The Service Syntax List appears.
Edit service syntax by typing E in the Act field beside service syntax. Optionally, you can add new syntax by typing I.The following figure appears. Use this panel to customize how to collect statistics on specific objects. You must have specified the object name and object type on the Edit Action Services panel or when you created an object set.
BMCSTATS panel
DEAE BMCSTATS Row 1 to 26 of 55
Command ===> Scroll ===> CSR
Service Syntax: BMCSTATS.I431937S
Type Service Syntax options. Then press End. More: +
-------------------------- What to Collect -----------------------------------
TABLE . . . . . . * N (Y/N/S Y=ALL Tables, N=No Tables, S=Select Tables)
INDEX . . . . . . Y (Y/N Collect column statistics on all indexes)
SPACEONLY . . . . N (Y/N Collect space information only)
-------------------------- Index Space Options -------------------------------
Distribution Stats:
NUMCOLS . . . . 1 (1-64 Max index key columns to concatenate)
Histogram Stats:
IXNUMQUANTILES (1-100 Number of quantiles to collect)
NUMQCOLS . . . . (1-64 Number of columns for quantiles)
-------------------------- Table Space Options -------------------------------
FREQVAL . . . . . (Y/N Collect frequent value statistics)
-------------------------- Table and Index Space Options ---------------------
COUNT . . . . . . 10 (1-300 Max number of frequent values to collect)
FREQTYPE . . . . . M (M/L/B M=Most L=Least B=Both)
-------------------------- BMC Stats Reporting and Update Options ------------
SAVESTATS . . . . Y (Y/N Save statistics in STATS DB)
DELETEAGE . . . . 32767 (0-32767 Days for deleting old STATS DB entries)
REPORT . . . . . . Y (Y/N Print statistics report)
-------------------------- DB2 Catalog Update Options ------------------------
UPDATEDB2 . . . . N (N/A/P/S N=None, A=All, P=Accesspath, S=Space)
HISTORY . . . . . N (N/A/P/S N=None, A=All, P=Accesspath, S=Space)
DELETEHISTAGE 32767 (0-32767 Days for deleting history table entries)
RESET ACCESSPATH N (N/Y Reset accesspath statistics for all tables)
RESETHISTORY . . N (N/Y Inserts history rows for which access path
statistics are reset)
OMITCARD0 . . . . N (Y/N - Bypass catalog update if cardinality is 0)
-------------------------- Stats Processing Options ---------------------------
BADOBJECTRC . . . 4 (0-8 Return code when object is bypassed)
RECALL . . . . . . N (Y/N Recall archived data datasets)
FORCEROLLUP . . . N (Y/N Produce aggregate stats when missing parts)
MSGLEVEL . . . . . 0 (0/1 0-Normal msgs, 1-Additional msgs)
911ACTION . . . . I (I/S Ignore or Stop at -911 SQL errors)
-------------------------- Stats Tuning Options ------------------------------
TASKS . . . . . . 5 (1-16 Multitasking level)
TSSAMPLEPCT . . . N (Y/N/1-50 Random sampling tablespace statistics)
IXSAMPLEPCT . . . N (Y/N/1-50 Random sampling for index statistics)
ATBWORKAREA . . . (Y/N Freq/card work areas above the 2G bar)
OPTIMIZECOMMIT . . (Y/N Reduce DELETE, INSERT, UPDATE commits)
QUIESCEINTERVAL (0/10000-1000000 Partitions processed before
storage reorganization)
-------------------------- Sort Options --------------------------------------
SORTNUM . . . . . (0-255 Number of SORTWKs for Histogram Stats)
SORTDEVT . . . . . (Device type for sort data sets for Histogram
Stats)- Set the BMCSTATS utility parameters on the main panel (or accept the defaults).
- When you return to the service syntax list, select the syntax by typing S in the Act field.
Press END to return to the Edit Action Services panel.The Edit Action Services panel reappears and shows the BMCSTATS utility as a service (see the following figure).
Edit Action Services panel for BMCSTATS
DEAE --------------- Edit Action Services --------------- Row 1 to 1 of 1
Command ===> Scroll ===> CSR
Action: PAYROLL.DEMO03
Listed is the ordered set of Services contained in the Action. Select one or
more options. Then press Enter.
D =Delete I =Insert L =Like A =After B =Before M =Move P =Properties
S =Syntaxes O =Syntax Options V =View Syntax U =Use default syntax
OS =Object Set
More: >
Act Service Object Name/Pattern Type Part Object Set Name Syntax Name
*** **************************** Top of data **********************************
BMCSTATS QZUD40.% TS BMCSTATS-TES
*** *************************** Bottom of data ********************************- (optional) Add other services to the action, as follows:
- Insert a blank service after BMCSTATS by typing I in the Act field and pressing Enter.
- Display the Service List by typing % in the Service field of the blank line and press Enter.
- Select a service by typing S to the left of the service and pressing Enter.The Edit Action Services panel is displayed. (For more information about editing action services, For more information, see the link below to the relevant topic.
- Press END to return to the Action List panel.
To generate JCL for your action, type G in the Act field and press Enter.The Action Job Generation panel appears. The Action Job Generation panel controls generating, editing, and submitting JCL. The default values come from the user options file.
The Status field shows the current status of the Service Action. The DO_WORKIDS table provides the status. The field can be set to one of the following:
- Not Generated—indicates the Action has not been generated
- Generated Not Executed—indicates the Action has been generated but not submitted
- Executed Successfully—indicates the Action has been generated, submitted, and ran successfully
- Executed with Errors—indicates the Action has been generated, submitted, and completed unsuccessfully
- Specify the action generation parameters and press Enter. If you need instructions for completing this panel, see Generating-Service-Actions.
- Depending on the options that you specified on the second Action Job Generation panel, choose one of the following actions:
- To override the Product Options File (POF) settings, select Override POF Values.
- To build the worklist, select Build Worklist.
To review the built worklist, select Edit Worklist.
For detailed information about the worklist commands, see Worklist-commands.
- To build the JCL, select Build JCL.
To review the built JCL, select Edit JCL.
When you review the built JCL, you can submit it for execution by typing SUB in the Command line.
To run the job automatically after building the worklist and JCL, select Submit JCL.
As an alternate method (if you selected Edit JCL) type SUB on the ISPF Edit panel.
Asterisks (*) in the Build Worklist and Build JCL fields indicate that the product has generated them.
Press Enter to confirm that you want to submit the BMCSTATS job. If you do not want to submit the job now, press END to return to the Edit Action Services panel.The following figure displays the JCL for a sample BMCSTATS utility job.
JCL for BMCSTATS utility job
//RDAJXN4U JOB (5213),'UTILITY-JENBMCS',
// CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1),
// NOTIFY=RDAJXN4
/*ROUTE XEQ BMCPLX1
/*JOBPARM SYSAFF=DB2A
//***************************************************
//* CREATED BY : RDAJXN4
//* TIMESTAMP : 3/10/yyyy.17.27.54
//* ENVIRONMENT: ISPF 7.1MVS TSO
//* RELEASE : Vvv.rr.mm 05/06/yyyy
//* DB2 VERSION: vvvvv
//***************************************************
//*--------------------------------------------------------------------
//* DASD MANAGER WORKLIST EXECUTION
//*--------------------------------------------------------------------
//STEP1 EXEC PGM=AEXEMAIN,REGION=0M,
// PARM='DS815ECA'
//STEPLIB DD DISP=SHR,DSN=DB2.MSTRPLAN.LOAD
// DD DISP=SHR,DSN=SYS3.DEBA.DSNEXIT
// DD DISP=SHR,DSN=SYS2.DB2V81M.DSNLOAD
//********************************************************************
//* LOADLIB CONCATENATATION FOR RUNNING AT STAGE 2
//* AJXSTEPU -- SEE AEX.V71S2.SLIB(ASUSTEPU)
//* ID: ASU
//********************************************************************
// DD DISP=SHR,DSN=ASU.QATEST.LOAD
// DD DISP=SHR,DSN=ASH.QATEST.LOAD
// DD DISP=SHR,DSN=CSGI.SASC.V650.LOAD
// DD DISP=SHR,DSN=AUS.DOPSEC.LOAD
// DD DISP=SHR,DSN=AUS.EDITPR.LOADLIB
// DD DISP=SHR,DSN=DB2.DPD.LOAD
//* ******************************************************************
//ABNLIGNR DD DUMMY
//DSSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//AEXIN DD *
ASU
SSID DEBA
WORKID JENBMCS
LINES 54 STATS
VCAT DEBACAT
UTILITYID JENBMCS.ASUUTIL
//SYSIN DD SPACE=(CYL,(15,15)),UNIT=SYSDA,DISP=(NEW,PASS),
// DSORG=PS,LRECL=80,BLKSIZE=3200,RECFM=FB
//SYSPRINT DD SPACE=(CYL,(15,15)),UNIT=SYSDA,DISP=(NEW,PASS)
//*JGENSRPT DD SYSOUT=*,
//* DSORG=PS,LRECL=80,BLKSIZE=6160,RECFM=FB
//SYSTSIN DD SPACE=(TRK,(1,1)),UNIT=SYSDA,DISP=(NEW,PASS),
// DSORG=PS,LRECL=80,BLKSIZE=3200,RECFM=FB
//SYSTSPRT DD DISP=(NEW,PASS),
// DSN=&&TSPRT1,
// SPACE=(CYL,(5,5)),UNIT=SYSDA,
// DSORG=PS,LRECL=137,BLKSIZE=3155,RECFM=VBA
//AEXPRINT DD SYSOUT=*
//WORKLIST DD DISP=SHR,
// DSN=RDAJXN3.TEST10.JCL(FIG100W)
//*--------------------------------------------------------------------
//* END OF JOBSTEP
//*--------------------------------------------------------------------
//*--------------------------------------------------------------------
//* END OF JOB
//*--------------------------------------------------------------------The following figure provides an example of a BMCSTATS worklist.
BMCSTATS worklist
***************************** Top of Data ******************************
-TIME 000000 'yyyy-06-24-17.41.50.00005'
* GENERIC BMCSTATS
-SSID 000001 DEBA
-WKID 000002 JENBMCS
-SYNC 000003
-BMCU 000004 ASUSMAIN
BMCSTATS TABLESPACE JEND30.%
TABLE (ALL)
EVENTS N
TASKS 2
KEYCARD Y
NUMCOLS 2
COUNT 5
**************************** Bottom of Data ****************************