DB2- Optimization Hints

In Db2 , optimizer selects most optimum access path. Sometimes, your query taking longer cpu time, then, you can influence the default hint.

In Oracle, we can add optimizer HINT into SQL.

Ex:

SELECT * /*+ USE_ NL */ FROM S_EMP;

But, in DB2 the process is different.

The following steps are needed in DB2.

OPTIMIZATION HINTS on installation panel DSNTIP8 to YES.
BIND package with EXPLAIN (YES) to populate the plan_table.
Update the rows in the PLAN_TABLE with OPTHINT name (e.g., “goodpath”).
Set up the BIND package to use OPTHINT(‘goodpath’).
Query the PLAN_TABLE to see if the hint was used. Also note the messages in the BIND job, which tell you how many hints have been applied.

The important point like Oracle there is no standard HINTS. Just we need to follow, tweaking SQL steps. We also need to verify HINT name in PLAN_TABLE