问题:DB2 DPF环境下,如何查看表空间的page使用情况?
答:最好的办法是使用表函数MON_GET_TABLESPACE
1.)查看某个表空间在所有节点上的使用情况
$ db2 "select substr(tbsp_name,1,30) as TBSPNAME,member, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES from table(MON_GET_TABLESPACE('USERSPACE1',-2))"
TBSPNAME MEMBER TBSP_TOTAL_PAGES TBSP_USED_PAGES TBSP_FREE_PAGES
------------------------------ ------ -------------------- -------------------- --------------------
USERSPACE1 2 4096 1760 2304
USERSPACE1 0 4096 1760 2304
USERSPACE1 1 4096 1760 2304
USERSPACE1 3 4096 1760 2304
4 record(s) selected.
2.) 查看某个节点上所有表空间的使用情况
$ db2 "select substr(tbsp_name,1,30) as TBSPNAME,member, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES from table(MON_GET_TABLESPACE( '',-1))"
TBSPNAME MEMBER TBSP_TOTAL_PAGES TBSP_USED_PAGES TBSP_FREE_PAGES
------------------------------ ------ -------------------- -------------------- --------------------
TEMPSPACE1 2 1 1 0
USERSPACE1 2 4096 1760 2304
IBMDB2SAMPLEREL 2 32768 29792 2944
IBMDB2SAMPLEXML 2 4096 1408 2656
NEW109 2 1 1 0
TBS_DATA_AM 2 8192 4960 3200
TBS_DATA_DIM 2 4096 864 3200
TBS_HSYDATA_AM 2 4096 160 3904
DBP_TABSPACE 2 4096 96 3968
DPF_TABSPACE 2 4096 96 3968
TS_ZX01 2 327 0 0
NEW 2 4096 1120 2944
DMS_TBS 2 1000 96 864
13 record(s) selected.
3.) 查看所有节点所有表空间的使用情况
$ db2 "select substr(tbsp_name,1,30) as TBSPNAME,member, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES from table(MON_GET_TABLESPACE( '',-2))"
TBSPNAME MEMBER TBSP_TOTAL_PAGES TBSP_USED_PAGES TBSP_FREE_PAGES
------------------------------ ------ -------------------- -------------------- --------------------
TEMPSPACE1 2 1 1 0
USERSPACE1 2 4096 1760 2304
IBMDB2SAMPLEREL 2 32768 29792 2944
IBMDB2SAMPLEXML 2 4096 1408 2656
NEW109 2 1 1 0
TBS_DATA_AM 2 8192 4960 3200
TBS_DATA_DIM 2 4096 864 3200
TBS_HSYDATA_AM 2 4096 160 3904
DBP_TABSPACE 2 4096 96 3968
DPF_TABSPACE 2 4096 96 3968
TS_ZX01 2 327 0 0
NEW 2 4096 1120 2944
DMS_TBS 2 1000 96 864
SYSCATSPACE 0 16384 15632 748
TEMPSPACE1 0 1 1 0
USERSPACE1 0 4096 1760 2304
IBMDB2SAMPLEREL 0 24576 22528 2016
IBMDB2SAMPLEXML 0 4096 1376 2688
SYSTOOLSPACE 0 4096 116 3976
NEW00 0 1 1 0
NEW109 0 1 1 0
TBS_DATA_AM 0 8192 4960 3200
TBS_DATA_DIM 0 4096 864 3200
SYSTOOLSTMPSPACE 0 1 1 0
TBS_HSYDATA_AM 0 4096 160 3904
DBP_TABSPACE 0 4096 96 3968
DPF_TABSPACE 0 4096 96 3968
NEW 0 4096 1120 2944
TEMPSPACE1 1 1 1 0
USERSPACE1 1 4096 1760 2304
IBMDB2SAMPLEREL 1 40960 38240 2688
IBMDB2SAMPLEXML 1 4096 1440 2624
NEW109 1 1 1 0
TBS_DATA_AM 1 8192 4960 3200
TBS_DATA_DIM 1 4096 864 3200
TBS_HSYDATA_AM 1 4096 160 3904
DBP_TABSPACE 1 4096 96 3968
DPF_TABSPACE 1 4096 96 3968
NEW 1 4096 1120 2880
DMS_TBS 1 1000 96 864
TEMPSPACE1 3 1 1 0
USERSPACE1 3 4096 1760 2304
IBMDB2SAMPLEREL 3 40960 37024 3904
IBMDB2SAMPLEXML 3 4096 1376 2688
NEW109 3 1 1 0
TBS_DATA_AM 3 8192 4960 3200
TBS_DATA_DIM 3 4096 864 3200
TBS_HSYDATA_AM 3 4096 160 3904
DBP_TABSPACE 3 4096 96 3968
DPF_TABSPACE 3 4096 96 3968
TS_ZX01 3 327 0 0
NEW 3 4096 1120 2880
DMS_TBS 3 1000 96 864
53 record(s) selected.
参考资料:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0053943.html?cp=SSEPGG_9.7.0%2F3-6-1-3-9-30&lang=en
答:最好的办法是使用表函数MON_GET_TABLESPACE
1.)查看某个表空间在所有节点上的使用情况
$ db2 "select substr(tbsp_name,1,30) as TBSPNAME,member, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES from table(MON_GET_TABLESPACE('USERSPACE1',-2))"
TBSPNAME MEMBER TBSP_TOTAL_PAGES TBSP_USED_PAGES TBSP_FREE_PAGES
------------------------------ ------ -------------------- -------------------- --------------------
USERSPACE1 2 4096 1760 2304
USERSPACE1 0 4096 1760 2304
USERSPACE1 1 4096 1760 2304
USERSPACE1 3 4096 1760 2304
4 record(s) selected.
2.) 查看某个节点上所有表空间的使用情况
$ db2 "select substr(tbsp_name,1,30) as TBSPNAME,member, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES from table(MON_GET_TABLESPACE( '',-1))"
TBSPNAME MEMBER TBSP_TOTAL_PAGES TBSP_USED_PAGES TBSP_FREE_PAGES
------------------------------ ------ -------------------- -------------------- --------------------
TEMPSPACE1 2 1 1 0
USERSPACE1 2 4096 1760 2304
IBMDB2SAMPLEREL 2 32768 29792 2944
IBMDB2SAMPLEXML 2 4096 1408 2656
NEW109 2 1 1 0
TBS_DATA_AM 2 8192 4960 3200
TBS_DATA_DIM 2 4096 864 3200
TBS_HSYDATA_AM 2 4096 160 3904
DBP_TABSPACE 2 4096 96 3968
DPF_TABSPACE 2 4096 96 3968
TS_ZX01 2 327 0 0
NEW 2 4096 1120 2944
DMS_TBS 2 1000 96 864
13 record(s) selected.
3.) 查看所有节点所有表空间的使用情况
$ db2 "select substr(tbsp_name,1,30) as TBSPNAME,member, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES from table(MON_GET_TABLESPACE( '',-2))"
TBSPNAME MEMBER TBSP_TOTAL_PAGES TBSP_USED_PAGES TBSP_FREE_PAGES
------------------------------ ------ -------------------- -------------------- --------------------
TEMPSPACE1 2 1 1 0
USERSPACE1 2 4096 1760 2304
IBMDB2SAMPLEREL 2 32768 29792 2944
IBMDB2SAMPLEXML 2 4096 1408 2656
NEW109 2 1 1 0
TBS_DATA_AM 2 8192 4960 3200
TBS_DATA_DIM 2 4096 864 3200
TBS_HSYDATA_AM 2 4096 160 3904
DBP_TABSPACE 2 4096 96 3968
DPF_TABSPACE 2 4096 96 3968
TS_ZX01 2 327 0 0
NEW 2 4096 1120 2944
DMS_TBS 2 1000 96 864
SYSCATSPACE 0 16384 15632 748
TEMPSPACE1 0 1 1 0
USERSPACE1 0 4096 1760 2304
IBMDB2SAMPLEREL 0 24576 22528 2016
IBMDB2SAMPLEXML 0 4096 1376 2688
SYSTOOLSPACE 0 4096 116 3976
NEW00 0 1 1 0
NEW109 0 1 1 0
TBS_DATA_AM 0 8192 4960 3200
TBS_DATA_DIM 0 4096 864 3200
SYSTOOLSTMPSPACE 0 1 1 0
TBS_HSYDATA_AM 0 4096 160 3904
DBP_TABSPACE 0 4096 96 3968
DPF_TABSPACE 0 4096 96 3968
NEW 0 4096 1120 2944
TEMPSPACE1 1 1 1 0
USERSPACE1 1 4096 1760 2304
IBMDB2SAMPLEREL 1 40960 38240 2688
IBMDB2SAMPLEXML 1 4096 1440 2624
NEW109 1 1 1 0
TBS_DATA_AM 1 8192 4960 3200
TBS_DATA_DIM 1 4096 864 3200
TBS_HSYDATA_AM 1 4096 160 3904
DBP_TABSPACE 1 4096 96 3968
DPF_TABSPACE 1 4096 96 3968
NEW 1 4096 1120 2880
DMS_TBS 1 1000 96 864
TEMPSPACE1 3 1 1 0
USERSPACE1 3 4096 1760 2304
IBMDB2SAMPLEREL 3 40960 37024 3904
IBMDB2SAMPLEXML 3 4096 1376 2688
NEW109 3 1 1 0
TBS_DATA_AM 3 8192 4960 3200
TBS_DATA_DIM 3 4096 864 3200
TBS_HSYDATA_AM 3 4096 160 3904
DBP_TABSPACE 3 4096 96 3968
DPF_TABSPACE 3 4096 96 3968
TS_ZX01 3 327 0 0
NEW 3 4096 1120 2880
DMS_TBS 3 1000 96 864
53 record(s) selected.
参考资料:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0053943.html?cp=SSEPGG_9.7.0%2F3-6-1-3-9-30&lang=en