How can I delete x number of rows

How can I delete x number of rows?

delete from (select 1 from howardg.prod_bal fetch first 10 rows only)

How can I delete x number of rows in a loop?

CREATE  PROCEDURE DELETE_IN_LOOP()

  LANGUAGE SQL

  BEGIN

    DECLARE at_end INTEGER DEFAULT 0;

    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

 

    REPEAT         

      delete from (select * from TABSCHEMA.TABNAME_TB a where a.CIL1 = 'Y' and a.DATE = '2012-09-04' fetch first 1000000 rows only );

    UNTIL ( at_end = 1)

    END REPEAT;

  END

The goal is to avoid log file full. If using table scan, the SP may scan the table 5 times of the original. May consider creating index before the delete statement.

How can I delete x number of rows in a loop for any table?

CREATE PROCEDURE DELETE_WITH_COND(IN tbname varchar(100), IN vpred varchar(200))

    MODIFIES SQL DATA

    NOT DETERMINISTIC

    NULL CALL

    LANGUAGE SQL

BEGIN

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE at_end INTEGER DEFAULT 0;

DECLARE txt VARCHAR(2000);

DECLARE stmt STATEMENT;

DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

SET txt = 'delete from (select * from ' || tbname || ' where ' || vpred || ' fetch first 1000000 rows only)';

PREPARE stmt FROM txt;

    REPEAT

        EXECUTE stmt;

    UNTIL ( at_end = 1)

    END REPEAT;

END