SELECT - pct utilization of DB2

[MrFilip]

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

[axisminden]

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

michael

[MrFilip]

Thank you very much,

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

this is it

[balkark]

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

[terlisimo]

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