Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Pages show as "used" in empty chunk

  • 1.  Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 08:05 PM
    IDS 12.10.FC12
    Solaris 10 1/13

    Folks,

    A tutorial question...

    I was playing around with dbspaces during the past few days.  I created a brand new dbspace (a single chunk).  So, it is completely empty (except for 53 pages [reserved pages, I presume]).  I then dbimported a database (from a prior dbexport).  All the database objects were in this single dbspace (tables, indexes, simple large objects, and catalog).  Nothing from any other database was put into this dbspace.

    Then, I DROPed the database.

    Server Studio confirms zero objects now present in the chunk.  But, it also shows 4003 pages of space as "used".

    I can't "repack" or "shrink" because those functions require a table name as an input parameter, and no tables are present.

    The question is, after dropping the database, why am I not exactly back to where I was when I first created the dbspace, and before importing anything into it?  That is, why don't I, once again, see an empty dbspace (with 53 pages used)?

    Thank you.

    David Grove


  • 2.  RE: Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 08:14 PM
    Below is an example of a empty dbspace. 53 pages used where dbspace:TBLSpace taking up 50 pages initially. Dbimport would have resulted in dbspace:TBLSpace to grow and allocate more extents. Even though the database has been dropped, the TBLSpace would still be there and not removed. 

    You can verify your dbspace usage using oncheck -pe <dbspace name>.

    DBspace Usage Report: dbs8k               Owner: informix  Created: 04/21/2020
    
    
     Chunk Pathname                             Pagesize(k)  Size(p)  Used(p)  Free(p)
         3 /u1/chunks/ol_test13/dbs8k                     8     2500       53     2447
    
     Description                                                   Offset(p)  Size(p)
     ------------------------------------------------------------- -------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     dbs8k:'informix'.TBLSpace                                            3       50
     FREE                                                                53     2447
    
     Total Used:       53
     Total Free:     2447​


    ------------------------------
    CASEY TAN
    ------------------------------



  • 3.  RE: Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 08:22 PM
    Ah, Thank you.  I forgot about the TBLSPACE.

    I've already dropped that dbspace and am recreating it for some additional stuff I am testing, so I can't immediatley try this.  But, I wonder if I could "shrink" it, and then observe the size to be back to the original extent?

    DG

    ------------------------------
    David Grove
    ------------------------------



  • 4.  RE: Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 08:41 PM
    Not possible to shrink TBLSPACE. 

    You can use onspace -ef and -en option to control the sizing though.

    ------------------------------
    Casey Tan
    ------------------------------



  • 5.  RE: Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 09:14 PM
    I think there is a RFE for shrinking the tblspace, actually thought it had made FC3 but .....

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 6.  RE: Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 08:17 PM
    Run oncheck -pe and look at what is in the chunk - first guess would be a tblspace

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 7.  RE: Pages show as "used" in empty chunk

    Posted Thu June 11, 2020 08:38 PM
    Thank you, Paul.  You and Casey are spot on.

    Our databases have been running for years.  I haven't fooled around with dbspace alterations for a long time.

    Color me red.

    DG

    ------------------------------
    David Grove
    ------------------------------



  • 8.  RE: Pages show as "used" in empty chunk

    Posted Fri June 12, 2020 06:25 AM
    David:

    You created almost 4,000 objects in the database you created so there are 4,003 Tabelspace Tablespace pages (header pages for each table or index) that were created. A new dbspace starts out with an empty Tablespace which is initially created with 100 pages when the first table is created there and extended as needed. No problem, you should still be able to drop the dbspace since those tablespace pages are now empty. You can see those pages both in an oncheck -pe report and in sysmaster:systabnames and sysmaster:sysextents.

    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.








  • 9.  RE: Pages show as "used" in empty chunk

    Posted Fri June 12, 2020 06:18 PM
    Art:

    Thank you.  Except for the occasional addition of chunks, most of our Informix instances have had the same space layout for many years.  I just didn't have to deal with dbspace issues.  I am now engaged in a re-organization of much of our Informix disk layouts.  As is obvious, I just didn't immediately recall some of the Informix architectural basics.

    DG

    ------------------------------
    David Grove
    ------------------------------