db2 CREATE DATABASE **** ALIAS **** USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM USER TABLESPACE MANAGED BY DATABASE USING "(FILE '****' 51200)"EXTENTSIZE 16 PREFETCHSIZE 16 OVERHEAD 14.06 TRANSFERRATE 0.33 CATALOG TABLESPACE MANAGED BY DATABASE USING "(FILE '****Xsys' 51200)" EXTENTSIZE 8 PREFETCHSIZE 8 OVERHEAD 14.06 TRANSFERRATE 0.33 TEMPORARY TABLESPACE MANAGED BY DATABASE USING "(FILE '****Xtemp' 51200)" EXTENTSIZE 32 PREFETCHSIZE 32 OVERHEAD 14.06 TRANSFERRATE 0.33;
db2 CONNECT TO **** user db2inst1 using db2inst1;
db2 CREATE BUFFERPOOL ****XPOOL SIZE 5000 PAGESIZE 16384;
db2 DEACTIVATE DATABASE ****;
db2 RESTART DATABASE **** user db2inst1 using db2inst1;
db2 CONNECT RESET;
db2 CONNECT TO **** user db2inst1 using db2inst1;
db2 CREATE REGULAR TABLESPACE ****XSPACE IN NODEGROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE USING "(FILE '****XSPACE'12800)" EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL ****XPOOL OVERHEAD 24.100000 TRANSFERRATE 0.900000;
db2 CREATE REGULAR TABLESPACE ****XIDX IN NODEGROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE USING "(FILE '****XIDX'6400)" EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL ****XPOOL OVERHEAD 24.100000 TRANSFERRATE 0.900000;
db2 CREATE TEMPORARY TABLESPACE ****XTMP IN NODEGROUP IBMTEMPGROUP PAGESIZE 16384 MANAGED BY DATABASE USING "(FILE '****XTMP'6400)" EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL ****XPOOL OVERHEAD 24.100000 TRANSFERRATE 0.900000;
db2 ALTER TABLESPACE SYSCATSPACE PREFETCHSIZE 16 OVERHEAD 24.100000 TRANSFERRATE 0.900000;
db2 ALTER TABLESPACE TEMPSPACE1 PREFETCHSIZE 16 OVERHEAD 24.100000 TRANSFERRATE 0.900000;
db2 ALTER TABLESPACE USERSPACE1 PREFETCHSIZE 16 OVERHEAD 24.100000 TRANSFERRATE 0.900000;
改为自增长
db2 alter tablespace ****XSPACE autoresize yes increasesize 102400 k maxsize none
db2 alter tablespace ****XTMP autoresize yes increasesize 102400 k maxsize none
db2 alter tablespace ****XIDX autoresize yes increasesize 102400 k maxsize none
db2 CONNECT TO **** user db2inst1 using db2inst1;
运行sql脚本
db2 -tvf /home/wzl/create.sql
1、切换到db2用户
su - db2inst1
2、连接db2的数据库
这里所使用的数据库为 WAREHOUS，根据实际情况连接到需要的数据库
db2 connect to WAREHOUS
备注：db2 LIST DATABASE DIRECTORY 可以查看实例下有哪些数据库
3、查看表空间使用率，确认需要扩容的表空间
db2 "select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size order by 1"
4、查看表空间信息
db2 "LIST TABLESPACES"
5、通过Tablespace ID查找表空间容器
例如：查找Tablespace ID =8 的表空间使用的容器
[db2inst1@GZDC-SVR-TIVOLI ~]$ db2 list tablespace containers for 8 show detail
Tablespace Containers for Tablespace 8
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00002/TGKLZ_CPU
Type = File
Total pages = 134072
Useable pages = 134016
Accessible = Yes
6、扩容容器大小
扩容有多种方式，这种方式就是将相应的容器都扩大1000页，也就是增加1000页。
db2 " ALTER TABLESPACE TSTGKLZ_CPU EXTEND (FILE '/home/db2inst1/db2inst1/NODE0000/SQL00002/TGKLZ_CPU' 1000) "
FAQ：
[root@localhost dev]# su - db2inst1
[db2inst1@localhost ~]$ db2 connect to rsaca61;
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = RSACA61
[db2inst1@localhost ~]$ db2 "select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size order by 1";
TABLESPACE_NAME TABLESPACE_TYPE TOTAL_MB USED_MB FREE_MB PAGE_SIZE
-------------------- --------------- -------------------- -------------------- -------------------- -----------
RSACA61IDX ANY 100 1 98 16384
RSACA61SPACE ANY 200 137 62 16384
RSACA61TMP SYSTEMP 100 1 98 16384
SYSCATSPACE ANY 200 81 118 4096
SYSTOOLSPACE LARGE 32 0 31 4096
SYSTOOLSTMPSPACE USRTEMP 0 0 0 4096
TEMPSPACE1 SYSTEMP 200 0 199 4096
USERSPACE1 LARGE 200 199 0 4096
8 record(s) selected.
[db2inst1@localhost ~]$ db2 list tablespaces show detail;
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 51200
Useable pages = 51192
Used pages = 20776
Free pages = 30416
High water mark (pages) = 20776
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 16
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 51200
Useable pages = 51168
Used pages = 64
Free pages = 51104
High water mark (pages) = 64
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 51200
Useable pages = 51184
Used pages = 51184
Free pages = 0
High water mark (pages) = 51184
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 1
Tablespace ID = 3
Name = RSACA61SPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 68800
Useable pages = 68768
Used pages = 8768
Free pages = 60000
High water mark (pages) = 8768
Page size (bytes) = 16384
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1
Tablespace ID = 4
Name = RSACA61IDX
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 369600
Useable pages = 369568
Used pages = 96
Free pages = 369472
High water mark (pages) = 96
Page size (bytes) = 16384
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1
Tablespace ID = 5
Name = RSACA61TMP
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 62400
Useable pages = 62368
Used pages = 64
Free pages = 62304
High water mark (pages) = 64
Page size (bytes) = 16384
Extent size (pages) = 32
Prefetch size (pages) = 16
Number of containers = 1
Tablespace ID = 6
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8188
Used pages = 152
Free pages = 8036
High water mark (pages) = 152
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 7
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
[db2inst1@localhost ~]$
[db2inst1@localhost ~]$ db2 list tablespace containers for 3 show detail;
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61SPACE
Type = File
Total pages = 12800
Useable pages = 12768
Accessible = Yes
[db2inst1@localhost ~]$ db2 list tablespace containers for 4 show detail;
Tablespace Containers for Tablespace 4
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61IDX
Type = File
Total pages = 6400
Useable pages = 6368
Accessible = Yes
[db2inst1@localhost ~]$ db2 list tablespace containers for 5 show detail;
Tablespace Containers for Tablespace 5
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61TMP
Type = File
Total pages = 6400
Useable pages = 6368
Accessible = Yes
[db2inst1@localhost ~]$ db2 list tablespace containers for 2 show detail;
Tablespace Containers for Tablespace 2
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61
Type = File
Total pages = 51200
Useable pages = 51184
Accessible = Yes
[db2inst1@localhost ~]$ db2 "ALTER TABLESPACE USERSPACE1 EXTEND (FILE '/home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61' 5000)";
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 " ALTER TABLESPACE rsaca61TMP EXTEND (FILE '/home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61TMP' 5000)";
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 " ALTER TABLESPACE rsaca61IDX EXTEND (FILE '/home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61IDX' 5000)";
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 " ALTER TABLESPACE rsaca61SPACE EXTEND (FILE '/home/db2inst1/db2inst1/NODE0000/SQL00003/rsaca61SPACE' 5000)";
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2stop force;
07/05/2018 23:19:12 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@localhost ~]$ db2start;
07/05/2018 23:19:18 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@localhost ~]$
