DB2大型数据库之实操数据库、存储组和表空间

大家好，欢迎来到IT知识分享网。

实验目的

新建数据库和数据放置

创建新的DB2数据库
更改一些默认的数据库配置选项
创建新的存储组以支持应用存储
创建一组表空间来支持我们计划创建的数据库对象
使用SQL查询和db2pd命令查看与新数据库关联的选项和磁盘存储

同一个实例下不允许有同名的数据库，不同实例下可以有同名的数据。

创建数据库

首先需要连接实例

Linux系统先连接LInux用户并登录，然后切换到实例所在的用户：

su – db2inst1

输入密码即可

创建sample数据库

db2samp

输出：

DB20000I The CREATE DATABASE command completed successfully.

创建其他数据库，这里是musicdb数据库

db2 create db musicdb

输出：

DB20000I The CREATE DATABASE command completed successfully.

查看当前实例的所有数据库：

db2 list db directory

输出：

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = MUSICDB

Database name = MUSICDB

Local database directory = /home/db2inst1

Database release level = 15.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

Database 2 entry:

Database alias = SAMPLE

Database name = SAMPLE

Local database directory = /home/db2inst1

Database release level = 15.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

可以看到db2inst1实例下有2个数据库，分别是musicdb与sample。

如果是Windows系统则使用命令db2 create database musicdb on c:\创建musicdb数据库。

设置主辅日志空间

设置主日志5个，辅助日志10个

db2 update db cfg using logprimary 5 logsecond 10

输出：

SQL1363W 中的“W”表示警告。要重新连接实例与数据库才生效。

查看配置细节：

db2 get db cfg show detail | more

在这条命令的返回结果里可以看到刚刚设置主日志为5个，辅助日志为10个的命令已生效。

创建应用存储storage

前置条件：

cd /home

sudo mkdir inst24

cd inst24

sudo mkdir path1

sudo mkdir path2

不放在同一个目录里，怕出错

sudo mkdir inst23

cd inst23

sudo mkdir ddl

sudo chmod 777 -R /home/inst23

查看创建目录及文件结果

ls -l /home/inst

输出结果：

drwxrwxrwx 2 root root 4096 May 31 22:11 ddl

其中ddl目录专门用于存放ddl文件，后续用到的文件全在这里面，先上传，再赋权

sudo chmod 777 -R /home/inst23

切换到脚本所在的目录

cd /home/inst23/ddl

修改ddl目录下的/home/inst13/ddl/create_stogroup.ddl里的路径：

准确路径应该为：

create stogroup app_data on ‘/home/inst24/path1′,’/home/inst24/path2’ ;

使用的Linux命令为

sudo vim /home/inst13/ddl/create_stogroup.ddl

按i修改路径，修改完后先按esc键退出编辑模式，再输入:wq保存退出

切换到脚本所在的目录

cd /home/inst23/ddl

连接到数据库：

db2 connect to musicdb

输出：

Database Connection Information

Database server = DB2/LINUXX8664 11.5.4.0

SQL authorization ID = DB2INST1

Local database alias = MUSICDB

执行文件：

db2 -tvf create_stogroup.ddl

输出：

遇到的问题：

使用“db2 -tvf create_stogroup.ddl”报错“DB21005E An error occurred while accessing the file “create_stogroup.ddl”.可能是ddl文件要加目录，且已经存在并有权限。

使用“db2 -tvf create_stogroup.ddl”报错“DB21007E End of file reached while reading the command.”表示create_stogroup.ddl文件末尾缺少分号，需要加上。

列出当前数据库的存储组信息：

db2pd -db musicdb -storage

输出：

Database Member 0 — Database MUSICDB — Active — Up 0 days 00:02:18 — Date 2024-05-31-22.27.19.015691

Storage Group Configuration:

Address SGID Default DataTag Name

0x00007F2AF796A820 0 Yes 0 IBMSTOGROUP

0x00007F2AF796A940 1 No 0 APP_DATA

Storage Group Statistics:

Address SGID State Numpaths NumDropPen

0x00007F2AF796A820 0 0x00000000 1 0

0x00007F2AF796A940 1 0x00000000 2 0

Storage Group Paths:

Address SGID PathID PathState PathName

0x00007F2AF798E000 0 0 InUse /home/db2inst1

0x00007F2AF79B2000 1 1024 InUse /home/inst24/path1

0x00007F2AF79B0000 1 1025 InUse /home/inst24/path2

创建表空间

create_tablespace_tsp01.ddl代码

在APP_DATA存储空间里撞见一个初始值为1M，递增步长为100K，扩展大小为4的TSP01表空间。

create tablespace TSP01 using stogroup APP_DATA initialsize 1M increasesize 100 K extentsize 4 ;

