Issue with running a DB2 Script from DataStage Job

Question

I have a DB2 script as below -

BEGIN

DECLARE var1 INTEGER;
DECLARE var2 INTEGER;
SET var1=<<value1>>;
SET var2=<<value2>>;

    BEGIN 
        WHILE (var1 <= var2)
        DO
            DELETE FROM (SELECT * FROM table_name WHERE ID >= var1 FETCH FIRST 2000 ROWS ONLY);
        COMMIT;
        SET var1 = (var1+2000);
        END WHILE;
    END;
END

The script has been tested on DB Visualizer with some settings changes at the tool level (SQL Statement Delimiter: @) and it is running fine. Now the same script has been put in a .txt file and the file is being called from a DB2 Connector in a DataStage job. The job fails with the below error -

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ARE var1 INTEGER". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=2. SQLSTATE=42601

I have tried adding --#SET DELIMITER @ at the beginning of the script but still getting same error.

I think this will only need a small change but not able to identify it.

Answer 1

Do it the DataStage way - build a simple job with a Db2 Connector selecting the data that should get deleted as a source and also a Db2 connector as target stage. In the target specify delete - the commit size will ensure your transaction limit.