分类目录归档：DB2

db2的DMS表空间管理主要有Exent/Reduce/Resize/Add/begin new stripe set,下面分别对这几项进行介绍
Exent扩展已有容器

[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail

            Tablespace Containers for Tablespace 3

 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 3200
 Useable pages                        = 3168
 Accessible                           = Yes


SQL> select 3200*32/1024 from dual;

3200*32/1024
------------
         100

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data extend (file 
'/home/db2inst2/dms/tbs_data01.dbf' 20M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail

            Tablespace Containers for Tablespace 3

 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 3840
 Useable pages                        = 3808
 Accessible                           = Yes

SQL> select 3840*32/1024 from dual;

3840*32/1024
------------
         120

Reduce缩减已有容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data reduce (file     
'/home/db2inst2/dms/tbs_data01.dbf' 40M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail

            Tablespace Containers for Tablespace 3

 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 2560
 Useable pages                        = 2528
 Accessible                           = Yes

SQL> select 2560*32/1024 from dual;

2560*32/1024
------------
          80

Resize重设容器大小

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data resize (file     
'/home/db2inst2/dms/tbs_data01.dbf' 30M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail

            Tablespace Containers for Tablespace 3

 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes

SQL> select 960*32/1024 from dual;

960*32/1024
-----------
         30

Add增加新容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data add (file        
'/home/db2inst2/dms/tbs_data02.dbf' 30M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail

            Tablespace Containers for Tablespace 3

 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /home/db2inst2/dms/tbs_data02.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes

Add增加新容器后，会在容器件进行数据Rebalance

begin new stripe set增加新容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data begin new stripe set (file 
'/home/db2inst2/dms/tbs_data03.dbf' 10M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail

            Tablespace Containers for Tablespace 3

 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /home/db2inst2/dms/tbs_data02.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 2
 Name                                 = /home/db2inst2/dms/tbs_data03.dbf
 Type                                 = File
 Total pages                          = 320
 Useable pages                        = 288
 Accessible                           = Yes

begin new stripe set当已有容器使用完之后，再使用新增加容器

1.创建数据库

[db2inst2@xifenfei ~]$ db2 "create database xifenfei automatic storage yes 
on /home/db2inst2/dbauto dbpath on /home/db2inst2/xifenfei 
using codeset utf-8 territory cn collate using system"

DB20000I  The CREATE DATABASE command completed successfully.

DBPATH ON表示数据库目录
USING CONDESET codeset TERRITORY territory指定数据库编码集(Codeset)和区域(Territory)
automatic storage yes on /home/db2inst2/dbauto 表示启用自动存储管理，on指定路径

2.查看数据库

[db2inst2@xifenfei ~]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = XIFENFEI
 Database name                        = XIFENFEI
 Local database directory             = /home/db2inst2/xifenfei
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

3.连接数据库

[db2inst2@xifenfei ~]$ db2 connect to xifenfei

   Database Connection Information

 Database server        = DB2/LINUX 9.5.0
 SQL authorization ID   = DB2INST2
 Local database alias   = XIFENFEI

4.创建缓冲池

[db2inst2@xifenfei ~]$ db2 "create bufferpool bp32k size 100 pagesize 32k"
DB20000I  The SQL command completed successfully.

DB2默认创建库指创建4k的bufferpool，因为下面需要创建32k的表空间，所以需要先创建32k的bufferpool

5.创建数据库管理(DMS)的数据表空间

[db2inst2@xifenfei ~]$ db2 "create large tablespace tbs_data pagesize 32k managed by database 
using (file '/home/db2inst2/dms/tbs_data01.dbf' 100M) extentsize 32 prefetchsize automatic 
bufferpool bp32k no file system caching"

DB20000I  The SQL command completed successfully.

no file system caching关闭文件系统缓存,默认选项

6.创建系统管理(SMS)的临时表空间

[db2inst2@xifenfei ~]$ db2 "create temporary tablespace tbs_temp pagesize 32k 
managed by system using ('/home/db2inst2/xifenfei/temp')  bufferpool bp32k"

DB20000I  The SQL command completed successfully.

7.创建系统管理(SMS)的用户临时表空间

[db2inst2@xifenfei ~]$ db2 "create user temporary tablespace tbs_user_temp pagesize 32k 
managed by system using ('/home/db2inst2/xifenfei/user_temp')  bufferpool bp32k"

DB20000I  The SQL command completed successfully.

8.自动存储管理(Automatic Storage)的表空间

[db2inst2@xifenfei ~]$ db2 "create tablespace tbs_index pagesize 32k bufferpool bp32k"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 "create tablespace tbs_data2 initialsize 10M increasesize 10M maxsize 10G"
DB20000I  The SQL command completed successfully.

数据表空间选择DMS,临时表空间选择SMS

9.查看表空间状态

[db2inst2@xifenfei ~]$ 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                          = 16384
 Useable pages                        = 16380
 Used pages                           = 10500
 Free pages                           = 5880
 High water mark (pages)              = 10500
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System 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)                  = 32
 Prefetch size (pages)                = 32
 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                          = 8192
 Useable pages                        = 8160
 Used pages                           = 96
 Free pages                           = 8064
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 3
 Name                                 = TBS_DATA
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 3200
 Useable pages                        = 3168
 Used pages                           = 96
 Free pages                           = 3072
 High water mark (pages)              = 96
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 4
 Name                                 = TBS_TEMP
 Type                                 = System managed space
 Contents                             = System 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)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 5
 Name                                 = TBS_USER_TEMP
 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)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 6
 Name                                 = TBS_INDEX
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 992
 Used pages                           = 96
 Free pages                           = 896
 High water mark (pages)              = 96
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 7
 Name                                 = TBS_DATA2
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2560
 Useable pages                        = 2528
 Used pages                           = 96
 Free pages                           = 2432
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

10.查看表空间和容器相关信息

[db2inst2@xifenfei ~]$ db2 list tablespace containers for 6 show detail

            Tablespace Containers for Tablespace 6

 Container ID                         = 0
 Name                                 = /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000006/C0000000.LRG
 Type                                 = File
 Total pages                          = 1024
 Useable pages                        = 992
 Accessible                           = Yes

11.显示表空间配置信息/使用信息/容器信息

[db2inst2@xifenfei ~]$ db2pd -d xifenfei -tablespaces

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:07:23

Tablespace Configuration:
Address    Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x9F43E060 0     DMS  Regular 4096   4        Yes  4        1     1         Off 1        0          3            SYSCATSPACE
0x9F43E6B0 1     SMS  SysTmp  4096   32       Yes  32       1     1         On  1        0          31           TEMPSPACE1
0x9F442EB0 2     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           USERSPACE1
0x9F73B2E0 3     DMS  Large   32768  32       Yes  32       2     2         Off 1        0          31           TBS_DATA
0x9FAFE320 4     SMS  SysTmp  32768  32       Yes  32       2     2         On  1        0          31           TBS_TEMP
0x9FB029B0 5     SMS  UsrTmp  32768  32       Yes  32       2     2         On  1        0          31           TBS_USER_TEMP
0x9FB05420 6     DMS  Large   32768  32       Yes  32       2     2         Off 1        0          31           TBS_INDEX
0x9FB0BB20 7     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           TBS_DATA2

Tablespace Statistics:
Address    Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x9F43E060 0     16384      16380      10500      0          5880       10500      0x00000000 0          0          
0x9F43E6B0 1     1          1          1          0          0          0          0x00000000 0          0          
0x9F442EB0 2     8192       8160       96         0          8064       96         0x00000000 0          0          
0x9F73B2E0 3     3200       3168       96         0          3072       96         0x00000000 0          0          
0x9FAFE320 4     1          1          1          0          0          0          0x00000000 0          0          
0x9FB029B0 5     1          1          1          0          0          0          0x00000000 0          0          
0x9FB05420 6     1024       992        96         0          896        96         0x00000000 0          0          
0x9FB0BB20 7     2560       2528       96         0          2432       96         0x00000000 0          0          

Tablespace Autoresize Statistics:
Address    Id    AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x9F43E060 0     Yes Yes 33554432    -1          No  None        None                       No  
0x9F43E6B0 1     Yes No  0           0           No  0           None                       No  
0x9F442EB0 2     Yes Yes 33554432    -1          No  None        None                       No  
0x9F73B2E0 3     No  No  0           0           No  0           None                       No  
0x9FAFE320 4     No  No  0           0           No  0           None                       No  
0x9FB029B0 5     No  No  0           0           No  0           None                       No  
0x9FB05420 6     Yes Yes 33554432    -1          No  None        None                       No  
0x9FB0BB20 7     Yes Yes 10485760    10485760    No  -2147483648 None                       No  

Containers:
Address    TspId ContainNum Type    TotalPgs   UseablePgs StripeSet  Container 
0x989BDE10 0     0          File    16384      16380      0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000000/C0000000.CAT
0x9F43ED00 1     0          Path    1          1          0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000001/C0000000.TMP
0x9F443500 2     0          File    8192       8160       0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000002/C0000000.LRG
0x9F73B930 3     0          File    3200       3168       0          /home/db2inst2/dms/tbs_data01.dbf
0x9F73DE90 4     0          Path    1          1          0          /home/db2inst2/xifenfei/temp
0x9FB05020 5     0          Path    1          1          0          /home/db2inst2/xifenfei/user_temp
0x9FB0B4A0 6     0          File    1024       992        0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000006/C0000000.LRG
0x9FB0C170 7     0          File    2560       2528       0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000007/C0000000.LRG

1.创建新实例

[root@xifenfei ~]# cd /opt/db2/V9.5/instance/
[root@xifenfei instance]# ./db2icrt -h
DBI1001I  Usage:

 db2icrt [-h|-?]
         [-d]
         [-a AuthType]
         [-p PortName]
         [-s InstType]
         -u FencedID InstName

[root@xifenfei instance]# useradd Fence2 
[root@xifenfei instance]# passwd Fence2 
Changing password for user Fence2.
New UNIX password: 
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.
[root@xifenfei instance]# useradd db2inst2
[root@xifenfei instance]# passwd db2inst2
Changing password for user db2inst2.
New UNIX password: 
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.
[root@xifenfei instance]# ./db2icrt -a server -p 55555 -s ese -u Fence2 db2inst2
DBI1070I  Program db2icrt completed successfully.




[root@xifenfei instance]# su - db2inst2
[db2inst2@xifenfei ~]$ db2 get instance

 The current database manager instance is:  db2inst2

[db2inst2@xifenfei ~]$ db2ilist
db2inst1
db2inst2

[db2inst2@xifenfei ~]$ db2greg -dump
S,DB2,9.5.0.0,/opt/db2/V9.5,,,0,0,,1332659153,0
S,DAS,9.5.0.0,/opt/db2/V9.5/das,lib/libdb2dasgcf.so,,,, ,,
I,DAS,9.5.0.0,dasusr1,/home/dasusr1/das,,1,,/opt/db2/V9.5/das,,
I,DB2,9.5.0.0,db2inst1,/home/db2inst1/sqllib,,1,0,/opt/db2/V9.5,,
I,DB2,9.5.0.0,db2inst2,/home/db2inst2/sqllib,,1,0,/opt/db2/V9.5,,

2.启动关闭实例

[db2inst2@xifenfei ~]$ db2stop
03/31/2012 11:57:48     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst2@xifenfei ~]$ db2start
03/31/2012 11:57:52     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

3.删除实例

--先需要关闭实例
[root@xifenfei instance]# ./db2idrop db2inst2
DBI1070I  Program db2idrop completed successfully.