Illegal symbol LOOP in DB2-CodePudding

I have one table which has records more than 4 millions. So I am trying to delete the records in loop. Below is the db2 query i have tried.

del_loop: 
LOOP
  DELETE FROM (
   SELECT FROM BOM c
   WHERE EXISTS (SELECT 1 FROM TESTOBJ t
                WHERE c.OID = t.OID
   ) FETCH FIRST 1000 ROWS ONLY
  )
IF SQLCODE = 100 THEN LEAVE del_loop;
END IF;
--COMMIT;
END LOOP loop;

But its throwing below error

[Code: -104, SQL State: 42601]  ILLEGAL SYMBOL "DEL_LOOP". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: DECLARE. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11

Can someone help on this

CodePudding user response：

You need to follow the syntax rules of one of the types of compound statements: inlined or compiled.

CodePudding user response：

Too many errors.
You must use so called compound statement and different statement delimiter, say @ as in the example.
The text below is for Db2 Command Line Processor, which can be placed to some file and executed with db2 -f myfile.sql as is presuming, that you have connection to your database in the current session.

--#SET TERMINATOR @
CREATE TABLE COLUMNS AS 
(
SELECT TABSCHEMA, TABNAME, COLNAME FROM SYSCAT.COLUMNS
) WITH DATA IN USERSPACE1@

BEGIN
    DECLARE SQLCODE INT;
    del_loop: 
    LOOP
        DELETE FROM 
        (
            SELECT 1 
            FROM COLUMNS 
            -- WHERE EXISTS ...
            FETCH FIRST 100 ROWS ONLY
        );
        IF SQLCODE = 100 THEN LEAVE del_loop; END IF;
        COMMIT;
    END LOOP del_loop;
END@