Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2003
    Posts
    16

    Unanswered: IMPORT DB2 Stored Procedure

    Hi all,
    I have 500 .sql files (containing stored procedures). I am looking for a easy way to upload all these stored procedure into DB2 8.1 under windows 2000. The development center provided way to import stored procedure 1 at a time. I am looking for a way to add all these stored procedures in 1 shot.

    I would appreciate your help.

    Thanks
    Selva Kumar

    P.S: If you can please specify the complete syntax and procedure.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Selva,
    Why don't you first combine all the sql files into one file:

    copy *.sql ALLSP.ddl

    Then run the single script:

    db2 -tvsf ALLSP.ddl


    HTH

    Andy

  3. #3
    Join Date
    May 2003
    Posts
    16
    I tried your way. I get error. I just want to know how to compile and link a stored procedure in a .sql file to a database from command line or through batch file. I have 500 such files.

    Thanks and I appreciate your support.

    Selva Kumar



    Originally posted by ARWinner
    Selva,
    Why don't you first combine all the sql files into one file:

    copy *.sql ALLSP.ddl

    Then run the single script:

    db2 -tvsf ALLSP.ddl


    HTH

    Andy

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Selva,
    Does each of your .sql files contain a "CREATE PROCEDURE" statement? If So Do you have a C compiler installed and configured on the DB2 server?

    Andy

    Originally posted by sselvakumar72
    I tried your way. I get error. I just want to know how to compile and link a stored procedure in a .sql file to a database from command line or through batch file. I have 500 such files.

    Thanks and I appreciate your support.

    Selva Kumar

  5. #5
    Join Date
    May 2003
    Posts
    16
    Yes.. Each of the sql has a create procedure in it. I have Microsoft C++ 6 installed. Using Db2 app, I am able to import the .sql file and works fine. But my problem is I have 500 such files and definitlely I do not want to import them 1 at a time. I know how I can do it but unable to find the right way...I would like a batch file which can be run on each of these files that would compile and link my stored procedure to my database.

    Thank you very much for your support. I am right now at work working on this hurdle.

    Selva Kumar
    OHIO




    Originally posted by ARWinner
    Selva,
    Does each of your .sql files contain a "CREATE PROCEDURE" statement? If So Do you have a C compiler installed and configured on the DB2 server?

    Andy

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Selva,
    I realize the problem with my first post. I forgot you are trying to process stored procedures where each statement inside the SP is terminated by a semicolon (. The command I gave you wants the "Create Procedure" terminated by a semicolon and that cannot work properly. Try this:

    Create a file (at.txt) with just an @ followed by a CR-LF;

    Then run:

    for %i in (*.sql) DO copy %i+at.txt %1.xxx
    copy *.xxx ALLSP.ddl
    db2 -td@ -vsf ALLSP.ddl

    Note: the extension .xxx can be anything, just mak it something unique so the copy only gets the files you created with the FOR command.

    Let me know if this works.

    Andy



    Originally posted by sselvakumar72
    Yes.. Each of the sql has a create procedure in it. I have Microsoft C++ 6 installed. Using Db2 app, I am able to import the .sql file and works fine. But my problem is I have 500 such files and definitlely I do not want to import them 1 at a time. I know how I can do it but unable to find the right way...I would like a batch file which can be run on each of these files that would compile and link my stored procedure to my database.

    Thank you very much for your support. I am right now at work working on this hurdle.

    Selva Kumar
    OHIO

  7. #7
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39
    Hi,
    Can you try the below option

    copy *.sql ALLSP.ddl

    DO a replace for "create procedure" with "@ create procedure". The @ is specified as a delimiter .

    Then run the single script:

    db2 -td@ ALLSP.ddl

    Thanks
    Sateesh


    Originally posted by sselvakumar72
    Yes.. Each of the sql has a create procedure in it. I have Microsoft C++ 6 installed. Using Db2 app, I am able to import the .sql file and works fine. But my problem is I have 500 such files and definitlely I do not want to import them 1 at a time. I know how I can do it but unable to find the right way...I would like a batch file which can be run on each of these files that would compile and link my stored procedure to my database.

    Thank you very much for your support. I am right now at work working on this hurdle.

    Selva Kumar
    OHIO

  8. #8
    Join Date
    May 2003
    Posts
    16
    Thank you very much. I tried your way and it worked.

    Appreciate your help

    regards,
    Selva Kumar

  9. #9
    Join Date
    Jul 2003
    Posts
    78

    "db2 -td@ ALLSP.ddl" is not a valid command

    Hello,

    Your posts are great. And I try to follow the steps and test it, but when come to the last step : "db2 -td@ ALLSP.ddl "in DB2 CLP, the following message is what I got:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "ALLSP.ddl". Expected tokens may include: "JOIN <joined_table>".
    SQLSTATE=42601

    Can any of you tell me what should I do exactly? I'd appreciate.

  10. #10
    Join Date
    May 2003
    Posts
    16
    db2 -td@ ALLSP.ddl..

    The file ALLSP.ddl should be containing multiple stored procedures. Each of these stored procedures need to be seperated by a delimiter (for the above command, '@' is the delimiter). The argument '-td@' determines the delimiter.

  11. #11
    Join Date
    Jul 2003
    Posts
    78

    I did and checked the ALLSP.ddl file, read on

    I try "db2 -td@ -vsf ALLSP.ddl" and got the following message:

    .......

    'CREATE' is not recognized as an internal or external command,
    operable program or batch file.

    Now what should I do? Thank you

  12. #12
    Join Date
    May 2003
    Posts
    16
    Check if you have database connection estabilished...

    Could you post some contents of you ALLSP.DLL

  13. #13
    Join Date
    Jul 2003
    Posts
    78

    first thing I did is connect to the database, read on

    Thank you for replying.

    I use "!" as dilimeter. Here is the content of my ALLSP.ddl:

    !CREATE PROCEDURE SPYFILES.Insert_Accum_Desc
    (
    @accumType char(20),
    @accumName char(20),
    @accumText char(90)
    )
    LANGUAGE SQL
    BEGIN

    INSERT INTO ACCUMDESC(
    ACCMTYPE, ACCMNAME, ACCMTEXT)
    VALUES(
    @accumType, @accumName, @accumText);
    end;



    !CREATE PROCEDURE SPYFILES.Delete_Accum_Desc
    (
    in @accumType char(20),
    in @accumName char(20)
    )
    LANGUAGE SQL
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    BEGIN
    DELETE FROM ACCUMDESC
    WHERE ACCMTYPE = @accumType and
    ACCMNAME = @accumName;
    END!

  14. #14
    Join Date
    May 2003
    Posts
    16
    why do you have '@' symbol before the parameter names?

  15. #15
    Join Date
    Jul 2003
    Posts
    78

    ok, I remove @, but the error is still stays, now what?

    Originally posted by sselvakumar72
    why do you have '@' symbol before the parameter names?

Posting Permissions

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