CODEWITHSUNDEEP
sqldb2heap-memorymonitoringquery-engine
Prashant Kumar Singh
Prashant Kumar Singh

Reputation: 3

Facing Error : -101 THE STATEMENT IS TOO LONG OR TOO COMPLEX in IBM DB2 SQL Run

Introduction: I work on DB2 databases in one of my projects. In one of the databases, a job that executes an SQL is running.

Problem: For the past few days, this job has been failing with the below error code.

-101 THE STATEMENT IS TOO LONG OR TOO COMPLEX

Description:

  1. Query is optimized to class 7.

  2. Query consists of a select statement with complex and multiple joins. This select statement is used to insert data into a table. ex:

INSERT INTO table_name (column_name_1, column_name_2, ....)

SELECT * from table_name_1 t1 join table_name_2 t2 on t1.col_1=t2.col_2;

However, the joins are much more lengthy and complex. Plus, a large number of columns are involved.

  1. This query was running for a long time in the system but never failed. This query was also never changed.

  2. The number of record counts in the source tables is also reducing with each passing day because the application is about to get decommissioned.

Solution Attempts:

  1. I tried to look for this error on the Internet and got this link.

Based on this I doubled the STMTHEAP value and the query ran but it failed again the next day. I further doubled the STMTHEAP value and then it ran but it failed again the next day. This is going on till date.

  1. I also tried to set the STMTHEAP value to Automatic but the result was the same as above.

Other resources that I used:

https://www.ibm.com/docs/en/db2/11.1?topic=parameters-stmtheap-statement-heap-size

https://www.ibm.com/docs/en/ias?topic=parameters-sortheap-sort-heap-size

Ask: Can any of you help me resolve this issue?

Upvotes: 0

Views: 575

Answers (0)

Related Questions