执行文件：

db2 -tvf /home/inst23/ddl/create_tablespace_tsp01.ddl

输出：

create tablespace Tsp01 using stogroup APP DATA initialsize 1M increasesize 108 K extentsizeDB20000I The SQL command completed successfully.

查看表空间信息

db2pd -db musicdb -tablespaces | more

输出

Database Member 0 — Database MUSICDB — Active — Up 0 days 00:08:20 — Date 2024-05-31-22.33.21.

Tablespace Configuration:

Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name

0x00007F2AFB50EF40 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE

0x00007F2AFB51C300 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1

0x00007F2AFB5296C0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1

0x00007F2AFB536A80 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE

0x00007F2AFB543E40 4 DMS Large 4096 4 Yes 8 1 1 Def 2 0 3 Yes TSP01

Tablespace Statistics:

Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState

0x00007F2AFB50EF40 0 32768 32764 31804 0 960 31804 31804 0x00000000 0 0 No n/a

0x00007F2AFB51C300 1 1 1 1 0 0 – – 0x00000000 0 0 No n/a

0x00007F2AFB5296C0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a

0x00007F2AFB536A80 3 8192 8188 152 0 8036 152 152 0x00000000 0 0 No n/a

0x00007F2AFB543E40 4 256 248 20 0 228 20 20 0x00000000 0 0 No n/a

Tablespace Autoresize Statistics:

Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF

0x00007F2AFB50EF40 0 Yes Yes -1 No None None No

0x00007F2AFB51C300 1 Yes No 0 0 No 0 None No

0x00007F2AFB5296C0 2 Yes Yes -1 No None None No

0x00007F2AFB536A80 3 Yes Yes -1 No None None No

0x00007F2AFB543E40 4 Yes Yes No None None No

Tablespace Storage Statistics:

Address Id DataTag Rebalance SGID SourceSGID

0x00007F2AFB50EF40 0 0 No 0 –

0x00007F2AFB51C300 1 0 No 0 –

0x00007F2AFB5296C0 2 -1 No 0 –

0x00007F2AFB536A80 3 -1 No 0 –

0x00007F2AFB543E40 4 -1 No 1 –

Containers:

Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container

0x00007F2AFB4FF800 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT

0x00007F2AFB 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP

0x00007F2AFB5088C0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG

0x00007F2AFB4EE5C0 3 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG

0x00007F2AFB4F85E0 4 0 File 128 124 1024 0 /home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG

0x00007F2AFB4F8810 4 1 File 128 124 1025 0

create_tablespaces.ddl

create tablespace tsp02 managed by database using (file 'tsp02' 128) extentsize 2 autoresize yes maxsize 2 M ; create tablespace tsp03 managed by database using (file 'tsp03' 1024) extentsize 8 autoresize yes maxsize 10 M ; create tablespace tsp04 managed by automatic storage using stogroup app_data initialsize 100 K maxsize none extentsize 2; create tablespace tsp05 using stogroup app_data initialsize 64 K maxsize 1 M extentsize 2; create regular tablespace tsp06 extentsize 4;

执行文件：

db2 -tvf create_tablespaces.ddl

列出所有的表空间

db2pd -db musicdb -tablespaces | more

输出：

                 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

Tablespace ID = 1

Name = TEMPSPACE1

Type = System managed space

Contents = System Temporary data

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 2

Name = USERSPACE1

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 3

Name = SYSTOOLSPACE

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 4

Name = TSP01

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 5

Name = TSP02

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 6

Name = TSP03

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 7

Name = TSP04

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 8

Name = TSP05

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

   Detailed explanation:

   Normal

Tablespace ID = 9

Name = TSP06

Type = Database managed space

Contents = All permanent data. Regular table space.

State = 0x0000

   Detailed explanation:

   Normal

使用DB2命令与SQL语句查询表空间相关信息

select_tablespaces.sql

select substr(tbspace,1,18) as tbspace, substr(definer,1,10) as definer, tbspaceid, tbspacetype, datatype, sgname from syscat.tablespaces;

db2 -tvf select_tablespaces.sql

输出：

select substr(tbspace,1,18) as tbspace, substr(definer,1,10) as definer, tbspaceid, tbspacetype, datatype, sgname from syscat.tablespaces

TBSPACE DEFINER TBSPACEID TBSPACETYPE DATATYPE SGNAME

—————— ———- ———– ———– ——– ——————————————————————————————————————————–

SYSCATSPACE SYSIBM 0 D A IBMSTOGROUP

TEMPSPACE1 SYSIBM 1 S T IBMSTOGROUP

USERSPACE1 SYSIBM 2 D L IBMSTOGROUP

