Managing the data lifecycle
Hi! It’s been a while since I posted an entry, and with the announcement of DB2 Optimization Expert for z/OS V2.1, I wanted to give you some highlights of what this release offers.
The biggest news about this release is the ability to run in an integrated desktop with IBM Data Studio, Rational, InfoSphere, and other products built on Eclipse V3.4.1, which includes products such as:
• IBM Data Studio Developer 2.1
• Rational Application Developer V18.104.22.168
• Rational Software Architect V22.214.171.124
• InfoSphere Data Architect V7.5.1
This integration is accomplished with shell sharing, and Michael Hsing has a recent entry on this topic, and it also includes links to other shell sharing articles.
So what does that integration provide? If you are a user of any of the above products, you can now invoke DB2 Optimization Expert without leaving the environment of those products. To me this is ideal for developers who want to perform query analysis on SQL contained within their code without leaving the IDE. My last blog entry provided information on those tools and advisors and can be reviewed here. You can also see the advisors in action in this demo that Thuan blogged about earlier.
One important note: The ability to shell-share with other Eclipse-based products such as Data Studio is only available with DB2 Optimization Expert. The Optimization Service Center included with the DB2 z/OS Accessories Suite does not include this capability.
For those of you who are DBAs and not developers, I encourage you to try DB2 Optimization Expert 2.1, since the integrated desktop is our stated direction. However, since this release contains the same features and functions as V1.2.2, you can continue using it and slowly migrate to the new release.
The primary focus of this release was shell sharing; our future release will focus on enhancements to our tools and advisors, as well as improved usability and workflow.
Stay tuned, more is coming.
Traditionally query tuning has been perceived to be a function only performed by DBAs, but with a product such as DB2 Optimization Expert for z/OS, I’d like to suggest that this perception could change and become a task that a developer could perform. At the very least, developers could do some due diligence before calling in a DBA. As a former DBA, I was always more impressed with developers who were willing to do some of their own analysis before calling me.
It’s understandable why developers don’t give much thought to query tuning. First of all, developers tend to only focus on the results a query returns. Am I getting the data I need? Secondly, developers tend to be under tight deadlines to complete projects, and don’t have the time to truly examine how queries perform. Thirdly, understanding how the EXPLAIN tables relate and how to interpret the information contained in them can be intimidating and cryptic.
Optimization Expert offers several tools and advisors that can help a developer write more efficient SQL:
This tool takes a query and formats it so that is easier to read, and provides the ability to expand and collapse sections of the SQL. If you click on a table in the FROM clause, it will highlight related columns in the SELECT and WHERE clauses. Conversely, when you select a column in the SELECT or WHERE clause, it will highlight the table the column is related to. Additionally, if your statement references a view, Query Annotation provides the ability to drill down into the SQL in the view. The figure below is an example of a simple SQL statement that references a view, and how you can drill into the view to see the underlying SQL. An additional feature offered in this tool is Annotation, which gives you pertinent statistics used by the optimizer at the table and column level.
Access Plan Graph
This tool generates a diagram of the current access plan for a query, showing how DB2 accesses data to process a query. This information is the same information that is in the PLAN_TABLE, but is presented in an easy-to-read, visual format. Yes it is similar to the graph displayed in Visual Explain, but has some additional features such as infopops, which display information as you hover your mouse pointer over a node. The information displayed is dependent on the type of node that you hover over.
In the example below, the infopop provides information about the TBSCAN node, the Cardinality, Total Cost, I/O Cost, CPU Cost, that a sequential prefetch is being perfomed, as well as page ran access since this table is partitioned. Currently the Query node is selected, indicated by the squares in the four corners, and the Node Descriptor is providing costing information for this query. If you were to select the TBSCAN node additional information beyond what is displayed in the infopop would be displayed in the Node Descriptor.
Access Path Advisor
This advisor interrogates the plan table and highlights potential access path performance issues and provides recommendations on how to eliminate such issues. An explanation and description of issues are given to you, and the recommendations are ranked by severity as Low, Medium, High and Disaster. The figure below shows the results of running Access Path Advisor with three low severity warnings. When you select one of the warnings, a description and explanation for that warning are displayed, as well and the plan table information.
This advisor is the one single tool in Optimization Expert that can assist a developer in writing efficient SQL. It interrogates the SQL statement and makes recommendations on how to rewrite the query to make it more efficient based on best practices rules. When you click on a recommendation, it highlights the line of the SQL statement that the recommendation pertains to. Once again, an explanation and description are provided to you. The figure below shows the results of running Query Advisor. In this example there are two query rewrite recommendations. When you select a recommendation, the line of SQL is highlighted, and you are presented with a description of how to rewrite the query, as well as an explanation of the recommendation.
Most SQL performance issues are never discovered until they are in a production environment, where they impact Service Level Agreements, consume precious resources, or in extreme situations cause application outages. If an organization were to use Optimization Expert and expose it to their developers, production SQL performance issues could be eliminated or greatly reduced. Additionally, it could be used as an educational tool for coding efficient SQL.
Currently Optimization Expert is an Eclipse RCP application, but we will soon be releasing a version that will integrate into Data Studio and support shell-sharing. This is another reason for developers to incorporate this into their development process.
If you’re a DBA, do you think any of your developers would like to learn this tool? Do any of you have experience in your shop with developers doing query tuning? If so, I’d love to hear from you about what works and what doesn’t work, or any other suggestions that would make this tool even better for developers and DBAs.
One final note, for those of you attending IOD in October, I encourage you to attend the following hands-on lab: 2568A – SQL Tuning for Everyone: Using IBM Data Studio Optimization Expert for DB2. This lab will be led by two members of the Optimization Expert team. I think it will be well worth your time.
-- Ray Willoughby
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 email@example.com, 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.