DB2 10.5 for Linux, UNIX, and Windows

stmt_conc - Statement concentrator configuration parameter

This configuration parameter sets the default statement concentrator behavior.

Configuration type

Database

Parameter type

Configurable online
Configurable by member in a DB2® pureScale® environment

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, except for the values of literals in the statements, 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')

This parameter can have a significant impact on access plan selection because it alters the statement text. The 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.

The stmt_conc configuration parameter 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 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).