SYSTOOLSPACE DB2INST1 3 D L IBMSTOGROUP

TSP01 DB2INST1 4 D L APP_DATA

TSP02 DB2INST1 5 D L –

TSP03 DB2INST1 6 D L –

TSP04 DB2INST1 7 D L APP_DATA

TSP05 DB2INST1 8 D L APP_DATA

TSP06 DB2INST1 9 D A IBMSTOGROUP

10 record(s) selected.

select_mon_get_cont.sql

select varchar(container_name, 80) as container_name, varchar(tbsp_name, 20) as tbsp_name, pool_read_time from table(mon_get_container('', -2)) as t order by tbsp_id ;

db2 -tvf select_mon_get_cont.sql

输出：

select varchar(container_name, 80) as container_name, varchar(tbsp_name, 20) as tbsp_name, pool_read_time from table(mon_get_container(”, -2)) as t order by tbsp_id

CONTAINER_NAME TBSP_NAME POOL_READ_TIME

——————————————————————————– ——————– ——————–

/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT SYSCATSPACE 943

/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP TEMPSPACE1 0

/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG USERSPACE1 1

/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG SYSTOOLSPACE 24

/home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG TSP01 3

/home/inst24/path2/db2inst1/NODE0000/MUSICDB/T0000004/C0000001.LRG TSP01 1

/home/db2inst1/db2inst1/NODE0000/SQL00004/tsp02 TSP02 5

/home/db2inst1/db2inst1/NODE0000/SQL00004/tsp03 TSP03 3

/home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000007/C0000000.LRG TSP04 3

/home/inst24/path2/db2inst1/NODE0000/MUSICDB/T0000007/C0000001.LRG TSP04 2

/home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000008/C0000000.LRG TSP05 3

/home/inst24/path2/db2inst1/NODE0000/MUSICDB/T0000008/C0000001.LRG TSP05 2

/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000009/C0000000.USR TSP06 7

13 record(s) selected.

dbpaths.sql

select substr(type,1,30) as db_path_type, substr(path,1,50) as path_name from sysibmadm.dbpaths order by 1 ;

使用SQL查询诊断数据路径：

db2 -tvf dbpaths.sql

输出：

select substr(type,1,30) as db_path_type, substr(path,1,50) as path_name from sysibmadm.dbpaths order by 1

DB_PATH_TYPE PATH_NAME

—————————— ————————————————–

DBPATH /home/db2inst1/db2inst1/NODE0000/SQL00004/

DBPATH /home/db2inst1/db2inst1/NODE0000/SQL00004/MEMBER00

DB_STORAGE_PATH /home/inst24/path2/

DB_STORAGE_PATH /home/inst24/path1/

DB_STORAGE_PATH /home/db2inst1/

LOCAL_DB_DIRECTORY /home/db2inst1/db2inst1/NODE0000/sqldbdir/

LOGPATH /home/db2inst1/db2inst1/NODE0000/SQL00004/LOGSTREA

TBSP_CONTAINER /home/db2inst1/db2inst1/NODE0000/SQL00004/tsp03

TBSP_CONTAINER /home/db2inst1/db2inst1/NODE0000/SQL00004/tsp02

9 record(s) selected.

也可以使用指令直接查看musicdb数据库的应用存储

db2pd -db musicdb -storage | more

输出：

Database Member 0 — Database MUSICDB — Active — Up 0 days 00:18:08 — Date 2024-05-31-22.43.09.

Storage Group Configuration:

Address SGID Default DataTag Name

0x00007F2AF796A820 0 Yes 0 IBMSTOGROUP

0x00007F2AF796A940 1 No 0 APP_DATA

Storage Group Statistics:

Address SGID State Numpaths NumDropPen

0x00007F2AF796A820 0 0x00000000 1 0

0x00007F2AF796A940 1 0x00000000 2 0

Storage Group Paths:

Address SGID PathID PathState PathName

0x00007F2AF798E000 0 0 InUse /home/db2inst1

0x00007F2AF79B2000 1 1024 InUse /home/inst24/path1

0x00007F2AF79B0000 1 1025 InUse /home/inst24/path2

查看musicdb的表空间

db2pd -db musicdb -tablespaces | more

输出：

Database Member 0 — Database MUSICDB — Active — Up 0 days 00:18:58 — Date 2024-05-31-22.43.59.

Tablespace Configuration:

Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name

0x00007F2AFB50EF40 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE

0x00007F2AFB51C300 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1

0x00007F2AFB5296C0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1

0x00007F2AFB536A80 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE

0x00007F2AFB543E40 4 DMS Large 4096 4 Yes 8 1 1 Def 2 0 3 Yes TSP01

