Short text
Number of parameter markers in a dynamic SQL statement causing Single Execution
Parameter Description
Setting this profile parameter to a value other than -1 causes the DBSL to assume any SQL statement with more than the specified auto_singleex_threshold number of host variables (parameter markers) will be treated as an SQL statement that will likely be executed only once (“single execution”). The advantage of single execution is SQL package space savings. The disadvantage of single execution is the additional prepare cost for SQL statements that are in fact executed more than once.
An alternative to single_execution for avoiding SQL package overflow whilst keeping SQL statements persistent to avoid prepare costs of reused SQL statements, is package distribution. Package distribution can be enabled by way of a table hint (see SAP Note: )
If you are encountering SQL package overflows (Syslog entries D52, please open an OSS ticket to BC-DB-DB4 so we have an opportunity to assist you in adding an appropriate profile parameter (dbs/db4/dbsl_tablehint) to optimize SQL statement management performance.
Application Area
Database
Default Value
0
Who is permitted to make changes?
DB administrator
Database System Restrictions
The parameter is only evaluated on DB2 for IBM i.
Are other parameters affected or dependent?
No.
Values allowed
-1: disable
0,1:100
other positive values n lower than or equal 10000: n
