DB2 10.5 for Linux, UNIX, and Windows

Statement concentrator

The statement concentrator modifies dynamic SQL statements at the database server so that similar SQL statements can share the access plan, thus improving performance.

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 memory usage. The statement concentrator avoids this memory usage by allowing compiled statements to be reused, regardless of the values of the literals. The memory usage that is associated with modifying the incoming SQL statements for a OLTP workload is small for the statement concentrator, when compared to the savings that are realized by reusing statements that are in the package cache.

The statement concentrator is disabled by default. You can enable it for all dynamic statements in a database by setting the stmt_conc database configuration parameter to LITERALS.

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 and output from the MON_GET_ACTIVITY_DETAILS table function show the original statement if the statement concentrator modified the original statement text. Other monitoring 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 issued:

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

These statements share the 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.

The statement concentrator requires that the length attributes for VARCHAR and VARGRAPHIC string literals to be greater than the lengths of the string literals.

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).

Because statement concentration alters the statement text, statement concentration impacts access plan selection. The statement concentrator works best when similar statements in the package cache have similar access plans. If different literal values in a statement result in different access plans or the value of a literal makes a significant difference in plan selection and execution time (for example, if the presence of the literal allows an expression to match an expression-based index key), then do not enable the statement concentrator for that statement.