Sintax Error in procedure code

Bruno Almeida

2016-09-08 15:15:39 UTC

Post by Bruno Almeida
Hi friends,
DB2 LUW 10.1
I'm trying to execute a procedure on CLP, but there is a sintax error that I was unable to solve.
$ db2 -tvf proc.sql
drop procedure APR.PURGE_TABLE_INFO_TMT
DB20000I The SQL command completed successfully.
CREATE PROCEDURE APR.PURGE_TABLE_INFO_TMT (IN TABLE_NAME varchar(64), IN CHUNK smallint, IN MID varchar(24), IN FIRST_TMT timestamp, IN SECOND_TMT timestamp)
BEGIN
DECLARE SQLCODE INTEGER;--
declare rcount integer;--
declare v_dyn_SQL varchar(200);--
declare stmt_delete statement;--
--version1.0
set v_dyn_SQL = 'delete from (select 1 from ' || TABLE_NAME || ' where MACHINE_ID=''' || MID || ''' and COLLECT_TIME between ''' || varchar(FIRST_TMT) || ''' and ''' || varchar(SECOND_TMT) || ''' FETCH FIRST ' || char(CHUNK) || ' rows only) as D';--
prepare stmt_delete from v_dyn_SQL;--
loop: LOOP
execute stmt_delete;--
get diagnostics rcount = ROW_COUNT;--
IF (SQLCODE = 100 OR RCOUNT < CHUNK) THEN LEAVE loop;--
END IF;--
COMMIT;--
END LOOP loop;--
END
DB20000I The SQL command completed successfully.
$ db2 "call APR.PURGE_TABLE_INFO_TMT ('schema.tabname', 2000, '080027935001DCAD27681116', '2016-09-01-16.00.00.000000', '2016-09-01-16.59.00.000000')"
SQL0104N An unexpected token "FETCH" was found following
"-01-16.59.00.000000'". Expected tokens may include: ")". SQLSTATE=42601
Can you help me with this issue?
Thanks in advance.
Bruno.

When I run this delete statement on CLP, there is no sintaxe error:

$ db2 "delete from (select 1 from schema.tabname where machine_id = '080027935001DCAD27681116' and collect_time between '2016-09-01-15.00.00.000000' and '2016-09-01-15.59.00.00000' fetch first 2000 rows only) as D "
DB20000I The SQL command completed successfully.

Thanks.

Bruno.