Using Visual Explain in Access Client Solutions to Enhance the Performance of SQL Processing
Our monthly newsletter topics here at iTech Solutions typically zero-in on purely technical subjects related to IBM i hardware/software and system-level functionality, but every now and then a topic comes along that is more development-related in nature but is also so technically noteworthy that it has both development-level and technical-level relevance, and this is one of those topics.
The SQL tsunami has long since taken over the IBM i world with most every shop doing some form of SQL development, it could be in the form of embedded SQL in ILE RPG programs, creating SQL stored procedures, etc., or deploying 3rd-party apps that use native SQL for all of their database I/O. While most IBM i shops are now doing some form of SQL development work, most installations do not have a database administrator on staff and thus the task of “tuning” the SQL environment on IBM i for optimal performance sometimes falls onto an applications developer who may not be aware of the very powerful DB2 database performance tooling that is available right now that’s baked-into the IBM i software environment, and one of those powerful tools is Visual Explain in IBM’s Access Client Solutions client software.
Access Client Solutions, or “ACS” as it is now commonly referred to, is the follow-on to IBM’s longtime iSeries Access for Windows (previously called Client Access) product which is no longer being enhanced by IBM. Moving forward, ACS is now IBM’s de facto strategic software platform for client-side interaction with the IBM i environment. We all know ACS for the daily meat & potatoes functionality that it provides, almost everyone will touch it at some point during their day for 5250 session emulation, printer sessions, etc., the basic stuff, but many do not know that it also includes some very significant tools and one of them called Visual Explain can be immensely useful in determining what needs to be done to radically speed up SQL processing in your IBM i environment.
Within the Access Client Solutions main portal (Figure #1) there is a function available called Run SQL Scripts which enables you to make a database connection to the selected system and run SQL statements directly on that system. Clicking on the Run SQL Scripts option will result in the window shown in Figure #2, and within the window you can enter SQL statements to run just like you can do from a green screen session using the venerable STRSQL command, but, this SQL statement processor is the old STRSQL command on steroids…
The example SQL statement entered in the Figure #2 window is a very simple and typical SELECT statement that looks for a specific column value and joins a couple of tables to get the result. Clicking on Run at the top of the Window and then All in the dropdown menu that appears will run the SQL statement and display the results at the bottom half of the window as shown in Figure #3. All seems pretty simplistic and unexciting, right? Well, here’s where you invoke the pure magic of Visual Explain in ACS…
Arguably the most valuable piece of knowledge to have when fine-tuning SQL performance in DB2 on IBM i or any other SQL platform is knowing precisely what types of indexes need to be prebuilt over tables to make frequently used SQL statements (especially SELECT statements) run faster. Having the correct SQL indexes built prior to running a SELECT statement can mean the difference between that SQL statement taking 30 seconds to run or 3 seconds, but how do you know what indexes need to be built? This is where Visual Explain in ACS earns its stripes.
Let’s revisit the SQL statement that we ran in Figure #3, but this time let’s run it using Visual Explain as shown in Figure #4, you click Visual Explain on the top menu and then click Run and Explain and the window in Figure #5 will appear, giving you all sorts of great information on the SQL statement that just completed. On the Visual Explain window click Actions at the top and the click Advisor in the dropdown menu and the Index and Statistics Advisor window will appear as shown in Figure #6 showing you exactly what indexes DB2’s deep expert algorithms are recommending to be created to enable the sample SQL statement just ran to run at maximum performance. Clicking the Show SQL button at the bottom of the window will show you the precise SQL index creation commands to use to create the recommended indexes, and, to make it all even more simple and powerful and magical, simply click the Create button and Visual Explain will create the indexes for you! Now how cool is that.
So, to summarize the automated wizardry that just happened here, you went into Visual Explain and ran a sample SQL statement, Visual Explain employed DB2’s expert algorithms to recommend what indexes you needed to create to make that SQL statement run as efficiently as possible, and finally you simply clicked a button and the indexes to speed it all up were automatically created for you. You just did some pretty high level and expert data base performance tuning with just a few mouse clicks and no programming! Now granted the example SQL statement used here is simplistic, but this tool was designed to analyze very complex SQL statements involving many tables and join/select criteria and recommend index creations that can dramatically cut down on run times for those statements. Pure magic.
Many readers of our newsletters are IBM i system administrators and IT managers responsible for the technical implementation and use of IBM i in their organizations, but many are also wearing both technical and application development hats or are technical mentors to developers on their teams, so we hope that our readers find the powerful capabilities in Visual Explain useful for application in their own environments.