Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Space free , sysmaster and onstat_d output are different !!!!!

  • 1.  Space free , sysmaster and onstat_d output are different !!!!!

    Posted 10 days ago
    Hi

    on 11.70.FC8 (i know i have to upgrade , it s ongoing :) )

    i calculated the space free with onstat -d and the statement below, but the output are different

    with onstat -d (free column * page size), each dbspace has different pagesize

    and on sysmaster 
    database sysmaster;
    select name[1,8] dbspace, -- name truncated to fit on one line
    round(sum(chksize * d.pagesize), 2) Total_size, -- sum of all chuncks size pages
    round(sum(nfree * d.pagesize),2) Space_Free,
    round (100 - (sum(nfree)) / (sum(chksize)) * 100, 2) percent_Used
    from sysdbspaces d, syschunks c
    where d.dbsnum = c.dbsnum
    and d.is_blobspace = 0
    and d.is_sbspace = 0
    group by 1;


    does anyone faced it before ?

    Thanks :)


    ------------------------------
    John Smith
    ------------------------------


  • 2.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 10 days ago
    For regular dbspaces (as you are limiting the query to), syschunk.nfree & chksize are in units of the system default page size (sysshmvals.sh_pagesize) and not the dbspace page size.  It's a bit confusing when comparing against onstat -d.

    ------------------------------
    Mike Walker
    ------------------------------



  • 3.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 10 days ago
    But  in my case, sh_pagesize is the same as pagesize !!

    sh_pagesize

    4096

    ------------------------------
    John Smith
    ------------------------------



  • 4.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 10 days ago
    You said that each dbspace has a different pagesize...

    Is it wrong for ALL dbspaces?

    Do you have some dbspaces with names greater than 8 characters which are being grouped together?

    ------------------------------
    Mike Walker
    ------------------------------



  • 5.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 7 days ago
    Hello

    Is it wrong for ALL dbspaces? no only for those with a page size different than the default page size (4096, onstat -b)

    ------------------------------
    John Smith
    ------------------------------



  • 6.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 7 days ago
    Yes, but do i have to the sh_pagesize instead of the each dbspace page size ? 

    ------------------------------
    John Smith
    ------------------------------



  • 7.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 9 days ago
    Here is a copy of my notes in my dbspace.ec utility about page size in syschunks:


           /*
             The semantics of syschunks and page sizes changes from version to version:
                             < v10        V10               v11.10/11.50             v11.70+
                             -------       ------------          ----------------              ---------------
             chksize    # pages  # pages          # default pages       # default pages
             pagesize   DNA      # def pgs/pg   # bytes/page           # bytes/page
             nfree        # pages  # def pages    # def pages      dbspace: # def pages
                                                                                              blbspc:  # blb pages
                                                                                            sbspace: #sbpages
           */

    So, v11.70 stores the number in nfree is the number of default sized pages (so 4K on MAC, Windows or AIX, 2K everywhere else). 

    So, your free space line:
    round(sum(nfree * d.pagesize),2) Space_Free,

    should be: 
    round(sum(nfree * <def pagesize>) Space_Free,

    You can get that by either selecting the pagesize from the chunk table or querying the sysmaster:sysshmvals.sh_pagesize column
    (divided by 1024 if you want KB).



    ------------------------------
    Art Kagel
    ------------------------------



  • 8.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 9 days ago
    Oh, forgot, you can always just use dbsavail from my utils2_ak package which you can download from my web site at www.askdbmgt.com/my-utilities

    Art

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



  • 9.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 7 days ago
    Mr Art

    it s all right :) , nfree column is number of 4k pages (default) :)

    thanks a lot

    ------------------------------
    John Smith
    ------------------------------



  • 10.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 7 days ago
    Ah, good, you answered your own question. Yes, you always use the default page size for free in the latest releases for regular and temp dbspaces. For blobspaces and smart blobspaces use the space's pagesize instead.

    Did I mention that you can just use my dbsavail utility? And if you need to get the data into your own application, dbsavail installs stored procedures to handle all of the heavy lifting, so you could just call them.

    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.








  • 11.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 7 days ago

    « The good teacher is the teacher that pushes the pupil to ask himself the good questions and triggers the little thing that makes him start the right reasoning, then answer by himself ��"

     

     

     






  • 12.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 5 days ago
    Edited by John Smith 5 days ago
    Hi :)

    also, please,  for having a database size,

    what about ti_nptotal and ti_npused for tables with ti_pagesize != def pagesize (4096)

    the values are  also in def page size ? or in space pagesize ?

    and the database contains data in a sbpace , does the  sbpace data included with the statement below ?

    SELECT
    dbsname, tabname, ti_nptotal, ti_npused, ti_pagesize
    FROM
    sysdatabases AS d,
    systabnames AS n,
    systabinfo AS i
    WHERE n.dbsname = d.name
    AND ti_partnum = n.partnum

    ------------------------------
    John Smith
    ------------------------------



  • 13.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 5 days ago
    This one IS in units of the dbspace page size (ti_pagesize).

    ------------------------------
    Mike Walker
    ------------------------------



  • 14.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 5 days ago
    Thanks
    and does it include the data in the SBspace ?

    ------------------------------
    John Smith
    ------------------------------



  • 15.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 5 days ago
    i think no :)
    forget about it :)
    thank youuuuuuuu :)

    ------------------------------
    John Smith
    ------------------------------



  • 16.  RE: Space free , sysmaster and onstat_d output are different !!!!!

    Posted 7 days ago
    Yup - just what I said:  For regular dbspaces (as you are limiting the query to), syschunk.nfree & chksize are in units of the system default page size (sysshmvals.sh_pagesize) and NOT the dbspace page size.  :-)



    ------------------------------
    Mike Walker
    ------------------------------