Implement LIMIT clause in DELETE Statement

Expand DELETE syntax implementing LIMIT clause and maybe also ORDER BY.

Delete syntax can be like:

DELETE [FROM] tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

This limit + order by syntax is already implemented in other major database servers like Oracle or MySQL

Business case for LIMIT clause:
Imagine we should delete a really big number of rows in a table (e.g. DELETE mytable WHERE invdate = TODAY)

In this example, the number of rows deleted is really big and server is always failing throwing a "long transaction" error

It's not possible to convert mytable to a raw table becuase this is a production environment and this table is also used in other parallel transactions.

Only current method to delete this rows is to create a cursor and deleting the rows one by one. This is equivalent to send to the server millions of SQL delete statements each deleting a single row (we don't know any other batch info to group this deletes)

This is performing really bad and stress the server a lot innecessarely.

Implementing limit will solve this problem as we can delete data in batch sizes we already know our server is able to process without throwing long transaction exception:

for (var x = 1 to 100)
    DELETE mytable WHERE invdate = TODAY LIMIT 250000;
    IF ( DBINFO('sqlca.sqlerrd2') = 0) THEN
        EXIT FOR;
    END IF
end for;

This is much more efficient for deleting 2.5 million rows as this will take only 10 delete executions and without implementing the limit clause it tales 2.5 million delete statements executions.

There are a lot of requests in forums about how to solve this issue in informix, and implementing this syntax will solve it