Using Reopt(ONCE) or REOPT(ALWAYS) with DSNTIAUL

Larry Kirkpatrick

Using Reopt(ONCE) or REOPT(ALWAYS) with DSNTIAUL
I just had a "ah ha" moment regarding query tuning (plug not intended).

I encountered a query with the following predicate:

WHERE MY_DATE_COL1 < CURRENT DATE

I did not know that DB2 treated the CURRENT DATE register like a host variable. I did know that REOPT(ONCE) and/or REOPT(ALWAYS) caused host variables to be evaluated at run time but, I did not know that this applied to the date/time/timestamp registers.

The above predicate originally had a filter factor of .0333 (as I said, DB2 treated it like a host variable). When I coded the query with the literal of todays date, the filter factor changed to .99 (which was very accurate). Running the original query had an elapsed time of 4.75 minutes and a CPU time of 49 seconds. Running it with a literal caused it to run in .19 minutes (of elapsed time) and 4.66 seconds of CPU time! Since I was running with DSNTIAUL, I tried rebinding the package with REOPT(ONCE) - removed the original query from the cache and reran. It then ran with the better performance.

I am thinking that as a general rule, it probably would be better to bind DSNTIAUL with REOPT(ONCE) and/or REOPT(ALWAYS). What are your thoughts and/or experiences on this?

Larry

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv