Java Stored procedures cannot get updated class file recognised

[Alex]

Hi all,

Just been dabbling with java stored procedures and I'm having
problems replacing

System: db2 8.1.4 on RH 7.1 linux system

1). Look at java jdbc samples as supplied with db2 and run the
SpServer and Spclient progs against the default database SAMPLE. And
everything works.

2). Copy SpServer.java to SpDevinfo.java and write code to update a
field in one of my databases
3). update the spcat and SpCreate.db2,SpDrop.db2 as applicaable to my
code
4). Rewrite SpClient.java as appropriate to use my sp
5). Compile SpDevinfo.java and copy class file to
~db2inst1/sqllib/function
6). Run spcat to delete stored procedure and recreate it - get error
coz sp didn't exist 1st time but it adds the sp
7). Go into GUI control centre and check that the SP is visible - it
is
8). Run modifed SpClient to update my database.
9). Get error message returned from sp because I screwed up some of
the code.
10). Alter code, recompile SpDevinfo.java and overwrite class file in
~db2inst1/sqllib/function
11). Run spcat to drop and create new sp
12). Run Spclient - and get exactly same message as before BUT I've
replaced the error string originially returned with a different one.

It looks like whatever I do, I'm still running the class file from the
1st incarnation of the stored procedure and not the new one I wrote.

Any idea why I'm always getting the original class file?

Just to test,

a) remove SpDevinfo.class from ~db2inst1/sqllib/function
b). Run spcat to delete and recreate stored procedure - it works
c). Run Spclient - and it finds the sp and returnes the same error
message.

Looks like the class is somewhere else - or cached somewhere

Any help appreciated.
Alex

[Rhino]

It seems likely that you have KEEPDARI set to YES. KEEPDARI is a database
manager setting that causes cacheing of stored procedures and UDFs; the
effect of KEEPDARI is that DB2 often fails to see the newest version(s) of
stored procs. Having KEEPDARI set to YES on a production machine where
updates to the proc are infrequent makes sense for performance reasons but
most people will prefer setting KEEPDARI to NO on development machines.

If your machine is indeed a development machine, I'd suggest executing the
following commands in this exact order, then stopping and starting DB2, and
trying your stored procedure again:
db2 update dbm cfg using keepdari no

db2 terminate

db2 force application all

db2stop

db2start

If that doesn't help, post again and I can suggest less likely sources for
your problem.

Rhino

"Alex" <A.******@hull.ac.uk> wrote in message
news:b2**************************@posting.google.c om...

Hi all,

Just been dabbling with java stored procedures and I'm having
problems replacing

System: db2 8.1.4 on RH 7.1 linux system

1). Look at java jdbc samples as supplied with db2 and run the
SpServer and Spclient progs against the default database SAMPLE. And
everything works.

2). Copy SpServer.java to SpDevinfo.java and write code to update a
field in one of my databases
3). update the spcat and SpCreate.db2,SpDrop.db2 as applicaable to my
code
4). Rewrite SpClient.java as appropriate to use my sp
5). Compile SpDevinfo.java and copy class file to
~db2inst1/sqllib/function
6). Run spcat to delete stored procedure and recreate it - get error
coz sp didn't exist 1st time but it adds the sp
7). Go into GUI control centre and check that the SP is visible - it
is
8). Run modifed SpClient to update my database.
9). Get error message returned from sp because I screwed up some of
the code.
10). Alter code, recompile SpDevinfo.java and overwrite class file in
~db2inst1/sqllib/function
11). Run spcat to drop and create new sp
12). Run Spclient - and get exactly same message as before BUT I've
replaced the error string originially returned with a different one.

It looks like whatever I do, I'm still running the class file from the
1st incarnation of the stored procedure and not the new one I wrote.

Any idea why I'm always getting the original class file?

Just to test,

a) remove SpDevinfo.class from ~db2inst1/sqllib/function
b). Run spcat to delete and recreate stored procedure - it works
c). Run Spclient - and it finds the sp and returnes the same error
message.

Looks like the class is somewhere else - or cached somewhere

Any help appreciated.
Alex