[DB2-L] Using Reopt(ONCE) or REOPT(ALWAYS) with DSNTIAUL

Walter Janißen

[DB2-L] Using Reopt(ONCE) or REOPT(ALWAYS) with DSNTIAUL
Hi Larry

Very interesting. I didn't know that before. I will also consider binding DSNTIAUL and DSNTEP2(4) with REOPT(ONCE).

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Larry
Gesendet: Freitag, 7. Januar 2011 17:26
An: [login to unmask email]
Betreff: [DB2-L] 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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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