What is wrong with this Script? Problem with DECLARE etc, etc

[okonita]

Hi all,
My environment is DB2 UDBv8.2 on Windows and Linux.
I am getting a lot of errors on the script you see below. Some of the error I am geting are:


-----------------------------------Start of Script-----------------------------------
---------------------original creation by Norm Wong------------------------

CREATE PROCEDURE DB2ADMIN.REORGT S( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA

P1: BEGIN

DECLARE procresult RESULT_SET_LOCA TOR VARYING ;

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 "_SET_LOCAT OR VARYING". Expected tokens may include: "<psm_semicolon >". LINE NUMBER=23. SQLSTATE=42601

DECLARE TABLE_SCHEMA VARCHAR(128) ;

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 "DECLARE TABLE_SCHEMA VARCHAR" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601.
Same thig with all DECLARE shown below.

DECLARE TABLE_NAME VARCHAR(128) ;
DECLARE CARD INTEGER ;
DECLARE OVERFLOW INTEGER ;
DECLARE NPAGES INTEGER ;
DECLARE FPAGES INTEGER ;
DECLARE ACTIVE_BLOCKS INTEGER ;
DECLARE TSIZE INTEGER ;
DECLARE F1 FLOAT ;
DECLARE F2 FLOAT ;
DECLARE F3 FLOAT ;
DECLARE REORG CHAR(3) ;
DECLARE has_lob INTEGER DEFAULT 0 ;
DECLARE retval INTEGER DEFAULT 0 ;
DECLARE reorgcmd VARCHAR(512) ;
DECLARE at_end SMALLINT DEFAULT 0 ;
DECLARE not_found CONDITION FOR SQLSTATE '02000' ;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1 ;

Can someone show me what I am doing wrong? I need this help as soon as I can get it...
Thank you all
Okonita

[Remi]

Hello, send you an example


CREATE PROCEDURE RACE.SP_LISTARP AISES6(
IN OPCION VARCHAR(2) ,
IN CODPAIS VARCHAR(3) ,
IN DENPAIS VARCHAR(30),
IN TIP VARCHAR(1)
)
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE SQLSELE1 CHAR(512);
DECLARE C1 CURSOR WITH RETURN FOR S1;
-- DEBERAN PASAR EL VALOR DE CODPAIS INCLUYENDO EL PORCENTUAL '02%'--
IF TIP = '1' THEN
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ? FETCH FIRST 5000 ROWS ONLY ';
ELSE
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ?
FETCH FIRST 5000 ROWS ONLY';
END IF;
PREPARE S1 FROM SQLSELE1;
OPEN C1 USING CODPAIS;
END;