IBM Data and AI Ideas Portal for Customers


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The product management team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

Additional Information

To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

IBM Employees:

The correct URL for entering your ideas is: https://hybridcloudunit-internal.ideas.aha.io


Status Future consideration
Workspace Informix
Components Informix Server
Created by Guest
Created on Aug 9, 2021

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


Needed By Quarter
  • Guest
    Sep 6, 2021

    Or, you can use my dbdelete utility which deletes 8192 rows in a single statement and commits transactions in configurable size (default 10,000 rows). Dbdelete completely avoids the long transaction problem. Dbdelete in included in my utils2_ak package which you can download and use for free from my web site at www.askdbmgt.com/my-utilities.htm