RE: How to update a stored procedure?

["Gary L Peskin" <garyp@xxxxxxxxxxxx>]

Buck --

There are a number of PTFs in this area.  What release of OS/400 are you
on?  How current are you on the PTFs?  What is the message ID for
"Trigger program or external routine detected an error."  What is the
second level help?  Are there messages preceeding this in the job log
after you issue your call?  If possible, can you post the relevant
portion of your job log (between entering the CALL command and the
issuance of the error message).  You may also want to turn on STRDBG.
I'm not sure if that makes a difference but sometimes it does.  Also,
have your joblog set for the maximum amount of information (4 00
*SECLVL).

Thanks,
Gary

> -----Original Message-----
> From: java400-l-admin@midrange.com
> [mailto:java400-l-admin@midrange.com] On Behalf Of Buck Calabro
> Sent: Tuesday, August 13, 2002 12:29 PM
> To: java400-l@midrange.com
> Subject: How to update a stored procedure?
>
>
> While debugging my store procedure situation (I can get
> PARAMETER STYLE JAVA to work, just not DB2GENERAL) I ran
> across behaviour that confuses me somewhat.
>
> It seems that in order to update a SP you MUST sign off.
> This indicates to me that the JVM that DB2 is using is part
> of the job, rather than a single system task.  Anyway, I
> stumbled across this by having the same name for my method as
> my class: public class buck { public static void buck(... I
> compile it on the PC, ftp it to the 400, STRSQL and  create
> procedure buckProc (in sqlstm char (255))  language java
> parameter style java  external name 'buck!buck'  CREATE
> PROCEDURE statement complete.
>
>  call buckProc ('select * from master')
>  CALL statement complete.
>
> Woo hoo!  It works.  But then I decide to change the name of
> the method to something more meaningful.  I leave STRSQL up,
> edit the .java to public class buck { public static void
> resultSet(... compile it, ftp it and  drop procedure buckProc
>  DROP PROCEDURE statement complete.
>
>  create procedure buckProc (in sqlstm char (255))
>  language java parameter style java
>  external name 'buck!resultSet'
>  CREATE PROCEDURE statement complete.
>
>  call buckProc ('select * from master')
>  Trigger program or external routine detected an error.
>
> Exiting STRSQL doesn't help.  Signing off allows me to call
> the SP with the new method name.
>
> This has significant ramifications during the testing cycle.
> Is there no way to refresh the classes loaded in the JVM
> other than signing off?
>   --buck