DB2 Java Stored Procedure refresh Issues

[Arindam Roy]

Hello,

The problem mentioned below has been faced while trying to execute a
stored procedure.

In this case they have deployed an SP and then trying to make changes
to the out parameters but the changes made are not reflected on the
output tab where we get the results displayed.

We tried doing the following steps and still we are getting the first
result and the changes are not reflected.

1. We dropped the stored procedure in question and removed the
references of the jar id and also ensured that the jar file is deleted
from the server.
2. We then deployed the SP to the server with the changed output
parameters and this created a jar with new jar id.
3. We then tried executing the SP from the database where the SP
content had the changes made. But still we were facing the same
problem.

We also thought the the cache needs to be refreshed and we executed
the command "call sqlj.refresh_classes (void);". But this too does not
refresh the results.

Can anybody help us solve this issue....
Thanks in advance,
Arindam Roy

[Rhino]

"Arindam Roy" <ar*********@fidelity.com> wrote in message
news:e2**************************@posting.google.c om...

Hello,

The problem mentioned below has been faced while trying to execute a
stored procedure.

In this case they have deployed an SP and then trying to make changes
to the out parameters but the changes made are not reflected on the
output tab where we get the results displayed.

We tried doing the following steps and still we are getting the first
result and the changes are not reflected.

1. We dropped the stored procedure in question and removed the
references of the jar id and also ensured that the jar file is deleted
from the server.
2. We then deployed the SP to the server with the changed output
parameters and this created a jar with new jar id.
3. We then tried executing the SP from the database where the SP
content had the changes made. But still we were facing the same
problem.

We also thought the the cache needs to be refreshed and we executed
the command "call sqlj.refresh_classes (void);". But this too does not
refresh the results.

Can anybody help us solve this issue....

The main cause for this problem is usually the KEEPDARI parameter.

KEEPDARI is a performance parameter which causes cacheing of stored
procedures. It should be set to "YES" in a production environment but should
normally be "NO" in a development environment.

I suggest you determine the value of KEEPDARI in your environment via this
command, issued from a DB2 Command Window:
db2 get dbm cfg | more

Scroll down until you see a line that starts:
"Keep DARI process (KEEPDARI) = "

If the value of KEEPDARI is not appropriate, execute these commands:
db2 update dbm cfg using keepdari no [or yes if you want KEEPDARI on]
db2 terminate
db2 force application all
db2stop
db2start

If this doesn't resolve your problem, take a careful look through your
preparation procedures for an inconsistency. For example, if your compile st
ep is writing the object code to directory FOO but the object code is being
copied into DB2 from the BAR directory, the code that is being compiled is
different from the code that is being stored in DB2. In that case, no amount
of recompiling will resolve the problem: you have to revise your preparation
procedures to fix that problem.

Rhino

[Sean McKeough]

If you want to run with keepdari=yes (for performance), you have another
option...just "call sqlj.refresh_classes()", and on the next invocation
(in a new uow), the new jar/class will be picked up. (I think this
function is doccumented as jar specific, but it destroys the old
classloader, and so should pick up any class changes too).

Rhino wrote:

"Arindam Roy" <ar*********@fidelity.com> wrote in message
news:e2**************************@posting.google.c om...

Hello,

The problem mentioned below has been faced while trying to execute a
stored procedure.

In this case they have deployed an SP and then trying to make changes
to the out parameters but the changes made are not reflected on the
output tab where we get the results displayed.

We tried doing the following steps and still we are getting the first
result and the changes are not reflected.

1. We dropped the stored procedure in question and removed the
references of the jar id and also ensured that the jar file is deleted
from the server.
2. We then deployed the SP to the server with the changed output
parameters and this created a jar with new jar id.
3. We then tried executing the SP from the database where the SP
content had the changes made. But still we were facing the same
problem.

We also thought the the cache needs to be refreshed and we executed
the command "call sqlj.refresh_classes (void);". But this too does not
refresh the results.

Can anybody help us solve this issue....

The main cause for this problem is usually the KEEPDARI parameter.

KEEPDARI is a performance parameter which causes cacheing of stored
procedures. It should be set to "YES" in a production environment but should
normally be "NO" in a development environment.

I suggest you determine the value of KEEPDARI in your environment via this
command, issued from a DB2 Command Window:
db2 get dbm cfg | more

Scroll down until you see a line that starts:
"Keep DARI process (KEEPDARI) = "

If the value of KEEPDARI is not appropriate, execute these commands:
db2 update dbm cfg using keepdari no [or yes if you want KEEPDARI on]
db2 terminate
db2 force application all
db2stop
db2start

If this doesn't resolve your problem, take a careful look through your
preparation procedures for an inconsistency. For example, if your compile st
ep is writing the object code to directory FOO but the object code is being
copied into DB2 from the BAR directory, the code that is being compiled is
different from the code that is being stored in DB2. In that case, no amount
of recompiling will resolve the problem: you have to revise your preparation
procedures to fix that problem.

Rhino