Hi guys,

I am trying to translate some functionality from Oracle to DB2 9.1 and was wondering if any of you DB2 gurus out there could give me a hand. I need to call a command line application from within a DB2 stored procedure and wait for the application to finish before the stored procedure continues. In Oracle we call a Java procedure from within an Oracle stored procedure to achieve this and I was wondering if the same is possible within DB2?

I have been doing some reading up on this and it looks like it may be possible to do this using SQLJ, however finding information on how exactly to do this is proving to be very difficult. If you could reply with maybe a very basic example of how this could be achieved (some sort of Hello World example if possible) I would very much appreciate it.

Thanks in advance....

SQLJ is the wrong answer. You can write a stored procedure entirely in Java, which is I guess what you want to do.

There are samples in the DB2 manuals: http://publib.boulder.ibm.com/infoce...d/r0007620.htm

Thanks for your reply....I just have one more question. I need to be able to call the java procedure from a normal bog standard sql procedure and wait until there is a return from the java procedure before continuing with the initial procedure. Something like this.....

1) Start initial procedure
2) Call the java procedure from within the initial procedure
3) Wait until the java procedure has returned
4) Complete the initial procedure.

Im sorry to be a pain, however I am very new to DB2 and have not been able to find this exact solution anywhere on the net. I am guessing that the DB2 community is either very quiet, very small, or both :-)

Sorry, I don't see any question in your second post. A Java stored procedure is invoked in the same manner as an SQL stored procedure. Obviously, you can call one SP from another - that's standard SQL, to which, by the way, DB2 adheres more strictly than Oracle...

So you are saying that I can call my java procedure like this.....

"CALL MyJavaProc (params);"

If that is the case it is a lot easier than Oracle.....

Yes, of course you can do that.

I'm curious: how would this work in Oracle?

In Oracle you have to essentially build a "wrapper procedure" around the class before you can use it.

Btw, here is an article that describes how to do operating system calls (like file I/O) from SQL: http://www.ibm.com/developerworks/db...303stolze.html