Introduction

DB2 for i5/OS on V5R4 delivers new ways to improve the performance of database queries through several popular dynamic SQL interfaces. The SQL Call Level Interface (CLI) has provided users with a new connection attribute to tune the optimization goal used with the database queries. The Java Database Connectivity (JDBC) interfaces for IBM® Developer Kit for Java, also known as Native JDBC, and Toolbox, have also provided access to a new connection property that can control the optimization goal of the queries. Those familiar with System i and database query performance analysis and tuning, understand that controlling the optimization goal is a critical step to optimizing performance. Beginning in V5R4, these dynamic interfaces can enjoy the same direct programmatic control found elsewhere in System i. This article focuses on the new CLI attribute for setting the optimization goal.

Optimization goal interfaces

Applications executing SQL statements have had the option of tuning the optimization goal through several mechanisms in the past:

• Query Options File (QAQQINI) -- The OPTIMIZATION_GOAL option can be specified with one of the following values to control the optimization goal for the entire system or a specific connection (job). There is no default setting since the default optimization goal value depends on the SQL interface being used. For the default values for each interface, refer to Table 1.
• *ALLIO
• *FIRSTIO

• OPTIMIZE FOR N ROWS clause -- Optimization can be built into SQL requests directly by using the OPTIMIZE FOR N ROWS clause. Smaller values for N tend to cause the query optimizer to use a FIRSTIO goal, while larger values such as ALL ROWS results in the usage of the ALLIO goal.
  
• CLI SQLSetConnectAttr() API and SQL_ATTR_QUERY_OPTIMIZE_GOAL attribute
• SQL_ALL_IO (default)
• SQL_FIRST_IO
  
• Toolbox JDBC connection property, "query optimize goal", and DataSource method, setQueryOptimizeGoal()
• 0 = Use the default goal *FIRSTIO, unless extended dynamic packages are used, causing the goal to be *ALLIO
• 1 = *FIRSTIO
• 2 = *ALLIO
  
• Native JDBC connection property, "query optimize goal", and DataSource method, setQueryOptimizeGoal()
• 0 = Use the default goal *ALLIO
• 1 = *FIRSTIO
• 2 = *ALLIO
  

The new CLI and JDBC interfaces

The new CLI and JDBC options provide a more programmatic, and in some cases a more granular approach to tuning the optimization goal. Since the new CLI connection attribute is scoped to the connection, it takes affect for all SQL queries executed after setting the attribute. This allows environments that run with many threads in server mode to have the benefit of running with distinct optimization goals across the multiple connections, if desired. It also makes setting the optimization goal more conducive to a tuning-on-the-fly strategy. Note that these JDBC and CLI interfaces only affect connections to System i servers running at i5/OS V5R4 or later.

The intent of this article is not to give an in-depth discussion of the optimization goal settings, but a brief description may be useful to some readers. By specifying an optimization goal of FIRSTIO, the application can force the optimization of queries to return the output of the first page of results faster. ALLIO optimizes with the goal of running the entire query to completion in the shortest amount of elapsed time. A good way to visualize these options is to think of the FIRSTIO option as a bike and the ALLIO option as being analogous to an airplane. If you only have a short distance to cover, such as a sprint, then the bike is most likely the best option since there is virtually no startup costs involved. However, despite the higher startup costs, over a longer distance, ALLIO is the better solution. Understanding the distance, or the queries' output behavior, is crucial in determining the most effective option for the individual query.

One of the purposes of this article is to prove a practical implementation of the new settings in a CLI environment by taking several queries and testing the performance of the two optimization settings; first input/output (FIRSTIO) and all input/output (ALLIO). It was decided that iSeries Navigator's SQL Performance Monitor feature would be a useful tool for measuring the queries' open, fetch, and total response time as well as the optimizer implementation. A CLI program was constructed to run SQL requests with either optimization goal setting. The CLI constants, SQL_FIRST_IO and SQL_ALL_IO, are available for the new connection attribute.

				
 attr = SQL_FIRST_IO;
 rtnc = SQLSetConnectAttr(hdbc,SQL_ATTR_QUERY_OPTIMIZE_GOAL,&attr,0);

