Reclaim Dbspace after a purge operation
Saradhi Motamarri asked how to reclaim space after a purge and whether table and index shrink can run for a whole database. SangGyu Jeong and Art Kagel said TABLE/INDEX REPACK SHRINK and DEFRAGMENT work on one table or index (or partition) at a time, online, with light impact and are restartable. Doug Lawry said InformixHQ can do it for a whole database.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi Art, I read in one of your posts that to reclaim table/ index space after a deletes, needs to run the API function ("table shrink") and ("index shrink") manually. Is this process can be run for an entire database (as many tables might have been cleaned in a purge operation)? How heavy this operation, mean any impact to the online transactions when the shrink operation runs in the background? #Informix
Hi Saradhi, Sorry for interrupting the question. The admin or task routine that performs the defragment function can be executed for a specific table or index. There is no option to perform the entire database. https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_sapi_107.htm The defragment function can be performed online, but I think it is burdensome to perform the entire table and index in batch. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
Thank you SangGyu.
Saradhi: You are thinking of the "TABLE REPACK SHRINK" and "INDEX REPACK SHRINK" API functions. These commands, like the "DEFRAGMENT" and "DEFRAGMENT PARTNUM" API functions can only be issued for a single table or index, or a partition of one, at a time. It is fairly lightweight on most server resources but a bit more intense, as one might expect, in its effects on IO performance, though not outrageous. These are send-and-forget operations that are interruptable and restartable and will not cause damage if the server crashes during the run. They are online operations and do not lock the table/index except for very brief locks to link in new copies of pages and unlink old ones. You can read more about them in the Administrator's Reference manual online. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Thank you Art.
Hi Saradhi. This can easily be done for a whole database using InformixHQ: ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Hi Doug, Thank you.