Question & Answer
Question
Why does TBSP_USED_PAGES and TBSP_USED_SIZE_KB show zero entries while using the SYSIBMADM.TBSP_UTILIZATION administrative view against a SMS tablespace ?
Cause
TBSP_USED_PAGES is the total number of pages in use for a DMS table space.
For an SMS table space , it is equal to the value of tablespace_total_pages monitor element.
Answer
To get TBSP_USED_PAGES and TBSP_USED_SIZE_KB information for a SMS tablespace you will need to turn on the DFT_MON_BUFPOOL.
Example:
1. Checking the status of the DFT_MON_BUFPOOL Monitor switch:
- db2 get dbm cfg show detail | grep -i DFT_MON_BUFPOOL
Buffer pool (DFT_MON_BUFPOOL) = OFF OFF
2. Created a Test SMS tablespace:
db2 "create tablespace test MANAGED BY SYSTEM using ('/home/db2v97/test')"
DB20000I The SQL command completed successfully.
3. Created a table tab1 in test tablespace:
db2 "create table tab1 (c1 int not null,c2 int,c3 int) in test"
DB20000I The SQL command completed successfully.
4. Inserted some data into the tab1 table:
db2 "insert into tab1 with temp (c1,c2,c3) as (values (1,1,1) union all select c1+1,c1+2,c1+3 from temp where c1 < 100) select * from temp"
DB20000I The SQL command completed successfully.
5. Running the TBSP_UTILIZATION administrative view retrieves zero rows for TBSP_USED_PAGES and TBSP_USED_SIZE_KB values for the TEST tablespace:
- db2 "select substr(TBSP_NAME,1,40) as tbsp_name,TBSP_USED_SIZE_KB,TBSP_USED_PAGES from sysibmadm.tbsp_utilization"
TBSP_NAME TBSP_USED_SIZE_KB TBSP_USED_PAGES
---------------------------------------- -------------------- --------------------
SYSCATSPACE 97008 24252
TEMPSPACE1 4 1
USERSPACE1 640 160
SYSTOOLSPACE 752 188
SYSTOOLSTMPSPACE 4 1
TEST 0 0
6 record(s) selected.
6. Enable the DFT_MON_BUFPOOL monitor switch:
- db2 attach to db2v97
- db2 update dbm cfg using DFT_MON_BUFPOOL on
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
- db2 get dbm cfg show detail | grep -i DFT_MON_BUFPOOL
Buffer pool (DFT_MON_BUFPOOL) = ON ON
Note:
The change takes effect immediately if you explicitly ATTACH to the instance before modifying the dft_mon_xxxx switch settings. Otherwise the setting takes effect the next time the instance is restarted
7. Running the TBSP_UTILIZATION administrative view will retreive the values now.
- db2 "select substr(TBSP_NAME,1,40) as tbsp_name,TBSP_USED_SIZE_KB,TBSP_USED_PAGES from sysibmadm.tbsp_utilization"
TBSP_NAME TBSP_USED_SIZE_KB TBSP_USED_PAGES
---------------------------------------- -------------------- --------------------
SYSCATSPACE 97008 24252
TEMPSPACE1 4 1
USERSPACE1 640 160
SYSTOOLSPACE 752 188
SYSTOOLSTMPSPACE 4 1
TEST 8 2
6 record(s) selected.
In summary, the Buffer monitor switch(DFT_MON_BUFPOOL) should be enabled for tablespace_total_pages while using the SYSIBMADM.TBSP_UTILIZATION administrative view against a SMS tablespace.
Related Information
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tablespaces","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1;10.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21673872