IBM Support

Query using Text Search indexes results with error: IQQP9002E query exceeded maximum number of permitted BooleanQuery clauses

Troubleshooting


Problem

Query using Text search Indexes results with error: IQQP9002E query exceeded maximum number of permitted BooleanQuery clauses

Symptom

Execution of query when using Text search indexes results in the following error written to the db2diag.log file.

2013-11-04-12.23.18.794210-480 I17178E672 LEVEL: Error
PID : 14687 TID : 139895632815872 PROC : db2sysc 0
INSTANCE:  NODE : 000 DB :
APPHDL : 0-7 APPID:
AUTHID :  HOSTNAME:
EDUID : 21 EDUNAME: db2agent () 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging function), probe:30
MESSAGE : Message
DATA #1 : String, 150 bytes
"IQQP9002E The specified wildcard query cannot be processed because the
query exceeded the maximum number [10000] of permitted BooleanQuery
clauses."
 

Cause

Text Search defaults expansion size for optimal performance.

The parameter to override this limit is: QueryExpansionLimit

Environment

Environments using Text Search Indexes

Diagnosing The Problem

If the following error is written to the db2diag.log file when executing a Text Search Query then the queryExpansionLimit parameter may be affecting the ability to run the query.

2013-11-04-12.23.18.794210-480 I17178E672 LEVEL: Error
PID : 14687 TID : 139895632815872 PROC : db2sysc 0
INSTANCE:  NODE : 000 DB :
APPHDL : 0-7 APPID:
AUTHID :  HOSTNAME:
EDUID : 21 EDUNAME: db2agent () 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging function), probe:30
MESSAGE : Message
DATA #1 : String, 150 bytes
"IQQP9002E The specified wildcard query cannot be processed because the
query exceeded the maximum number [10000] of permitted BooleanQuery
clauses."
 

Resolving The Problem

For DB2 Version 10.1 and earlier, the parameter can be modified by updating the sqllib/db2tss/config/config.xml file and adding
<queryExpansionLimit>4096</queryExpansionLimit>

The default value is 1024.

For DB2 Version 10.5, the configTool.sh which is found in the <instance path>/db2tss/bin directory must be used to configure the parameter.


Execute:

configTool.sh configureParams -configPath ../config -queryExpansionLimit 300000

Note that there may be performance implications in setting the queryExpansionLimit this high. Ensure the query has been written correctly to reduce number of rows returned.

Once the queryExpansionLimit has been updated, Text Search must be restarted. Issue:

db2ts stop for text

db2ts start for text

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Extenders - Text","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;9.5;10.1;10.5","Edition":"Advanced Enterprise Server;Enterprise Server;Express;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
07 December 2022

UID

swg21656114

Page Feedback