KNOWLEDGE BASE

Error "The statement is too long or too complex" Ater Triggering Action Filter in IBM DB2


Published: 13 Feb 2017
Last Modified Date: 31 Mar 2017

Issue

After triggering an action filter on a crosstab, then Tableau Desktop has slow performance, and the following error might occur:

[IBM][CLI Driver][DB2/AIX64]SQL0101N The statement is too long or too complex. SQLSTATE = 54001

Environment

  • Tableau Desktop
  • IBM DB2

Resolution

Option 1

Increase the "STMTHEAP" parameter in the IMB DB2 database to at least 8000. For more information on configuring this variable, see IBM's documentation:
stmtheap - Statement heap size configuration parameter

Option 2

Sort dimensions in the source sheet alphabetically, especially dimensions containing numeric data. For more information on how to sort fields, see How to Sort Data (Computed Sorts).

Option 3

Do not include all fields in the Action Filter by selecting "Selected Fields" in the Edit Action Filter dialog, and adding only the necessary fields. For more information on how to set up specific filters in an Action Filter, see Filter Actions.

Cause

IBM DB2 sorts all values in such a way that every string value must be listed in the query rather than specifying a range using < and >. Specifying alphabetical sort order rather than datasource order in Tableau Desktop allows the query to use ranges.

Additional Information

The current size of the "STMTHEAP" can be determined in Tableau Desktop using a custom SQL query:
  1. Open Tableau Desktop
  2. Connect to the IBM DB2 data source
  3. Select a Schema
  4. Drag "New Custom SQL" into the data connection window
  5. Enter the following SQL query, and click OK:

    SELECT NAME, VALUE, VALUE_FLAGS, MEMBER
    FROM SYSIBMADM.DBCFG
    WHERE NAME = 'stmtheap'


For more information on this error, see SQL0101N The statement is too long or too complex at IBM.

 

Did this article resolve the issue?