DB2实验环境设置

实验一：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’

 

在命令中心中输入命令 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'

4.       通过 SYSCAT.TABLES 视图查看参照完整性约束的相关信息（选做）。

在命令中心中输入如下 SQL 语句，其中 CHILDREN 为参照该表的所有表的个数， PARENTS 为该表所要参照的表的个数：

   select tabname , parents , children
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