Informix

Expand all | Collapse all

LIMIT DELETE ROWS

  • 1.  LIMIT DELETE ROWS

    Posted 29 days ago
    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 29 days ago
    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
    ------------------------------



  • 3.  RE: LIMIT DELETE ROWS

    Posted 29 days ago
    Thank you Art :)

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



  • 4.  RE: LIMIT DELETE ROWS

    Posted 29 days ago
    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





  • 5.  RE: LIMIT DELETE ROWS

    Posted 29 days ago
    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
    ------------------------------



  • 6.  RE: LIMIT DELETE ROWS

    Posted 29 days ago
    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 29 days ago
    Agreed if so :-)

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



  • 8.  RE: LIMIT DELETE ROWS

    Posted 29 days ago
    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 28 days ago
    Cool procedure Doug!

    Art

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