Database Analyzer: How to perform a quick PDA Stats Collection on an object?
search cancel

Database Analyzer: How to perform a quick PDA Stats Collection on an object?

book

Article ID: 60226

calendar_today

Updated On:

Products

Database Analyzer for DB2 for z/OS

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

Collect Statistics Using the Online Interface