Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Columbia, SC
    Posts
    3

    Unanswered: Problems running CREATE PROCEDURE script @Windows command-line

    Hello,

    My goal is to run a build script for our ASP.NET front-end DB2/iSeries back-end system. To accomplish this I need to find a way to run a Create Procedure script from a command-prompt. Obviously I can fire up the DB2 Command Center and run scripts in the UI, but that is no way to execute a build script!

    At the DB2 CLP (command-line processor) I am able to invoke the script by the standard syntax:

    >db2 - f test.db2

    where test.db2 is the file that contains my Create Procedure statement. Unfortunately, the DB2 CLP does not seem to understand the script, and throws all manner of DB21034E errors (invalid syntax).

    Is there a way to accomplish my goal, and if so, where is it documented?

    My build machine is configured with Windows XP Professional and DB2 Connect 7.2 FP5. On the iSeries we are running OS/400 v5R1.

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Posts
    3

    Re: Problems running CREATE PROCEDURE script @Windows command-line

    Originally posted by chrisfalter
    Hello,

    My goal is to run a build script for our ASP.NET front-end DB2/iSeries back-end system. To accomplish this I need to find a way to run a Create Procedure script from a command-prompt. Obviously I can fire up the DB2 Command Center and run scripts in the UI, but that is no way to execute a build script!

    At the DB2 CLP (command-line processor) I am able to invoke the script by the standard syntax:

    >db2 - f test.db2

    where test.db2 is the file that contains my Create Procedure statement. Unfortunately, the DB2 CLP does not seem to understand the script, and throws all manner of DB21034E errors (invalid syntax).

    Is there a way to accomplish my goal, and if so, where is it documented?

    My build machine is configured with Windows XP Professional and DB2 Connect 7.2 FP5. On the iSeries we are running OS/400 v5R1.

    Thanks!
    are you creating sql procedure?
    if so,you should define the statement termination character, not use the default semicolon (. like following:
    create procedure test()
    begin
    ......
    end
    @

    db2 -td@ -vf test.db2

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: Problems running CREATE PROCEDURE script @Windows command-line

    Hi,

    1. Can you show me the SP?
    2. You run the script in the Windows command prompt or the db2 CPL?

    Abel.

  4. #4
    Join Date
    Sep 2003
    Location
    Columbia, SC
    Posts
    3
    Yao -

    Your diagnosis was on the mark. The CLP was trying to execute individual statements, rather than passing the entire set of statements to DB2. Using your advice, I have made the scenario work. Thanks for your help!

    Abel -

    Here's how it works:

    from the DOS command-line - c:\sqllib\bin>db2cmd mytest

    mytest.cmd file contains:
    db2 connect to mydb user myuser using mypassword > test.out 2>&1
    db2 -td@ -v -f test.db2 -l log.out > test.out 2>&1
    exit

    test.db2 contains:
    -- PROCEDURE SPRTESTCJF --
    drop procedure SPRTESTCJF@
    create procedure SPRTESTCJF(
    IN prmAgencyId Char(10),
    IN prmUsername Char(25),
    OUT retCode INTEGER
    )

    language SQL
    result sets 2
    not deterministic
    set option datfmt=*iso

    BEGIN
    -- declare section
    DECLARE usercur CURSOR WITH RETURN FOR
    SELECT FIELD1, FIELD2, FIELD3
    FROM MYTABLE
    WHERE AGENTNBR = prmAgencyId
    AND USERNAME = prmUsername;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET retCode = -10;
    DECLARE EXIT HANDLER FOR NOT FOUND
    SET retCode = -1;

    -- execute logic
    SET retCode = 0;
    OPEN usercur;
    END
    @

  5. #5
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: Problems running CREATE PROCEDURE script @Windows command-line

    ok,
    first, you have a connect with the db? else, connect to... (before create sp)

    second, no break line in the command.
    correct example:
    ---
    connect to db user usuario using password
    select * from tabla
    connect reset
    -----
    INCORRECT example:
    ---
    connect to db user usuario using password
    select *
    from tabla
    connect reset
    -----
    Each line is processed with a commad.

    Saludos, Abel.

  6. #6
    Join Date
    Sep 2003
    Location
    Columbia, SC
    Posts
    3
    Abel -

    Thanks for your reply. Both of your points, which are quite valid, are addressed in my code. First, the connection is established in line 1 of mytest.cmd:

    db2 connect to mydb user myuser using mypassword > test.out 2>&1

    Second, when the CLP is processing the file test.db2, it uses a token to separate statements, rather than end-of-line character. In this case, the -td@ option sets the statement separator token to '@'. Thus test.db2 gets processed as 2 statements: a "DROP PROCEDURE" statement and a "CREATE PROCEDURE" statement.

Posting Permissions

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