Dear friends,

I am creating a SQL procedure in DB2 on z/Os and giving the syntax error though the SQL is correct. We are in Db2 V8. Does SQL procedures allowed to create on z/os? I heard some thing to use development center to create.
Any ideas. My sql is as follws here.

CREATE PROCEDURE DRUGLIST
( IN PHOSPNO CHAR(4)
,OUT PMNEMONIC CHAR(8)
,OUT PDRUGSTAT CHAR(1)
,OUT PDRUGNM CHAR(76)
,OUT PDURATION CHAR(2)
,OUT PFREQ CHAR(10)
,OUT PDOSE DECIMAL(5, 2)
,OUT PSQLCODE INTEGER
,OUT PSQLSTATE CHAR(5)
,OUT PSQLERRMC VARCHAR(250)
)
RESULT SETS 0
MODIFIES SQL DATA
EXTERNAL NAME DRUGLIST
NO DBINFO
WLM ENVIRONMENT TDB2SID1
STAY RESIDENT NO
COLLID ICVPC
PROGRAM TYPE MAIN
RUN OPTIONS 'TRAP(OFF),RPTOPTS(OFF)'
COMMIT ON RETURN NO
LANGUAGE SQL
P1: BEGIN
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
SELECT
MNEMONIC
,DRUGSTAT
,DRUGNM
,DURATION
,FREQ
,DOSE
INTO PMNEMONIC
,PDRUGSTAT
,PDRUGNM
,PDURATION
,PFREQ
,PDOSE
FROM IDVPCS01.ITORDSET
WHERE HOSPNO = PHOSPNO ;

SET PSQLCODE = SQLCODE ;
SET PSQLSTATE = SQLSTATE;
SET PSQLERRMC = 'ADIOS' ;
END P1
I got the following error. I tried to work around to remove the semi colon and it didn't work.
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: ;
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

Are you using
END
@

as the statement terminator?

Thank you for the reply.

I use this way for the SQL code

P1: BEGIN

Sql statements;

END P1
;

Should I use different way?

Thanks,
Srini

Here is a layout for Stored procedure

CREATE PROCEDURE procedurename (IN tmp INTEGER)
P1: BEGIN
sql goes here
P1:END

@

Thank you. I tried with your suggestion. It didn't work. Same issue. Looks like SQL procedures syntax different from cobol procedure definitions on z/os environment.

It worked after removing the declare statements. Did this procedure create an entry in catalog tables like SYSIBM.SYSROUTINES/SYSPARMS tables. I did not see any entry in these tables after the SQL execution. I wonder where this procedure definition stores in catalog table for DB2 sql procedures?

Not sure about DB2 z/Os. For DB2 UDB they are under application objects--> stored procedure

On DB2 z/OS V8, you have no real LANGUAGE SQL procedures available yet. You need to create a job that precompiles the stored proc (into some host language) and then use that.