Query optimization in DB2 using REOPT

by | Mar 1, 2008

In Oracle world, DBAs generally say that Oracle can do static and dynamic SQL. When they say this, it amuses a DB2 DBA since Oracle way of static and dynamic SQL as perceived by an Oracle DBA is no way near to what is meant by static and dynamic SQL in DB2.

In Oracle, if you set CURSOR_SHARING to FORCE, a SQL using literals can be used to run with parameter markers.

For example: Query SELECT * FROM EMPLOYEE WHERE HIRE_DATE BETWEEN ’01-JAN-2000′ AND ’02-FEB-2000′ becomes as SELECT * FROM EMPLOYEE WHERE HIRE_DATE BETWEEN :SYS_0 AND :SYS_1.

The Oracle DBA will treat original query as dynamic but turned into static by the use of CURSOR_SHARING=FORCE. This is not what a DB2 DBA will mean by a static or dynamic SQL. In DB2 world, both of the above query will still be dynamic. The static query in DB2 is the one for which the access path is already determined ahead of the time and stored in the database with the use of the DB2 packages.

In any database including DB2, it is a quagmire to decide between parameterized and non-parameterized queries. SQL Server application developers like to write SQL without using parameter markers since that produces a better access paths than a query using parameter markers.

Oracle DBAs can choose to start using parameter markers without changing the SQL but by using CURSOR_SHARING=FORCE. This sounds very good in theory but this has inherent problems:

For example, the query SELECT * FROM EMPLOYEE WHERE HIRE_DATE BETWEEN ’01-JAN-2000′ AND ’02-FEB-2000′ should use an index since the result set is less than 1 percent. But if same query is using second date as ’02-FEB-2006′, the use of the index will be an overkill and a plain full table scan will be much better since the result set may be larger than 60% of the total number of rows in EMPLOYEE table. But, this can not be changed since CURSOR_SHARING=FORCE will use same access path no matter what values you use for the parameter markers.

DB2 suffers from same problem where using a SQL without parameter markers may produce a better access plan but you pay cost of the compilation of the SQL every time. If you use parameter markers, you can avoid the cost of compiling the SQL every time but you may end up with a sub-optimal plan since DB2 does not know the values of the parameter markers at the time of compilation of the SQL. Every database suffers from this problem.

But, there is a way to overcome this problem in DB2 by the use of the REOPT parameter. This parameter allows the best of both world where the use of the parameter markers, special registers, host variables are still used but DB2 does a peek on the parameters at run time and access path is optimized.

The REOPT bind option have 3 possible values:

  1. NONE (the default). The values of any host variables, parameter markers, or special registers in an SQL statement aren’t used to optimize this statement’s access path.
  2. ONCE. The first time it’s executed, the access path for a given SQL statement is optimized using the actual values for any host variables, parameter markers, or special registers. This access path will be used in all subsequent executions of the statement.
  3. ALWAYS. Each time that a statement containing host variables, parameter markers, or special registers is executed, the values of these variables are used to optimize the access path for the statement.

But how do you take advantage of the REOPT parameter?

Fortunately, you do not have to do anything in your SQL or in your application to do this change.

First, create NULLIDR1 and NULLIDRA collection sets in DB2. They are not there by default. Use these commands to create them. [Note: This is essential for static and dynamic SQL as well].

db2 connect to sample
db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA
db2 terminate

Static SQL

Examples of static SQL in DB2:

  1. A Stored procedures written in SQL PL
  2. A Stored procedure written using SQLJ
  3. Any application written in COBOL or C/C++ using embedded SQL statements.
  4. Any Java program using SQLJ
  5. Any Java program using method style SQL but taking advantage of pureQuery feature of DB2.

If you are using static SQL using CLI/ODBC driver, you will need to set REOPT=3 or 4 in db2cli.ini file or you can use REOPT option when using BIND command.

However, if you are using SQL PL stored procedures, you have 2 ways of setting REOPT parameter.

1. At global level by using 

C:\>db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL REOPT 3"

2. At session level by using

CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL EXPLSNAP ALL REOPT 3')

People generally ask a question – which one I should use ONCE or ALWAYS. In my experience, using ONCE gives you better performance so start with ONCE. A query that was taking 64 seconds ran in less than a second by using REOPT option for an application that was using static SQL by the use of the embedded SQL statements.

Dynamic SQL

Any application like C/C++/C#, COBOL, JAVA etc. that does not have embedded SQL statements will send dynamic SQL to DB2.

If you are using Type-4 JDBC driver property for your application, you can set use one of the collections by setting jdbcCollection property at the driver level.

Set following property in your Java program for the connection.

jdbcCollection=NULLIDR1 –> You will be using REOPT ONCE.

If you set

jdbcCollection=NULLIDRA —> then you will be using REOPT ALWAYS.

By using REOPT option, you will be having a much better performance than without using it. DB2 will do a much better job of reevaluating access path by peeking into parameter values at the run time but by also retaining other benefits of static SQL or parameter markers.

If you compare DB2 with SQL Server or Oracle, you will notice that DB2 takes middle approach of using parameter markers and still reevaluating a new access path at run time.

But, there are situations where REOPT(ONCE) does not solve the problem and the performance may actually degrade. This also makes life of a DBA much harder since the access path used at run time is different than then a DBA sees through the explain plan. A DBA will have to get different explain plan by using range of parameters and if access path varies, it is an indication of the problem where you do not get consistent performance – a promise that a DB2 DBA makes to his manager.

But, there is a solution to this issue also. This generally happens when there are frequency and quantile statistics present for that table. DB2 with frequency statistics may choose a specific access path (like using a smallest index) and this may be optimal for a specific parameter value but not for other values. DB2 optimizer without distribution statistics will not use a specific access path.

if you do this RUNSTATS command, it will gather distribution and quantile statistics for the table.

RUNSTATS ON TABLE vikram.employee WITH DISTRIBUTION AND INDEXES ALL

So, if you notice that even after using REOPT (ONCE) option, you are not getting a consistent performance, try creating statistics on the table without distribution and see the impact. For those situations, you will get a better result.

RUNSTATS ON TABLE vikram.employee

But, you should only do above change if you are still not getting the performance results that you expect.