Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Dbspace contents

  • 1.  Dbspace contents

    Posted Mon September 28, 2020 06:48 AM
    Hi to All,

    how to list what a dbspace contain ?

    thanks :)

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


  • 2.  RE: Dbspace contents

    Posted Mon September 28, 2020 09:52 AM
    John,
     
    oncheck -pe will give you all extents (disk storage allocations of objects), in other words, the entire contents of a dbspace, for all dbspaces. If you have a lot of objects .... this listing will be huge. You can redirect to a filename oncheck -pe > oncheckpe.out for example.
     
    oncheck -pe dbspace_name will give you all extents (disk storage allocations of objects), in other words, the entire contents of a dbspace, for the named dbspace only. Redirect works here as well.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here:
     
     
     





  • 3.  RE: Dbspace contents

    Posted Mon September 28, 2020 11:26 AM
    Thanks a lot; i already dit it but on a space that appears to be not empty, but when i ran oncheck -pe on that dbspace, no objects are listed
    that"s what i wanted a sql statement 

    but yes, oncheck -pe works for other dbspaces, and there are objects in it

    thanks a lot

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



  • 4.  RE: Dbspace contents

    Posted Mon September 28, 2020 11:32 AM
    in fact, it"s about physical dbspace

    on config file, it s rootdbs that is mentionned, with more then 6 Gb, but rootdbs is only 1gb sized.

    PHYSDBS rootdbs
    PHYSFILE 6400000
    PLOG_OVERFLOW_PATH $INFORMIXDIR/tmp
    PHYSBUFF 512


    oncheck -pe physdbs


    oncheck -pe physdbs


    DBspace Usage Report: physdbs Owner: informix Created: 08/21/2001


    Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p)
    2 /dev/chklink/rchkphys 4 262100 53 262047

    Description Offset(p) Size(p)
    ------------------------------------------------------------- -------- --------
    RESERVED PAGES 0 2
    CHUNK FREELIST PAGE 2 1
    physdbs:'informix'.TBLSpace 3 50
    FREE 53 262047

    Total Used: 53
    Total Free: 262047


    Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p)
    91 /dev/chklink/rchkphys_1 4 10485735 10223619 262116

    Description Offset(p) Size(p)
    ------------------------------------------------------------- -------- --------
    RESERVED PAGES 0 2
    CHUNK FREELIST PAGE 2 1
    PHYSICAL LOG 3 10223616
    FREE 10223619 262116

    Total Used: 10223619
    Total Free: 262116

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



  • 5.  RE: Dbspace contents

    Posted Wed September 30, 2020 07:20 AM
    Hi

    i was wondering why the PHYSDBS parameter is rootdbs, and oncheck -pe returnes that there is a physical log in physdbs !!!

    I just figured it out, maybe i"m so in late :)

    because PHYSDBS was deprecated in 11.50.xc1, so no more needed

    thanks to all :)

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



  • 6.  RE: Dbspace contents

    Posted Mon September 28, 2020 09:58 PM
    You can use oncheck -pe

    Or query sysmaster.systabnames:

    SELECT dbsname, tabname, partnum, dbinfo('dbspacename', partnum)
    FROM systabnames
    WHERE dbinfo('dbspacename', partnum) = 'mydbspace';

    Art





  • 7.  RE: Dbspace contents

    Posted Tue September 29, 2020 11:05 AM
    John Smith?  Whaddaythinkthisis, a motel?  :-)
    (Joke stolen from an old Don Rickles sketch.)

    A bunch of years ago I wrote a shell script called fragments.sh, using a "library" named fragment_list.sh.  You can get it from the IIUG repository at https://sourceforge.net/projects/iiug-software-repository/files/DBAdmin-Tools/fragments.shar/ 
    I've included a text file for for documentation.  One of the options to fragments.sh is -D <dbspace>.

    More recently I wrote and uploaded a Perl package including the script partitions.pl but, as you just reminded me, I never got around to adding the
    --DBspace option to that one.  Shame on me, because it runs SO much faster than the shell script.  If interested, you can find that one at https://sourceforge.net/projects/iiug-software-repository/files/DBAdmin-Tools/Fragments.shar.gz/download

    Workaround to my lapse: Perhaps you can use the Perl script but pipe the output to a grep for the desired DBspace.

    Thanks for the reminder.  There are a few items I've not gotten  around to.

    -- Jacob S.