IBM Support

DB2 Text Search performance degradation when STMT_CONC is set to LITERALS

Troubleshooting


Problem

Turning statement concentrator ON results in sub-optimal plan generation for Text Search queries.If the performance of queries with statement concentrator enabled is unacceptable, then statement concentrator should be disabled either at global leval or at statement/session level.

Resolving The Problem

You can turn statement concentrator on and off globally with the STMT_CONC database configuration parameter.

For CLI/ODBC, you can set the SQL_ATTR_STMT_CONCENTRATOR connection or statement attribute.

For .NET applications, you can use DB2ConnectionStringBuilder.StatementConcentrator and DB2Command.StatementConcentrator.

For SQLJ and JDBC, the statementConcentrator property can be set in com.ibm.db2.jcc.DB2BaseDataSource.

For embedded SQL and SQL/PL or PSM, there is no statement level control.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21607350

Page Feedback