SQL0101N The statement is too long or too complex. SQLSTATE=54001
in [DB2]
|
Prev: SQL0101N The statement is too long or too complex. SQLSTATE=54001
Next: DB2 8.2 Workgroup Server Edition limitattions
From: Dave Hughes on 2 Jul 2008 20:56 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.
From: Liam Finnie on 3 Jul 2008 09:36
On Jul 2, 8:56 pm, "Dave Hughes" <d...(a)waveform.plus.com> wrote: > 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. |