Troubleshooting SQL performance problems

book

Article ID: 76075

calendar_today

Updated On:

Products

CA Bind Analyzer for DB2 for z/OS CA Detector CA SQL-Ease for DB2 for z/OS CA Sysview Performance Management Option for DB2 for z/OS CA Database Detector for DB2 for z/OS CA Plan Analyzer for DB2 for z/OS CA Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

trying to determine what SQL statement(s) are involved in an application program that went from 30 minutes to 8 hours run time.

We are trying to determine what SQL statement(s) are involved in an application program that went from 30 minutes to 8 hours run time.
Could you assist us determine what kind of DB2 traces we need to run and collect this information into SMF files and then if you could tell us which
SYSVIEW batch report name we could use to obtain the SQL performance accounting/statistics used by this program.
.

Environment

Z/OS DB2

Resolution

There are multiple different ways to monitor this sort of thing.
It really depends on the level of detail you require. 
I would suggest Report BTDSQSMS and BTSSQSMS for Dynamic and Static SQL. 
These reports use the Sysview for DB2 History file NOT SMF which i think gives you more flexibility. 

For SMF input you could look at Reports BTSQLTRL and BTSQLSM1.