联系:手机/微信(+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