Score optimization goals with SQL performance on DB2 for i5/OS

Use the new CLI and JDBC optimization goal interfaces to tune SQL performance

Understanding and implementing the best SQL query optimization goal can improve many queries' response time greatly. DB2® for i5/OS on V5R4 delivers a new way to control query optimization through several popular dynamic SQL interfaces. This article shows how to implement the new performance tuning enhancement in the CLI. As an added benefit, you have to leverage iSeries Navigator's Performance Monitor feature to analyze any query's optimization goal.


Scott L. Forstie (, Senior Software Engineer, IBM

Scott Forstie is a senior software engineer at IBM, and he is the SQL development leader for DB2 for IBM i in Rochester, MN. Before working on DB2, he worked on UNIX® enablement for the AS/400® and S/390® systems.

Andrew J. Sloma (, Staff Software Engineer, IBM

Andrew Sloma works on the DB2 for i5/OS development team in Rochester, Minn. His responsibilities include development for the SQL CLI and native JDBC driver interfaces.

16 November 2006

Also available in Chinese


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
Table 1. Optimization goal defaults for each SQL interface
InterfaceDefault optimization goal
Native JDBC driverALLIO
Toolbox JDBC driverFIRSTIO - If extended dynamic packages are used, the default is All I/O.
iSeries access for Windows Open Database Connectivity (ODBC), Object Linking and Embedding (OLE) DB, and .NET clients.FIRSTIO - If extended dynamic packages are used, the default is All I/O.
Embedded static SQLFIRSTIO
Embedded dynamic SQLALLIO
  • 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)
  • 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.

Listing 1. CLI sample code controlling optimization goal
 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.

Figure 1. Run SQL Scripts interface
Run SQL Scripts interface

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 2. Comparing SQL Monitors
Comparing SQL Monitors

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.

Figure 3. SQL Monitor comparison output for query 1
SQL Monitor comparison output

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.

Figure 4. SQL Monitor comparison output for query 2
SQL Monitor comparison output for query 2

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.

Figure 5. Analyze output of optimization settings
Analyze output of optimization settings

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.

Figure 6. Visual Explain analysis
Visual Explain output

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.



Get products and technologies

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



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management, IBM i
ArticleTitle=Score optimization goals with SQL performance on DB2 for i5/OS