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, COMMENTS, COMM_LIT]
The COMMENTS and COMM_LIT values are available in Db2® version 11.5.4 and later.
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, COMMENTS, or COMM_LIT, 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).

When a stmt_conc value of COMMENTS is used, then simple SQL comments appearing at the end of each line of the SQL statement are removed from the statement prior to inserting the statement into the dynamic SQL cache. Thus, when this setting is used the following statements would share a single entry in the dynamic SQL cache:
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO=10  -- issued from appserver ABC – issued at 2023-10-25-13:25
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO=10  -- issued from appserver QRZ – issued at 2023-10-21-08:02

As the comments beginning with – are removed from the statement text in the cache. The initial comment removed from the first statement issued prior to the insertion into the cache is captured in the stmt_comment monitor element.

When COMM_LIT is specified as the value for stmt_conc, then both the LITERALS and COMMENTS based concentration are in effect.