Scribd
Upload
5 views3 pages

Hunting Bad SQL: Effective Strategies

Bad SQL usually does not occur during testing but when it does, it is important to determine if it is a one-time issue, a new normal workload, or an existing workload change. To hunt for bad SQL, the steps are to capture the SQL using a trace, analyze it using optimization tools, and get the SQL changed through code reviews and regression testing. A specific trace method is outlined to identify SQL during a particular workload by starting and stopping a trace that collects SQL information and stores it in SMF files.

Uploaded by

senthur123
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views3 pages

Hunting Bad SQL: Effective Strategies

Bad SQL usually does not occur during testing but when it does, it is important to determine if it is a one-time issue, a new normal workload, or an existing workload change. To hunt for bad SQL, the steps are to capture the SQL using a trace, analyze it using optimization tools, and get the SQL changed through code reviews and regression testing. A specific trace method is outlined to identify SQL during a particular workload by starting and stopping a trace that collects SQL information and stores it in SMF files.

Uploaded by

senthur123
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

#3 Underestimating the effects of SQL

Bad SQL usually does not happen when applications


are
Unit tested
System tested or shakedown tested
Integration tested
But when bad SQL happens, what do we ask?
A one-off situation
A new steady state
New workload
Existing work changes
DBA misstep
Sequence to the Hunt for Bad SQL
Capturing the SQL
Trace
On-line monitors
Analyzing the SQL
Optimization Service Center
Optim Query Tuner
Getting the SQL changed
Hopefully there is a
feedback/code review process
Regression test sandbox
A word on statically-bound
packages using pureQuery
Trace Method
Identify a time period when the app runs (or when you like
to run a particular workload)
At the appropriate time, use DB2 Interactive (DB2I), option
7, and run the following trace:
-START TRACE(MON) CLASS(1) IFCID(316,317,318) DEST(SMF)
Run any test
Turn off trace using TNO:
-STOP TRACE(MON) TNO(###)
Flip System Management Facility (SMF) files

You might also like