1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

SELECT - pct utilization of DB2

Discussion in 'TSM Operation' started by MrFilip, Jul 13, 2011.

  1. MrFilip

    MrFilip New Member

    Joined:
    Jul 13, 2011
    Messages:
    2
    Likes Received:
    0
    Location:
    Czech Republic
    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: Jul 13, 2011
  2.  
  3. axisminden

    axisminden New Member

    Joined:
    Mar 18, 2008
    Messages:
    170
    Likes Received:
    4
    Location:
    Minden
  4. MrFilip

    MrFilip New Member

    Joined:
    Jul 13, 2011
    Messages:
    2
    Likes Received:
    0
    Location:
    Czech Republic
    Thank you very much,

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

    this is it :)
     
  5. balkark

    balkark New Member

    Joined:
    May 12, 2007
    Messages:
    44
    Likes Received:
    2
    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
     
  6. terlisimo

    terlisimo Member

    Joined:
    Mar 11, 2009
    Messages:
    65
    Likes Received:
    10
    Location:
    Zagreb, Croatia
    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: Oct 12, 2011
: db2, select, sql, tsm 6

Share This Page