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
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