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

Look at the line: IN Chg_sts_cd smallint(2),

It is wrong.

Andy

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

The following lines are wrong also:

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

Andy

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

Look in the manual for the CREATE PROCEDURE command. It will show you how and where to declare a cursor.

Andy

You may want to study examples in the manual first then. Declaration section must precede executable statements.

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

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

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