Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: need help to create a SP in DB2

    Hi,
    My script spcrterr.db2 contains
    CREATE PROCEDURE pq5.spcrterr
    (
    IN incdnt_id char(20),
    IN Err_msg_cd integer,
    IN Chg_sts_cd smallint(2),
    IN Cmt_id smallint,
    IN Cmt_lang_cd CHAR(2),
    OUT SQL_ERROR_CD INTEGER,
    OUT APP_ERROR_CD INTEGER
    )
    SPECIFIC pq5.spcrterr
    LANGUAGE SQL
    /* This stored procedure updates status code in pq5.incdnt_all, add insert one row in pq5.incdnt_chg_hist and pq5.incdnt_chg_err_msg_rltn.*/

    BEGIN

    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE error_cd INTEGER DEFAULT 0;
    DECLARE currentTSP timestamp;
    DECLARE USRID INTEGER(4);
    DECLARE DIV_ID SMLLINT(2);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET SQL_ERROR_CD = SQLCODE;
    SET currentTSP = current timestamp;
    SET SQL_ERROR_CD = 0;
    set APP_ERROR_CD = 500;
    update pq5.incdnt_all set STS_CD = 2064 where INCDNT_ID = incdnt_id;
    if SQL_ERROR_CD<>0 then
    return 1;
    end if;
    DECLARE Cs1 CURSOR for select usrid,div_id from pq5.incdnt_all where INCDNT_ID=incdnt_id;
    OPEN Cs1;
    FETCH Cs1 INTO USRID,DIV_ID;
    INSERT into pq5.incdnt_chg_hist (incdnt_id,changing_usrid,changing_div_id,cmt_id,c mt_lang_cd)
    values(incdnt_id,USRID,DIV_ID,Cmt_id,Cmt_lang_cd);
    CLOSE Cs1;

    if SQL_ERROR_CD<>0 then
    return 1;
    end if;
    update pq5.incdnt_chg_err_msg_reltn set incdnt_id = incdnt_id,incdnt_chg_tmstmp = currentTSP,err_msg_cd = Err_msg_cd
    where incdnt_id = incdnt_id;
    if SQL_ERROR_CD<>0 then
    return 2;
    end if;
    set APP_ERROR_CD = 0;
    END@

    on command prompt mode I tried to execute the below command
    db2 -td@ -svf spcrterr.db2
    but it throws below erro message:
    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 "smallint" was found following "r, IN
    Chg_sts_cd". Expected tokens may include: "CHAR". LINE NUMBER=5.
    SQLSTATE=42601

    please hep me on this.

    Thanks
    Surjya

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at the line: IN Chg_sts_cd smallint(2),

    It is wrong.

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    26
    Thank a lot.
    After correcting that one i am getting another error like

    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 "INTEGER" was found following "tamp; DECLARE
    USRID". Expected tokens may include: "GRAPHIC". LINE NUMBER=20.
    SQLSTATE=42601

    Thanks
    Surjya

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The following lines are wrong also:

    DECLARE USRID INTEGER(4);
    DECLARE DIV_ID SMLLINT(2);

    Andy

  5. #5
    Join Date
    Jan 2010
    Posts
    26
    I am getting below error.
    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 "<cursor declaration>" was found following "".
    Expected tokens may include: "<SQL statement>". LINE NUMBER=30.
    SQLSTATE=42601


    Is it like I cannot declare cursor like this or something else.
    I am sorry, this is my first PL/SQL SP in DB2.

    Thanks
    Surjya

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in the manual for the CREATE PROCEDURE command. It will show you how and where to declare a cursor.

    Andy

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by surjyakp View Post
    I am sorry, this is my first PL/SQL SP in DB2.
    You may want to study examples in the manual first then. Declaration section must precede executable statements.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2010
    Posts
    26
    Can i use
    SET SERVEROUTPUT ON;
    in side the stored proceduer.
    Because it is giving me below error.

    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 "SERVEROUTPUT" was found following "R_CD =
    SQLCODE; SET". Expected tokens may include: "SSA". LINE NUMBER=25.
    SQLSTATE=42601


    Thanks
    Surjya

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by surjyakp View Post
    Can i use
    SET SERVEROUTPUT ON;
    in side the stored proceduer.
    Because it is giving me below error.

    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 "SERVEROUTPUT" was found following "R_CD =
    SQLCODE; SET". Expected tokens may include: "SSA". LINE NUMBER=25.
    SQLSTATE=42601


    Thanks
    Surjya
    No. It is not valid syntax. Please read the manual for the correct syntax.

    Andy

  10. #10
    Join Date
    Jan 2010
    Posts
    26
    I have declared like below in source code
    CREATE PROCEDURE pq5.spcrterr
    (
    IN incdnt_id char(20),
    IN Err_msg_cd integer,
    IN Chg_sts_cd smallint,
    IN Cmt_id smallint,
    IN Cmt_lang_cd CHAR(2),
    OUT SQL_ERROR_CD INTEGER,
    OUT APP_ERROR_CD INTEGER
    )
    But i found below order of parameters through db2 client control centre
    CMT_LANG_CD CHARACTER (2) IN
    SQL_ERROR_CD INTEGER (4) OUT
    APP_ERROR_CD INTEGER (4) OUT
    INCDNT_ID CHARACTER (20) IN
    ERR_MSG_CD INTEGER (4) IN
    CHG_STS_CD SMALLINT (2) IN
    CMT_ID SMALLINT (2) IN

    How is it possible. But I want the order what I have mentioned in the source code. What I need to do to maintain the order?

    Thanks
    Surjya

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here are some hints:

    1) DO NOT USE the Control Center to create Stored Procedures. It was not made for that.
    2) The order of variables in a SP does not matter. The order of the parameters does matter.
    3) Read the manual on CREATE PROCEDURE.
    4) Repeat step 3 until you understand the syntax.

    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
  •