Results 1 to 7 of 7

Thread: system tables

  1. #1
    Join Date
    Jan 2008
    Posts
    13

    Wink Unanswered: system tables

    Hi All, Can anyone help me which DB2's system table keeps the inforamation about tablespace container's for example number of used pages, number of free pages, usable pages etc.
    thanks
    sunny

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That information is not kept in the system catalog tables (except for some stale statistics that are only updated after a runstats). You can do a tablespace snapshot to get the information.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2link
    Hi All, Can anyone help me which DB2's system table keeps the inforamation about tablespace container's for example number of used pages, number of free pages, usable pages etc.
    SYSIBM.SYSTABLEPART
    (at least, on z/OS)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    SYSIBM.SYSTABLEPART
    (at least, on z/OS)
    Since he mentioned containers, he was talking about DB2 or LUW. In any event, that catalog stats are only updated after a runstats or stospace utility (for z/OS), depending on the number being looking at.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Keeping such statistics current in the catalog is not a good idea in the first place. The reason being that each write operation on a container that causes changes to those statistics would trigger an update to the catalog. And catalog updates must be locked. So besides requiring 2 writes instead of just 1, it also impacts concurrency in a negative way. Therefore, the mentioned statistics are only snapshot information of the point in time when RUNSTATS was executed the last time.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    Keeping such statistics current in the catalog is not a good idea in the first place. The reason being that each write operation on a container that causes changes to those statistics would trigger an update to the catalog.
    What about runtime statistics (RTS) ?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Same thing. So if you have a system where you really need to squeeze out the last fraction of a percent for performance, you will typically turn off RTS.

    DB2 can try to do things in a smarter way like maintaining deltas of changes on the per-table level and then writing those changes only once.

    The same could be done on a container-level. However, the need for current statistics on a container-level is much, much less. Today, you usually use automatic storage and don't care about container-stats anyway. RTS on the other hand is used for a lot of different things now, and the usage will expand over time.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •