An overview of DB2 Optimization Expert for z/OS (by a former DBA)
IBM_Optim 27000269HS Visits (3218)
I am a design architect for DB2 Optimization Expert for z/OS (OE), which is IBM’s query optimization solution. I would like to give you a brief overview of what OE is and how it can help you identify and resolve query optimization issues.
Prior to joining IBM I was a DB2 for z/OS DBA for twenty plus years and can relate to the challenges DBAs face every day, such as:
Back then query tuning was a very manual process, and like me, I’m sure many of you developed your own processes and methodologies to capture the information needed to analyze query access paths. Then IBM released Visual Explain which provided a nice graphical view of the access path, as well as catalog information related to a query. Later a Statistics Advisor feature was added which helped identify what statistics should be gathered to improve query performance. This was good stuff, but Visual Explain only performed analysis on a single query, and there were limitations on what sources the SQL could be captured from.
In March 2007 when DB2 for z/OS V9.1 was delivered, two new query optimization tools were released: OSC (Optimization Service Center) and OE. OSC is a no-charge offering of the tools and offers a subset of OE features. I will explain more about the features in the chargeable vs. no-charge offerings in a future post. OE V1.1 only supported DB2 for z/OS V9.1, but based on customer feedback, DB2 for z/OS V8.1 and data sharing support were added and delivered in March 2008 with OE V1.2.
So what does OE offer? It has three distinct sets of features:
Thesee are the tools and advisors that operate on a single query:
And these are the tools and advisors that work on a workload (a set of statements):
Finally, here is the capability of workload performance monitoring (only available for V9.1 environments):
This monitoring capability is not intended to be a replacement for Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS, DB2 Query Monitor for z/OS, or related tools that are full-blown monitors with sophisticated interfaces.
Each of these feature sets can be discussed in great detail and I will defer that discussion to future posts.
I would like to close by posing a few questions to you. You can cut and paste these questions into a note and send them to firstname.lastname@example.org, or post your comments here by clicking on the Add a Comment link below.
1. What are your primary pain points in tuning SQL?
3. What tools are you currently using to resolve query problems?
Until next time.