Mandatory settings for DB CFG parameters

You must use the following DB2® settings specific to Sterling B2B Integrator.

The following table lists the database code set mandatory setting:

Parameter	Mandatory value
Database Code Set	UTF-8

The following table lists the mandatory settings for currently committed semantics and statement concentrator:

Feature	Description	Value
Currently Committed Semantics	With the Cursor Stability (CS) isolation level, if a transaction changes a row and another transaction tries to read that row before the first transaction commits the changes, the second transaction waits until the commit. With the currently committed semantics of the CS isolation level all the read operation gets the data before to the start of the write operation - readers do not block writers (reader avoid locking) and writers do not block readers (readers bypass locks). Currently committed semantics requires more log space for write operations. In addition, extra space is required for logging the first update of a data row during a transaction, which can have an insignificant or measurable impact on the total log space used. Performance considerations might be applicable in a database where there are significant lock conflicts when using currently committed semantics. The committed version of the row is retrieved from the log, and it performs better if you fetch the record directly from the log buffer, instead of reading from the logs, which is on the disk. Therefore, to improve the performance of retrieving previously committed data, you might consider an increase to the value of the logbufsz parameter.	Recommended value: ON. (The default setting is ON in all the latest version of DB2.)
Statement concentrator	DB2 uses less server resources while processing queries that have parameters instead of queries that have literal values in them. DB2 will compile an SQL statement once and will cache it. It presents the same query execution plan from the cache the next time for the same query thereby utilizing fewer resources to compile/prepare the same statement. However, it becomes a difficult task when SQL statements use literal values instead of parameters matching incoming statements to what is already present in the statement. To prevail over this situation, statement concentrator modifies dynamic SQL statements at the database server so that similar, but not identical, SQL statements can share the same access plan.	You can enable statement concentrator by running the following SQL statement: `db2 update db cfg for <db-alias> using stmt_conc literals`

Feature

Description

Value

Currently Committed Semantics

With the Cursor Stability (CS) isolation level, if a transaction changes a row and another transaction tries to read that row before the first transaction commits the changes, the second transaction waits until the commit.

With the currently committed semantics of the CS isolation level all the read operation gets the data before to the start of the write operation - readers do not block writers (reader avoid locking) and writers do not block readers (readers bypass locks).

Currently committed semantics requires more log space for write operations. In addition, extra space is required for logging the first update of a data row during a transaction, which can have an insignificant or measurable impact on the total log space used.

Performance considerations might be applicable in a database where there are significant lock conflicts when using currently committed semantics. The committed version of the row is retrieved from the log, and it performs better if you fetch the record directly from the log buffer, instead of reading from the logs, which is on the disk. Therefore, to improve the performance of retrieving previously committed data, you might consider an increase to the value of the logbufsz parameter.

Recommended value: ON.

(The default setting is ON in all the latest version of DB2.)

Statement concentrator

DB2 uses less server resources while processing queries that have parameters instead of queries that have literal values in them. DB2 will compile an SQL statement once and will cache it. It presents the same query execution plan from the cache the next time for the same query thereby utilizing fewer resources to compile/prepare the same statement.

However, it becomes a difficult task when SQL statements use literal values instead of parameters matching incoming statements to what is already present in the statement. To prevail over this situation, statement concentrator modifies dynamic SQL statements at the database server so that similar, but not identical, SQL statements can share the same access plan.

You can enable statement concentrator by running the following SQL statement:

db2 update db cfg for <db-alias> using stmt_conc literals