Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


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:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


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


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


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
    Reply
    |
    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