SQL was first developed in the ’70s as a method to communicate with relational databases. It has since evolved to support many more data retrieval options, data types and other complexities. As a result, improperly coded and untuned SQL can also adversely impact DB2 performance and data center Service Level Agreements (SLAs). There are several reasons for this.
In some cases, DB2 subsystems lose their original optimization for SQL statement processing because changes to code in the SQL have occurred over time without corresponding adjustments being made to DB2. In other cases, the data payloads being invoked by SQL statements are much larger than they used to be, given the growth of data volumes and perhaps the absence of data distribution statistics. In still other cases, third-party software doesn’t allow you to alter the software’s SQL statements.
To help ensure you meet your SLA, you need to make sure all SQL statements are analyzed and thoroughly tested before putting them into production. Use the EXPLAIN command to understand the access path DB2 will take to seek the data and then analyze for access path improvement. A best practice in this area is to automate this to eliminate the need to manually review each and every SQL statement. This can be embedded into your normal change control process to halt the processing before it reaches production. Using this proactive approach will allow you to correct poorly performing SQL statements before they hit production.
If poorly performing SQL statements appear in production, one way to find and fix these problems is to use monitoring and detection tools suited for this purpose. The more traditional monitoring tools are able to issue alerts when performance slows. However, when these tools are used in combination with an SQL “detection” tool capable of deep diving into SQL performance metrics for a more complete analysis, the DBA’s ability to tune DB2 for performance is greatly enhanced.
Let’s say there’s a performance problem in DB2. The DBA knows performance can be influenced by the access path into the data that DB2 takes. Using such detection tools, the DBA can see the most system resource-intensive SQL statements. By selecting the SQL statement consuming the most CPU, the DBA can EXPLAIN the individual statement directly from the display and get recommendations for changing it to improve performance.
Of course, there are areas where sites are limited—such as when they’re running software from third-party vendors that gives them limited (or no) access to the SQL statements in the third-party application. In these cases, you can still do some tuning at the systems level by affecting the parts of the system that can be changed outside the SQL statements.
One example of this is to do analysis of the SQL that has been run on the system to see if additional indexes on the object would improve performance. If so, generally, third-party applications will allow these types of changes to their objects and still remain supported. Furthermore, additional subsystem tuning through the use of additional or expanded buffer pools, ZPARM settings, data sharing configuration and other system settings can be helpful in tuning third-party applications. All associated trade-offs within these scenarios still must be considered during this analysis.
For instance, in the case of a data warehouse where data is only occasionally refreshed, adding indexes may not matter as much because you won’t need to rebuild indexes often. However, if you are working with an Online Transaction Processing (OLTP) application where data is constantly being updated, indexes may slow response times. Furthermore, good DB2 housekeeping around statistics and object maintenance is required as well to ensure the DB2 optimizer isn’t thrown off by disorganized data or incorrect statistics.
No DB2 optimization strategy works in every circumstance, but you can improve your DB2 optimization. From the onset of applications development, include DB2 optimization and tuning as a concurrent task with application code development and tuning. If you coordinate these activities upfront and then continue to synchronize them with every change, your database won’t drift from your applications and DB2 performance will remain optimized.
As a workaround for third-party applications that prevent you from synchronizing with vendor SQL code changes, look at improving DB2 performance by adding indexing—understanding that there are trade-offs to be considered as far as overhead and maintenance are concerned.