联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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