Informix

 View Only
  • 1.  calculation of Required logical log

    Posted Fri March 12, 2021 12:47 AM
      |   view attached
    Hello All,

    We need to the data purging for table which is having huge data. We are renaming table with other table and then Re-creatingthe original table and from backup table again inserting data based on condition like "INSERT INTO cs_pmpt_regn SELECT * from cs_pmpt_regn_backup where date > '01-01-2019 10:20:30'
    and could insert millions of records.

    So How can calculate the estimation for how many logical log may required for transaction?

    I have attached the word file with table rows, extent size and used extent size and number of logical log.

    Kindly help to share the logical log estimation calculation.



    Thanks
    Amit Patel
     


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

    #Informix

    Attachment(s)

    doc
    cs_pmpt_regn.doc   30 KB 1 version


  • 2.  RE: calculation of Required logical log

    Posted Fri March 12, 2021 01:42 AM

    1. Read up on raw tables (beware of restrictions!)

    https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.admin.doc/ids_admin_0514.htm

    2. Consider "smaller transactions" of inserts ... and can also use this to provide an estimate of usage (i.e. do a small subset and check log ID and log POS before and after from onstat -l)

    (Always provide O/S and Informix Version ... helps people responding).



    ------------------------------
    Jon Ritson
    ------------------------------



  • 3.  RE: calculation of Required logical log

    Posted Fri March 12, 2021 02:20 AM
    Dear John ,

                    Thanks for Replying.

                     Currently working on IDS 11.70 with base Solaris 5.11 :) 

                     may be will upgrade to IDS 14.10 next month.



    Thanks
    Amit Patel

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



  • 4.  RE: calculation of Required logical log

    IBM Champion
    Posted Fri March 12, 2021 02:15 AM
    Amit:

    You may be able to use either my dbcopy or demove utility, both included in my utils2_ak package, to copy the data using smaller transactions!

    You can download the latest release of utils2_ak from my web site at www.askdbmgt.com/my-utilities


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