DB2 Version 10.1 for Linux, UNIX, and Windows

Statement concentrator reduces compilation overhead

The statement concentrator modifies dynamic SQL statements at the database server so that similar, but not identical, SQL statements can share the same access plan.

In online transaction processing (OLTP), simple statements might repeatedly be generated with different literal values. In such workloads, the cost of recompiling the statements can add significant overhead. The statement concentrator avoids this overhead by allowing compiled statements to be reused, regardless of the values of the literals.

The statement concentrator is disabled by default. It can be enabled for all dynamic statements in a database by setting the stmt_conc database configuration parameter to LITERALS.

The statement concentrator improves performance by modifying incoming dynamic SQL statements. In a workload that is suitable for the statement concentrator, the overhead that is associated with modifying the incoming SQL statements is minor compared to the savings that are realized by reusing statements that are already in the package cache.

If a dynamic statement is modified as a result of statement concentration, both the original statement and the modified statement are displayed in the explain output. The event monitor logical monitor elements, as well as output from the MON_GET_ACTIVITY_DETAILS table function show the original statement if the statement concentrator has modified the original statement text. Other monitor interfaces show only the modified statement text.

Consider the following example, in which the stmt_conc database configuration parameter is set to LITERALS and the following two statements are executed:

   select firstnme, lastname from employee where empno='000020'
   select firstnme, lastname from employee where empno='000070'

These statements share the same entry in the package cache, and that entry uses the following statement:

   select firstnme, lastname from employee where empno=:L0

The data server provides a value for :L0 (either '000020' or '000070'), based on the literal that was used in the original statements.

Because statement concentration alters the statement text, it has an impact on access plan selection. The statement concentrator should be used when similar statements in the package cache have similar access plans. If different literal values in a statement result in significantly different access plans, the statement concentrator should not be enabled for that statement.

The statement concentrator may cause the length attributes for VARCHAR and VARGRAPHIC string literals to be greater than the length of the string literal.

The statement concentrator might cause some built-in functions to return different result types. For example, REPLACE can return a different type when statement concentrator is used. The WORKDEPT column is defined as CHAR(3), the following query returns VARCHAR(3) when statement concentrator is disabled:

 SELECT REPLACE(WORKDEPT,'E2','E9') FROM EMPLOYEE

When stmt_conc=LITERALS, the two string literals are replaced with parameter markers and the return type is VARCHAR(6).