Squash Your Mainframe SQL Tuning Anxiety With Toad™ for DB2 z/OS

Fear and confusion: two emotions you probably don’t like associated with your job.  I see it often in my line of work, and have seen it for many years since I started working with mainframe files and databases that people needed to access.  

Your organization might have an awful lot of great data up on the mainframe, and someone probably wants to make good use of it.   Maybe you have to make good use of it.   Fact:  Tuning SQL that accesses DB2 on z/OS can cause anxiety, on this foreign (to many) platform.   Sometimes the tricky part is simply accessing the data up on the mainframe to test SQL against.  Other points of confusion might include how to generate and view access plans from DB2 z/OS SQL, and the behavior and organization of indexes on DB2 z/OS.   Whatever the roadblock to your comfort level, you’re not alone.  Mainframe SQL development/tuning doesn’t fit the skillset of many IT or development professionals anymore unless they have the right tool to help them.

If you’re one of these people, anxious about suddenly finding yourself tasked with tuning SQL for DB2 on z/OS – optimizing statements before they go to production, or when one is already in production –  I’d like to introduce you to Toad™ for IBM® DB2 z/OS.    

The key to why Toad for DB2 z/OS is going to be your friend is the layer it creates between you and the DB2 subsystem you’ll be connecting to and interacting with.   With Toad, tuning SQL to run on DB2 is as easy on z/OS as it is on Linux, UNIX, or Windows.  Even if you’ve never touched DB2, Toad will help you get productive quickly.

Here are some top reasons Toad makes the mainframe so accessible to everyone who needs to tune SQL:

• Allows less experienced developers and DBAs to access DB2 without having mainframe expertise and experience.
• Connection management.   Connect to DB2 z/OS data sources just as you do from other client applications, and keep all those connection details stored right inside Toad.
• Improve code performance.  Debug stored procedures, format code and optimize SQL performance.
• Optimize SQL statements automatically through rewrites and indexing, and compare scenarios before deciding to actually deploy a new version of the SQL or a new index.

Now, let’s discuss SQL Optimization, that last bullet point.   Quest’s solution is a must-have for mainframe DB2 developers for quick, easy SQL tuning.

Benefits of SQL Optimization using Toad for DB2 z/OS  

• Tune SQL statements from several sources using Toad integration between components – bring SQL statements into Quest SQL Optimizer for DB2 z/OS from:
  • The Toad Editor, as the SQL statement is being built or altered
  • The Toad Dynamic Statement Cache tab for a selected z/OS subsystem in the Database Explorer
  • Packages or Plans
• As fast as you might be in writing alternatives for a SQL statement – re-arranging things, trying different joins, etc. – the Toad SQL Optimizer is going to be much, much faster!   You might see hundreds of alternative re-write ideas generated in a matter of minutes, where it might take days for someone to write them manually.
• Generate index ideas (virtual indexes) and choose which to test (the index is created, then dropped) to find the optimal index for your SQL statement.
• Quickly compare SQL alternatives and their access plans side-by-side.

Here’s a quick look at SQL Optimizer for DB2 z/OS:

 Figure 1:  Click the ‘tuning’ button to bring the statement that’s in context to SQL Optimizer

Figure 2:  Get the DB2 access plan for the statement

Figure 3:  Click “Rewrite” to generate the alternative SQL scenarios; click “Test Run SQL” to execute them against DB2

Figure 4:  Click “Index” to generate virtual indexes (not physically created until you run these Index alternatives…)

Figure 5:  When you run an index alternative to see how the SQL statement will perform with that new index, SQL Optimizer creates the index object on the database, and requires the selection of a Storage Group.

Figure 6:  The Index 1 alternative now shows elapsed time and other run-time metrics which you can compare with other alternatives you’ve executed during this tuning session.

Figure 7:  Compare any alternatives side by side.

Figure 8:  You have control over “Intelligence Level” of the rewrite engine’s attempts to identify alternative ways to write your SQL statement – the higher the level, the longer the rewrite phase will take, but more creative and useful rewrites might be identified.

You can ask questions about the material in this post, or offer your insights and feedback, by joining us on the Toad World forum at Toad for DB2 Forum.