iSeries Navigator SQL performance tools

The SQL Performance Monitor is started from the iSeries Navigator Run SQL scripts interface by choosing the Monitor > Start SQL Performance Monitor task from the drop-down menu in Figure 1. Then, the C program containing the CLI calls was called using the CL prefix to issue an i5/OS CALL command.

The first query tested only returned a result set of 12 rows, when targeting a database with around 1 GB of data. Using the Performance Monitor, refer to Figure 2 below, the comparison feature was used to visually lineup side-by-side the FIRSTIO and ALLIO performance numbers.

Figure 3 contains the output of the SQL Monitor comparison containing the timings from the testing of the two optimization goals. In this case, the ALLIO run for the first query was significantly faster than the FIRSTIO run in the time it took to return all 12 rows. Interestingly, the open processing took less time in the FIRSTIO run than in ALLIO, but the difference in fetch time made ALLIO a much better choice for overall response time. This was because the optimizer picked a plan that spent a little extra effort at open time building structures to fetch all the data in a timely manner, not just the first few rows as it assumed was the case in the FIRSTIO run.

The second query evaluated uses an SQL statement that returns thousands of result set rows. However, in this case the CLI application really only cares about the first 40 rows in its initial processing. The application processes the rest of rows eventually, but not until later on. A practical implementation of this would be in the context of a Web page search engine. The page wants to get the first few results displayed as quickly as possible, and while the rest of the results matter and may potentially be displayed, the common user does not think of these later results as being critical to see in their initial viewing or the search results. As you can see from the performance results in Figure 4, the access plan built with the FIRSTIO goal for the second query took half the time of the ALLIO goal in getting the first 40 rows returned to the application.

The Analyze function for the SQL Performance Monitors can also be used us to investigate the settings that affected query optimization. The analysis can be launched from either the Run SQL Scripts interface by selecting the Analyze task from the Monitor drop-down menu in Figure 1, or from the SQL Performance Monitors view (see Figure 2) in the iSeries Navigator tree by right-clicking on a monitor and selecting the Analyze task. Running the Analyze function, results in the monitor summary shown in Figure 5. Notice, the optimization goal setting of FIRSTIO highlighted in Figure 5.

Use Visual Explain for optimization analysis

Another way to understand the optimization goal used by the DB2 for i5/OS query optimizer during plan creation, is to utilize the Visual Explain tool that is part of the iSeries Navigator. The Visual Explain tool can be used on SQL statements in a monitor collection by right-clicking on a monitor and selecting the Show Statements task. By analyzing the Visual Explain output in Figure 6, from the second query, it is possible to confirm that the optimizer ran with the expected optimization goal of FIRSTIO.

The new V5R4 analyze and compare features in the iSeries Navigator DB2 Performance toolset provide new and exciting ways to explore query performance. The additional capability provided by CLI and JDBC to allow for programmatic control of the optimize goal, give more power to applications.

Resources

Learn

• "DB2 Universal Database for iSeries Database Performance and Query Optimization": Find additional reading on the OPTIMIZATION_GOAL QAQQINI option.
  
  
• "DB2 Universal Database for iSeries SQL Reference": Read the documentation on the OPTIMIZE FOR n ROWS clause.
  
  
• iSeries Information Center: Find information on setting CLI connection attributes.
  
  
• IBM Toolbox for Java JDBC properties: Get details on the JDBC connection attribute and the JDBC Datasource method.
  
  
• Educational resources for Business Partners: Learn how to use the DB2 for i5/OS tools within iSeries Navigator, using downloadable, self-study tutorials.
  
  
• DB2 for i5/OS: Find the latest DB2 Universal Database for iSeries product information.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Optimization goal interfaces
• The new CLI and JDBC interfaces
• iSeries Navigator SQL performance tools
• Use Visual Explain for optimization analysis
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers