Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Reclaim Dbspace after a purge operation

  • 1.  Reclaim Dbspace after a purge operation

    Posted Tue May 05, 2020 09:04 PM
    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?


  • 2.  RE: Reclaim Dbspace after a purge operation

    Posted Tue May 05, 2020 09:49 PM
    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
    ------------------------------



  • 3.  RE: Reclaim Dbspace after a purge operation

    Posted Tue May 05, 2020 09:55 PM
    Thank you SangGyu.





  • 4.  RE: Reclaim Dbspace after a purge operation

    Posted Tue May 05, 2020 10:35 PM
    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


    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.








  • 5.  RE: Reclaim Dbspace after a purge operation

    Posted Tue May 05, 2020 10:50 PM
    Thank you Art.





  • 6.  RE: Reclaim Dbspace after a purge operation

    Posted Wed May 06, 2020 06:56 AM
    Hi Saradhi.

    This can easily be done for a whole database using InformixHQ:



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



  • 7.  RE: Reclaim Dbspace after a purge operation

    Posted Thu May 07, 2020 06:27 AM
    Hi Doug,
    Thank you.