473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

SQL0101N The statement is too long or too complex. SQLSTATE=54001

DB2 9.5

I keep geting the message.

SQL0101N The statement is too long or too complex. SQLSTATE=54001

When one of my sql statements takes over 60 seconds to compile the sql
statement.
Is there any parameter that controls how long DB2 allows a statement
to compile for.

I have increased the statement heap size and this does not solve the
problem.

Eric.
Jul 2 '08 #1
3 15794
Eric Davidson wrote:
DB2 9.5

I keep geting the message.

SQL0101N The statement is too long or too complex. SQLSTATE=54001

When one of my sql statements takes over 60 seconds to compile the sql
statement.
Is there any parameter that controls how long DB2 allows a statement
to compile for.

I have increased the statement heap size and this does not solve the
problem.

Eric.
How much have you increased the statement heap? With one extremely
complex query we wound up increasing it to 32768 (pages, or 128Mb)
before we got rid of the warnings about sub-optimal performance (we
didn't get full blown errors in our case).

Strangely, we're on 9.5, and despite STMTHEAP being set to AUTOMATIC,
it didn't increase the allocation by a single page while these warnings
were occurring - eventually we set it to manual and just kept doubling
the setting until the warning disappeared. The actual amount we
required is probably somewhere between 64Mb and 128Mb, but we're not
short of memory on the server, so I didn't bother refining the setting
to find out where the cutoff was.
Cheers,

Dave.
Jul 3 '08 #2
Eric Davidson wrote:
DB2 9.5

I keep geting the message.

SQL0101N The statement is too long or too complex. SQLSTATE=54001
Is this an UPDATE/DELETE or INSERT statement?
Do you have triggers on the table and/or RI?
Does the trigger update other tables which have triggers, which....
(you get the picture).
Similar scenarios can happen with SQL Functions calling SQL Functions.
Oftentimes I have seen stuff liek this:
VALUES CASE WHEN compelxfoo() = 1 THEN 1 WHEN complexfoo() = 2 THEN 2
.....END

Teh macro extension of SQL Function and triggers can get you in trouble...

So please post (or send me if you feel more comfortable with it) more
info (statement text and involved object defs)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 3 '08 #3
On Jul 2, 8:56 pm, "Dave Hughes" <d...@waveform.plus.comwrote:
Eric Davidson wrote:
DB2 9.5
I keep geting the message.
SQL0101N The statement is too long or too complex. SQLSTATE=54001
When one of my sql statements takes over 60 seconds to compile the sql
statement.
Is there any parameter that controls how long DB2 allows a statement
to compile for.
I have increased the statement heap size and this does not solve the
problem.
Eric.

How much have you increased the statement heap? With one extremely
complex query we wound up increasing it to 32768 (pages, or 128Mb)
before we got rid of the warnings about sub-optimal performance (we
didn't get full blown errors in our case).

Strangely, we're on 9.5, and despite STMTHEAP being set to AUTOMATIC,
it didn't increase the allocation by a single page while these warnings
were occurring - eventually we set it to manual and just kept doubling
the setting until the warning disappeared. The actual amount we
required is probably somewhere between 64Mb and 128Mb, but we're not
short of memory on the server, so I didn't bother refining the setting
to find out where the cutoff was.

Cheers,

Dave.
Hi Dave,

This behaviour you notice is documented with the stmtheap database
configuration parameter:

Recommendation: In most cases the default AUTOMATIC setting for this
parameter is acceptable. When set to AUTOMATIC, there is an internal
limit on the total amount of memory allocated during the dynamic
programming join enumeration phase of compilation. If this limit is
exceeded, the statement is compiled using greedy join enumeration, and
is only limited by the amount of remaining appl_memory or
instance_memory, or both. If your application is receiving SQL0437W
warnings, and the runtime performance for your query is not
acceptable, you might want to consider setting a sufficiently large
manual stmtheap value to ensure that dynamic join enumeration is
always used.

So, in your case, you were hitting the internal memory limit during
dynamic join enumeration, so DB2 automatically switched to greedy join
enumeration to complete the query compilation (which uses much less
memory). The rationale for this behaviour is that some queries may
require huge amounts of memory during dynamic join enumeration, and we
don't want a single query compilation to consume all the memory on the
box. Once we fallback on greedy join enumeration, we lift the
internal memory limit (only when stmtheap is set to AUTOMATIC), to try
to ensure we can succesfully compile the statement.

Eric,

If you get the SQL0101N error even when stmtheap is set to AUTOMATIC
(as opposed to a SQL0437W warning), then changing stmtheap to a fixed
value (no matter how large) will not likely fix the problem (since
setting stmtheap to a fixed value imposes an upper bound on the heap,
whereas there is no fixed heap upper bound during greedy join
enumeration when it's set to AUTOMATIC). It's possible that you're
running out of memory for the entire instance (you can use the
admin_get_dbp_mem_usage table function or 'db2pd -dbptnmem' to query
your instance's memory consumption). Or, there may be other reasons
why the compiler is unable to compile your statement - you may need to
alter your statement, or change your optimization class settings (I'm
not too familiar with the compiler/optimizer's inner workings, but if
you post your SQL statement, I'm sure some other forum lurkers may be
able to help you out :-) ).

Cheers,
Liam.
Jul 3 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: db2sysc | last post by:
We have defined a NICKNAME to go to Mainframe table and when do a SELECT * FROM NICKNAME from a version 8 client we get SQL0101N error. The same select from V7 client works fine. The SELECT...
3
by: Jason Gyetko | last post by:
I'm running DB2 v8.1 FP5 and am trying to link tables via ODBC from Access 2002 to my DB2 database. Access lets me connect to the database and returns a list of all tables I can select from, but...
3
by: Klemens | last post by:
Hi, I get an implicit -101 on an alter table statement on changing the length of one column ALTER TABLE XTRADE.LIEFERANT ALTER COLUMN ZENTRALE_NUMMER SET DATA TYPE VARCHAR(13) DB21034E The...
5
by: Jurgen Haan | last post by:
can anyone give me any information about this message? SQL State: 51002 Native: -805 Message: SQL0805N Package "NULLID.SYSLH203 0X5359534C564C3031" was not found. SQLSTATE=51002 - Error...
1
by: Michael | last post by:
I want to insert a row into an ITEM table if certain SKUs are inserted. There are two triggers where each looks for a particular SKU and inserts the appropriate matching row in the same table. ...
0
by: java7man | last post by:
All - in writing some UDFs I am getting this error SQL0101N The statement is too long or too complex. What I am doing is not that complex. Basically, performing 5 0r 6 queries...
0
by: Chakhari | last post by:
I used a framework that save data in the data base DB2 when i try to save in different tables in the same time there' s an error sql SQL0101N The statement is too long or too complex. ...
0
by: aj | last post by:
DB2 8.2 FP14 LUW Red Hat AS Interesting one here. I modified a trigger body yesterday, changing a numeric constant used in in INSERT statement. I did not add any SQL to the trigger.. The...
1
by: bhavna.k.dwivedi | last post by:
Hi, I am getting the error "SQL0101N The statement is too long or too complex. SQLSTATE=54001" while trying to rebind a package on the database. I am connected as instance owner.Here's the info...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.