stmt_conc - Statement concentrator configuration parameter

This configuration parameter sets the default statement concentrator behavior.

Configuration type
Database
Parameter type
  • Configurable online
Propagation class
Statement boundary
Default [range]
OFF [OFF, LITERALS]
Upgrade Note
  • If you are upgrading from a Db2® Version 9.8 Fix Pack 4 pureScale environment or earlier, the value of stmt_conc is set to the value on member 0.

This configuration parameter enables statement concentration for dynamic statements. The setting in the database configuration is used only when the client does not explicitly enable or disable the statement concentrator.

When enabled, the statement concentrator modifies dynamic statements to allow increased sharing of package cache entries.

The statement concentrator is disabled when the configuration parameter is set to OFF. When the configuration parameter is set to LITERALS, the statement concentrator is enabled. When the statement concentrator is enabled, SQL statements that are identical (aside from literal values or simple comments) might share package cache entries.

For example, when stmt_conc is set to LITERALS, the following statements share an entry in the package cache
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO='000020' 
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO='000070'
The entry in the package cache uses the following statement:
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO=:L0
The Db2 database system provides the value for :L0 based on the literal used in the original statements:
:L0(either '000020' or '000070')

The setting of LITERALS can have a significant impact on access plan selection because it alters the statement text. The LITERALS statement concentrator must be used only when similar statements in the package cache have similar plans. For example, if different literal values in a statement result in different plans, then statement concentrator must not be set to LITERALS.

Setting the stmt_conc configuration parameter to LITERALS might 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 stmt_conc = LITERALS 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).