Aug 25, 2017 3:23:48 PM by Tim Fritz
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:
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
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.
Written by Tim Fritz
Quest Software solution architect - database performance management and administration solutions. Former DB2 developer and DBA. IBM Certified Database Administrator – DB2 LUW; Microsoft MCP, Certified Technical Specialist – SQL Server.