Troubleshooting SQL performance problems

Document ID : KB000076075
Last Modified Date : 17/04/2018
Show Technical Document Details
Introduction:
trying to determine what SQL statement(s) are involved in an application program that went from 30 minutes to 8 hours run time.
Question:
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
Answer:
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.