DB2 for SQL performance Optimization Goal evaluation on the I5/os

Brief introduction

DB2 for I5/os on V5R4 provides new ways to improve the performance of database queries through some popular dynamic SQL interfaces. The SQL call Level Interface (CLI) provides a new connection property for the user to tune the optimization target used by the database query. The Java Database connectivity (JDBC) interface for Ibm®developer kits for Java (also known as Native JDBC) and Toolbox also provides a new connection property to control the query optimization objective. Those who are familiar with System I and database query performance analysis and tuning will understand that controlling optimization goals is a key step in optimizing performance. Starting with V5R4, these dynamic interfaces can also take advantage of the direct procedural controls that appear elsewhere in System I. This article discusses the new CLI properties that are used to set optimization goals.

Optimizing the Target interface

In the past, an application executing an SQL statement could choose to tune the optimization goals through several mechanisms:

Query Options File (Qaqqini)--You can control the optimization goal of an entire system or a specific connection (task) by specifying the OPTIMIZATION_GOAL option as one of several values. Because the default optimization target value depends on the SQL interface being used, there is no default setting for this option. For the default values for each interface, refer to table 1.

*allio

*firstio

Table 1. Default optimization objectives for each SQL interface

Interface	Default optimization goal
Cli	Allio
Native JDBC Driver	Allio
Toolbox JDBC Driver	Firstio-If you use an extended dynamic package, the default value is all I/O
ISeries access for Windows Open Database Connectivity (ODBC), Object linking and Embedding (OLE) DB and. NET Clients	Firstio-If you use an extended dynamic package, the default value is all I/O
Embedded static SQL	Firstio
Embedded dynamic SQL	Allio
qsqprced API	Firstio
strSQL Utility	Firstio
Runsqlstm Utility	Allio

OPTIMIZE for N rows clause-you can build optimizations directly into your SQL request by using the OPTIMIZE for n rows clause. A smaller N value may cause the query optimizer to use the Firstio target, whereas if the value is large (for example, all ROWS), the query optimizer will use the Allio target.

CLI sqlsetconnectattr () API and Sql_attr_query_optimize_goal properties

Sql_all_io (default)

Sql_first_io

Toolbox JDBC Connection Properties "query optimize Goal" and DataSource method Setqueryoptimizegoal ()

0 = Use the default target *firstio, but if you use an extended dynamic package, use the target *allio

1 = *firstio

2 = *allio

Native JDBC Connection Properties "query optimize Goal" and DataSource method Setqueryoptimizegoal ()

0 = Use default target *allio

1 = *firstio

2 = *allio

New CLI and JDBC interfaces

The new CLI and JDBC options provide a more procedural and, in some cases, finer-grained ways to tune optimization goals. Because the new CLI connection property is scoped to the entire connection, it affects all SQL queries that are executed after the property is set. This is advantageous for environments that run many threads in server mode, because the environment can use different optimization goals on multiple connections if necessary. In addition, this makes setting optimization goals more useful for dynamic tuning strategies. Note that these JDBC and CLI interfaces only affect the connection to the System I server running on I5/os V5R4 or later.