Problems running Java stored procedures in UDB 6.1 on NT 
Author Message
 Problems running Java stored procedures in UDB 6.1 on NT

Hi all

I am using the Stored Procedure builder to create a simple stored
procedure. I have 2 databases on the same NT machine.

I can create, bind and run a stored procedure on one database, called
I then try to create the same stored procedure on the different
database (called Test). I manually create the procedure (with the same
name and signature) and then copy the code from the original one
(copying-pasting stored procedure itself does not work. If IBM people
are intersted i could possibly fill out at least 10 bug reports on
system hangs and crashes, i got tired of doing that already and

I can bind the stored procedure for the Test database, and that works.
However, when i run it i get the following error:
[IBM][CLI Driver][DB2/NT] SQL0805N  Package "TRASH.S7493439" was not
found.  SQLSTATE=51002

I looked at packages in the system and the TRASH package obviously does
not exist in the TEST databases - they are different physical DBs on
the same machine. I have even deleted the TRASH stored procedure and
all the packages from that DB and the TEST stored procedure still does
not work

If anybody has any suggestions, i'd greatly appreciate any hints. I'm a
newbie, and i've been banging my head against this for a while, and
it's getting really frustrating.

Alternately, if there are any other ways to create/bind stored
procedures in Java outside of SPB, please let me know



Sent via http://www.***.com/
Before you buy.

Mon, 11 Nov 2002 03:00:00 GMT
 Problems running Java stored procedures in UDB 6.1 on NT


> Alternately, if there are any other ways to create/bind stored
> procedures in Java outside of SPB, please let me know

We gave up on the SPB. We have a support call to IBM about it, but it's already a lost
cause. I'll repost the 9-easy manual steps (again)... These are the steps to set up &
create a java stored procedure manually in DB2 UDB ver. 6.1


# 1. generate java from .sqlj file (it's java w/ sqlj directives)
 sqlj MyClass.sqlj

(if you don't know anything about .sqlj files, read the manual on static/embedded java
stored procedures. The Stp.sqlj sample is a great place to start too).

 # 2. compile the java

 # connect to db
 db2 connect to <database> user <userid> using <password>
( note, this can effect the creator / owner & execution )

 # 3. drop old package
 db2 'drop package "MYCLASS"'

 # 4. build package
 db2profc -url=jdbc:db2:<database> -user=<userid> -password=<password>
-prepoptions="package  using MyClass isolation UR" MyClass_SJProfile0

( if you let it default the package to isolation level CS, you may regret it..
i do not know what the SPB does in this case, but Cursor Stability may cause
lock waits,

 # 5. Change group ownership of files
 chgrp <unix group> MyClass_*
 chgrp <unix group> MyClass*.class
(important in Unix if developer logs in as someone other than db owner)

 # 6. copy java classes & .ser file to
 cp MyClass*.class /home/<db2instance>/sqllib/function
 cp MyClass*.ser /home/<db2instance>/sqllib/function
( i think in Win NT its c:\sqllib\function or some such )

 # 7. set schema for creation of stored proc
 db2 set current schema MYSCHEMA
(not necessary if the tables belong to the db owner & developer logs in as same)

 # 8. drop the old stored procedure

 # 9. create stored procedure

(note, the RESULT SETS clause is absolutely necessary if you are return row sets... for
other parameters, like getting more than one result set, doing INOUT parameters, it is
very important to Read The Manual)

FWIW, in ver. 7, DB2 UDB has an internal stored procedure language: "SQLPL" ( I think ).
And definitely *DO* look at the java samples for the SAMPLE database, esp. the .SQLJ

(IBM = "It's Better Manually")

Wed, 13 Nov 2002 03:00:00 GMT
 [ 2 post ] 

 Relevant Pages 

1. UDB NT - Java Stored Procedures

2. How to export (DDL+data+stored procedures) Db2 6.1 on os/390 to nt

3. Upgrade vom UDB 5.2 to UDB 6.1

4. UDB 6.1 FP 2 / Windows NT

5. Where are NT DB2 UDB 6.1 fix paks?

6. DB2 UDB 6.1 Client kills DB2/NT 2.1.2

7. Calling a Java Stored Procedure from another Java Stored Stored Procedure

8. Problem with UDB 6.1 SP5

9. Problems with UDB 6.1 FP5

10. UDB 6.1 installation problems with NIS under Solaris

11. DB2Cconnect on AIX to UDB 6.1 OS/390 problems

12. Archive logs problem with UDB 6.1 and OS.390 2.8 (ADCD 2.8)

Powered by phpBB® Forum Software