DB2 optimization hints and profiles

Hints versus normal optimization

The optimizer is cost-based. A hint is you saying “use this path (or these options) anyway.” That is powerful and dangerous. A hinted index that is later dropped, or a hinted nested loop after the inner table grows 100×, will not magically stay cheap. Treat hints as documented exceptions, not as a substitute for RUNSTATS.

Subsystem parameter OPTHINTS must be YES or Db2 ignores PLAN_TABLE hint names. Statement-level hints live in the access path repository after BIND QUERY; they still depend on a supported Db2 level and matching SQL text.

Related but different: APREUSE on BIND/REBIND applies the previous package path as an internal hint. Prefer APREUSE and PLANMGMT when you only want stability. Use the techniques on this page when you need a path that is not “whatever we bound last week,” or when dynamic SQL must pick a named path.

Two families of hints

When both a PLAN_TABLE hint and a statement-level hint apply to the same statement, PLAN_TABLE hints take precedence. Do not maintain two conflicting stories for one SQL text.

PLAN_TABLE access path hints and OPTHINT

The older, well-known method: capture a good explain, then tell Db2 to reuse those PLAN_TABLE rows as the path.

1. EXPLAIN the statement (or BIND EXPLAIN(YES)) so PLAN_TABLE has the path you like.
2. Copy or update those rows: set OPTHINT to a name you choose (for example GOODIX1). Adjust METHOD, ACCESSTYPE, ACCESSNAME, MATCHCOLS, and related columns if you are forcing a different path than the last explain—only columns IBM documents as hintable.
3. For static SQL, BIND or REBIND the package with OPTHINT(GOODIX1).
4. For dynamic SQL, issue SET CURRENT OPTIMIZATION HINT = 'GOODIX1' in the same session (the executing authid's PLAN_TABLE is the one Db2 reads).
5. Re-EXPLAIN. HINT_USED should show the hint name when it applied. If it is blank, the hint was invalid or not found.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- After EXPLAIN, tag the path
UPDATE PLAN_TABLE
   SET OPTHINT = 'GOODIX1'
 WHERE QUERYNO = 1001
   AND EXPLAIN_TIME = '2026-08-13-10.00.00.000000';
 
-- Dynamic SQL in the same SQLID:
SET CURRENT OPTIMIZATION HINT = 'GOODIX1';
 
EXPLAIN PLAN SET QUERYNO = 1002 FOR
  SELECT EMPNO, LASTNAME
    FROM HR.EMPLOYEE
   WHERE WORKDEPT = 'A00';
 
SELECT HINT_USED, ACCESSTYPE, ACCESSNAME, MATCHCOLS
  FROM PLAN_TABLE
 WHERE QUERYNO = 1002
 ORDER BY QBLOCKNO, PLANNO;

1
2
3
REBIND PACKAGE (HR.EMPPGM.(V2))
  OPTHINT(GOODIX1)
  EXPLAIN(YES)

Weak spots of PLAN_TABLE hints:

• They hang off QUERYNO and an authorization ID's PLAN_TABLE. Adding a statement in the program can renumber QUERYNO.
• Dynamic hints follow the current SQLID PLAN_TABLE—easy to miss in a shop with many IDs.
• Invalid hints (dropped index, impossible METHOD combination) fall back to normal optimization, sometimes without anyone noticing unless you check HINT_USED.

That is why IBM later added statement-level hints that match SQL text.

Statement-level hints (optimization profiles on z/OS)

On Db2 for Linux, UNIX, and Windows, an optimization profile is often an XML document. On Db2 for z/OS, the equivalent “profile” is the access path repository: you load input tables and run BIND QUERY. People still say “optimization profile” for this set of objects.

DSN_USERQUERY_TABLE

Create the user query table (sample DDL is in SDSNSAMP with the other EXPLAIN tables). Each row identifies a statement:

• QUERY_TEXT — the SQL text Db2 will match (formatting rules matter; tools often copy from SYSPACKSTMT or the cache)
• SCHEMA — default schema used when the statement was bound/prepared
• HINT_SCOPE — system-wide versus package
• COLLECTION, PACKAGE, VERSION — optional package scope
• Option columns: REOPT, STARJOIN, MAX_PAR_DEGREE, DEF_CURR_DEGREE, SJTABLES, and related
• QUERYNO — ties to PLAN_TABLE rows when you are forcing an access path

Runtime / option hints need only DSN_USERQUERY_TABLE (unique QUERYNO).Access path hints also need correlated PLAN_TABLE rows with the same QUERYNO. Selectivity overrides use DSN_PREDICAT_TABLE and DSN_PREDICATE_SELECTIVITY so you can tell the optimizer “this predicate is more selective than SYSCOLDIST says.”

BIND QUERY and FREE QUERY

1
2
3
4
5
6
BIND QUERY LOOKUP(NO)
-- Reads DSN_USERQUERY_TABLE (+ PLAN_TABLE / predicate tables)
-- Inserts SYSIBM.SYSQUERY and SYSIBM.SYSQUERYOPTS
 
FREE QUERY QUERYID(integer)
-- Removes a repository hint

After a successful BIND QUERY, delete or disable the input row in DSN_USERQUERY_TABLE so a later BIND QUERY does not overlay the repository by accident. Validate with EXPLAIN: look for ACCESSPATH_HINT, SELECTVTY_OVERRIDE, or OPTION_OVERRIDE style indicators in EXPLAIN output for your version, and for HINT_USED on PLAN_TABLE.

Static statements pick up statement-level hints at REBIND. Dynamic statements pick them up at prepare. Scope search is typically package version, then package, then system-wide—most specific wins among statement-level hints.

What you can influence

• Access path — index versus scan, join method, join sequence (via PLAN_TABLE columns Db2 accepts as hints)
• REOPT — NONE / ONCE / ALWAYS / AUTO for that statement
• Parallelism — MAX_PAR_DEGREE, DEF_CURR_DEGREE (1 versus ANY)
• Star join — STARJOIN, SJTABLES
• Filter factors — predicate selectivity overrides when stats lie for one statement

You generally cannot hint “run this 50% faster.” You hint how to run. Measure with accounting and EXPLAIN cost after the hint sticks.

A safe procedure

1. Prove the path in a sandbox with EXPLAIN and a representative data volume.
2. Prefer fixing RUNSTATS, clustering, or SQL first.
3. If you hint, name it, document why, and record the QUERYID / OPTHINT name in change control.
4. REBIND or prepare, confirm HINT_USED (or the statement-level override flags).
5. After the next Db2 version or index change, re-validate. Hints rot.

Virtual indexes (DSN_VIRTUAL_INDEXES) are a what-if input for EXPLAIN, not a production hint. Do not confuse “pretend this index exists” with “force this path in production.”

Explain It Like I'm Five

The optimizer is a GPS that picks a driving route from traffic reports (statistics). A hint is you writing on a sticky note “always take Maple Street.” OPTHINT is the name on that sticky note, stuck to a map you already drew (PLAN_TABLE). Statement-level hints are a rule at the city hall: “any car whose trip description matches this sentence must take Maple Street,” even if the car did not bring your old map. BIND QUERY is filing that city-hall rule. If Maple Street is closed next year and you forget to peel off the sticky note, you still try to drive it—so check the note when roads change.

Exercises

1. Find OPTHINTS on your subsystem. If it is NO, ask why before experimenting.
2. EXPLAIN a sandbox SELECT, UPDATE PLAN_TABLE.OPTHINT, SET CURRENT OPTIMIZATION HINT, EXPLAIN again, and read HINT_USED.
3. List three reasons HINT_USED might stay blank.
4. Compare APREUSE versus OPTHINT in one paragraph for your team wiki.
5. Read SDSNSAMP for DSN_USERQUERY_TABLE and sketch the columns you would fill for a package-scoped option hint that sets REOPT(ALWAYS).