Amit:You cannot do that with SQL, no. However, that's why I wrote my dbdelete utility. Note the -b flag:
$ dbdelete
dbdelete - Delete rows from an Informix table.
Usage: dbdelete
-d database
-b FetBufSize
-t table
<-h host>
<-S> <-V> <-R> <-I> <-v>
<-w waittime>
<-u keylist>
<-o>
<-s where clause | -q select stmt>
<-B SQL bufsize>
<-p PDQRPIORITY>
-d - Database from which to delete.
-b - Size of a transaction buffer. Controls the number of rows deleted
in a single transaction. (Default: 32767. Range: 2048 -> 32767)
-t - Table from which data is to be deleted.
-h - Host to delete rows from (Default: $INFORMIXSERVER).
-S - Silent mode. Supress messages.
-V - Print version information and exit.
-v - Enable verbose output.
-I - Use ifx_row_id instead of rowid. Supports partitioned tables
that were not created WITH ROWIDS. Should be faster for other
partitioned tables as well.
-P - Pause in milli-seconds between deletes.
-w - # of seconds to wait for locked rows to be released (if < 0 wait
forever). (Default 10.)
-u - Comma separated list of columns forming a key which can be used to
identify rows to delete. Separate -u options may be used.
(Default: ROWID.)
-o - Do not close the fetch cursor between fetch rounds. This is
slower, however, it will prevent dbdelete from deleting rows that
have been inserted since the original cursor was opened. Note:
this option does NOT eliminate the dangers of using dbdelete on
a table that is being actively inserted to. Rows inserted BEFORE
the cursor is open are still at risk. Caviat emptor applies.
-s - Where clause for use in selecting ROWIDS or row keys to delete.
Do not include the keyword "WHERE".
-q - Full SELECT statement to select rowids (or keys) to delete. If
keys are returned you must also include -u
-B - Set the size of the SQL DELETE statements that are generated.
(DEFAULT: 2048, MAXIMUM: 65500) N.B. 2K seemed most efficient in
testing. This option is effective only for delete by rowid (ie no
'-u' options).
-p - PDQPRIORITY to set for the task.
-R - Do not replicate deletions. Begins transactions with the WITHOUT
REPLICATION clause included.
The standard Informix environment variable FET_BUF_SIZE will override the
default value of the (-b flag) Commit size if within the valid range. Higher
values for -b will commit larger blocks of rows deleted and use more locks.
The default setting deletes 8192 rows in each transaction. Dbdelete is included in my utils2_ak package which you can download for free from my web site at: My Utilities
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Wed June 30, 2021 06:43 AM
From: AMIT PATEL
Subject: LIMIT DELETE ROWS
Hello All,
Can we Limit the Delete Records in Informix?
Or Is it possible to set number of record deletion per Commit?
Thanks
Amit Patel
------------------------------
AMIT PATEL
------------------------------
#Informix