• Sintax Error in procedure code

    From Bruno Almeida@21:1/5 to All on Thu Sep 8 08:08:59 2016
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Bruno Almeida@21:1/5 to Bruno Almeida on Thu Sep 8 08:15:39 2016
    On Thursday, September 8, 2016 at 12:09:13 PM UTC-3, Bruno Almeida wrote:
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)