RE: The statement is too long or too complex - Very Urgent

RE: The statement is too long or too complex - Very Urgent

 

  

The SQL0101 typically translates back to a very large optimizer search space. That is, the number of potential access techniques is quite large and exceeds either stmtheap or applheapsz. Given this, reducing the search space may help. You may want to consider:

1) Reducing the optimization level (set current query optimization... or adjusting the db config)
2) Enable DB2_REDUCED_OPTIMIZATION
3) Disable DB2_EXTENDED_OPTIMIZATION (both 2 and 3 are db2set variables)
4) Increase stmtheap or applheapsz in the hopes of accommodating the memory demands of compiling the statement.
5) Or as Mike Abel suggested below -- simplifying/breaking up the statement.

Unfortunately, most of these are global changes with the exception of 1 (set current query...) and 5. As such, these changes may have a more widespread impact.

As for the other server -- check for configuration differences such as CPU speed/bufferpools/lock list etc. These will limit the number of plan candidates. Also, its possible that the config on the 'good' server results in a greedy join enumeration being fired off. This would also limit the optimizer's search space dramatically and avoid the SQL0101.


-----Original Message-----
From: Abel Mike J
[mailto:db2udbdba-ezmlmshield-x88744633.[Email address protected]
Sent: Wednesday, June 29, 2005 4:41 PM
To: LazyDBA Discussion
Subject: RE: The statement is too long or too complex - Very Urgent



What is the actual size of the statement? There is a max limit of 64K for the length. Try simplifying the statement. Also, if the statement involves executing complex triggers, this error might happen. Do whatever you can to decrease the size of the statment (smaller correlation names, temp table usage, etc).






-----Original Message-----
From: Harish Reddy V.
[mailto:db2udbdba-ezmlmshield-x2069382.[Email address protected]
Sent: Wednesday, June 29, 2005 12:08 PM
To: LazyDBA Discussion
Subject: The statement is too long or too complex - Very Urgent


Hi,

We are facing following when running long SQL (Informatica Mappings)

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

sqlstate = 54001

My STMTHEAP and APPLHEAPSZ are set to 4096.

I have run same SQL on different Server having same database having STMTHEAP 3072 and APPLHEAPSZ 4096. It has run successfully.

I would like what could be reason I was able to run same SQL in that particualar server. In order to run this, what should be done on my first server where I have got the above error.

Regards

Harish







---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



CONFIDENTIALITY NOTICE

This e-mail and any attachments, as well as any documents from a file server of International Truck
and Engine Corporation or its affiliates, are intended for the addressee and may contain information
that is privileged, confidential, proprietary, or otherwise protected by law. Any dissemination,
distribution, or copying is prohibited. If a confidentiality or nondisclosure agreement exists
between International and the recipient or the recipient's employer, this e-mail and any attachments
hereto, as well as any documents from a file server of International Truck and Engine Corporation or
its affiliates, this notice serves as marking as CONFIDENTIAL information of International Truck and
Engine Corporation or its affiliates. If you have received this communication in error, please
contact the original sender.


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page