Version Info
UDB Version 7.1 on windows 2000.

Functionality required
1. A script that will copy all the stored procedure from a database to a folder in windows 2000
2. Suggestion of any version control software for SP that is closely tied to UDB

Welcome all ideas and suggestions

select text from sysibm.sysprocedures where procschema = 'procedureonwer or what ever your cretiria > spv11.sql


example


procschema is 'TEST'

db2 -x " select text from sysibm.sysprocedures where procscema = 'TEST' " > spv11.sql


you can extract all procedures scripts into spv11.sql


hope this may help you , let me know if any questions


Thank You



Lekharaju Ennam

If the procedure is greater than a certain size (don't remember what it is exactly) , the above statement may trucncate the SP text ...

To work around it

db2 -x "select specificname from syscat.procedures where language='SQL'" | while read spname
do
db2 "export to /dev/null of del lobfile $spname modified by lobsinfile select text from syscat.procedures where specificname='
$spname'"
mv $spname.001 $spname.db2
done

Cheers
Sathyaram

famudba thanks!

Sathyaram thanks for your input.

I get what you are trying to do but i am unable to run the script the way you have mentioned.

I did put everything in a runscript.sql and the looping especially the WHEN READ SPNAME does not work. I am a newbie in script wriring. What kind of scripting is this (MSDOS script etc). Can u please eloborate how to write the script? I do understand the stuff you are trying to achieve but unable to make it work. Is there any website or pdf that I can get my hands to understand udb scripting?

hmmm ... not sure ...
Actually this is korn shell script (ksh)

Try this

db2 -x "select specificname from syscat.procedures where language='SQL'" > splist.txt
cat splist.txt| while read spname
do
db2 "export to /dev/null of del lobfile $spname modified by lobsinfile select text from syscat.procedures where specificname='
$spname'"
mv $spname.001 $spname.db2
done

In Windows command shell that would look something like this:

n_j thanks! I figured out from the help command in DOS prompt.

But I have a question. My desktop is windows 2000 and I have installed KORN shell. I would like to write a sample KORN shell and run the shell under DB2CMD window. Can somebody walk me thru an example of how I can accomplish this. Remember I am newbie in KORN shell but have excellent knowledge in DB2:-)

An example with mix of db2 commands and korn shell specific commands would be excellent!

www.db2click.com/scripts.htm

Sathyaram,

Thanks for the url.

Well I am in my $ prompt. Copied the script db2_tspace.ksh to my /tmp directory

$ -ls does list the script

but when i run the script like

$ db2_tspace.ksh -d clnt

I get a message
$ db2_tspace.ksh[72]: db2: not found [No such file or directory] error when connecting to database clnt

However, at db2cmd window
CONNECT TO CLNT
works fine!

Am I missing something???

The path is not set ..

Check the %PATH% in your windows command line ... Make sure you include all the db2 related directories in the $PATH in ksh

BTW, are you using cygwin ??? In case you are, you will find information on using it with db2 in www.db2click.com - scribble pad section

Cheers
Sathyaram

Sathyaram,

Thanks! I ran the same script in CYGWIN bash (born again shell) and it worked like a charm.

The UWIN implementation of korn shell is buggy and I removed it from my computer.

This case is now closed. Thanks for all you wonderful people for helping me!