Blog
DB2utor

Categories

October 01, 2007

Smart Reoptimization for Dynamic SQL

Most applications on z/OS are written in COBOL and use static SQL. With static SQL you know the columns, tables and filter criteria needed before run-time execution. This is the most efficient way to access DB2. The program source code goes through a precompiler to pull out all of the SQL statements and put them in a Database Request Module (DBRM). (Note: DBRM is a data set member that’s created by the DB2 precompiler and that contains information about SQL statements.) The DBRM then goes through a BIND process, invokes an optimizer to build optimized access paths for each statement and then stores this information in the DB2 catalog as a package. The optimizer will use current statistics in the catalog for the table and the columns being processed. When the columns in the where predicate are being compared to a literal, DB2 is very good at figuring out an optimal access path.  However, if the comparison is to a host variable, DB2 has to use some default filter factors to define the access path. Sometimes the default is good and sometimes it can be bad, causing performance issues.

To help improve performance, DB2 V7 provided a new BIND parameter REOPT(VARS). This parameter was only for static SQL not dynamic. This parameter told DB2 to look at the value in the host variable, parameter markers and special registers at run time and build a new access path each time the statement is executed. Because of the overhead of doing a reoptimization at run time you’ll want to make sure you separate out the poor performing SQL and use REOPT(VARS) only on these statements.

Dynamic SQL was very limited on z/OS due to the cost of building the access path.  However, over time more and more work has moved off of z/OS to application servers connecting back to DB2 on z/OS. These applications are usually written in Java and all SQL is dynamic. These applications have the same problem because they’re using host variables and not literals. DB2 V8 came out with a new REOPT(ONCE) parameter to help dynamic SQL. However, the problem with this parameter is that DB2 would reoptimize the SQL but only once. If the first value found in the host variable wasn’t a good representation of values that will be used, then you may have a great access path for that first value but a poor execution path for the rest of the values being processed.

DB2 9 for z/OS introduced a new REOPT(AUTO) parameter optimizes the access path and stores it in the dynamic statement cache. If the host variable value changes, then another access path is built and stored in the cache. DB2 is now smart enough to not optimize the dynamic statement unless the values in the host variables have changed and it doesn’t have an existing access path for the given host variable value.

Did you find this information helpful?  Please post a comment.