Database Analyzer: How to perform a quick PDA Stats Collection on an object?
Article ID: 60226
Updated On:
Products
Issue/Introduction
How can I quickly generate and submit some JCL to collect PDA Stats?
Environment
Release: R20
Component: PDA
Resolution
The JCL to carry out a PDA Stats Collection can be generated quickly using DB2 Object Manager.
1. Enter the DB2 Object Manager (U) on the Database Management Solutions for DB2 for z/OS Menu.
2. Once at the DB2 Object Manager Menu select the object type to be processed like (1) for Tablespace, and for Tablespaces enter the DBNAME and TABLESPACE value or leave the Tablespace blank to get a list.
UOPRRTSM - Object Mgr - Tablespace Selection - yyyyy/mm/dd hh:mm
COMMAND ==> SCROLL ==> CSR
DBNAME : DBCORP SUBSYSTEM CONNECTION ID: ssid
TABLESPACE: TSE%
Loc: LOCAL ------------------------------------------------- User ID: authid1
OPT TABLESPACE CREATOR PART DATABASE VCATNAME
_ TSEMP authid1 ALL DBCORP DSNDPGP
_ TSEMP authid1 001 DBCORP DSNDPGP
_ TSEMP authid1 002 DBCORP DSNDPGP
_ TSEMP authid1 003 DBCORP DSNDPGP
_ TSEMP authid1 004 DBCORP DSNDPGP
_ TSEMP1 authid1 ALL DBCORP DSNDPGP
_ TSEMP1 authid1 001 DBCORP DSNDPGP
_ TSEMP1 authid1 002 DBCORP DSNDPGP
_ TSEMP1 authid1 003 DBCORP DSNDPGP
_ TSEMP1 authid1 004 DBCORP DSNDPGP
******************************** BOTTOM OF DATA *******************************
3. To choose the correct function use the "P" line command for PDASTATS next to the object to be processed. P is "PDA Statistics Collection". You also have for Indexes , "D - PDA Index Statistics Collection."
UOPRRTSM - Object Mgr - Tablespace Selection - yyyyy/mm/dd hh:mm
COMMAND ==> SCROLL ==> CSR
DBNAME : DBCORP SUBSYSTEM CONNECTION ID: ssid
TABLESPACE: TSE%
Loc: LOCAL ------------------------------------------------- User ID: authid1
OPT TABLESPACE CREATOR PART DATABASE VCATNAME
_ TSEMP authid1 ALL DBCORP DSNDPGP
_ TSEMP authid1 001 DBCORP DSNDPGP
_ TSEMP authid1 002 DBCORP DSNDPGP
_ TSEMP authid1 003 DBCORP DSNDPGP
_ TSEMP authid1 004 DBCORP DSNDPGP
P TSEMP1 authid1 ALL DBCORP DSNDPGP
_ TSEMP1 authid1 001 DBCORP DSNDPGP
_ TSEMP1 authid1 002 DBCORP DSNDPGP
_ TSEMP1 authid1 003 DBCORP DSNDPGP
_ TSEMP1 authid1 004 DBCORP DSNDPGP
******************************** BOTTOM OF DATA *******************************
4. At the "PDA - Build" screen enter the options for the collection and PF3 out to generate the JCL.
UOPPDA ---- PDA - Build ----- yyyy/mm/dd hh:mm
COMMAND ==>
Database : DBCORP
Tablespace: TSEMP1 Partition(s): ALL
EXECUTION OPTIONS
Abend Task : N
PROCESSING OPTIONS
Update CATSTATS: Y Statistics SSID : ssid
PERFORMANCE OPTIONS
Vsam Buffers: 0096
Max Tasks : 0002
5. Preview the JCL , enter your jobcard details, PF3 to see the JCL. It should all be ready to run unless more changes are needed on the jobcard.
The SYSIN JCL should look this:
//your jobcard
//*
//* MODEL = MJPTIUT DATE = mm-dd-yyyy TIME = hhmmss
//*
//UTIL0001 EXEC PGM=PTLDRIVM,
// PARM='EP=UTLGLCTL/ssid,',
// REGION=0M
//*
//STEPLIB DD DISP=SHR,DSN=PTIPROD.RD200.PRD.CDBALOAD
// DD DISP=SHR,DSN=DSNDPGP.PRIVATE.SDSNEXIT
// DD DISP=SHR,DSN=hlq.SDSNLOAD
//PTILIB DD DISP=SHR,DSN=PTIPROD.RD200.PRD.CDBALOAD
// DD DISP=SHR,DSN=DSNDPGP.PRIVATE.SDSNEXIT
// DD DISP=SHR,DSN=hlq.SDSNLOAD
//PTIPARM DD DISP=SHR,DSN=PTIPROD.RD200.PRD.CDBAPARM
//*
//PTIMSG DD SYSOUT=*
//PTIIMSG DD SYSOUT=*
//*
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//ABNLIGNR DD DUMMY SUPPRESS ABENDAID DUMPS
//STXXMSG DD SYSOUT=*
//*
//SYSIN DD *
PDASTATS TABLESPACE DBCORP.TSEMP1
STATS-LOCID ssid
MAXTASKS 2 VSAM-BUFFERS 96
UPDATE-CATSTATS ALL
The other way to get to the same JCL is from RC/Query. Make a list of objects like Tablespaces again, make sure that your RC/Query Profile has the "UTILITIES ==> Y" and then use the RUNSTATS line command and the same JCL can be generated.
This function is documented in Collect DB2 Object Statistics Using PDASTATS Utility.
Additional Information
Feedback
