实验一:DB2实验环境设置(2学时)
实验任务:
A.
安装
DB2
数据库系统。
B.
建立拥有系统管理权限(
SYSADM
)的新用户(
inst1
)和组(
adm1
)。
C.
建立新的
DB2
实例(
inst1
)。
D.
增加一些新的系统变量,并且更新
数据库管理器配置文件(
DBM CFG
)
以反映刚刚创建的新的系统管理用户组。
一. 安装DB2数据库系统
将
DB2
数据库系统安装在本地机器上。
二. 建立新用户
1.
在控制面板中双击
“
用户帐户
”
选项,按以下要求创建新用户:
用户名:
inst1
全名:
inst1 Instance SysAdmin
描述:
System Administrator for inst1 Instance
密码:
inst1
用户组:
Administrators
2.
退出用户当前登陆的系统,然后以用户
inst1
的管理员身份登陆到操作系统
。在以后的实验中,当要求登陆到系统中时,除特别说明外都是要求以
inst1
身份登陆。
三. 创建实例
1.
用
db2ilist
命令查看实例。
在
DB2
的
Windows
版本安装完成后
,
系统会自动建立一个默认名为
DB2
的实例。可以打开
DB2
命令窗口并输入
db2ilist
命令,来查看该实例是否存在。
选择开始菜单
—>
程序
—>IBM DB2—>
命令行工具
—>
命令窗口
;
输入
db2ilist
。该命令会
显示
DB2
是该主机的一个实例。
2.
新建
inst1
实例
在命令窗口中输入如下命令,并回车,可完成该实例的创建。
db2icrt inst1
3.
再次输入
db2ilist
命令,将会发现
inst1
实例已成功创建。
4.
输入
exit
退出
DB2
命令窗口。
三. 建立DB2系统管理员用户组adm1
要求:
组名
:
adm1 (adm1
应小写
)
描述
:
inst1 Instance SysAdmin Group
成员
:
db2admin
;
inst1
四. 设置环境
1.
检查当前
DB2
环境变量,并将建立的实例
inst1
设置为系统默认实例。
打开控制面板的“系统属性”窗口
,
选择“高级”标签页
,
然后点击“环境变量”按钮;弹出的环境变量窗口被分为上下两部分
,
上半部分所定义的用户变量只影响当前用户,而系统变量则影响每一个用户
。
查看系统变量中
DB2INSTANCE
所对应的值
,
默认情况下为
DB2
,
现将其改为
inst1
(
双击系统变量列表框中
“DB2INSTANCE DB2”
,
在编辑系统变量窗口的变量值一栏中输入
inst1
,
然后点击“确定”按钮)
。
2.
启动
DB2-inst1
和
DB2DAS - DB2DAS00
服务,并将其启动类型设置为自动。
双击“控制面板”
à
“管理工具”
à
“
服务”窗口。在服务列表中分别右键单击“
DB2 - inst1”
,和“
DB2DAS - DB2DAS00”
,在弹出菜单中选择“属性”,并在“属性”窗口中,将“常规”标签页中的“启动类型”设为自动;然后单击启动按钮,启动相应服务。
(DAS
也可以通过
db2admin start
和
db2admin stop
来启动和关闭
)
。
3.
检查
DB2
环境变量
在
Windows
操作系统的命令提示符窗口中,输入
set | more
命令,检查
DB2
环境变量是否设置正确。假如不对重复上面必要的步骤予以纠正。
4.
检查
DB2
注册表变量
在
DB2
命令窗口输入
db2set -all
命令来检查
DB2
注册表变量。
DB2ADMINSERVER
的值应为
DB2DAS00
,
DB2SYSTEM
的值与主机名相同。
(注:使用带
-all
选项的
db2set
命令可以查看系统中设置的所有配置文件注册表变量。)
5.
设置
DB2COMM
变量
为了对实例
inst1
和全局注册表变量提供
tcp/ip
协议的支持,输入如下命令设置
DB2COMM
变量:
db2set DB2COMM=TCPIP
–
i inst1
db2set DB2COMM=TCPIP -g
注:
要设置当前实例的一个参数时输入格式为
db2set parameter=value
要为特定实例设置参数值时输入格式为
db2set parameter=value
–
i instance_name
要设置一个全局级的注册表变量时输入格式为
db2set parameter=value
–
g
要查看能够被设置的所有配置文件注册表变量,可输入
db2set -lr
6.
再次查看注册表变量。
在
DB2
命令窗口中输入
db2set
–
all
,注意在输出列表中实例级的注册表变量旁边标以“
[i]
”
,全局级的注册表变量旁边标以“
[g]
”
。
7.
重启操作系统,并以用户
inst1
登陆(用户密码按照先前所设都为
inst1
)。
五. 使用DB2控制中心来查看和设置数据库管理器配置参数
选择开始菜单
à
程序
à
IBM DB2
à
一般管理工具
à
控制中心,打开“控制中心”窗口。
1.
查找
inst1
实例
在控制中心窗口中选择主机名左边的“
+
”
号;选择
“
实例
”
左边的
“+”
号,如果
“
实例
”
下没有列出
inst1
,
则右键单击
“
实例
”
,在弹出菜单中选择
“
添加
”
,在
“
添加实例
”
窗口中的
“
远程实例名
”
栏输入
“inst1”
,
点击
“
应用
”
按扭
,
再点击
“
取消
”
按扭
。
2.
查看数据库管理器配置文件。
右键单击控制中心里的“
inst1
”
实例,在弹出菜单中选择“配置参数”,则打开“
DBM
配置”窗口,在该窗口下,可以查看或更改数据库管理器配置文件。
(1)
“
管理
”
下的
AUTHENTICATION
参数。选择该参数所对应的值
,
并点击值右边的省略号按扭
,
在弹出的
“
更改
DBM
配置参数
-AUTHENTICATION”
窗口中列出
5
个值
:
“
服务器
”
、
“
客户机
”
、
“
服务器加密
”
、
“Kerberos”
和
“Kerberos
加密
”
。
其中
“
服务器
”
选项是指验证过程将在服务器上进行
,
这是
DB2
默认的身份鉴别方式
。
点击
“
取消
”
按钮关闭
“
更改
DBM
配置参数
-AUTHENTICATION”
窗口。
(2)
“
管理
”
下的
SYSADM_GROUP
参数。该参数为实例定义具有系统管理
SYSADM
权限的组名
,
值必须是一个由用户管理器创建的组名
。
默认情况下
SYSADM_GROUP
并没有值
,
这意味着
Windows
的管理员用户将会继承
DB2
的
SYSADM
权限
。
要想更改这种默认的设置
,
可以先建立一个用户组
,
该组拥有一些特定成员
,
然后将该组名设置为
SYSADM_GROUP
参数值
,
只有该组的成员才拥有
SYSADM
权限。
(3)
“
环境
”
下的
NODETYPE
参数
。该
参数值为
“
带有本地和远程客户机的数据库分区服务器
”
,
该值由
DB2
设置的
,
且不能被更改。
3.
将
SYSADM_GROUP
参数更新为
adm1
在“
DBM
配置”窗口中选择
SYSADM_GROUP
的值,然后点击该值右边的省略号按钮,在系统管理权限组下输入“
adm1
”
,点击“确定”按钮。然后点击“
DBM
配置”窗口中“确定”按钮,再点击弹出的“
DB2
消息”窗口的“关闭”按钮。
为使新设置的数据库管理器配置参数生效,需要重新启动
inst1
实例。方法:右键单击
inst1
,并在弹出菜单中选择“停止”,点击“确定”按钮,再点击“关闭”按钮。右键单击
inst1
,并在弹出菜单中选择“启动”。如果系统显示错误的消息,请阅读相关的错误信息,并在下一步修正该错误。
4.
更新
TCP/IP
服务名
(SVCENAME
参数
)
,设置它的值为
55000
。
上一步如果收到一个
SQL5043N
错误信息,表示未能成功启动对一个或多个通信协议的支持(尽管核心数据库管理器功能启动成功)。
在本实验第四部分的第
5
步中,曾将
db2comm
参数值设为
tcpip
。当
inst1
实例启动时,
DB2
会试着启动该通信协议,但是却未能找到一个服务端口来使用。因此在数据库管理器配置文件中,需要设置参数
SVCENAME
,来指定一个端口号或者一个服务文件入口名,来为前来访问的
TCP/IP
客户端服务。
在控制中心中右键单击
inst1
,并在弹出菜单中选择“设置通信”,在“设置通信
-inst1”
窗口中令
TCP/IP
复选框选中,并且单击该复选框右边的“属性”按钮,在“配置
TCP/IP
–
inst1”
窗口中的服务名一栏输入
inst1(
或者其他名
)
,再在端口号一栏中输入
55000
,连续单击“确定”按钮关闭这些配置窗口。
5.
重新启动实例。
方法同
4
中所述。
实验二:创建数据库/表空间(2学时)
实验任务:
A.
建立数据库;
B.
建立表空间;
C.
执行脚本文件来建立多个表空间;
D.
访问那些包含表空间信息的
SYSCAT
视图;
E.
列出表空间相关信息;
F.
列出容器相关信息。
一. 创建数据库
1.
查看
create database
指令语法
创建数据库可以通过
DB2
命令
create database
来完成。通过在线帮助工具可以查看
create database
指令语法。
(1)
选择菜单“工具”
à
“
命令中心
”
,或相应的图标来启动“命令中心”;
(2)
在“命令中心”中选择
“
交互式
”
标签页,并且在
“
命令
”
下的文本框中输入
“ ? create database”
;
(3)
选择菜单
“
交互式
—>
执行
”
(或
ctrl+enter
,或点击左上角工具栏的齿轮图标)。
(4)
从
Create database
命令语法可以看出,该命令可以指定:数据库名﹑数据库位置(控制文件﹑设定文件﹑事务日志文件以及默认的表格空间要建立在什么位置)﹑数据库别名﹑国别区码﹑排序集﹑默认数据块大小,该语句也可为编目表空间﹑临时表空间﹑用户表空间指定特定值。
此外,也可以通过
“
创建数据库向导”来创建一个新数据(本实验采用该方法)。
2.
通过“创建数据库向导”创建数据库。
(1)
在“控制中心”中,右键单击
inst1
下的
“
数据库
”
,在弹出菜单中选择
“
创建
—>
使用向导创建数据库
”
;
(2)
在向导的
“
名称
”
页面中,数据库名一栏输入
MUSICDB
,其它值均按默认设置,点击
“
下一步
”
按钮;
(3)
分别选择向导左边的
“
用户表
”
﹑
“
目录表
”
﹑
“
临时表
”
标签页,并点击相应页面的
“
下一步
”
按钮完成相关设置。
“
用户表
”
﹑
“
目录表
”
﹑
“
临时表
”
默认使用的表空间类型为
SMS
,即各页面中的“低维护
-
自动增加(系统管理的表空间)(
L
)”选项。
(4)
在向导的
“
性能
”
页面中,因为
MUSICDB
的表空间均按照系统缺省设置,所以此时不能修改数据块大小或者预取大小。(如果为用户表﹑目录表﹑临时表设置了特定的容器,则可以通过
“
性能
”
页面设置数据块或预取大小)。点击
“
下一步
”
按钮开始数据库
“
区域
”
的设置。
(5)
向导的
“
区域
”
页面可以设置数据库的语言环境和整理顺序,系统已自动设置完成这些选项,直接点击
“
下一步
”
按钮。
(6)
在向导
“
总结
”
页面中,会显示前几步所设的创建数据库的参数。点击
“
显示命令
”
按钮即可查看创建该数据库的全部命令。单击“上一步”则回到前面步骤修改创建参数。点击
“
总结
”
页面的
“
完成
”
按钮,则生成数据库。
等待一小段时间,系统就会弹出
“DB2
消息
”
窗口提示数据库已经创建完毕,并且询问是否启动
“
配置顾问程序
”
来调整数据库。点击
“
否
”
按钮关闭该窗口。这时,所创建的
MUSIC
数据库会出现在控制中心对象树中
“
数据库
”
节点下。
3.
查看数据库信息。
系统数据库目录包含了当前实例下所有数据库的条目信息,可以在“命令中心”输入命令“
list db directory
”
来查看系统数据库目录中
MUSICDB
的信息。
(1)
数据库建立时如果未设置别名,系统会自动为它设置一个与数据库名相同的别名。
MUSICDB
的别名为
MUSICDB
。
(2)
目录条目类型的
“
间接
”
是指该数据库为本地数据库(运行在同一主机),而
“
远程
”
是指数据库运行在另一个远程系统中。本实验中
MUSICDB
的目录条目类型为
“
间接
”
。
4.
查看数据库的连接状态。
(1)
在“命令中心”输入如下命令来检查现在的连接状态:
get connection state
执行该命令后,输出窗口显示连接状态为
“
可连接而未连接
”
。
(2)
输入如下命令连接到
MUSICDB
:
connect to MUSICDB
(3)
再次输入命令
“get connection state”
检查连接状态,输出窗口显示连接状态为
“
可连接并已连接
”
,并列出相应的“
SQL
授权标识”及“本地数据库别名”等信息。
5.
修改数据库配置文件
每一个数据库都有它自己的数据库配置文件,该文件包含了该数据库的相关信息和配置参数。要查看
MUSICDB
数据库配置文件,可在控制中心的对象树中右键单击“
MUSICDB
”
,并在弹出菜单中选择“配置参数”。借助弹出的“数据库配置
—
MUSICDB
”
窗口,可以看查或更改相应的参数值。
(1)
在“命令中心”中输入命令
“update db cfg for MUSICDB using maxlocks 20 num_freqvalues 12”
,该命令将每个应用程序的最大锁列表的百分比设为
20
,并指定当
RUNSTAS
命令上指定了
With Distribution
选项时,将收集的最高频出现的值的数目设为
12
。
(2)
查看
MAXLOCKS
和
NUM_FREQVALUES
的当前值与延迟值是否相同。
connect to MUSICDB
get db cfg for MUSICDB show detail
通过输出窗口可见这两个参数的当前值和延迟值是一致的。
6.
查看表空间及表空间容器等信息
(1)
数据库创建时,三个表空间也同时被创建,在命令中心中输入命令
list tablespaces
,可查看这些表空间的相关信息。结果如下:
SYSCATSPACE——ID 0
TEMPSPACE1——ID 1
USERSPACE1——ID 2
(2)
表空间容器信息可以通过命令
list tablespace containers
获取。
要查看
MUSICDB
中标识为
0
的表空间容器信息,可输入命令:
list tablespace containers for 0
。
该容器类型为路径
/
目录,此目录为
X
:
/inst1/NODE0000/SQL00001/SQLT0000.0
。
(3)
要查看临时表空间(
ID=1
)和默认用户表空间(
ID=2
)的容器信息,可用下列语句实现:
list tablespace containers for 1
list tablespace containers for 2
临时表空间的默认路径为
X
:
/inst1/NODE0000/SQL00001/SQLT0001.0
用户表空间的默认路径为
X
:
/inst1/NODE0000/SQL00001/SQLT0002.0
7.
查看系统目录表和视图
在命令中心中输入命令
list tables for system
列出系统目录表和视图,这些表和视图存储在
SYSCATSPACE
表空间中。
SYSCAT
模式拥有所有建立在系统目录表上的视图,可通过该模式的这些视图来访问系统目录表。例如在命令中心中输入如下命令查看所有模式为“
SYSIBM
”
(即系统目录表)的表名和表标识号:
select tabname
,
tableid from syscat.tables where tabschema='SYSIBM' and type='T'
二. 建立表空间
数据库
MUSICDB
需要额外的几个表空间,本节先通过“控制中心”来创建其中一个表空间。其余表空间通过的脚本命令文件来创建。
1.
通过向导建立表空间
dms01
(1)
在控制中心的左边的对象树上,右键单击数据库
“MUSICDB”
下的
“
表空间
”
,在弹出菜单中选择“创建”;
(2)
在
“
创建表空间向导
”
的第一页中,指定新表空间的名称为
dms01
,并点击
“
下一步
”
;
(3)
在向导的
“
类型
”
页面,选择表空间的类型为常规,并点击
“
下一步
”
;
(4)
在
“
空间管理
”
页面,选择该表空间为“数据库管理表空间”,并在下一步的
“
容器
”
页面中,点击
“
添加
”
按钮,弹出
“
定义容器
”
窗口。
(5)
选择容器大小的计算单位为
4KB
页,并在大小一栏输入
160
,选择驱动器为
X
:,在容器一栏输入
dms/dms01
,然后点击
“
确定
”
按钮。继续点击
“
下一步
”
按钮;
(6)
在向导的
“
读
/
写
”
页面,将数据块大小设置为
4
个
4KB
页面;
(7)
设置完该表空间的所有性质后,单击
“
总结
”
页面的
“
显示
SQL”
按钮查看刚才所作的设定,
SQL
语句应为:
CREATE REGULAR TABLESPACE DMS01 PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE 'X
:
/dms01' 106 ) EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 4 TRANSFERRATE 0.33 BUFFERPOOL "IBMDEFAULTBP" DROPPED TABLE RECOVERY OFF
,点击
“
关闭
”
按钮关闭
“
显示
SQL”
窗口;
(8)
点击向导的完成按钮。
在命令中心中输入命令
list tablespaces show detail
检查新创建的表空间。(应先执行
connect to MUSICDB
)
2.
执行脚本命令文件来创建其它表空间。
(1)
在加载并执行脚本之前,首先要检查命令中心的选项和工具设置,并完成下面设置:
在命令中心中,选择
“
命令中心
—>
选项
”
菜单,选择
“
命令中心选项
”
窗口的
“
执行
”
标签页,确保
“
自动落实
SQL
语句
”
和
“
若发生错误则停止执行
”
两选项都被选中,选择
“
结果
”
标签页,确保
“
详细(将命令文本回送执输出)
”
选项被选中,然后点击
“
确定
”
按钮。
选择
“
工具
—>
工具设置
”
菜单,选择
“
工具设置
”
窗口的
“
一般
”
标签页,确保该页面的
“
使用语句终止符;
”
选项被选中。关闭
“
工具设置
”
窗口。
(2)
通过执行脚本文件
CRTBLSP
创建其余表空间。
在命令中心中,选择
“
脚本
”
标签页,选择菜单
“
脚本
—>
导入
”
;
确保
“
导入
”
窗口中的系统名一栏与
Windows
系统名一致。选择目录和文件列表框找到
CRTBLSP
脚本,单击
“
确定
”
按钮。该脚本将创建
dms02
,
dms03
,
dms04
,
dms05
,
dms06
和
sms01
六个表空间,执行该脚本。
3.
查看表空间信息
。
(1)
在控制中心对象树中右键单击
MUSICDB
下的表空间,并在弹出菜单中选择
“
刷新
”
。在控制中心里并不能查看到表空间的标识号,但可通过在命令中心中输入
list tablespace
看到。
表空间及其关联的标识号如下:
SYSCATSPACE—ID 0
TEMPSPACE1—ID 1
USERSPACE1—ID 2
DMS01—ID 3
DMS02—ID 4
DMS03—ID 5
DMS04—ID 6
DMS05—ID 7
DMS06—ID 8
SMS01—ID 9
在命令中心中输入命令:
list tablespaces show detail
,查看表空间的详细信息。
(2)
SMS
表空间并不使用预先格式好的容器,因此
SMS
的目录容器所在的文件系统中可用空间的变动会直接影响到
SMS
表格空间的容量,而
DMS
表空间则要设置预留空间的大小。
查看标识号为
3
的表空间的容器的详细信息:
list tablespace containers for 3 show detail
容器类型为文件,且该文件为
X
:
/dms/dms01
。
查看标识号为
9
的表空间的容器的详细信息:
list tablespace containers for 9 show detail
容器类型为路径
/
目录,且路径为
x
:
/sms/sms01
和
x
:
/sms/sms02
(3)
表空间的信息也可通过视图
SYSCAT.TABLESPACES
来访问,该视图中包含表空间信息的列有:
TBSPACE-
表空间名
DEFINER-
表空间创建者的用户名
TBSPACEID-
表空间的内部标识号
TBSPACETYPE-
表空间类型,
D
代表
DMS
,
S
代表
SMS
DATATYPE-
表空间可存储的数据的类型。
L
代表只能存储大对象数据对象,
A
代表所有永久对象,
T
代表只能存储临时表。
在命令中心中输入下列
SQL
语句,可实现上述信息的查询:
select tbspace, definer, tbspaceid, tbspacetype, datatype from syscat.tablespaces
(4)
特定的表所相关的表空间信息可以通过
SYSCAT.TABLES
视图来访问,该视图中包含表空间信息的列有:
TBSPACEID-
该表所在的主表空间内部标识号;
TBSPACE-
该表所在的主表空间名称;
INDEX_TBSPACE-
包含该表索引的表空间;
LONG_TBSPACE-
包含该表大对象数据的表空间。
为列出表
SYSIBM.SYSTABLES
所在表空间的信息,可在命令中心输入如下
SQL
语句:
select tabname, tbspaceid, tbspace, index_tbspace, long_tbspace
from syscat.tables
where tabname=’SYSTABLES’
from syscat.tables
where tabname=’SYSTABLES’
在命令中心中输入命令
connect reset
断开与
MUSICDB
的连接。
实验三:创建数据库对象(2学时)
实验任务:
A.
建立表;
B.
建立索引;
C.
建立视图;
D.
建立别名;
E.
对其中一表添加参照完整性约束;
F.
对其中一表添加检查约束;
G.
对其中一表添加触发器;
H.
访问这些对象相关的系统编目信息。
一. 创建表
1.
利用向导创建表
artists
。
(1)
在控制中心刷新表空间,并确保在开始创建表之前,能看到所有的表空间。
(2)
在控制中心的
MUSICDB
数据库对象列表中,右键单击“表”,并在弹出菜单中选择“创建”,则弹出“创建表向导”对话框。
(3)
在“创建表向导”中,在“表名”处输入
artists
,然后单击“下一步”定义列。
ü
在列定义窗口中,点击“添加”按钮
,
在“添加列”对话框中,输入列名
artno
,在数据类型中,选择
SMALLINT
,去掉“可空”前面的选择
(
该列不能为空
)
,点击“应用”按钮,继续定义其它列。
ü
定义列
name
,选择数据类型为
VARCHAR
,长度为
50
,允许为空;
ü
定义
classification
列,数据类型为
CHARACTER
,长度为
1,
不允许为空;
ü
定义
bio
列,数据类型
CLOB
,
LOB
单位选择
KBytes,
长度为
100
。
LOB
选项的“记录”和“压缩”均选上,并选择“可空”;
ü
定义
picture
列。数据类型为
BLOB,LOB
单位为
Kbytes,
长度为
500
。选择
LOB
选项的“压缩”选项,选择“可空”。
(4)
在继续下面操作前,要确保上述定义的列具有下列的性质。如果没有,则用“更改”按钮进行更改(此处定义错误,可能会使后续结果不正确)。
Column name Data type Length Nullable LOB option
----------- --------- ---------- -------- ----------
artno smallint No
name varchar 50 Yes
classification character 1 No
bio clob 100KBytes Yes Logged,Compact
picture blob 500 KBytes Yes Compact
(5)
按“下一步”进入表空间窗体,为
artists
表定义表空间。
在表空间处,选择
DMS01
表空间;选择“使用单独的索引空间”,并选择
DMS02
表空间;选择“使用独立的长空间”并选择
DMS03
表空间。然后单击“下一步”,在新表上定义键。
(6)
按“添加主键”,在可用列中,选择
ARTNO
列,并按下
>
按钮,将
ARTNO
加入到“选择的列”中(只有非空列才被选择,所以,主键必须被定义为非空)。
(7)
在“总结”窗口,按下“显示
SQL
”,则会出现如下的
SQL
语句。其中,约束名会有所不同。
CREATE TABLE INST1.ARTISTS ( ARTNO SMALLINT NOT NULL , NAME VARCHAR (50) , CLASSIFICATION CHARACTER (1) NOT NULL , BIO CLOB (100 K ) LOGGED COMPACT ,
PICTURE BLOB (500 K ) NOT LOGGED COMPACT ,
CONSTRAINT CC1044760857306 PRIMARY KEY ( ARTNO) ) IN "DMS01" INDEX IN
"DMS02" LONG IN "DMS03" ;
(8)
按“完成”创建该表。
2.
利用
Create Table
语句创建表
albums
。
在“命令中心”中输入下列语句,创建数据表
stock
(需要先连接到数据库,即
connect to musicdb
)。
create table albums
(title varchar (50),
artno smallint not null,
itemno smallint not null)
in dms04
index in dms05;
在控制中心左边的对象树中右键单击
MUSICDB
下的“表”
,在弹出菜单中选择“刷新”,再在右边的内容面板中找到
artists
表
和
albums
表
,查看这两个表。如果表不符合要求,则将其删除,然后再返回上面步骤重新建立。
3.
导入脚本文件创建其余表。
在“命令中心”的脚本模式下导入
CRTABLES
的脚本文件创建其余表,在执行它之前,先查看脚本内容,注意表
STOCK
、
CONCERTS
、
REORDER
将分别把表数据和索引放在哪些表空间。
按
Ctrl+Enter
执行脚本文件。
4.
查看所建表的信息。
在控制中心左边的对象树中右键单击
MUSICDB
下的“表”
,在弹出菜单中选择“刷新”,然后再在右边内容面板中查看脚本所创建的表,确保这些表都建立得正确。
要查看表的详细信息,可以在内容面板中右键单击所要查看的表,在弹出菜单中选择“改变”。使用此方法查看
albums
表。
5.
通过在命令中心中导入执行
GRANTS
脚本,将
inst1
模式下所有表的
select
权限授予
public
,并在脚本执行完毕后检查权限授予是否成功。
注意查看该脚本的具体内容,以及相应的
SQL
语句的写法。
6.
查看模式
inst1
所有表的列信息(选做)。
(1)
表的列信息可以通过
SYSCAT.COLUMNS
视图查询到。在
DB2
命令行处理器输入如下指令执行
X
:
/cf23
下的
tabchk.sql
脚本,并将输出结果重定向到
tabchk.file
中。
打开
“
开始菜单
—>
程序
—>IBM DB2—>
命令行工具
—>
命令窗口
”
,输入:
X
:
Cd /cf23
(数据文件所在目录)
db2 connect to musicdb
db2 –tf tabchk.sql > tabchk.file
more tabchk.file
或者输入
db2 select tabname
,
colname
,
typename from syscat.columns where tabschema = 'inst1' order by 1, 2 > tabchk.file
(2)
检查输出文件
tabchk.file
与
tabchk.master
内容上的差别(须确保两文件的内容没有任何差别)。首先在命令提示符下输入指令:
fc /w tabchk.file tabchk.master | more
如果输出消息显示
“FC
:找不到相异处
”
,
tabchk.file
与
tabchk.master
相一致。如果输出显示两个文件某些行不相同,则应仔细检查相应错误,删除建立不当的表,并返回到上步重新建立。
7.
查看模式
inst1
表空间的信息(选做)。
(1)
某个表所相关的表空间信息可以通过
SYSCAT.TABLES
视图来获得。输入一条
SQL
语句来从
SYSCAT.TABLES
获取模式
inst1
下所有表的表名(
TABNAME
)、存储该表数据的表空间(
TBSPACE
)、存储该表索引的表空间(
INDEX_TBSPACE
)信息,并将结果集按表名排序。完整的
SQL
语句为:
select tabname
,
tbspace
,
index_tbspace from syscat.tables where tabschema= 'inst1' order by tabname
X
:
/cf23
下的脚本文件
tbschk.sql
包含该
SQL
语句,在
DB2
命令窗口中进入
x
:
/cf23
目录下,输入:
db2 –tf tbschk.sql > tbschk.file
more tbschk.file
(2)
检查输出文件
tbschk.file
与
tbschk.master
内容上的差别。
fc /w tbschk.file tbschk.master | more
如果输出消息显示
“FC
:找不到相异处
”
,
tbschk.file
与
tbschk.master
相一致。如果输出显示两个文件某些行不相同,则应仔细检查相应错误,删除建立不当的表,并返回到上步重新建立。
二. 创建索引
1.
在
stock
表的
itemno
列上建立名为
item
的索引。
控制中心的对象列表中右键单击
MUSICDB
下的“索引”,并在弹出菜单中选择“创建”,在“创建索引”窗口中按下列要求填入相关信息。其中,
索引模式为
inst1
;索引名为
item
;表模式为
inst1
;表名为
stock
。
在“可用的列”中选择
itemno
并单击“
>
”
按钮将该列加入到“选择的列”中,然后点击“确定”按钮。
2.
为
albums
表的
itemno
列建立唯一索引。其中,索引模式为
inst1
;索引名为
itemno
;表模式为
inst1
;表名为
albums
;
在“可用的列”中选择
itemno
并单击“
>
”
按钮将该列加入到“选择的列”中,并且选中“唯一”复选框,然后点击“确定”按钮。
有关索引的信息可以通过
SYSCAT.INDEXES
视图获取。
三. 建立视图
1.
在控制中心中建立一名为
music
的视图。
控制中心的对象列表中右键单击
MUSICDB
下的“视图”,在弹出菜单中选择“创建”;
选择视图的模式为
inst1
;
视图名一栏中输入
music
;
点击
SQL
语句框右边的“清除”按钮,并输入:
as select title, classification, name from inst1.albums, inst1.artists where inst1.artists.artno= inst1.albums.artno
;
点击“确定”按钮。
2.
利用
Create View
语句创建视图
inventory
。
在“命令中心”中,输入下列语句,建立视图
inventory
。
connect to musicdb;
create view inventory (type, itemno, totcost, totqty)
as select type, itemno, sum (price * qty), sum(qty)
from stock group by type, itemno;
3.
查看视图信息
(1)
在控制中心的对象树中右键单击
MUSICDB
下的
“
视图
”
,并在弹出菜单中选择
“
刷新
”
,然后可见在右边的内容面板中列出了新建立的视图。
(2)
视图的相关信息可以通过
SYSCAT.VIEWS
和
SYSCAT.TABLES
来获取。
List tables
同样可以列出数据库中的视图(类型为
V
)。
在“命令中心”中输入如下命令,查看新建立的视图是否列出:
connect to MUSICDB
;
list tables for user
;
或者
connect to MUSICDB
;
select viewschema, viewname from syscat.views where definer=user
;
四. 建立别名
1.
在控制中心中为
artists
表建立别名
singers
,为
reorder
表建立别名
emptystock
。
2.
别名的相关信息可以通过视图
SYSCAT.TABLES
来获取,此外
list tables
命令也能获取当前连接的用户下的别名列表。
输入如下命令列出别名列表:
connect to MUSICDB
;
list tables for user
;
或者
select tabname, type from syscat.tables where tabschema=user
;
五. 添加参照完整性约束条件
本部分实验在表
artists
与
albums
之间以及
albums
与
stock
之间添加参照完整性约束。
1.
修改
albums
表,并且为它定义与表
artists
的参照完整性约束。
(1)
在该表
itemno
列上建立主键。
在控制中心中右键点击
albums
表并在弹出菜单中选择“改变”,再在“改变表—
albums
”
窗口中
选择
“
键
”
标签页,点击
“
添加主键
”
按钮,在
“
定义主键
”
窗口
“
可用的列
”
中选择
itemno
,然后点击
“>”
按钮,并点击
“
确定
”
按钮。
(2)
为该表定以外键。
点击
“
添加外键
”
按钮,在
“
添加外键
”
窗口中,表模式一栏选择
inst1
,表名一栏选择
artists
,注意这时主键框内显示该表主键为
artno
,再选择
“
可用的
”
列中
artno
,点击
“>”
按钮使该列成为外键,选择
“
删除时
”
一栏为
CASCADE
,约束名为
fkartno
,点击
“
确定
”
按钮。
2.
在命令中心脚本模式下执行
CRRI
脚本,该脚本修改
STOCK
表并为其定义参照完整性约束。(在运行脚本前,请仔细查看相关
SQL
语句的书写方法。)
3.
通过
SYSCAT.REFERENCES
视图查看参照完整性约束的相关信息(选做)。
在命令中心中输入如下
SQL
语句获取
inst1
模式下所有参照完整性约束的名称、所在表名、参照表的模式、参照表名、删除规则、外键所在列名、主键所在列名:
select constname, tabname, reftabschema, reftabname, deleterule, fk_colnames, pk_colnames
from syscat.references
where tabschema='inst1'
from syscat.references
where tabschema='inst1'
4.
通过
SYSCAT.TABLES
视图查看参照完整性约束的相关信息(选做)。
在命令中心中输入如下
SQL
语句,其中
CHILDREN
为参照该表的所有表的个数,
PARENTS
为该表所要参照的表的个数:
select tabname
,
parents
,
children
from syscat.tables
from syscat.tables
where tabschema='inst1' and (parents>0 or children>0) order by 2
,
3 desc
5.
参照完整性约束定义结果检查(选做)
在
DB2
命令窗口中执行脚本
richk.sql
,通过
SYSCAT.TABLES
视图获得当前连接的用户模式参照完整性约束的相关信息,
richk.sql
的内容为:
select substr(tabname, 1, 18), parents, children
from syscat.tables
where tabschema = user order by tabname
db2
–
tf richk.sql > richk.file
more richk.file
然后检查该输出文件的内容与
richk.master
有无差别:
fc /w richk.file richk.master | more
如果输出显示两文件内容存在差异,则应仔细检查相关信息后重新修改表。
六. 为stock表添加检查约束
1.
修改
stock
表并为该表添加检查约束
cctype
。
该约束确保
stock
表
type
字段的取值必须是字符
'D'
、
'C'
、
'R'
之一。
在控制中心中右键单击
stock
表,在弹出菜单中选择“改变”,在“改变表—
stock
”
窗口中选择“检查约束”标签页,点击“添加”按钮,再在检查条件框中输入:
type in ('D', 'C', 'R')
约束名一栏输入
cctype
,点击“确定”按钮关闭“添加检查约束”窗口,再点击“确定”按钮关闭“改变表—
stock
”
窗口。
2.
检查约束的相关信息可通过
SYSCAT.CHECKS
、
SYSCAT.COLCHECKS
、
SYSCAT.TABCONST
和
SYSCAT.TABLES
这些视图获取。
在命令中心中输入:
select constname, tabname, colname from syscat.colchecks
3.
在命令中心中输入如下
SQL
语句,列出的约束中,
TYPE
为
K
表示是一个检查约束,
TYPE
为
P
表示是一主键,
TYPE
为
F
表示是一外键
select constname, tabname, type from syscat.tabconst
4.
DB2
命令窗口中输入如下命令执行脚本
ckchk.sql
,并将输出结果重定向到
ckchk.file
:
Db2 –tf ckchk.sql > ckchk.file
More ckchk.file
然后检查该输出文件的内容与
ckchk.master
有无差别:
fc /w ckchk.file ckchk.master | more
七. 为 reorder表创建触发器,当stock表某一存货少于6时,reorder表中将会插入一条新记录(选做)
1.
将要建立的触发器应具备下列性质:
ü
触发器名为
reorder
;
ü
触发时机为更新
stock
表某些记录的
qty
字段值后该字段值小于或等于
5
;
ü
新记录变量
new
通过
n
来引用;
ü
该触发器将新记录变量的
itemno
值和当前时间截插入到
reorder
表;
ü
应为
for each row mode db2sql
触发器。
右键单击控制中心
MUSICDB
下的“触发器”,在弹出菜单中选择“创建”,在创建触发器窗口中,输入触发器模式选为
inst1
,表或视图模式选为
inst1
,触发器名为
reorder
,表或视图名称选为
stock
,触发操作的时间选为“之后”,导致执行触发器的操作设为“更新列”并选择
qty
列,然后选择“触发操作”标签页,在“新行的相关名”中输入
n
,在触发操作框内填入如下
SQL
语句:
when (n.qty<=5)
insert into reorder values (n.itemno, current timestamp)
点击“显示
SQL
”
按钮查看完整的
SQL
语句。
点击“确定”按钮,然后在控制中心查看该触发器是否列出。
2.
触发器的相关信息可以通过
SYSCAT.TRIGGERS
和
SYSCAT.TRIGDEP
获取。
在命令中心中输入如下
SQL
语句:
select trigname
,
tabname
,
trigevent from syscat.triggers
其中
trigevent
为
I
表示触发操作为
insert
,
D
表示触发操作为
delete
,
U
表示触发操作为
update
。
在命令中心中输入下列
SQL
语句:
select trigname, btype, bschema, bname from syscat.trigdep
实验四:移动与操纵数据(4学时)
实验任务:
A.
学习
Insert
、
Select
和
Update
等
SQL
语句的应用;
B.
使用
import
工具从一文件读取数据写入表格;
C.
使用
load
工具从一文件读取数据快速写入表格;
D.
建立异常表;
E.
管理检查约束,触发器以及检查暂挂状态。
一. 数据插入、删除
1.
利用
SQL
语句插入数据,并查看结果。
(1)
将下列数据插入到表
albums
中(一次插入一行)
TITLE ARTNO ITEMNO
Greatest Hits 1 1
Voice in the Wind 2 5
在“控制中心”,右键单击表albums,选择“内容采样”,查看表中结果。
(2)
将下列数据插入到表
artists
中
ARTNO NAME CLASSIFICATION BIO PICTURE
99 Double Dare R
Patti & Cartwheels S
1 Alabama C
2 Bogguss, Suzy S
3 Black, Clint C
Brooks, Garth C
5 Chapin-Carpenter, Mary R
6 Gill, Vince C
7 Jackson, Alan C
8 Judds C
在“控制中心”,右键单击表artists,选择“内容采样”,查看表中结果。
(3)
重新进行⑴的插入操作,并查看
albums
表中的插入结果。
(4)
将下列数据一次插入到表
albums
中
TITLE ARTNO ITEMNO
American Pride 1
Something Up My Sleeve 2 4
Put Yourself in My Shoes 5 7
在“控制中心”,右键单击表albums,选择“内容采样”,查看表中结果。
2.
删除数据
(1)
删除表
artists
中
ARTNO
为
99
的记录信息。
(2)
将表
albums
中
TITLE
为
Voice in the Wind
的记录删除。
(3)
将
CLASSIFICATION
为
R
的演员的唱片集从
albums
表中删除。
二. 使用import工具导入数据到artists表,稍后执行脚本文件导入数据到albums和stock表。首先用用户名inst1登陆。
1.
使用
import
工具导入数据到
artists
表。
(1)
导入数据
在控制中心中右键单击
artists
表,在弹出菜单中选择“导入”。在“导入表—
artists
”
窗口中,按要求输入下列信息:
ü
“
导入文件”一栏输入
X
:
/....../artists.exp
;
ü
“
导入文件类型”设为集成交换格式(
IXF
);
ü
“
导入方式”设为
INSERT
;
ü
“
消息文件”一栏输入
X
:
/cf23/art.msg
,单击“确定”按钮。
(2)
Windows
命令提示符窗口中输入
more < art.msg
,查看消息文件中的警告或错误信息,并注意有多少行数据成功插入表中。
2.
在“命令中心”的脚本模式下分别运行脚本
imp_albu
和
imp_sto
,该脚本将数据导入到
albums
表和
stock
表
中。脚本执行完毕后查看消息文件
albums.msg
和
sto.msg
是否有任何附加信息,并注意分别有多少行数据成功插入表中。
三. 创建异常表—artists,albums,stock
所有的异常表将存储在
SMSEXP
表空间,注意异常表的定义与原表非常相似,前
N
列的列名和数据类型与原表精确匹配。第
N+1
列为可选列,数据类型为
timestamp
。第
N+2
列也为可选列,且只有在第
N+1
列存在时才能被创建,该列必须定义成
32KB
或更大的
CLOB
类型,用来存放导致该行被拒的特定约束信息。
在命令中心脚本模式下导入并执行脚本
crexptab
,该脚本分别为
artists
、
albums
、
stock
创建异常表
artexp
、
albexp
、
stoexp
。
四. 备份数据库
后面的实验将使用
Load Replace
命令装入数据,要使该操作可恢复,需要先将现有的数据进行备份。数据库备份与恢复的细节将会在以后的实验有所涉及。
在
db2
命令窗口中输入如下命令实现数据库备份操作:
X
:
Cd /
Md X:/backup
Db2 force application all
Db2 backup db MUSICDB to X:/backup
五. 使用load插入模式载入数据到concerts表
右键单击
concerts
表,在弹出菜单中选择“装入(
L
)
”
;在“类型”页,选择“将数据附加到表中”,在“文件”页,输入下列信息:
ü
要载入文件为
concerts.exp
;
ü
文件格式为
ixf
格式;
ü
载入数据使用插入模式;
ü
载入过程中所有消息重定向到
concerts.msg
文件当中。
ü
在“调度任务执行”中,选择“立即执行而不保存历史任务”
在“总结”页面,查看相应的
SQL
语句,并点击“完成”。
查看消息文件
concerts.msg
,注意有多少行成功载入并插入到表中:
more < concerts.msg
六. 使用load工具的替换模式载入数据到表artists中
在命令中心运行脚本
load_art
,将数据装入到
artists
表中。
ü
本次载入使用了与前次
import
不同的
IXF
输入文件,且使用了替换选项将先前导入的行替换掉。,在带有异常选项的载入过程中,所有违反唯一限制(主键或唯一索引)的数据行将会放入
artists
的异常表
artexp
中。
ü
查看
artexp
表中是否存有那些违反
artists
上唯一键索引的数据行,在命令中新脚本模式下导入并执行
selexp
。
ü
查看消息文件
X
:
/cf23/load_art.msg
内容:
more < load_art.msg
七. 使用SET INTEGRITY命令管理检查暂挂状态
1.
用
Set Integrity
管理检查暂挂状态
(1)
在命令中心导入并执行脚本
seltab
,该脚本将对表
artists
、
stock
、
albums
执行查询操作,查看执行结果。
(2)
上述脚本执行后返回
SQL0668
原因代码
1
,命令中心中输入:
? SQL0668
因为这些表处于检查暂挂状态,所有对这些表的数据操作都不能执行,输出信息同时提示用户执行
SET INTEGRITY
语句以消除表的检查暂挂状态。
(3)
在命令中心中执行脚本
listtbst
查看这些表上检查约束的状态。
Artists
表处于检查暂挂状态。
(4)
SET INTEGRITY
语句可被用来检查数据是否违反参照完整性约束,处于检查暂挂状态的表(
artists
表)应被指定一个与它对应的异常表。在命令中心中导入并执行
setcsts_arts
脚本来检查
artists
表中参照完整性约束。
(5)
警告
“sql3601”
。在命令中心中输入
? Sql3601
,由帮助信息可知
set integrity
语句导致一个或多个表处于检查暂挂状态。
(6)
命令中心中导入并执行
listtbst
脚本,执行后可见
albums
和
stock
表处于检查暂挂状态。
(7)
命令中心中输入并执行脚本
setcsts_2
,为
albums
和
stock
表设置完整性,检查它们上的参照完整性,并指定它们的异常表。
(8)
对
artists
、
stock
、
albums
表执行查询操作以确认这些表已消除检查暂挂状态,在命令中心中导入并执行脚本
listtbst
和
seltab
,执行结果表明检查暂挂状态都以消除。
2.
将异常表中的数据放回原来表中
(1)
为解除检查暂挂状态,那些违反约束的行被移动到异常表中。在命令中心中导入并执行脚本
selexp
察看异常表中的数据。
(2)
从
stock
移动到
Stoexp
的数据以及从
albums
移动到
albexp
的数据,这些数据的外键值都无法与
artists
表中的主键相匹配。执行下列
SQL
语句在
artists
表中插入一行数据:
insert into artists (artno, name, classification) values (100, 'Patti & Cart Wheels', 'S')
(3)
现在要将
stoexp
和
albexp
表中的行分别插入到表
stock
和
albums
中,首先执行脚本
selexp
查看两异常表中数据,检查两异常表的
MSG
列,注意并不需要将
N+1
和
N+2
列数据插入到
stock
和
albums
表。
(4)
在命令中心中导入并执行脚本
insexp
将
stoexp
和
albexp
中的数据插入到表
stock
和
albums
,
insexp
脚本也会对
stock
和
albums
执行查询操作以检查异常表中数据是否成功插入到原表。
八. 理解检查约束的强制执行
上节实验中为
stock
表的
TYPE
列添加的检查约束
cctype
,要查看该约束的内容,可以在“命令中心”中右键单击表
stock
并在弹出菜单中选择
“
改变
”
,再在
“
改变表
—stock”
窗口中选择
“
检查约束
”
标签页,选择
cctype
约束然后单击
“
更改
”
按钮,查看该约束的定义。
在命令中心中输入并执行如下
SQL
语句:
insert into stock values (302
,
'V'
,
100.00
,
20)
输出结果返回
SQL0545
,该错误代码表明因为插入的数据不满足检查约束
inst1.stock.cctype
,所以要请求的操作不被允许。
在命令中心中输入并执行如下
SQL
语句:
insert into stock values (302
,
'C'
,
100.00
,
20)
因为
'C'
满足检查约束
cctype
,所以该
SQL
语句成功执行。
九. 理解触发器的强制执行
回顾上节实验中在表
stock
的
QTY
列上创建的触发器
reorder
。选择命令中心的
“
脚本
”
标签页,然后输入并执行下列
SQL
语句:
select substr(text
,
1
,
200) from syscat.triggers where tabname='stock'
在命令中心中输入并执行下列
SQL
语句来修改
stock
表中
itemno
等于
302
的元组的
qty
字段值:
update stock set qty=3 where item=302
注意执行该语句后没有消息显示触发器是否被触发。
查看
reorder
表,检查在更新
stock
表
qty
列后触发器
reorder
是否被触发。在“命令中心”中输入:
terminate
connect to musicdb
select * from reorder
上节实验为表
reorder
建立了别名
emptystock
,在命令中心中输入并执行下列
SQL
语句:
select * from emptystock
输出结果表明对别名
emptystock
执行的查询操作会直接转化成对
reorder
表的操作。
实验五:数据查询(2学时)
实验任务:
A.
简单查询和连接查询;
B.
子查询;
C.
列函数和标量函数应用;
D.
分组;
E.
排序。
一. 利用SQL语句实现数据查询
打开“命令中心”,在“交互式”页面中,输入
Connect to MUSICDB
,连接到数据库。然后针对下列内容,写出相应的
SQL
查询语句。
各
SQL
语句,包括
Select
、
Insert
、
Update
和
Delete
等,也可以通过“命令中心”中的“
SQL
助手”完成。
1.
从
artists
表中,查询
CLASSIFICTION
为
C
或
B
的演员的信息。
2.
查询名(
NAME
)为
Beatles
的演员的唱片集的名称,并将结果按唱片集名称的升序排列。
3.
查询所有音乐会的信息,包括时间、地点以及演员的名称。
4.
查询
CLASSIFICTION
为
C
的演员所举办的音乐会的信息,包括时间、地点。
5.
查询在
Paris
举办的音乐会的演员名字,及这些演员所出版的唱片集名称。
6.
查询
CLASSIFICTION
为
B
的演员的唱片集的名称、库存数量,并按库存数量的升序排列结果。
7.
查询所有
albums
表中,有唱片集的演员的数量。
8.
按唱片类型(
TYPE
)分别统计唱片的数量、平均价格、最高价格及最低价格。
9.
按唱片类型(
TYPE
)分别统计唱片最低价格不小于
10.00
元的唱片的数量。
实验六:数据库恢复(2学时)
实验任务:
A.
为数据库和表空间创建备份镜像;
B.
从备份镜像恢复数据库;
C.
对数据库或表空间进行完全的前滚恢复;
D.
在异常状态下重新激活数据库。
一. 决定DB2目前的日志配置参数
1.
本实验开始之前应先在命令中心中执行脚本
recovrst
,将数据库的配置参数设置成本实验所需的值。
2.
在命令中心中输入并执行
connect to musicdb
,激活
MUSICDB
数据库。
3.
控制中心中右键单击
MUSICDB
数据库,在弹出菜单中选择“配置参数”,再在“数据库配置—
MUSICDB
”
窗口中查看“日志”下的
LOGFILSIZ
的值,该参数的值被设置为
6
,远小于
Windows
平台下它的默认值
250
。
4.
查看主日志文件的数目,
DB2
将于什么时候分配这些主日志文件?
在
“
数据库配置
—MUSICDB”
窗口中的
“
日志
”
下可查看到
LOGPRIMARY
参数值为
3
,当我们发出
ACTIVE DATABASE
命令或者第一个应用程序连接到数据库时,
DB2
将会分配这三个主日志文件,且其中每个日志文件的大小为
LOGFILSIZ+2
个
4KB
页,因此所有主日志文件将占据
3*(6+2)*4KB
,即
96KB
空间。
5.
查看辅助日志文件的数目,
DB2
将于何时分配这些辅助日志文件?
辅助日志文件会在事务未提交但主日志文件写满的时候根据需要一次分配一个(直到数据库配置参数
LOGSECOND
规定的数目),本实验中
LOGSECOND
为默认值
2
,因此系统最多允许两个辅助日志文件。辅助日志文件的大小同样由参数
LOGFILSIZ
指定。
6.
查看当前系统所使用的日志类型(循环日志还是归档日志),哪些参数可以提供日志类型的相关信息。
LOGRETAIN
和
USEREXIT
可被用来设置系统采用何种日志类型,本实验中这两个参数均被设为默认值否,因此系统采用循环日志方式。如果
LOGRETAIN
被设为
Recovery
或者
USEREXIT
被设为
Yes
,那么系统将采用归档日志方式。
7.
考虑循环日志能够支持崩溃恢复、版本恢复、前滚恢复中的哪些类型?
循环日志只能支持崩溃恢复和版本恢复,但还不能支持前滚恢复!
8.
考虑日志文件保存在哪里?
配置参数中的
LOGPATH
指定日志文件的保存路径,因此本实验的日志文件保存在
X
:
/inst1/NODE0000/SQL00001/SQLOGIR
下。
9.
数据库的工作单元可能被意外地中断,假如部分工作单元的所有变更完成和提交之前发生了电源故障,那么系统就需要执行崩溃恢复再次回到一致状态。命令
restart database
可以实现这个功能。如果
AUTORESTART
被设置成开(默认方式),那么崩溃发生后,应用程序首次连接到数据库时将会自动启动崩溃恢复机制。本实验
AUTORESTART
参数被设置为开。
10.
Windows
命令提示符下,进入目录
X
:
/inst1/NODE0000/SQL00001
。
11.
输入命令:
dir /w
,查看该路径下有哪些文件和目录。
12.
输入命令
cd SQLOGDIR
和
dir /w
可以查看到该目录下有三个日志文件,这与参数设置相符。
13.
选择命令中心的“交互式”标签页,再选择“命令中心—
>
选项”菜单,在“命令中心选项”窗口中选择“执行”标签页,确保“自动落实
SQL
语句”未被选中。点击“确定”按钮。
执行
connect to musicdb
执行
update stock set qty=qty+1
14.
如果上步执行后系统显示错误则跳到下一步,如果没有显示错误信息,则继续执行:
update stock set qty=qty-1
,这时系统会返回
SQL0964C
。
15.
为查看详细的错误信息,输入并执行
? SQL0964
。回答为什么更新操作会失败。
16.
选择命令中心的“交互式”标签页,再选择“命令中心—
>
选项”菜单,在“命令中心选项”窗口中选择“执行”标签页,选中“自动落实
SQL
语句”,点击“确定”按钮。
17.
本实验中日志文件故意被设置得很小,所以上面的更新操作未能成功完成,在命令中心中输入并执行如下命令:
rollback
18.
进入日志保存目录,查看该目录下的文件有几个:
cd X
:
/inst1/NODE0000/SQL00001/SQLOGIR
dir /w
可查看到该目录下现在多了两个文件,当采用循环日志且主日志文件已满时,
DB2
就会分配这两个多出来的文件作为辅助日志文件。
输入
cd /
,进入根目录下。
二. 循环日志条件下的数据库恢复
1.
在
Windows
命令提示符下输入
md /restore
在
X
驱动器下建立一个目录用来保存数据库备份。
2.
试图对
MUSICDB
数据库进行联机备份。
在控制中心中右键单击
MUSICDB
数据库,在弹出菜单中选择
“
备份
”
。
在
“
备份向导
”
窗口中,选择
“
映象
”
标签页,选择
“
介质类型
”
为文件系统,点击
“
添加
”
按钮,路径一栏输入
x
:
/restore
点击向导的
“
选项
”
标签页,这时注意
“
可用性
”
下的
“
联机
”
选项为灰色不可选,只能进行脱机备份。
3.
对
MUSICDB
数据库进行脱机备份,
在上步的
“
备份向导
”
窗口中
“
选项
”
标签页中选择可用性为
“
脱机
”
,不要选中
“
在启动脱机操作之前停顿数据库
”
选项;
点击备份向导的
“
调度
”
标签页,选择
“
立即运行而不保存历史任务
”
,然后点击
“
完成
”
按钮。
4.
系统返回
SQL01035
表明备份失败,根据提示消息可知目前正有另一个程序在连接并使用要脱机备份的数据库,点击
“DB2
消息
”
窗口的
“
关闭
”
按钮。
5.
为了确保所要备份的数据库的完整性,脱机备份要求独占该数据库。因为刚才我们通过命令中心连接到数据库且该连接一直保持到现在,所以脱机备份不能成功。在命令中心中输入如下命令强制所有用户或应用程序切断与该数据库的连接:
force application all
terminate
6.
按照第三步重新脱机备份数据库
MUSICDB
,这次备份成功。
7.
记录这次备份的时间截。
在控制中心中选择
“
工具
—>
日志
”
菜单,在
“
日志
”
窗口中选择
“
数据库历史
”
标签页,点击
“
数据库
”
一栏右边的
“…”
省略号按钮,选择相应的系统名、实例名、数据库名,然后点击
“
确定
”
按钮;
按
“
开始日期
”
排序
MUSICDB
所有事件列表,那么本次备份应在列表最上端,记录下该备份的开始日期及时间;
关闭
“
日志
”
窗口,然后回到控制中心来。
8.
如果要使用
restore
命令而不是控制中心来恢复数据库,且备份目录下存在数据库的多个备份镜象,那么,为了指定用哪一份(何时产生的)备份文件,备份的时间截信息就是必须的。
如果使用磁盘备份数据库,那么备份镜象的文件名就包含着时间截信息,如果使用磁带或者
Tivoli Storage Manager
管理的设备作为备份的存储介质,那么备份镜像的头部也包含着相关的时间信息。
9.
另外一种查看备份相关信息的方法是使用
list backup
命令,回顾上节实验我们使用
load
工具之前也备份了数据库
MUSICD
。命令中心中输入如下命令:
terminate
;
list backup all for musicdb
;
10.
查看上步命令的输出列表,注意所有的时间截及表空间信息。
11.
在
list backup
命令中可以不使用
ALL
关键字,可以指定一个特定的时间截或对象名来限制输出结果,例如要查看某时间以后对
MUSICDB
进行的所有备份,可以输入:
12.
list backup since yyyymmddhh for MUSICDB
13.
数据库管理器所维护的有关备份的历史信息如果从不删除会变得非常多,通过
prune history
命令可以整理那些备份的相关信息。命令行处理器或命令中心中输入命令查看的
prune
的在线帮助信息:
? Prune
14.
在
Windows
命令提示符下输入如下命令,可以发现备份的目录结构及文件名与备份的数据库名、实例名、时间截之间的对应关系:
x
:
cd restore
dir
cd MUSICDB.0
dir
cd inst1
dir
cd NODE0000
dir
cd CATN0000
dir
cd yyyymmdd
dir
可见备份文件名的格式都为
“hhmmss.001”
。
15.
本实验中数据库
MUSICDB
备份后其中数据还未经过任何改动,现在的数据与备份中的数据完全相同。首先执行下面
SQL
语句选择出编号为
77
的艺术家的名字和他的专辑名:
select name
,
title from artists a
,
albums b where a.artno =77 and a.artno= b.artno
;
16.
将编号为
77
的艺术家的名字改为
“Melanie and the Mechanics”
,并且将编号为
261
的专辑名改为
“Unmaterial Girl”
:
update artists set name='Melanie and the Mechanics' where artno=77
;
update albums set title ='Unmaterial Girl' where itemno=261
;
17.
执行下列连接查询查看上步的修改结果:
select name
,
title from artists a
,
albums b where a.artno =77 and a.artno= b.artno
;
可见这次的输出与前面的查询结果不同。
18.
假如现在
MUSICDB
所在的磁盘出现故障,我们将使用刚刚创建的备份镜像对
MUSICDB
进行恢复。输入
“
?
restore”
命令获得恢复命令的帮助信息。
19.
恢复前为确保当前没有用户或应用程序连接到
MUSICDB
数据库,命令中心中输入并执行如下命令:
force application all
;
terminate
;
20.
从
X
:
/restore
目录下读取备份文件对
MUSICDB
进行恢复。
在控制中心中右键单击
MUSICDB
数据库,在弹出菜单中选择
“
复原
”
;
选择
“
复原数据向导
”
窗口的
“
介绍
”
页面,选择
“
复原到现有数据库
”
;
选择向导的
“
复原对象
”
页面,选择
“
复原整个数据库
”
;
选择向导的
“
可用的映象
”
页面,选择刚刚备份的映象,然后点击
“>”
按钮;
选择向导的
“
调度
”
页面,选择
“
立即运行而不保存历史任务
”
;
选择向导的
“
总结
”
页面,点击
“
显示命令
”
按钮查看全部
restore
命令;
点击向导的
“
完成
”
按钮,等待一段时间后系统弹出
“DB2
消息
”
窗口显示命令成功完成,点击
“
确定
”
按钮关闭该消息窗口。
21.
重新连接到数据库,输入并执行下列连接查询语句:
connect to MUSICDB
;
select name
,
title from artists a
,
albums b where a.artno =77 and a.artno= b.artno
;
可见这次查询的输出结果反映的是更新前的原数据,所以恢复操作成功完成。
22.
上面的恢复操作虽然复原了更新前的数据,但是因为
DB2
使用了循环日志方式,无法前滚,所以无法救回备份后对数据库所作的更新。
三. 归档日志条件下的数据库恢复
1.
在
Windows
命令提示符下输入如下命令创建数据库的备份目录(
X
:
/RESTORE
):
MD /RESTORE
2.
更改数据库配置文件以采用归档日志方式。
在命令中心中输入并执行下列命令:
update db cfg for musicdb using logretain recovery
四. 重新设置实验环境
完成下列任务以确保剩余实验的环境设置正确:
1.
在命令中心中导入并执行
X
:
/cf23/clrec
脚本。
2.
检查数据库配置文件,确保目前数据库采用循环日志方式。
命令中心中输入并执行下列语句:
connect to musicdb
get db cfg for musicdb
可见输出结果中
“
恢复状态的日志保留
”
为
NO
。
实验七:管理权限(2学时)
实验任务:
A.
熟悉
DB2 UDB
中
PUBLIC
组的默认特权;
B.
学会管理
DB2
权限(
SYSADM
,
SYSCYRL
,
SYSMAINT
和
DBADM
),熟悉各种权限的操作能力;
C.
能够对单个用户和组授予特权;
D.
清楚何时组特权可用和何时需要个人特权等。
一. 默认的PUBLIC特权
1.
建立新用户
user1
(1)
使用
Windows
控制面板中的用户管理程序按下列要求创建一个新用户:
用户名:
user1
密码:
user1
全名:
USER1
描述:
DB2 User
(2)
在命令中心中输入如下命令确保没有应用程序或用户连接到
INST1
实例:
force application all;
terminate;
(3)
将
stock
表的更新权限授予
user1
用户:
connect to musicdb;
grant update on stock to user1;
2.
从
DB2
的控制中心,确定当前数据库的所有用户的默认权限。
从
DB2
控制中心,选择
MUSICDB
数据库,右键选择
”
权限
”
,然后分别选择“组”和“用户”页,查看有哪些组和用户,分别具有什么样的权限?
(1)
具有授权的任一个
ID
用户可以对
MUSICDB
进行的
操作包括:创建数据库、添加新的
PACKAGES
,连接到数据,以及隐式地创建模式等。
(2)
NOFENCE
列包含一个“
not
”符号,这表示
PUBLIC
组不具有创建
NOT FENCED
用户自定义函数的特权,该工作需要授予某个用户,和创建用户自定义函数相关的另一个命令
CREATE EXTERNAL ROUTINE
也不缺省地授予
PUBLIC
的特权。
(3)
在
LOAD
列上也具有“
not
”符号,表示
PUBLIC
组不能够进行
LOAD
操作,只有
SYSADM
,
DBADM
,或在数据库上具有
LOAD
及与
LOAD
操作相关的其他操作特权的用户,可以进行
LOAD
操作。
(4)
要查看所有的授权信息,可以通过
SYSCAT.DBAUTH
视图来进行。
3.
建立一个以不同用户连接到数据库
MUSICDB
的对话。
打开
DB2
命令窗口,输入
“title user1”
。以
user1
用户连接到数据
MUSICDB
db2 connect to musicdb user user1 using user1
输入下列
SQL
语句:
db2 “select substr(tbspace
,
1
,
18) from syscat.tablespaces”
由于
PUBLIC
组中,
SELECT
是默认的特权,自然
user1
用户也能够执行查询操作。
4.
验证建表权限
(1)
输入下列
SQL
语句:
db2 "create table test1 (partno integer
,
subpart integer) in DMS04"
该语句不能够成功执行,因为
user1
用户在
DMS04
表空间上没有
USE
特权。
(2)
user1
用户要想实现上述操作,可以通过其他用户对其授权实现,假设
corp_spy
用户具有相应的权限,可以实现对
user1
用户的授权。在
DB2
的命令窗口中输入下列命令即可:
title corp_spy
db2 connect to musicdb
db2 grant use of tablespace dms04 to user user1
(3)
user1
用户可以重新输入下列命令来实现上述操作:
db2 "create table test1 (partno integer
,
subpart integer) in DMS04"
此时,该语句就会成功执行。
5.
在命令中心,列出表空间
DMS04
中的所创建的所有表。
(1)
user1
可以通过下列方法可查看
MDS04
表空间的
ID
:
在控制中心,在
MUSICDB
数据库下选择
Views
,然后选择
TABLESPACES
视图,并用右键查看其内容,可以看到
DMS04
的
ID
为
6
。
(2)
在命令中心的交互页面,输入下列命令:
minate
connect to musicdb
select tabschema, name from syscat.tables where tbspaceid = 6
6.
以用户
inst1
身份,通过
GUI
将表
user1.test1
从数据库中删除。这里需要清楚的是,
inst1
之所以能够删除该表,是因为该用户具有系统管理员权限。
7.
撤销
PUBLIC
的默认权限。
在控制中心,在
MUSICDB
数据库的用户和组左侧单击
+
号,选择
DB Group
,然后在内容部分选择
PUBLIC
,右键选择
change
,然后去掉其连接数据库、创建表、创建包等选项。
8.
再以
user1
用户创建表
test1
:
db2 create table test1 (partno integer
,
subpart integer) in DMS04
由于
CREATETAB
特权被撤销,所以用户
user1
不能够再创建该数据表。但是此时
user1
与数据库的连接仍然处于活动状态。
二. DB2的权限
1.
Inst1
既是
Windows
的管理员,又是
DB2
的系统管理员。用
inst1
的
Windows
管理权限创建四个新的用户:
usersys
,
userctl
,
usermnt
和
userdba
,密码与用户名相同。在
DB2
的命令窗口,输入下列命令:
net user usersys usersys /add
net user userctl userctl /add
net user usermnt usermnt /add
net user userdba userdba /add
然后输入下列命令:
net localgroup STAFF /add
net localgroup STAFF userdba /add
下面以
usersys
来作数据库系统管理员。首先通过
inst1
实例的数据库管理器配置参数,查看一下当前的系统管理员组是否是
AMD1
。
2.
在
DB2
命令窗口中输入下列命令,可以查看
ADM1
组中的用户。
net localgroup ADM1
这些用户具有数据库管理员权限。要是其他用户也要具有该权限,需要将其加入到
ADM1
组中。
3.
在
DB2
命令窗口中输入下列命令,将
usersys
添加到
ADM1
组中,并可通过
net localgroup ADM1
命令查看加入
usersys
用户后
ADM1
组中用户的变化。
net localgroup ADM1 usersys /add
4.
在
DB2
命令窗口中输入
title usersys
。
5.
从
usersys
对话,可以确认
usersys
已经具有了相应的权限。
db2 connect to musicdb user usersys using usersys
db2 select name from inst1.artists
db2 grant delete on inst1.artists to public
db2 revoke delete on inst1.artists from public
db2 get snapshot for locks on musicdb |more
虽然在第一部分中的
Revoke
已经撤销了部分
public
特权,但这些语句全部能够成功执行,因为数据库系统管理员可以完成任何工作。
6.
作为具有
sysadm
权限的用户,
usersys
可以更新数据库管理器配置文件。如:
db2 update dbm cfg using sysctrl_group ctrl1 sysmaint_group maint1
该更改生效,需要执行下列语句:
db2 force application all
db2 terminate
db2stop
db2start
从控制中心,可以查看到更改后配置参数。
7.
在
DB2
命令窗口,创建一个名为
CTRL1
的组,并为之添加一个成员
userctl
,然后创建一个
MAINT1
组,并添加一个用户
usermnt
。
net localgroup CTRL1 /add
net localgroup MAINT1 /add
net localgroup CTRL1 userctl /add
net localgroup MAINT1 usermnt /add
8.
通过
title
命令分别为
userctl
和
usermnt
两个标题分别为
userct
和
usermn
命令提示窗口。
9.
在
userctl
的命令窗口中,输入下列语句以查看该用户具有的权限:
db2 connect to musicdb user userctl using userctl
db2 get authorizations
我们会发现该用户具有
SYSCTRL
权限,即系统控制权限。
10.
在
usermntl
的命令窗口中,输入下列语句以查看该用户具有的权限:
db2 connect to musicdb user usermnt using usermnt
db2 get authorizations
usermnt
用户具有
SYSMAINT
权限,即系统维护权限。
11.
在
userctl
的命令窗口中,输入下列语句(此时不要做其他任何授权操作):
db2 create table test1 (col1 char(1))
db2 drop table inst1.reorder
db2 select * from inst1.music
这三个语句均不能够成功执行,都会遇到权限错误。因为
SYSCTRL
权限不能够访问数据库对象,而
SYSMAINT
权限是
SYSCTRL
的子集,对
SYSCTRL
不能完成的操作,
SYSMAINT
也不能实现。
12.
在
userctl
的命令窗口中,执行下列
SQL
语句:
db2 select name from inst1.artists
该语句会成功执行,因为从表
artists
中查询数据的特权被授予了
PUBLIC
,该特权可以通过控制中心,在用户和组的查看。需要清楚,和
SYSCTRL
权限相关的特权不是对特定表的,而是面向实例的。
13.
在
userctl
的命令窗口中,为
MUSICDB
数据库指定一个新的别名:
db2 catalog db musicdb as ctrlsdb
14.
在
userctl
和
usermnt
的命令窗口中输入
db2 terminate
命令。
15.
将
userdba
添加为数据库管理员。在控制中心,右键点击
MUSICDB
数据库,选择
Authorities
,然后单击
ADD
命令,在用户页面,选择用户
userdba
,并为其添加
DBADM
权限。添加后可以通过控制中心,在
DBAUTH
视图中查看到该用户的权限。
16.
将
userctl
的的命令窗口标题改为
userdba
。
Title userdba
17.
在
userdba
的命令窗口中,输入下列命令,连接到
MUSICDB
数据,并执行如下操作:
db2 connect to musicdb user userdba using userdba
db2 select name from inst1.artists
db2 create table test1 (col1 char(1))
db2 create table inst1.dlc (col1 char(1))
db2 drop table inst1.dlc
三. DB2用户和组特权
1.
以
userdba
用户身份,将下列内容插入到
inst1.artists
表中。
db2 insert into inst1.albums values('DB2 Connect is My Life'
,
99
,
310)
db2 insert into inst1.albums values('Rustle Up'
,
99
,
311)
2.
系统维护权限用户
usermnt
没有
SQL
特权,从
userdba
的命令窗口对其授予
delete
特权:
db2 grant delete on inst1.albums to usermnt
3.
从
usermnt
的命令窗口,连接到数据库
MUSICDB
,并执行表
inst1.albums
的删除行操作:
db2 connect to musicdb user usermnt using usermnt
db2 delete from inst1.albums where itemno = 310
4.
如果上述删除操作被
inst1
发现,并且
inst1
通过与授权相似的方式,将
userdba
的
DBADM
权限收回。同时,
userdba
想将删除的内容重新插入:
db2 insert into inst1.albums values('DB2 Connect is My Life'
,
99
,
310)
但是该操作是被禁止的,因为
userdba
不再具有数据库管理权限,不能再对其他用户模式下创建的数据进行操纵。
5.
如果
userdba
是数据对象的创建者,当被撤销数据库管理权限时,仍可执行如上的操作。如对
userdba
创建的表
test1
,仍可进行下列操作:
db2 select * from test1
db2 drop table test1
6.
通过
db2 terminate
和
exit
命令分别关闭用户
userdba
、
usermnt
、
usersys
等对话窗口。
实验八:应用程序开发(选做)
实验任务:
练习并掌握常用的数据开发方法,实现和数据库的连接,并完成数据的读取等操作,主要内容包括:
A.
嵌入式
SQL
应用程序开发;
B.
基于
OLE DB/ADO
技术的
DB2
应用程序开发;
C.
基于
JDBC
技术的
DB2
应用程序开发
;
D.
基于
SQLJ
的
DB2
应用程序开发
。
该实验主要由学生课下完成。
一. 建立与数据库的连接,实现数据插入、删除和查询等操作。
1.
分别应用
嵌入式
SQL
、
OLE DB/ADO
、
JDBC
和
SQLJ
实现数据库的连接,并实现数据插入、删除和查询等操作,所查询的结果,需要以适当的形式呈现给用户。具体实现方法,请参阅电子书的相关内容,或其他应用开发技术文档。
需要实现内容如下:
(1)
连接到
MUSICDB
数据库
(2)
将下列数据插入到表
artists
中
ARTNO NAME CLASSIFICATION BIO PICTURE
200 Wang Fei R
201 Zhou Jielun S
202 Na Ying C
204 Zheng Zhihua S
(3)
将
artists
表中
ARTNO
为
200
的演员的名字(
NAME
)更改为
Zhang Xueyou;
将名字(
NAME
)为
Zheng Zhihua
的演员的
CLASSIFICTION
改为
C
。
(4)
删除表
artists
中名字为
Zhou Jielun
的演员的信息
;
将
artists
中编号大于
200
的演员的信息删除
。
(5)
实现实验五中的一之
1
、
2
、
3
、
7
、
8
的查询内容
。
说明:
本实验指导书的主要内容是在IBM培训教材(CF23)实验指导书的基础上翻译而来,并根据教学大纲对其中部分内容进行了修订。
参考文献:
1.
IBM
DB2 Universal Database Administration Workshop for Windows (Course Code CF231) —Instructor Exercises Guide
2.
杨鑫华,赵慧敏,丁传华等. 《数据库原理与DB2应用教程》.清华大学出版社. 2007.8