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

[Eric Davidson]

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.

[Dave Hughes]

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

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.

[Serge Rielau]

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

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

[Liam Finnie]

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

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_m em_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.