By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,813 Members | 1,236 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,813 IT Pros & Developers. It's quick & easy.

SQL0101N The statement is too long or too complex.

P: n/a
aj
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 trigger body is thick, and causes other triggers to fire, but
no recursion. The triggers have been used in production for many
months w/ no issues..

Suddenly end-users that performed the triggering action started getting
-101/54001.

I scratched my noggin, but did the usual, upping dbm cfg STMTHEAP. I
took it from 3072->4068, a 50% increase. End-users still got the
-101/54001.

So I reversed the change I made to the trigger, making it as it was
before. Still people were getting the -101.

So I started removing SQL from the trigger body and testing each time.

I found that once I went below a certain threshold as far as trigger
body length, it would work. Then I would put SQL back, go above the
threshold, and again get -101.

Mind you it wasn't removing and adding /particular/ SQL that caused it -
it was /any/ SQL.

I finally made all the triggers as they originally were (including my
numeric constant change), and did an (unscheduled) instance bounce.
Afterwards, everything worked again w/ no -101...

This is the first time I've seen this w/ DB2. Its as if memory
associated w/ the trigger stack was allocated and never freed, and
bouncing the instance freed it..

Any thoughts?

I assume DB2 is primarily written in C - do the developers do their
own memory management ala free, or is a 3rd party garbage collection
library like Boehm used?

aj
Aug 22 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.