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 Db2® version 11.5.4 and later, Db2 provides two different mechanisms for statement concentration.

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.
Some applications append statement comments that begin with "--" (simple SQL comments). These comments are not relevant to the compilation of the statement nor to the access plan generated. They may simply indicate a source application server or some other accounting information. When these statements are processed in the dynamic SQL package cache, statements that are completely identical, but have a different comment appended to the end of the statement, will, by default end up as different statements in the package cache. This duplication of entries in the package cache can reduce the capacity and hit ratio of the package cache - as well as resulting in extra compilation overhead for statements that are effectively identical.
By setting stmt_conc to COMMENTS, Db2 will strip out the these simple SQL comments from the statement text prior to inserting it in the dynamic SQL cache. This will ensure that all statements that are identical aside from the contents of the comments will share a single entry in the dynamic SQL package cache. This allows for space savings in the package cache and avoids unnecessary compilations. The stripped comment that was removed when the statement was inserted into the package cache is saved in memory and is available as the stmt_comments in monitoring queries (for example MON_GET_PKG_CACHE_STMT).
For example, if the following statements were issued:
```
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO=?  -- issued from appserver ABC at 2024-11-10-12:05
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO=?  -- issued from appserver CDE at 2024-11-09-08:07
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO=?  -- issued from appserver QRZ at 2024-11-11-14:12
```
with stmt_conc set to COMMENTS they would share a single entry in the dynamic SQL cache.

Setting stmt_conc to COMM_LIT will perform both 'literal' and 'comment' concentration.

Note: The COMMENTS and COMM_LIT options are available in Db2 version 11.5.4 and later.