Informix

Expand all | Collapse all

LIMIT DELETE ROWS

  • 1.  LIMIT DELETE ROWS

    Posted Wed June 30, 2021 06:44 AM
    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
    ------------------------------


  • 2.  RE: LIMIT DELETE ROWS

    Posted Wed June 30, 2021 10:14 AM
    Hi Amit,

    I don't think there's any built-in feature or settings within Informix that will allow you to do that but it can be done programmatically. Have a look at Art's dbdelete utility - https://www.askdbmgt.com/my-utilities.html.

    Regards,
    Edcel





  • 3.  RE: LIMIT DELETE ROWS

    Posted Wed June 30, 2021 07:56 AM
    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
    ------------------------------



  • 4.  RE: LIMIT DELETE ROWS

    Posted Wed June 30, 2021 03:49 PM
    SESSION_LIMIT_LOCKS will effectively do that if in a transaction. Note that there are locks for indexes too, so set it lower accordingly.

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 5.  RE: LIMIT DELETE ROWS

    Posted Wed June 30, 2021 10:34 PM
    Thank you Art :)

    ------------------------------
    AMIT PATEL
    ------------------------------



  • 6.  RE: LIMIT DELETE ROWS

    Posted Wed June 30, 2021 05:22 PM
    Doug, using SESSION_LIMIT_LOCKS will just terminate the transaction sooner, not break it up into smaller transactions which is what the OP needs.

    Art


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 7.  RE: LIMIT DELETE ROWS

    Posted Wed June 30, 2021 05:49 PM
    Agreed if so :-)

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 8.  RE: LIMIT DELETE ROWS

    Posted Thu July 01, 2021 01:58 AM
    In which case this pure SQL alternative might be another option for you, Amit:

    https://www.oninitgroup.com/faq-items/informix-stored-procedure-for-mass-delete

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 9.  RE: LIMIT DELETE ROWS

    Posted Thu July 01, 2021 08:37 AM
    Cool procedure Doug!

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------