SQL4301N RC4 and DB2ROUTINE_DEBUG

[Rhino]

Just a quick note to report on the resolution of a problem that gave me
serious grief in January. I put it aside for a while and, largely by
accident, found the solution a few minutes ago.

In short, I found myself getting SQL4301N Reason Code 4 - "The Java
interpreter has terminated itself and cannot be restarted." - whenever I
tried to execute a stored procedure from a client. I also got it whenever I
tried to run a stored procedure in the Stored Procedure Builder, EXCEPT for
the first run after I built the procedure. In other words, if I built a
stored proc, I could run it successfully once but any subsequent time I
tried to run it, I would get the SQL4301N RC4. That meant the only way I
could run stored procs was in the Builder and then only if I rebuilt it each
time before I ran it, even though it hadn't changed.

I should mention that I am running DB2 V7.2.7, i.e. V7.2 with Fixpack 9
applied, on a WinXP machine with all critical Windows service applied.

After going around in circles on this problem for a while, I finally
stumbled on the solution. I noticed that DB2ROUTINE_DEBUG was ON when I did
a "db2set" command. (That was a result of having made an unsuccessful
attempt to use the IBM Distributed Debugger in January.) Just for the heck
of it, I tried turning it off and recycling the instance to make the change
take effect, not really expecting any improvement.

Much to my surprise, my client programs can now call my stored procedures
just fine and the Stored Procedure Builder works fine again too.

I don't pretend to understand why this single setting should make such a big
difference to stored procedures but I wanted to report this to the group in
case anyone else ever encountered the same problem. I know some of us are on
older versions of DB2 so I thought I owed it to people to share what I
learned, just in case others confront the same problem somewhere down the
road.

By the way, if anyone can offer an explanation for how the DB2ROUTINE_DEBUG
setting messed up my stored procedures so badly, I'd be curious to hear it.

--
Rhino
---
rhino1 AT sympatico DOT ca
"If you're getting something for nothing, you're not using your own credit
card."

[Sean McKeough]

This was a bug in the sp debugger kicker code. It's fixed either in a
recent fixpack or in the next one coming out...

Rhino wrote:

Just a quick note to report on the resolution of a problem that gave me
serious grief in January. I put it aside for a while and, largely by
accident, found the solution a few minutes ago.

In short, I found myself getting SQL4301N Reason Code 4 - "The Java
interpreter has terminated itself and cannot be restarted." - whenever I
tried to execute a stored procedure from a client. I also got it whenever I
tried to run a stored procedure in the Stored Procedure Builder, EXCEPT for
the first run after I built the procedure. In other words, if I built a
stored proc, I could run it successfully once but any subsequent time I
tried to run it, I would get the SQL4301N RC4. That meant the only way I
could run stored procs was in the Builder and then only if I rebuilt it each
time before I ran it, even though it hadn't changed.

I should mention that I am running DB2 V7.2.7, i.e. V7.2 with Fixpack 9
applied, on a WinXP machine with all critical Windows service applied.

After going around in circles on this problem for a while, I finally
stumbled on the solution. I noticed that DB2ROUTINE_DEBUG was ON when I did
a "db2set" command. (That was a result of having made an unsuccessful
attempt to use the IBM Distributed Debugger in January.) Just for the heck
of it, I tried turning it off and recycling the instance to make the change
take effect, not really expecting any improvement.

Much to my surprise, my client programs can now call my stored procedures
just fine and the Stored Procedure Builder works fine again too.

I don't pretend to understand why this single setting should make such a big
difference to stored procedures but I wanted to report this to the group in
case anyone else ever encountered the same problem. I know some of us are on
older versions of DB2 so I thought I owed it to people to share what I
learned, just in case others confront the same problem somewhere down the
road.

By the way, if anyone can offer an explanation for how the DB2ROUTINE_DEBUG
setting messed up my stored procedures so badly, I'd be curious to hear it.

[Rhino]

Thanks for the info, Sean! I'm glad to hear that this situation is well
understood and already (being) resolved. I hadn't seen any other posts on
this problem via Google so I thought I was the only one encountering it.

Rhino

"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:c5**********@hanover.torolab.ibm.com...

This was a bug in the sp debugger kicker code. It's fixed either in a
recent fixpack or in the next one coming out...

Rhino wrote:

Just a quick note to report on the resolution of a problem that gave me
serious grief in January. I put it aside for a while and, largely by
accident, found the solution a few minutes ago.

In short, I found myself getting SQL4301N Reason Code 4 - "The Java
interpreter has terminated itself and cannot be restarted." - whenever I
tried to execute a stored procedure from a client. I also got it whenever I tried to run a stored procedure in the Stored Procedure Builder, EXCEPT for the first run after I built the procedure. In other words, if I built a
stored proc, I could run it successfully once but any subsequent time I
tried to run it, I would get the SQL4301N RC4. That meant the only way I
could run stored procs was in the Builder and then only if I rebuilt it each time before I ran it, even though it hadn't changed.

I should mention that I am running DB2 V7.2.7, i.e. V7.2 with Fixpack 9
applied, on a WinXP machine with all critical Windows service applied.

After going around in circles on this problem for a while, I finally
stumbled on the solution. I noticed that DB2ROUTINE_DEBUG was ON when I did a "db2set" command. (That was a result of having made an unsuccessful
attempt to use the IBM Distributed Debugger in January.) Just for the heck of it, I tried turning it off and recycling the instance to make the change take effect, not really expecting any improvement.

Much to my surprise, my client programs can now call my stored procedures just fine and the Stored Procedure Builder works fine again too.

I don't pretend to understand why this single setting should make such a big difference to stored procedures but I wanted to report this to the group in case anyone else ever encountered the same problem. I know some of us are on older versions of DB2 so I thought I owed it to people to share what I
learned, just in case others confront the same problem somewhere down the road.

By the way, if anyone can offer an explanation for how the DB2ROUTINE_DEBUG setting messed up my stored procedures so badly, I'd be curious to hear it.