calculation of Required logical log
Amit Patel asked how to estimate logical log space for re-inserting millions of rows with INSERT ... SELECT during table purging on IDS 11.70 (Solaris). Jon Ritson suggested raw tables or smaller transactions and measuring log id and position in onstat -l before and after a subset. Art Kagel suggested his dbcopy or dbmove utilities to copy in smaller transactions. No formula was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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
------------------------------
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 ------------------------------
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 ------------------------------