So the basics of analyzing SQL on db2 are in my post on Analyzing SQL. I wanted to go a bit beyond the basics.
I’m not doing to describe reading explain plans at this time, but it is absolutely critical knowledge for a DB2 dba.
So after you have the db2advis and db2exfmt output, you can use that to play around with the SQL. You can use the explain plan to find the biggest consumers of timerons, and see if there’s anything you can do in the SQL to eliminate or change the way that section is happening. The most obvious here is the addition of an index to eliminate a table scan, but eliminating an expensive sort or index scan is just as valid.
When you’ve changed the syntax, always make sure you get the right results, and then do the exact same analysis you originally did – generating the explain plan and db2advis output, and compare the number of timerons between the altered sql and the old SQL. Depending on the statement, I might try 4 or 5 different alterations before giving up.
It is important as you work through to track what you’re trying. I use a naming system for my sql files on the server to indicate alternate syntaxes tried, and most of the time also have a spreadsheet. The spreadsheet helps me have a place to note random comments or to go back and add more comments later if something I’m looking at later makes me think of something valid for a previously analyzed query.
List all the tables accessed by each SQL statement. Note multiple accesses to the same table (which may be needed, but still).
List all columns used in joins and then use the Commerce info center to make sure that all join columns are the first column in an index. Report join columns without an appropriate index to the requester.
Look for and report to the developers any queries that appear at first glance to return large result sets – such as ones that would return most of the orders table or most of the members table, etc – ask the developers if they’re necessary or if a smaller subset of the data is what is really being used. This same thing holds true for any queries that return * or a large number of columns.
While reviewing the SQL, keep your eyes open for syntax mistakes that may lead to unexpected joins or other issues.
Obviously if you can take the SQL and do a full analysis through the explain plans and DB2 Design Advisor, that’s preferred, but in some cases, that may not be possible.