Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: SP backup & version control

    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

    You are the creator of your own destiny!

  2. #2
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    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
    Lekharaju Ennam
    Certified Oracle8i & DB UDB DBA
    Florida A&M University

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Aug 2003
    Posts
    106
    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?

    You are the creator of your own destiny!

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In Windows command shell that would look something like this:
    Code:
    db2 -x "select specificname from syscat.procedures where language='SQL'" > splist.txt
    for /f  %%f in ("splist.txt") do db2 "export to nul of del lobfile %%f modified by lobsinfile select text from syscat.procedures where specificname='%%f'" & mv %%f.001 %%f.db2
    Last edited by n_i; 08-11-04 at 12:55.

  7. #7
    Join Date
    Aug 2003
    Posts
    106
    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!

    You are the creator of your own destiny!

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Aug 2003
    Posts
    106
    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???

    You are the creator of your own destiny!

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Aug 2003
    Posts
    106
    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!

    You are the creator of your own destiny!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •