Results 1 to 5 of 5
  1. #1
    Newcomer
    Join Date
    Jul 2011
    Location
    Czech Republic
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question SELECT - pct utilization of DB2

    Hi friends,

    I'm facing one problem with TSM 6.x . When I want to query the DB utilization the TSM 6 doesn't show pct_utilized:


    Database Name Total Pages Usable Pages Used Pages Free Pages
    -------------- ------------ ------------ ------------ ------------
    TSMDB1 5'737'703 5'737'159 4'918'175 818'984


    and also:

    tsm: MYTSMSERVER>select * from db

    DATABASE_NAME: TSMDB1
    TOT_FILE_SYSTEM_MB: 162816
    USED_DB_SPACE_MB: 94016
    FREE_SPACE_MB: 68770
    TOTAL_PAGES: 5737488
    USABLE_PAGES: 5736944
    USED_PAGES: 4917768
    FREE_PAGES: 819176
    BUFF_HIT_RATIO: 98.8
    TOTAL_BUFF_REQ: -433112246
    SORT_OVERFLOW: 0
    PKG_HIT_RATIO: 95.3
    LAST_REORG:
    FULL_DEV_CLASS: G1_LTO2
    NUM_BACKUP_INCR: 0
    LAST_BACKUP_DATE: 2011-07-13 07:00:21.000000
    PHYSICAL_VOLUMES: 0


    I need to SELECT the percent utilization using USABLE_PAGES and USED_PAGES (to count: USED_PAGES / (USABLE_PAGES * 0.01)). I guess it should be done with the function SUM, CAST, COUNT but I have no idea how to do it. I don't really want to use calculator every time I'm checking the DB utilization
    Last edited by MrFilip; 07-13-2011 at 05:41 AM.

  2. #2
    Member
    Join Date
    Mar 2008
    Location
    Minden
    Posts
    166
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Default

    Hi,
    you can take a look here.
    https://www-304.ibm.com/support/docv...id=swg27019794

    michael

  3. #3
    Newcomer
    Join Date
    Jul 2011
    Location
    Czech Republic
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Thank you very much,

    select sum(100-(FREE_PAGES*100) / USABLE_PAGES) as PCT_UTILIZED from db

    this is it

  4. #4
    Member
    Join Date
    May 2007
    Posts
    44
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Ran the script on my DB. Doesn't look right to me. I have 307 GB with 122 GB used.
    Maybe i dont understand the diff between free pages and util is. Can you explain?

    ANS8000I Server command: 'q db f=d'
    Database Name: TSMDB1
    Total Size of File System (MB): 307,200
    Space Used by Database(MB): 121,344
    Free Space Available (MB): 185,808
    Total Pages: 7,051,268
    Usable Pages: 7,051,132
    Used Pages: 5,806,808
    Free Pages: 1,244,324
    Buffer Pool Hit Ratio: 99.9
    Total Buffer Requests: 1,877,493,514
    Sort Overflows: 0
    Package Cache Hit Ratio: 99.2
    Last Database Reorganization: 09/27/2011 23:12:00
    Full Device Class Name: DD
    Incrementals Since Last Full: 0
    Last Complete Backup Date/Time: 10/01/2011 09:03:38
    ANS8000I Server command: 'select sum(100-(FREE_PAGES*100) / USABLE_PAGES) as PCT_UTILIZED from db'
    PCT_UTILIZED: 82.4

  5. #5
    Member
    Join Date
    Mar 2009
    Location
    Zagreb, Croatia
    Posts
    65
    Thanks
    0
    Thanked 7 Times in 3 Posts

    Default

    In TSM 6.2 it works like this:

    DB pages are allocated as-needed. Let's say your DB grows to 100GB. Then, you expire/delete 50% of objects. DB2 will at some point do a REORG of relevant tables and free the space. Query Database will show these as free pages but your DB size on disk and DB backup will still be 100GB.

    These free pages will be reused for new entries so your DB shouldn't grow beyond 100GB until most free pages are used up.

    On DB2, TSM uses the tablespace USERSPACE1 which is not automatically reduced. If you wish to reduce it manually you need to run:

    db2 alter tablespace USERSPACE1 lower high water mark
    db2 alter tablespace USERSPACE1 reduce max

    Stop TSM instance before doing this. To minimize database size you should also do a manual REORG on large tables. This can take a while... my 50GB DB is REORGed in about 20 minutes. REDUCE MAX and LOWER HIGH WATER MARK should complete in a few seconds.

    This is really a DB2 issue, not strictly a TSM issue. See here:

    Reclaiming unused storage in DMS table spaces
    Last edited by terlisimo; 10-12-2011 at 08:16 PM. Reason: typo

Similar Threads

  1. Replies: 5
    Last Post: 09-20-2010, 01:46 PM
  2. Replies: 5
    Last Post: 09-21-2007, 04:20 PM
  3. Data on Tape volume and Pct Utilization
    By khawts in forum Tape / Media Library
    Replies: 4
    Last Post: 09-21-2007, 03:47 PM
  4. Pct Util & Pct Migr FULL DISK STORAGEPOOL
    By David_FA in forum Performance Tuning
    Replies: 2
    Last Post: 01-19-2007, 03:15 AM
  5. Replies: 4
    Last Post: 11-08-2006, 02:38 PM

Tags for this Thread

Posting Permissions

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