0x00007F2AFB55F700 5 DMS Large 4096 2 Yes 2 1 1 Def 1 0 1 Yes TSP02

0x00007F2AFB56CAC0 6 DMS Large 4096 8 Yes 8 1 1 Def 1 0 7 Yes TSP03

0x00007F2AFB579E80 7 DMS Large 4096 2 Yes 4 1 1 Def 2 0 1 Yes TSP04

0x00007F2AFB 8 DMS Large 4096 2 Yes 4 1 1 Def 2 0 1 Yes TSP05

0x00007F2AFB 9 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes TSP06

Tablespace Statistics:

Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState

0x00007F2AFB50EF40 0 32768 32764 31804 0 960 31804 31804 0x00000000 0 0 No n/a

0x00007F2AFB51C300 1 1 1 1 0 0 – – 0x00000000 0 0 No n/a

0x00007F2AFB5296C0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a

0x00007F2AFB536A80 3 8192 8188 152 0 8036 152 152 0x00000000 0 0 No n/a

0x00007F2AFB543E40 4 256 248 20 0 228 20 20 0x00000000 0 0 No n/a

0x00007F2AFB55F700 5 128 126 16 0 110 16 16 0x00000000 0 0 No n/a

0x00007F2AFB56CAC0 6 1024 1016 56 0 960 56 56 0x00000000 0 0 No n/a

0x00007F2AFB579E80 7 24 20 14 0 6 14 14 0x00000000 0 0 No n/a

0x00007F2AFB 8 16 12 12 0 0 12 12 0x00000000 0 0 No n/a

0x00007F2AFB 9 8192 8188 36 0 8152 36 36 0x00000000 0 0 No n/a

Tablespace Autoresize Statistics:

Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF

0x00007F2AFB50EF40 0 Yes Yes -1 No None None No

0x00007F2AFB51C300 1 Yes No 0 0 No 0 None No

0x00007F2AFB5296C0 2 Yes Yes -1 No None None No

0x00007F2AFB536A80 3 Yes Yes -1 No None None No

0x00007F2AFB543E40 4 Yes Yes No None None No

0x00007F2AFB55F700 5 No Yes -4096 -1 No None No

0x00007F2AFB56CAC0 6 No Yes -4096 -1 No None No

0x00007F2AFB579E80 7 Yes Yes -1 No None None No

0x00007F2AFB 8 Yes Yes 65536 -1 No None No

0x00007F2AFB 9 Yes Yes -1 No None None No

Tablespace Storage Statistics:

Address Id DataTag Rebalance SGID SourceSGID

0x00007F2AFB50EF40 0 0 No 0 –

0x00007F2AFB51C300 1 0 No 0 –

0x00007F2AFB5296C0 2 -1 No 0 –

0x00007F2AFB536A80 3 -1 No 0 –

0x00007F2AFB543E40 4 -1 No 1 –

0x00007F2AFB55F700 5 0 No – –

0x00007F2AFB56CAC0 6 0 No – –

0x00007F2AFB579E80 7 -1 No 1 –

0x00007F2AFB 8 -1 No 1 –

0x00007F2AFB 9 -1 No 0 –

Containers:

Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container

0x00007F2AFB4FF800 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT

0x00007F2AFB 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP

0x00007F2AFB5088C0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG

0x00007F2AFB4EE5C0 3 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG

0x00007F2AFB4F85E0 4 0 File 128 124 1024 0 /home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG

0x00007F2AFB4F8810 4 1 File 128 124 1025 0 /home/inst24/path2/db2inst1/NODE0000/MUSICDB/T0000004/C0000001.LRG

0x00007F2AFB4F60A0 5 0 File 128 126 – 0 /home/db2inst1/db2inst1/NODE0000/SQL00004/tsp02

0x00007F2AFB4F6700 6 0 File 1024 1016 – 0 /home/db2inst1/db2inst1/NODE0000/SQL00004/tsp03

0x00007F2AFB4F1240 7 0 File 12 10 1024 0 /home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000007/C0000000.LRG

0x00007F2AFB4F1470 7 1 File 12 10 1025 0 /home/inst24/path2/db2inst1/NODE0000/MUSICDB/T0000007/C0000001.LRG

0x00007F2AFB4F2080 8 0 File 8 6 1024 0 /home/inst24/path1/db2inst1/NODE0000/MUSICDB/T0000008/C0000000.LRG

0x00007F2AFB4F22B0 8 1 File 8 6 1025 0 /home/inst24/path2/db2inst1/NODE0000/MUSICDB/T0000008/C0000001.LRG

0x00007F2AFB4F2700 9 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000009/C0000000.USR

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://haidsoft.com/118224.html