Many years ago a BIND option called REOPT(VARS) was introduced in DB2. This option was later renamed to REOPT(ALWAYS). The option works on static SQL using hostvariables and will force the DB2 optimizer to re-evaluate the access path on execution time based on the actual values of the hostvariables. This way DB2 may choose a better access path than the one chosen at BIND time.

Since the introduction DBA's has hated this option as it influences all SQL statements in the program/package and not just the SQL statements that might benefit from using REOPT(ALWAYS). Imagine a progam with a totally simple and efficient SQL statement executed millions of times during a day and in the same program you have a so-called "query from hell" with lost of BETWEEN's and LIKE's which typically are coded to be used for general purpose searches used a few times a day. If REOPT(ALWAYS) is enabled for such a program then DB2 will evaluate a new access path for both statements at each execution and the one being executed millions of times will become extremely heavy because of the constant re-evaluation while the seldom used SQL statement may benefit from REOPT(ALWAYS), but not enough to justify the poor performance of the once effective and still heavily used statement.

IBM has of course realised the poor design and in DB2 version 10 functionality to define REOPT(ALWAYS) on statement level was introduced. Meanwhile DBA's has taught themselves to reject any attempts to use REOPT(ALWAYS) even with the new functionality, typically because its existence is unknown. Unfortunately it is also quite hard to use REOPT(ALWAYS) on statement level, but I hope this tip will help you get there.

First step is to create a table called DSN_USERQUERY_TABLE. With 99% certainty there is such a table already defined on your DB2 system. You just need to create your own copy. Next step is to create a PLAN_TABLE and you probably already have one. Then you need to create an index on PLAN_TABLE consisting of the following columns in this order: QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID, OPTHINT. All these "up front" activities you just need to carry out once.

Now you are ready to activate REOPT(ALWAYS) on your "query from hell". Choose a QUERYNO not present in your PLAN_TABLE. I have chosen 117. Then you have to insert a row in DSN_USERQUERY_TABLE using this QUERYNO (and it must not be present here as well). Now assuming your SQL statement is located in program HELLPGM in collection HELLCOLL in section 10 (Query Monitor or CA Detector can help you identify the section your statement is placed in) you may use the following INSERT:

INSERT INTO DSN_USERQUERY_TABLE
( QUERYNO, SCHEMA, HINT_SCOPE, COLLECTION, PACKAGE, VERSION, REOPT
, SELECTVTY_OVERRIDE, ACCESSPATH_HINT, OPTION_OVERRIDE, QUERY_TEXT
)
SELECT 117, P.QUALIFIER, 1, P.COLLID, P.NAME, '*', 'Y', 'N', 'N', 'Y'
     , S.STATEMENT
FROM SYSIBM.SYSPACKSTMT S
   , SYSIBM.SYSPACKAGE P
WHERE P.LOCATION = ''
  AND P.COLLID = 'HELLCOLL'
  AND P.NAME = 'HELLPGM'
  AND P.LOCATION = S.LOCATION
  AND P.COLLID = S.COLLID
  AND P.NAME = S.NAME
  AND P.CONTOKEN = S.CONTOKEN
  AND S.SECTNO = 10
  AND S.EXPLAINABLE = 'Y'
  AND P.LASTUSED >= CURRENT DATE

If your program is not executed every day on the DB2 system where you want to use REOPT(ALWAYS) the above SQL will not work as the clause AND P.LASTUSED >= CURRENT DATE only selects the program/package if it has been used today. The clause prevents copying a lot of rows belonging to old versions of the package not in use any more. Please note column VERSION is set to '*' which means that all future versions of your program with exactly the same statement as in QUERY_TEXT will be able to use REOPT(ALWAYS). DSN_USERQUERY_TABLE can be used for other exiting stuff, but to activate REOPT(ALWAYS) the column REOPT must be set to 'Y'.

Next thing is to execute a so-called BIND QUERY. This simple DB2 command will translate the contents of your DSN_USERQUERY_TABLE and put it into two new SYSIBM tables called SYSIBM.SYSQUERY and SYSIBM.SYSQUERYOPTS. First at this point DB2 becomes aware of your intentions of using REOPT(ALWAYS) on a single SQL statement in your program.

Last step is to execute a REBIND of your program. Unfortunately the output from the REBIND command does not tell you whether DB2 accepted your REOPT(ALWAYS) or not. There is though a way to examine this by using the following SQL:

SELECT COLLID, NAME, VERSION, STATUS, STATEMENT
FROM SYSIBM.SYSPACKSTMT
WHERE STATUS = 'G'
  AND NAME = 'HELLPGM'
  AND COLLID = 'HELLCOLL'
  AND LOCATION = ''

The result should preferably be a single row where the column STATEMENT contains the SQL you want REOPT(ALWAYS) to work for. Now you finally know that your REBIND was a success.

We have four known "queries from hell" at my installation where we have used the above procedure. It has worked miracles for three of them where response times and CPU usage has dropped by a factor of ten. Unfortunately it did not make any difference for the last and worst of of them, so hope for miracles, but do not expect them. One of the challenges by using REOPT(ALWAYS) is that you do not know anything about the different access pathes DB2 chooses. So if REOPT(ALWAYS) does not improve or even makes things worse there is no way to find out why.

This tip does not go much into details as it is a large subject. However there is much more information in the presentation DB2 10 for z/OS optimization hints.