Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Exclamation Unanswered: Build stored procedures in a sql file?

    I have a .sql file who generate the stored procedures in a db2 database.
    Something like that:

    Create procedure sp1 ...
    @
    Create procedure sp1 ...
    etc.

    I have two questions:
    1) Is it necessary to build the procedures in this .sql file?
    When the application (written in Java) will call the procedures these must be already build?
    IF yes, please tell me if is it possible to build the procedures in this .sql file. If it is possible, please tell me how (a little tip).
    Thanks in advanced.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) No, it is not 100% necessary to build the SPs from the .sql script, but they do need to be built before they can be called. And building them from the script is one way to do that. Sps can be built using scripts such as this, the Development Center in V8.1 (Stored Procedure Builder in V7), or through some other method that can execute SQL (since a CREATE PROCEDURE is just SQL).

    To build then from the script, from the DB2 command window:

    db2 -td@ -svf myscript.sql

    HTH

    Andy

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    Build sp in a sql file

    I finished all the scripts who generate the database and all the objects.
    I have only one problem, the script muts build the procedures.
    The stored procedures in the script are separated by @.
    I use the syntax db2 -td@ -svf MyScript.sql but I met the error Db21005E with the message "An error occured while accessing the file MyScript.sql".
    I try to change the extension in db2 instead of sql but I receive the same message.
    Can anyone tell me wht syinax to use so the stored procedures not only be generated but build too? Tnahk you very much.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What OS and DB2 version?

    Andy

  5. #5
    Join Date
    Jun 2004
    Posts
    57

    Build SP in sql file?

    WIndows 2000 and udb express 8.1

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I looked the error you are getting:
    --------------------------------------------
    DB21005E An error occurred while accessing the file
    "<filename>".

    Explanation:

    The following could have caused the error:

    o file permissions do not allow file access

    o the file does not exist



    User Response:

    Correct the error and try again.

    -----------------------------------

    Make sure you are executing the script from the correct place.
    For example:
    Location of script: d:\myscript.sql

    Bring up "Command Window"
    switch to D drive:
    Switch to directory (CD \)
    run db2 -td@ -svf myscript.sql

    If this fails with the same error, check the permissions of the file.

    You can also run the script from the Command Center off of the Script tab.

    HTH

    Andy

Posting Permissions

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