As the DBA for a development project a couple of years ago, I was in
charge of migrating/promoting stored procedures from the development
environment to the QA and production environments once they had been
proven. I automated the process with a script that sucked the source
code from source database, removed proc if exsists in target
environment, compiled it in the target enviroment and logged the
operation in a tracking table.
This now I am faced with a similar situation, but now have deal with
Java stored procedures instead of SQL stored procedures. I would like
to script the same operation but have hit a few snags. It's difficult
to use the SP Builder since my builds are generally batch driven
(i.e., migrate all procs in an approved list). Plus, using the export
function from the SPB doesn't generate source code in the target DB
once the script is executed on the target DB.
Here are the highlights of what I have so far:
1. Suck out Java source code:
export to $PROCNAME.dat of del lobs to ./mylobs/ lobfile lobs1
modified b
y lobsinfile select procname,class_source from sysibm.sysjarcontents
jc, syscat.
procedures p where p.procname = '$PROCNAME' and
p.procschema='$PROCSCHEMA' and substr(p.jar_id,locate('.',p.jar_id)+1)
= jc.jar_id
2. Rename and compile lobfile
mv lobs.001 $PROCNAME.java
javac $PROCNAME.java
3. Turn it into a .jar
jar cvf $PROCNAME.jar $PROCNAME.class
4. drop existing procedure and jar if exists
drop procedure $PROCNAME
sqlj.remove_jar...
5. Install jar
sqlj.install_jar...
I have had sucess up to here, but have been stumped in finding a way
to generate the procedure definition and the way to install the source
code into the target database. If anyone can help me out with these
last two high level steps, that would be great. If there's even a
better way to do this altogether, then I'm all ears as well.
Thanks in advance,
Mike
