DB2 and optimization guidelines

Introduction
Create a simple table and put several rows into it.

create table testts (ide int, name varchar2(100))
create index ideindx on testts(ide)
insert into testts values(10,'NAME 10')
insert into testts values(12,'NAME 11')
insert into testts values(13,'NAME 12')
insert into testts values(14,'NAME 13')

Then consider simple SELECT statement

select from testts where ide = 10

Take a look at the access plan for this statement.

As one can imagine DB2 scans index in search for IDE value then picks up the rows from the table and it seems to be reasonable, much better then scanning through the table.
But assume that for some important reason we are sure that for that particular statement ignoring the index and scanning the table is better and runs faster. How to force DB2 to overcome standard behaviour and use another ?
Oracle 
In Oracle the answer is simple, use hints. We have to modify this statement and we are done.

select /*+ full(testts)  */ * from testts where ide = 10

DB2 
In DB2 answer is a bit more complicated and means "optimization guidelines". Firstly we have to enable our DB2 instance for using optimization guidelines.

db2set DB2_OPTPROFILE=YES

Then connect to database and execute a command:

db2 "call sysinstallobjects('opt_profiles', 'c', '', '')"

This command creates SYSTOOLS.OPT_PROFILE containing all optimization profiles and guidelines.
Prepare optimization guidelines
Having DB2 instance and database enabled for guidelines we have to create a XML file containing directives for DB2 engine for that particular statement (opt.xml)

<?xml version="1.0" encoding="UTF-8"?> 

<OPTPROFILE VERSION="9.1.0.0"> 

<STMTPROFILE ID="Guidelines for simple select"> 
  <STMTKEY SCHEMA="DB2INST1"> 
select 
  * 
from 
  testts 
where 
  ide=10 
</STMTKEY> 
<OPTGUIDELINES> 
<TBSCAN TABLE='TESTTS'/> 
<!-- <IXSCAN TABLE="TESTTS" INDEX="ideindx"/> --> 
</OPTGUIDELINES> 
</STMTPROFILE> 

</OPTPROFILE>

This optimization profile contains statement and guidelines for DB2 engine how to evaluate this statement. It simply says that DB2 should scan TESTTS table for this statement. Of course, one optimization profile can contain many guidelines for different statements issued by our application. Next step is to import this guidelines into SYSTOOLS.OPT_PROFILE to be used by DB2 engine. Prepare a file (opt.del)

"DB2INST1","SELECT_SIMPLE_OPTIMIZER","opt.xml"

and run a command:

db2 "import from opt.del of del modified by lobsinfile insert_update into 
systools.opt_profile"
db2 " FLUSH OPTIMIZATION PROFILE CACHE"

SELECT_SIMPLE_OPTIMIZER is the name of the profile. We can keep a lot of different profiles but only one can be used by the connection.
CLP, how it works 
To have a connection enabled for a given optimization profile we have to issue a command:

db2 SET CURRENT OPTIMIZATION PROFILE=SELECT_SIMPLE_OPTIMIZER

Very important: This option (CURRENT OPTIMIZATION PROFILE) is enabled only for this connection and expires when connection is closed.
 But how to verify that it is working ? With or without index this statement yields the same result. To verify it execute a command:

db2 SET CURRENT EXPLAIN MODE YES

The SQL statements are evaluated and executed normally but this command causes additionally that explain plan is saved in explain tables and can be viewed after. So now run a command again:

db2 "select * from testts where ide = 10"

And (at the server, not client, side) run a db2exfmt command to pick up the latest access plan.
db2exfmt
After running db2exfmt firstly look at the header and be sure that it is the latest plan, not the plan created the previous day.

DB2_VERSION:       10.01.1
SOURCE_NAME:       SQLC2J23
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2012-11-19-14.32.05.016549
EXPLAIN_REQUESTER: DB2INST1

This header information tells us that our optimization profile is enabled for this statement.

Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
        DB2INST1.SELECT_SIMPLE_OPTIMIZER
STMTPROF: (Statement Profile Name)
        Guidelines for simple select

And last but not least the access plan itself.

Optimized Statement:
-------------------
SELECT
  10 AS "IDE",
  Q1.NAME AS "NAME"
FROM
  DB2INST1.TESTTS AS Q1
WHERE
  (Q1.IDE = 10)

Access Plan:
-----------
        Total Cost:             6.82167
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
        1
     TBSCAN
     (   2)
     6.82167
        1
       |
        3
 TABLE: DB2INST1
     TESTTS
       Q1

IBM Data Studio
So far so good but regarding CLP (Command Line Processor). But what about other method of working with DB2, for instance IBM Data Studio  (free download)?
In IBM Data Studio we have to modify the connection parameter:

Then we can check the access plan for our simple SQL statement:

Java 
CLP and IBM Data Studio are great tools but we use them rather for developing and administering then running business application. In Java to take advantage of optimization profile it is necessary to modify URL used for connecting to DB2. Simple application is available here.

The URL:

        private static final String url = "jdbc:db2://aixt:60004/sample:currentExplainMode=YES;optimizationProfile=SELECT_SIMPLE_OPTIMIZER;";

After running this application we have to check the access plan by running db2exfmt tools at the server side. Warning: currentExplainMode is used only for testing purpose. If we are sure that optimization profile is in force we can remove it because it could bring down performance a little bit.
C++, ODBC, CLI 
Simple C++ application is available here.
If we use SQLDriverConnect we can modify the connection string:

#define CONNECTSTRING "DSN=SAMPLET;UID=db2inst1;PWD=db2inst1;CURRENTOPTIMIZATIONPROFILE=SELECT_SIMPLE_OPTIMIZER;DB2Explain=2"
...
    if (ConHandle != 0)
      RetCode = SQLDriverConnect(ConHandle,NULL,(SQLCHAR *)CONNECTSTRING,SQL_NTS,(SQLCHAR*)OutConnStr,255,&OutConnStrLen,SQL_DRIVER_NOPROMPT);

Another possibility (particularly if we use SQLConnect or do not have access to source code) is to modify db2cli.ini file at the client (not server) side. We can modify it manually or execute the following statements (assuming that SAMPLET is the name of the database alias).

db2 UPDATE CLI CONFIGURATION FOR SECTION SAMPLET USING CURRENTOPTIMIZATIONPROFILE SELECT_SIMPLE_OPTIMIZER
db2 UPDATE CLI CONFIGURATION FOR SECTION SAMPLET USING DB2Explain 2

The db2cli.ini file should contain:

[SAMPLET]
DB2Explain=2
CURRENTOPTIMIZATIONPROFILE=SELECT_SIMPLE_OPTIMIZER

After running our C++ application we can check the effectiveness of the change by running db2exfmt at the server side.
Summary 
As we can see optimization profile is very effective and powerful tool to modifying the way how DB2 executes SQL statements. Of course - we can do much more then forcing DB2 to use or not to use index. More information is described here. Comparing to Oracle hints it is a bit more complicated and requires different methods relating on the access method used by the application. But on the other hand we don't need access to source code to modify the access plan.