我的DB2 数据库版本是9.5 的,当前有一个表空间USERSPACE1是DMS 自动扩展管理,但当前使用率已经95%了还没自动扩展,请大家帮给各建议。
方法很多啦, 例如 get snapshot for tablepaces. 这是我的一个test system
关心两件事:auto extend and max size
Tablespace name = USERSPACE1
Tablespace ID = 2
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 8192
Tablespace Extent size (pages) = 32
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = No
Tablespace State = 0x\'00000000\'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 32
Total number of pages = 4096
Number of usable pages = 4064
Number of used pages = 1824
Number of pending free pages = 0
Number of free pages = 2240
High water mark (pages) = 1824
Initial tablespace size (bytes) = 33554432
Current tablespace size (bytes) = 33554432
Maximum tablespace size (bytes) = NONE
Increase size (bytes) = AUTOMATIC
Time of last successful resize =
Last resize attempt failed = No
Rebalancer Mode = No Rebalancing
Storage paths have been dropped = No
Minimum Recovery Time = 05/12/2015 19:29:51.000000
Number of quiescers = 0
Number of containers = 1
收起表空间使用情况
1 TBSP_TYPE TBSP_FREE_SIZE_KB TBSP_UTILIZATION_PERCENT
------------------ ---------- -------------------- ------------------------
SYSCATSPACE SMS 0 -1.00
TEMPSPACE1 SMS 0 -1.00
USERSPACE1 DMS 13568 95.39
LDAPSPACE DMS 356352 31.89
SYSTOOLSPACE DMS 32032 2.19
SYSTOOLSTMPSPACE SMS 0 -1.00
收起$ db2 connect to DB2DB
Database Connection Information
Database server = DB2/AIX64 9.1.6
SQL authorization ID = IDSLDAP
Local database alias = DB2DB
$ db2look -d DB2DB -l
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: IDSLDAP
-- This CLP file was created using DB2LOOK Version 9.1
-- Timestamp: 公元2015年10月21日 星期三 17时04分31秒
-- Database Name: DB2DB
-- Database Manager Version: DB2/AIX64 Version 9.1.6
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful
CONNECT TO DB2DB;
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------
CREATE BUFFERPOOL \"LDAPBP\" SIZE AUTOMATIC PAGESIZE 32768 NOT EXTENDED STORAGE;
CONNECT RESET;
CONNECT TO DB2DB;
------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE LARGE TABLESPACE \"LDAPSPACE\" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE
USING (FILE \'/webseal01/user/idsldap/ldap32kcont_DB2DB/ldapspace\'16384)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL LDAPBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
AUTORESIZE YES
INCREASESIZE 256 M
MAXSIZE NONE
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE \"SYSTOOLSPACE\" IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY DATABASE
USING (FILE \'/webseal01/user/idsldap/idsldap/NODE0000/SQL00001/SYSTOOLSPACE\'8192)
EXTENTSIZE 4
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
AUTORESIZE YES
MAXSIZE NONE
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE USER TEMPORARY TABLESPACE \"SYSTOOLSTMPSPACE\" IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING (\'/webseal01/user/idsldap/idsldap/NODE0000/SQL00001/SYSTOOLSTMPSPACE\')
EXTENTSIZE 4
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY OFF;
-- Mimic tablespace
ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
ALTER TABLESPACE USERSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
NO FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
$
收起