内容


developerWorks 图书频道

循序渐进 DB2 —— DBA 系统管理、运维与应用案例,第 15 章

DB2 常见问题总结

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: developerWorks 图书频道

敬请期待该系列的后续内容。

此内容是该系列的一部分:developerWorks 图书频道

敬请期待该系列的后续内容。

好了,您已经学习完前面的 14 章。本章我们把日常生活中常见的 DB2 问题做个总结,希望这些总结能够帮助您用好 DB2 数据库。

15.1 实例常见问题和诊断案例

15.1.1 实例无法启动问题总结

在 DB2 V8 之前,启动实例不需要 db2nodes.cfg 文件。 DB2 V8 之后引入了这个配置文件。这个配置文件主要是为了数据库分区设计的,下面我们来看看这个文件的内容,如图 15-1 所示。

图 15-1 db2nodes.cfg 文件
图 15-1  db2nodes.cfg 文件
图 15-1 db2nodes.cfg 文件

在这个文件中,最重要的是 XINZHUANG,这是我机器的主机名。是我创建实例的时候 DB2 自动读取机器主机名然后写到这个文件中的。所以如果您更改了机器的 hostname,而没有更改这个配置文件,那么您的实例无法启动。如图 15-2 所示,在我更改了我机器的主机名后,启动实例时报错。

图 15-2 启动实例报错
图 15-2  启动实例报错
图 15-2 启动实例报错

所以,如果您的实例无法启动,请检查 db2nodes.cfg,看该文件配置是否正常。

在确保您的实例的 db2nodes.cfg 文件配置正常后,如果启动实例仍然报错,那么这个时候请检查 db2systm 实例配置文件。实例启动时需要读取这个文件分配资源,因而这个文件被破坏也会导致实例无法正常启动。

在 HP-UX、Solaris 和 Linux 上安装 DB2 时需要设置相关操作系统内核参数 ( 共享内存、信号灯、消息队列等 ),如果内核参数设置不当,那么也会导致实例无法启动。

上述几个是实例无法启动的主要问题,当然其他还有很多原因,但是不太常见。例如,您如果更改实例目录下 security 目录下文件的属组和权限,或者数据库文件权限被改成了 777,那么实例可能也无法启动。当然这种情况很难检查,也不太常见。例如接下来的例子:

15.1.2 实例无法正常终止

某些情况下,数据库异常。我们需要停止实例,但是发出 db2stop 或 db2stop force 后,实例一直 hang 在那里无法正常终止。这种情况下我们只能通过如下方法来停止实例:

  • 执行 db2_kill 或 ps – efl |awk “ {print $2 } ” | xargs kill -9
  • 执行 ipcs – a | grep – i db2inst1( 您机器上的实例名 ),查看系统为该实例分配的共享内存
  • 执行 ipcrm – m 实例共享内存 ID 或 ipclean
  • 执行 ipcs | grep instancename | awk '{print " ipcrm -"$1" "$2 }' > ipcln
  • 执行 chmod +x ipcln
  • 执行 ipcln
  • 在窗口中执行 db2nkill 然后停止服务,实在不行重启机器

一般情况下,实例无法正常终止通常和进程异常退出或应用程序有关。用这种方式可以把实例停止,至于为什么实例无法正常终止,这个原因太多,我们在《深入解析 DB2 》一书中会详细讲解如何诊断实例 hang 在那里的可能原因。

15.1.3 实例启动报 SQL1042C 错误

客户将 DB2 从 V8.1,补丁 6 升级到 V8.2(V8.1,fp7) 或者更高补丁级别 ( 例如安装了新的补丁级别 FP14),虽然 db2iupdt 更新实例成功完成,但执行 db2start 仍然失败,错误代码为 SQL1042C:发生意外的系统错误。

SQL1042C 是 DB2 中最难诊断的问题,下面我们先检查 DB2 诊断日志文件,可以看到如下错误信息:

2007-11-24-05.29.30.989781+480 E4877143A332 LEVEL: Error (OS) 
 PID : 19720 TID : 1 PROC : db2start 
 INSTANCE: ppdb_itc NODE : 000 
 FUNCTION:DB2UDB, oper system services, sqloexec2, probe:2 
 CALLED : OS, -, unspecified_system_function 
 OSERR : ENOMSG (35) "No message of desired type" 
 2007-11-24-05.29.30.991066+480 I4877476A357 LEVEL: Severe 
 PID : 19720 TID : 1 PROC : db2start 
 INSTANCE: ppdb_itc NODE : 000 
 FUNCTION:DB2UDB, base sys utilities, sqleParallelDb2start, probe:32 
 MESSAGE : DiagData 
 DATA #1 : Hexdump, 4 bytes 
 0x0FFFFFFFFFFFEA6C : FFFF FBEE .... 
 2007-11-24-05.29.31.012963+480 I4877834A297 LEVEL: Warning 
 PID : 21056 TID : 1 PROC : db2updv8 
 INSTANCE: ppdb_itc NODE : 000 
 FUNCTION:DB2UDB, Common Trace API, db2updv8__dump_message, probe:10 
 MESSAGE : DB2UPDV8: Starting database manager failed.

AIX 上,系统错误 35 的含义是:

#define ENOMSG 35 /* No message of desired type */

经查,AIX 操作系统的内核目前仍然是 32 位:

# bootinfo -K 
 32

在 AIX 上,即使内核是 32 位,也可以以虚拟模式运行 64 位应用程序。但是 DB2 V8.2 中,设计上有些改变,所以要求 AIX 内核必须是 64 位才能运行 64 位实例。当把 AIX 内核从 32 位升级为 64 位之后,该问题就解决了。

15.1.4 实例目录误删除

如果实例目录不小心被误删除,且是双机热备环境,那么建议您切换到备机上,获取实例的 DBM 配置文件。然后执行:

db2 list db directory

查看数据库的目录,假如你的数据库的目录为“ /db2prod/data ”。

在主机上,执行 db2idrop 删除实例,然后执行 db2icrt 重新创建实例。实例创建后根据备机的 DBM 配置文件把配置文件同步。最后在该实例上编目数据库:

db2 catalog db mydb on /db2prod/data

15.1.5 实例崩溃问题

遇到实例崩溃的问题,首先查看 db2diag.log,根据里面的信息来分析数据库宕机的原因。再看 db2dump 目录中是否产生了 trap 文件。可以根据这些信息来分析原因,一般这类问题都需要 IBM 工程师协助解决。宕机的原因可以分为两类:一类是数据库的 BUG,即数据库的缺陷引起的,一般如果遇到了数据库的缺陷,那么都有临时的解决方案,或者通过安装最新的补丁来解决,对某些问题 IBM 也会提供临时的修订来解决 ( 需要付费 ) ;另一类是操作系统误操作等非产品问题导致的,对非产品问题导致的宕机尽量要避免。常见的实例宕机原因有:

  • 系统交换空间 (paging space) 用尽
  • 数据库的核心进程被 kill
  • 应用进程异常退出未释放资源

如何准确地定位是 DB2 的 bug 还是应用进程的问题,我们会在《深入解析 DB2 》一书中高级诊断部分详细讲解。

15.2 数据库常见问题总结

15.2.1 数据库日志空间满—— SQL0964C 错误

数据库运行时如果报 SQL0964C 的错误,那么一般和数据库日志有关,先看图 15-3 所示的案例。

图 15-3 数据库日志已满
图 15-3  数据库日志已满
图 15-3 数据库日志已满

DB2 使用的活动日志的最大空间是由公式

(logprimary + logsecond) * logfilsiz * 4096

计算出的大小来决定的 (logprimary、logsecond、logfilsiz 是数据库配置参数 ) 。在 DB2 中,一个长事务最多可以使用不超过 256GB 日志 (DB2 V5 是 2GB,DB2 V7 是 32GB,DB2 V8 是 256GB)

若该空间已全部被分配,而应用仍试图请求更多活动日志空间时,就会发生日志满的情况。此时,用户的更新、删除或插入操作都会使 DB2DIAG.LOG 中写入以下信息:

SQL0964C 数据库的事务日志已满。

SQL0964C 错误表明数据库日志已满,DB2 活动日志满通常是由于存在大量未提交事务的数据,使得活动日志的空间不能及时释放,使新的事务无法申请到可用日志空间,而最终报出 SQL0964C 的错误所致。要解决这些问题,您可以增加日志文件或日志个数。这种错误一般是批量插入、删除或更新时报的错误。所以最好是能够调整业务逻辑,分批次删除、更新或插入。这样做会降低日志报错的概率。

但还有另外一种原因,即在日志空间并未用尽的情况下,当某个占有最旧活动日志的应用长时间未作提交操作,阻止了日志的 LSN 的分配,造成日志空间无法使用,同样会引发这一日志满的报错。对于这种情况,可以提交该交易或利用 FORCE 命令来终止此应用程序,以便释放它所占用的日志空间,使 LSN 可以继续分配,空闲的日志空间可用。这里就提供了由这一原因导致日志满问题的解决方法。

首先检查 DB2 诊断日志文件 db2diag.log,在其中查找如下类似信息:

2008-01-16-02.53.54.935308 Instance:db2inst1 Node:016 
 PID:144252(db2agntp (SAMPLE) 16) Appid:*.* 
 data_protection sqlpgrsp Probe:50 Database:SAMPLE 
 Log Full -- active log held by appl. handle 787273 
 End this application by COMMIT, ROLLBACK or FORCE APPLICATION.

由此,可以找到最早持有日志空间的应用程序,其句柄为 787273 。使用 DB2 的快照工具,通过从快照的输出中查找如下类似信息:

Appl id holding the oldest transaction = 787273

同样可以找到这个应用程序的句柄。这时使用以下命令可以在无需断开数据库其他应用程序的连接的情况下强行终止该应用程序:

db2 force application (787273) 
 DB20000I FORCE APPLICATION 命令成功完成。
 DB21024I 该命令为异步的,可能不会立即生效。

根据提示,由于该命令是异步操作,所以可再次使用:

db2 list applications

验证应用是否已被真正停止,如果输出中已没有该应用,那么它所占有的日志空间会因应用程序被回滚而立即释放,DB2 日志因此重新可用。

15.2.2 数据库时区和时间

在数据库创建好之后,调整系统时间会造成数据库内部时间戳的异常。数据库中一些对象和时间相关,一旦时间不准确,调整时就需要很小心。错误的时间调整可能会造成很多问题,如:

  • 某些对象失效,例如:SQL0440N,找不到具有兼容自变量的类型为“ < 例程类型 > ”的名为“ < 例程名 > ”的已授权例程。
  • 数据库日志逻辑错误–宕机。
  • 常见错误–只调整时间,未调整时区。

正确的做法是在数据库创建之前,调整好时间和时区。如果在数据库创建好之后,确实需要调整时间、时区的,那么建议停止实例,然后正确地调整时间和时区。

15.2.3 中文乱码和代码页转换

执行命令 db2 connect to sample 后系统返回如下错误:
 SQL0332N There is no available conversion for the source code page "819" 
 to the target code page "1386". Reason Code "1". SQLSTATE=57017

DB2 要求源代码页与目标代码页是彼此兼容的。在上述例子中,源代码页为 819目标代码页为 1386 ,两者并不兼容才导致了数据库连接失败。解决方法如下:

请在执行 db2 connect 命令失败的机器上,开启 DB2 命令窗口并执行以下命令后再重新进行连接:
 db2set db2codepage=819

如何在 Windows 的命令编辑器中正确显示英文代码页数据库中的中文字符?

在 DB2 中,对于要存放双字节字符的数据库来说,我们支持的方式是选择其相应的双字节字符集的代码页创建数据库。

但在有些情况下,由于用户的某些特殊需求,可能会选择创建单字节字符集码页的数据库来存放双字节字符的数据。这里以中文这一双字节字符集为例,在中文环境 ( 如中文 Windows 操作系统 ) 下,用户对一个用英文码页 1252 创建的数据库中存放的中文字符进行查询操作。

当查询在 DB2 命令行处理器中进行时,返回的结果能正确地显示中文字符,如:

C:>db2 select * from test 
 A1 
 ---- 
我们
 1 条记录已选择。

但当查询是在 DB2 图形界面的命令编辑器工具中进行的时候,为能够连接 1252 码页的数据库,启动该图形化工具的环境必须有 DB2CODEPAGE=1252 的注册变量设置,但因此所有命令的输出结果中的中文字符都将被显示为乱码,该查询返回的结果中的中文字符也不例外,如:

C:>db2 select * from test 
 A1 
 ---- 
我们
 1 条记录已选择。

我们可以看到输出结果中的中文字符是乱码:

--------------- 输入的命令 ----------------- 
 connect to SBCSDB ; 
 ------------------------------------------ 
 connect to SBCSDB 
 ??¾Ý¿â Á ¬½ Ó ÐÅÏ ¢ 
Ê ý¾Ý¿â · þÎñÆ ÷ = DB2/NT 8.2.1 
 SQL ÊÚȨ±êÊ ¶ = LIWENLI 
± ¾ µ Ø Ê ý¾Ý¿â ± ðÃû = SBCSDB 
与目标的 JDBC 连接已成功。
 --------------- 输入的命令 -------------- 
 select * from test 
 ----------------------------------------- 
单个查询的结果显示在“查询结果”选项卡上。
成功地返回了 1 行。

在命令编辑器中的显示如图 15-4 所示。

图 15-4 命令编辑器中的显示结果
图 15-4 命令编辑器中的显示结果

出现上述情况下中文显示呈乱码的现象,主要是由于 DB2 的图形化工具是在有 DB2CODEPAGE=1252 这种 DB2 单字节字符集注册变量设置的 DB2 命令窗口中启动所导致的。由于 DB2 图形工具是通过 Java 程序实现的,因此,在这样的环境下,Java 初始化程序便会以单字节字符集方式启动 DB2 的图形化工具,从而出现上面描述的中文字符显示错误的问题。

为在 DB2 的命令编辑器中成功显示该 1252 码页数据库中的中文字符,可采用下述步骤:

(1) 在 DB2 命令窗口中,利用 db2set DB2CODEPAGE=1386( 中文码页 ) 设置中文码页环境,然后使用命令 db2ce 启动 DB2 命令编辑器。

此时,在命令编辑器中输入连接数据库的命令,会遇到以下报错:

------------------ 输入的命令 ----------------- 
 connect to SBCSDB ; 
 ------------------------------------------- 
 connect to SBCSDB 
 SQL0332N 没有从源代码页 "1252" 至目标代码页 "1386" 的转换。原因码是 "1" 。
 SQLSTATE=57017

(2) 不要退出已启动的命令编辑器,在启动命令编辑器的 DB2 命令窗口中重新设置 DB2CODEPAGE 注册变量为英文的 1252 码页:

db2set DB2CODEPAGE=1252 
 db2 terminate

(3) 再次尝试数据库的连接和查询操作,命令编辑器中返回的结果为:

------------------- 输入的命令 -------------------- 
 connect to SBCSDB ; 
 -------------------------------------------- 
 connect to SBCSDB 
数据库连接信息
数据库服务器 = DB2/NT 9.5 
 SQL 授权标识 = ORACLE 
本地数据库别名 = SBCSDB 
与目标的 JDBC 连接已成功。
 ---------------------- 输入的命令 --------------------- 
 select * from test; 
 --------------------------------------------- 
 select * from test 
 A1 
 ---- 
我们
 1 条记录已选择。

可以看到,当使用 1386 中文码页启动了命令编辑器后,在不退出命令编辑器的情况下,通过重新设置 DB2 的注册变量 DB2CODEPAGE 为 1252 英文码页的方法,既可实现英文码页数据库的连接,又可达到使各操作执行结果中的中文字符被正确显示的效果。

不过,由于使用单字节字符集码页创建的数据库来存放双字节字符的方式不为 DB2 所正式支持,所以这里介绍的方法只能帮助用户在 DB2 命令编辑器中正确显示数据库中的双字节字符,但在其他 Java 应用中,这样的数据库仍然可能会遇到同样的问题,而且代码页在创建数据库时一旦选择,以后就无法更改代码页,所以我们还是强烈建议选择相应的双字节字符集的代码页来创建数据库。您也可以在建数据库时选择 UTF(unicode) 方式以避免这一类问题发生。

总之,所有的代码页问题级基本上都是由于当前运行环境代码页和数据库服务器数据库代码页不一致造成的。如果在两者一致的情况下数据显示不正常,那么通常和创建数据库时指定单字节 (SBCS,欧美国家是单字节 ) 和双字节 (DBCS,东亚国家是双字节 ) 有关。

15.2.4 通信错误—— SQL30081N

请看下面的例子:

db2 connect to sample user Informix using Informix

SQL30081N 检测到通信错误。正在使用的通信协议:"< 协议 >" 。正在使用的通信 API:"< 接口 >" 。检测到错误的位置:"< 位置 >" 。检测到错误的通信功能:"< 功能 >" 。特定于协议的错误代码:"<rc1>"、"<rc2>" 和 "<rc3>" 。

一般出现这种错误请按照以下步骤检查:

(1) 在客户机器上用 ping 或 pctt 命令连接数据库服务器,确保连接成功。

(2) 用 netstat 命令检查实例侦听端口,确保实例端口处于“ listen ”或“ listening ”状态。

(3) 检查实例配置文件 SVCENAME,确保设置正确。

(4) 检查 db2set – all 输出,确保 DB2COMM 注册变量的通信协议设置正确。

(5) 检查 /etc/services 文件,确保实例端口所在行没有使用 TAB 空格,以及窗口上最后一行是回车。

有的时候客户的防火墙也会造成 SQL30081N 通信错误。

15.2.5 数据库备份、前滚暂挂

 当一个数据库从循环日志改成归档日志时,数据库要求进行一次脱机备份。在重新启动数据库后,数据库就处于备份暂挂 (backup pending) 的状态。要消除备份暂挂状态,就必须对数据库做完整备份。

当我们执行一个数据库的前滚恢复时,如果没有指定 without rolling forward,那么恢复后数据库会处于 roll-forward pending 状态。这种情况下我们只能对数据库前滚才能访问数据库。

总之,暂挂 (pending) 是 DB2 数据库内部保护完整性的一种机制,数据库处于什么样的暂挂 (pending) 状态,我们就执行相应的操作。

DB2 数据库启动的环节中有一个步骤是检查 SQLOGCTL.LFH.1 文件,这个文件中记录了数据库的活动日志情况。很多人由于没有深刻理解活动日志的作用,而有可能误删除活动日志。如果误删除了活动日志,那么数据库将无法连接。这也属于数据库损坏的一种情况,因为数据库的完整一致性受到了破坏。出现这种情况后,首先考虑是否有可以恢复的备份,如果有,可以从备份恢复,然后前滚到日志的末尾,以完全恢复该数据库。如果没有可用的备份来恢复,可以通过 IBM 的技术支持中心来协助解决 (IBM 内部通过一条命令 db2lfh 去修改 SQLOGCTL.LFH.1 文件 ) 。如果想自己解决那就只有使用 db2dart 工具了。

15.2.6 数据库活动日志删除

如何避免数据库的活动日志被删除?可以有以下措施:

  • 启用数据库的镜像日志功能,但是会带来性能的额外开销。
  • 启用数据库的日志出口程序,这样可以避免手工来删除活动日志目录中的日志。
  • 当一定要手工删除活动日志目录中的归档日志时,使用命令 PRUNE LOGFILE PRIOR TO log-file-name,可以避免将活动日志误删除。
  • 注意维护活动日志所在目录的权限。

15.2.7 数据库损坏 ( 数据页、索引页 ) —— SQL1043C

下面我们先来看一个实际的案例,请看下面的 db2diag.log 文件:

2008-09-22-11.46.45.864000+480 I805726H366 LEVEL: Error 
 PID : 1860 TID : 2732 PROC : db2syscs.exe 
 INSTANCE: DB2INST NODE : 000 
 FUNCTION:DB2UDB, buffer pool services, sqlbReadAndReleaseBuffers, probe:13 
 RETCODE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad" 
 DIA8400C A bad page was encountered. 

 2008-09-22-11.46.45.910000+480 I806094H413 LEVEL: Error 
 PID : 1860 TID : 2732 PROC : db2syscs.exe 
 INSTANCE: DB2INST NODE : 000 
 FUNCTION:DB2UDB, buffer pool services, sqlbReadAndReleaseBuffers, probe:13 
 DATA #1 : String, 126 bytes 
 Obj={pool:34;obj:6;type:0} State=x27 Page=140354 Cont=0 Offset=140352 BlkSize=12 
 sqlbReadAndReleaseBuffers error: num-pages=8 

 2008-09-22-11.46.45.942000+480 I806509H593 LEVEL: Error 
 PID : 1860 TID : 2732 PROC : db2syscs.exe 
 INSTANCE: DB2INST NODE : 000 
 MESSAGE : SQLB_OBJECT_DESC 
 DATA #1 : Hexdump, 68 bytes 
 0x04B6B5DC : 2200 0600 2200 0600 0000 0000 003A A2A6 "..."........:.. 
 0x04B6B5EC : 40E5 0000 0000 0000 0000 0000 0000 0000 @............... 
 0x04B6B5FC : 0000 0000 0101 0000 2700 0000 0000 0000 ........'....... 
 0x04B6B60C : 0010 0000 2000 0000 0100 0000 2200 0600 .... ......."... 
 0x04B6B61C : 408C 7400 @.t.SQL1034C 
The database is damaged.2008-09-22-11.46.46.020000+480 I807104H356 LEVEL: Error 
 PID : 1860 TID : 2732 PROC : db2syscs.exe 
 INSTANCE: DB2INST NODE : 000 
 FUNCTION:DB2UDB, buffer pool services, sqlbErrorHandler, probe:0 
 RETCODE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad" 
 DIA8400C A bad page was encountered. 

 2008-09-22-11.46.46.020000+480 I807462H351 LEVEL: Error 
 PID : 1860 TID : 2732 PROC : db2syscs.exe 
 INSTANCE: DB2INST NODE : 000 
 FUNCTION:DB2UDB, buffer pool services, sqlbErrorHandler, probe:0 
 DATA #1 : String, 75 bytes 
 Obj={pool:34;obj:6;type:0} State=x27 
 Prefetcher Error, in sqlbProcessRange 

 2008-09-22-11.46.46.020000+480 I807815H593 LEVEL: Error 
 PID : 1860 TID : 2732 PROC : db2syscs.exe 
 INSTANCE: DB2INST NODE : 000 
 MESSAGE : SQLB_OBJECT_DESC 
 DATA #1 : Hexdump, 68 bytes 
 0x04B6B5DC : 2200 0600 2200 0600 0000 0000 003A A2A6 "..."........:.. 
 0x04B6B5EC : 40E5 0000 0000 0000 0000 0000 0000 0000 @............... 
 0x04B6B5FC : 0000 0000 0101 0000 2700 0000 0000 0000 ........'....... 
 0x04B6B60C : 0010 0000 2000 0000 0100 0000 2200 0600 .... ......."... 
 0x04B6B61C : 408C 7400 @.t.

“ Obj={pool:34;obj:6;type:0} State=x27 ”中 pool 指表空间 ID,obj 指对象 ID 。从系统表中读取并判断是哪个表受到损坏,例如:

select tabname from syscat.tables where tbspaceid=4 and tableid=6

数据库最严重的故障莫过于数据库损坏,从上面的例子来看,我们的数据库中有数据页受到损坏。出现 SQL1034C 后,我们首先用操作系统命令,例如 AIX 操作系统中用“ errpt – d H – T PERM ”来判断系统是否出现硬件损坏;然后尝试能否使用 db2 restart db sample 命令让数据库执行崩溃恢复。

如果上述办法都不能解决问题,那么最好的办法是从备份恢复数据库。如果无法从备份恢复,那么可以根据损坏的原因尝试相应的解决方案。由于存储问题而导致部分数据文件损坏,但是数据库还可以连接,这种情况可以采用导出数据库的表结果和数据的方法来恢复数据库。当然对于损坏的表来说,导出是无法完成的,这时可以使用 db2dart 的导出数据功能来导出这些损坏的表的数据。如果数据库损坏到已经无法连接的程度,那么除了从备份恢复外,唯一的办法是使用 db2dart 来导出所有的数据了:

运行命令 db2dart /DDEL 
   # Table object data formatting start. 
   # Please enter 
   # TableIDor name,tablespace ID,first page,num of pages: 
  # (suffic page number with 'p' for pool relative),

按照提示输入表名、表空间 id、起始页数、需要导出的页数。如果您的数据库非常大,这将是一个工作量非常大的事情。所以建议大家做好数据库备份。

15.2.8 索引重新构建问题

当数据库中索引无效时,可以使用 DBM 配置参数 INDEXREC 决定索引重新构建的方式。

$ db2 get dbm cfg | grep – i indexrec

索引重新创建时间 (INDEXREC) = ACCESS

此参数指示数据库管理器何时将尝试重建无效的索引,以及在 DB2 前滚期间或在辅助数据库上重放 HADR 日志期间是否重做任何索引构建。

  • SYSTEM:在数据库管理器配置文件中指定的 use system setting 决定何时将重建无效的索引。
  • ACCESS:第一次访问索引时将重建无效的索引。
  • ACCESS_NO_REDO:第一次访问基础表时将重建无效的索引。
  • RESTART indexrec:的默认值。将在显式或隐式地发出 RESTART DATABASE 命令时重建无效的索引。
  • RESTART_NO_REDO:将在显式或隐式地发出 RESTART DATABASE 命令时重建无效的索引。

建议:在高端用户服务器上,如果重新启动所花费的时间不重要,那么此选项的最佳选择将是在数据库重新启动时重建该索引,以作为在崩溃后重新将该数据库联机的过程的一部分。将此参数设置为“ ACCESS ”或“ ACCESS_NO_REDO ”将导致重新创建索引时数据库管理器的性能降低。任何访问该特定索引或表的用户将不得不等待,直到索引被重新创建完成为止。如果将此参数设置为“ RESTART ”,那么重新启动数据库所花的时间将因重新创建索引而延长。但是,一旦数据库恢复联机,正常处理将不受影响。读者应该了解这几种方式的区别,以确定您的数据库选用哪种方式是最合理的。

15.2.9 DB2 实用程序不可用

在我们做完 DB2 升级或打完补丁以后,我们在执行数据库的一些命令或实用程序时常常会报一些错误,例如

SQL0805N Package “ NULLID.DYNEXPLN 0X5142316d564fa32 ” was not found

等类似的错误,对这种问题我们可以按照以下步骤来解决:

进入 $DB2HOME/sqllib/bnd 目录:

cd /home/db2inst1/sqllib/bnd

执行如下命令:

db2 connect to sample 
 db2 bind @db2ubind.list isolation cs blocking all grant public-----重新绑定应用程序绑定文件

15.2.10 快速清空表数据

对于使用 DB2 数据库的用户而言,有时候需要将表中数据清空。这里提供了 4 种数据删除的方法,以供用户根据自己的需求进行选择:

  • 使用 DELETE 语句,即:
DELETE FROM < 表名 >

该语句将清除表中所有数据,但由于这一操作会记日志,因此执行速度会相对慢一些。另外要注意的是,如果表较大,那么为保证删除操作的成功,应考虑是否留有足够大的日志空间。

  • 使用 NOT LOGGED INITIALLY 选项,即:
ALTER TABLE < 表名 > ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

这一方法仅在所操作的表在创建时选择了 NOT LOGGED INITIALLY 选项进行定义后才可使用。整个删除操作将不会记日志,因此执行速度是这 4 种方法中最快的一种,但删除的数据是不可恢复的。

  • 使用 LOAD 命令,即:
LOAD FROM /dev/null OF DEL REPLACE INTO < 表名 > NONRECOVERABLE --(UNIX 系统
或 LOAD FROM < 空文件 > OF DEL REPLACE INTO < 表名 > NONRECOVERABLE

在这一方法中,REPLACE 导入方式首先会将表中所有数据清空,然后 IMPORT/LOAD 又向表中导入了空数据,从而实现了数据的清除操作。

  • 使用 DROP/CREATE TABLE 语句,即:
DROP TABLE < 表名 > 
 CREATE TABLE < 表名 > < 字段的定义 >

如果保存有表的定义语句,或已利用 DB2LOOK 命令获得了表定义的脚本,那么也可先删除整个表,再重新创建表。如果表较大,那么用这种方法实现数据清空的速度会快于使用 DELETE 语句。但是用这种方法时要注意:如果这个表上有很多外键,那么需要维护外键的完整性。

15.2.11 表和索引统计信息不一致

当一个表正在被更新时,可以对其执行 RUNSTATS 命令,进行表和索引数据统计信息的收集,但根据更新操作级别的不同,得到的统计信息可能是不一致的。您可能会遇到如下的错误信息:

SQL2314W 某些统计信息处于不一致的状态。最近收集的 "< 对象 -1>" 统计信息与现有的 "< 对象 -2>" 统计信息不一致。

统计信息不一致可能会导致不理想的查询计划,SQL2314W 就是产生这种可能性的警告信息。您应该尝试在应用对该表的访问级别尽可能低 ( 或者如果可能的话没有任何操作 ) 的情况下来执行 RUNSTATS 命令,如尝试尽量避免在有更新操作的情况下进行 RUNSTATS 操作。

另外,RUNSTATS 命令默认使用的是“ ALLOW WRITE ACCESS ”选项,您也可以使用选项“ ALLOW READ ACCESS ”来执 RUNSTATS,这样,在 RUNSTATS 执行的时候,其他操作将不能更改该表。但这个选项会对应用的并行性有影响,因为任何想要更改表的操作都会处于等待状态。为了减少表被 ALLOW READ ACCESS 选项的 RUNSTATS 锁定的时间,您可以考虑使用 TABLESAMPLE 选项,这个选项导致 RUNSTATS 对表的部分采样数据而不是所有数据收集统计信息。合理选择采样数据大小,可以在确保统计信息一致性的情况下,加快 RUNSTATS 的速度。

如果以上的建议都无法阻止 SQL2314W 警告信息的出现,而检查访问该表的应用的存取计划时发现确实存在优化器未能自动选择最优的存取计划的情况,那么应考虑在尽量保证 RUNSTATS 可获得较高存取权限的时候重新执行 RUNSTATS,以便优化器重新产生最优的存取计划。不过对于因遇到 SQL2314W 而产生的非最优的存取计划,如本应选择索引扫描,但优化器选择了表扫描的情况,也可以考虑用 ALTER TABLE 语句将该表标记成“ volatile ”,以鼓励优化器选择索引扫描,而不考虑表扫描。

15.3 表空间状态

DB2 用表状态和表空间状态来控制对数据的访问,或者在特定情况下帮助保护数据库的完整性。下面我们来总结表空间和表中可能引出特定状态的更常见的一些条件,您可以用它们来识别哪些状态是有效的,以及如何作出正确响应,以便可以继续使用数据。理解这些状态还可以使我们更好地理解数据库的行为。

表空间状态在某些情况下被用来控制对数据的访问,或者在必要时被用来引出特定用户动作,以保护数据库的完整性。大多数状态产生于与某个 DB2 实用程序的操作相关的事件,例如加载实用程序,或者备份和恢复实用程序。下面我们举一些例子,以便准确地展示如何解释和响应管理数据库时可能碰到的状态。

db2tbst 命令接收十六进制的状态值,并返回相应的表空间状态 ( 参见图 15-5) 。例如,命令 db2tbst 0x0008 返回 State=Load Pending 。而该十六进制的状态值反过来又是 LIST TABLESPACES 命令输出的组成部分 ( 参见图 15-6) 。

图 15-5 db2tbst 命令
图 15-5  db2tbst 命令
图 15-5 db2tbst 命令

表空间的外部可见状态是由单个状态值的十六进制总和构成的。例如,如果表空间的状态是Backup Pending 和 Load in Progress,那么所返回的十六进制值就是 0x20020 (0x00020 + 0x20000) 。本例中,命令 db2tbst 0x20020 返回:

State = Backup Pending + Load in Progress
图 15-6 可以使用 LIST TABLESPACES 命令确定连接数据库中表空间的当前状态
使用 LIST TABLESPACES 命令确定连接数据库中表空间的当前状态
使用 LIST TABLESPACES 命令确定连接数据库中表空间的当前状态

下面我们讲解一些最经常碰到的表空间状态:

15.3.1 Backup Pending

在执行指定时间点 (point-in-time) 的表空间前滚操作之后,或者在执行指定了 COPY NO 选项的 LOAD 操作 ( 针对可恢复的数据库 ) 之后,表空间处于这种状态。在使用该表空间之前,必须备份该表空间 ( 或者是整个数据库 ) 。如果没有备份这个表空间,那么只能对其中包含的表进行查询,而无法更新它们。注意:在启用数据库进行前滚恢复之后,还必须立即对该数据库进行备份。如果 logretain 数据库配置参数被设为 RECOVERY,或者 userexit 数据库配置参数被设为 YES,那么该数据库是可恢复的。直到对这样的数据库进行了备份,您才可以连接它。备份后,backup_pending 数据库配置参数会被设为 NO 。

已知载入的输入文件 staff_data.del 具有以下内容:“ 11 , "Melnyk" , 20 , "Sales" , 10 , 70000 , 15000 ”。如下所示:

update db cfg for sample using logretain recovery; 
 backup db sample; 
 connect to sample; 
 load from staff_data.del of del messages load.msg insert into staff copy no; 
 update staff set salary = 69000 where id = 11; -- 此时表空间处于 BACK-UP PENGING 状态
 2.update db cfg for sample using logretain recovery; 
 connect to sample; --------- 此时数据库处于 BACKUP PENDING 状态

15.3.2 脱机

如果表空间的一个或多个容器存在问题,那么表空间就处于脱机 (Offline and Not Accessible) 状态。容器偶然可能会被重命名、移动或损坏。在该问题被纠正,且再次可以访问与该表空间相关的容器之后,可以通过断开数据库与应用程序的连接,然后重新连接数据库来消除该异常状态。或者,您可以执行一条 ALTER TABLESPACE 语句,指定 SWITCH ONLINE 子句来消除表空间的 Offline and Not Accessible 状态,从而无需断开其他应用程序与该数据库的连接:

connect to sample; 
 create tablespace data_space managed by database 
            using (file 'c:\prod\data_container1' 1024);

----- 模拟容器故障,手工执行操作系统 mv 或 rename 表空间容器为 'c:\prod\data_container2' 。

select * from staff--------- 此时数据库处于 Offline and Not Accessible 状态

该查询返回 SQL0290N( 不允许访问表空间 ),而 LIST TABLESPACES 命令返回 TS1 的状态值 0x4000(Offline and Not Accessible) 。将表空间容器 'c:\prod\data_container2' 重新命名为 'c:\prod\data_container1' 。这一次,该查询将运行成功。

什么情况下会处于 OFFLINE 状态呢?我举一个实际生产中的例子。在一个双机热备 HA 的环境中,客户在主机上重新创建了使用裸设备的表空间后,未同步 HA 环境。结果导致主机故障切换到备机时,由于裸设备权限不正确而导致表空间处于 OFFLINE 状态。

15.3.3 Quiesced Exclusive|Share|Update

当调用表空间停顿 (quiesce) 功能的应用程序独占 ( 读或写 )、共享或意向更新访问表空间时,该表空间就处于这种状态。您可以通过执行一条 QUIESCE TABLESPACES FOR TABLE 命令,将表空间置于 Quiesced Exclusive |Share |Update 状态。

在将表空间设置为 Quiesced Exclusive |Share |Update 之前,要确保它处于 Normal 状态。

connect to sample; 
 quiesce tablespaces for table staff reset; 
 quiesce tablespaces for table staff exclusive; ---------- 停顿排他
 quiesce tablespaces for table staff share; --------- 停顿共享
 quiesce tablespaces for table staff intent to update; ------- 停顿意图更新

从另一会话执行下列脚本:

connect to sample; 
 update staff set salary=50000 where id=60; 
 list tablespaces;

表空间 USERSPACE1 返回的信息显示,该表空间分别处于 Quiesced Exclusive|Share |Update 状态,可执行 quiesce tablespaces for table staff reset 消除这种状态。

15.3.4 Restore Pending 和 Storage Must be Defined

在执行了重定向恢复操作的第一部分之后 ( 即在发出 SET TABLESPACE CONTAINERS 命令之前 ),数据库的表空间就处于这种状态。在使用表空间之前,必须恢复表空间 ( 或者是整个数据库 ) 。直到成功完成恢复操作,您才可以连接到数据库。此时,restore_pending 信息数据库配置参数的值被设为 NO 。

当处于 Storage May be Defined 中的重定向恢复操作的第一部分完成时,所有的表空间都将处于 Restore Pending 状态。

在将恢复操作重定向到新数据库期间,如果省略了设置表空间容器的阶段,或者在设置表空间容器阶段无法获得指定的容器,那么数据库的表空间就会处于这种状态。某些时候会出现后一种情况,例如,指定了无效的路径名,或者是磁盘空间不足。

backup db sample;

假定该备份镜像的时间戳为 20080613204955:

restore db sample taken at 20040613204955 into mydb redirect; 
 set tablespace containers for 2 using (path 'ts2c1'); 
 list tablespaces;

LIST TABLESPACES 命令返回的信息显示,表空间 SYSCATSPACE 和 TEMPSPACE1 都处于 Storage Must be Defined、Storage May be Defined 和 Restore Pending 状态。 Storage Must be Defined 状态比 Storage May be Defined 状态更重要。

15.3.5 Roll Forward Pending

在对可恢复的数据库执行恢复操作之后,表空间就处于这种状态。在使用表空间之前,必须前滚该表空间 ( 或是整个数据库 ) 。如果 logretain 数据库配置参数被设为 RECOVERY,或者 userexit 数据库配置参数被设为 YES,那么该数据库是可恢复的。直到前滚操作成功完成,您才可以激活或连接到该数据库。此时,rollfwd_pending 信息数据库配置参数被设为 NO 。

当处于 Restore in Progress 中的在线表空间完成恢复操作时,该表空间处于 Roll Forward Pending 状态。

15.3.6 表空间状态总结

表空间状态在某些情况下被用来控制对数据的访问,或者在必要时被用来引出特定用户动作,以保护数据库的完整性。除了我们刚才所讲最常看到的几种表空间状态外,我们还会看到DMS Rebalance in Progress、Backup in Progress、Load in Progress、Reorg in Progress、Restore in Progress、Rollforward in Progress、Table Space Deletion in Progress 和 Table Space Creation in Progress 等状态。“… in Progress ”表示表空间处于正在进行某种操作期间的临时状态。如果这个操作过程出现异常,那么就转变为 pending 状态。这时就需要 DBA 去干预,采用适当的步骤来解除这种暂挂。上面我们举了好多例子,希望大家好好看看,多做练习。

15.4 LOAD 期间表状态总结

DB2 LOAD 实用程序通过表状态 ( 以及锁 ) 来获取对表的访问,并在执行载入操作时维护数据库的一致性。即使载入操作发生了异常终止,表状态也将会被保持。您可以用 LOAD QUERY 命令 ( 见图 15-7) 确定特定表的状态。 LOAD QUERY 命令在其运行时检查载入操作的状态,并返回表的状态。如果载入操作完成 ( 或异常终 ) 了,那么该命令只返回表的状态。

图 15-7 可以使用 LOAD QUERY 命令来确定指定表的状态
图 15-7 可以使用 LOAD QUERY 命令来确定指定表的状态
图 15-7 可以使用 LOAD QUERY 命令来确定指定表的状态

虽然在载入操作之前,表所在的表空间不再是停顿的 ( quiesce 是一种持久性的锁 ),但是Load in Progress表空间状态会在执行载入操作时阻止对从属表进行备份。 Load in Progress 表空间状态不同于Load in Progress表状态:所有的载入操作都使用 Load in Progress 表状态,但是指定 COPY NO 选项的载入操作 ( 针对可恢复的数据库 ) 还是使用 Load in Progress 表空间状态。

一个表可以同时处于几种状态之下。例如,如果将数据载入定义了表检查约束的表中,并指定 ALLOW READ ACCESS 选项,那么在执行载入操作期间,该表就处于Check PendingLoad in ProgressRead Access Only状态下。

15.4.1 Check Pending

如果在一个表上定义了表检查约束,但还未验证新数据与那些已定义约束的兼容性,那么该表就处于这种状态。例如,DB2 LOAD 实用程序当开始在定义了表检查约束的表上执行载入操作时,就将表的状态设置为 Check Pending 。如果想使该表恢复为Normal状态,则需要执行一条 SET INTEGRITY 语句。请看下面的案例:

假如载入的输入文件 staff_data.del 拥有以下内容:“ 11 , "Melnyk" , 20 , "Sales" , 10 , 700 0 0 , 15000 ”。执行:

connect to sample; 
 alter table staff add constraint max_salary check (100000 - salary >0); 
 load from staff_data.del of del insert into staff; 
 load query table staff;

LOAD QUERY 命令返回的信息显示,STAFF 表处于 Check Pending 状态。要想解除这种状态,执行:

set integrity for staff immediate checked;

15.4.2 Load Pending

如果在可提交数据之前,表上的正在执行的载入操作被异常终止,那么该表就处于这种状态。若要使该表恢复Normal状态,则需要调用 load terminate、load restart 或 load replace 操作。请看下面的案例:

已知载入的输入文件 staffdata.del 拥有大量数据 ( 例如,200 000 或更多条记录 ),创建一个包含载入操作目标表的小型表空间,新建一个名为 NEWSTAFF 的表:

connect to sample; 
 create tablespace data_space managed by database 
            using (file 'c:\prod\data_container1' 256); 
 create table newstaff like staff in ts1; 
 load from staffdata.del of del insert into newstaff; 
 load query table newstaff; 
 load from staffdata.del of del terminate into newstaff; 
 load query table newstaff;

LOAD QUERY 命令返回的信息显示,NEWSTAFF 表处于 Load Pending 状态;在执行 load terminate 操作之后,该表就重新处于 Normal 状态。

15.4.3 Load in Progress

这是一种只在执行载入操作期间才有效的临时状态。当载入操作失败或被中断时,执行 load terminate 或 restart 可以返回正常 (Normal) 状态。

已知载入的输入文件 staffdata.del 拥有大量数据 ( 例如 200 000 或更多条记录 ):

update db cfg for sample using logretain recovery; 
 backup db sample; 
 connect to sample; 
 create table newstaff like staff; 
 load from staffdata.del of del insert into newstaff copy no;

在执行载入操作时,从另一会话执行下列脚本:

connect to sample; 
 load query table newstaff;

LOAD QUERY 命令返回的信息显示,NEWSTAFF 表处于 Load in Progress 状态。

15.4.4 Not Load Restartable

当执行完前滚操作,接着出现一个失败的载入操作,而该操作未被成功地重新启动或终止时,表就处于这种状态。该表还将处于Load Pending状态。若要使该表恢复Normal状态,则需要执行一条 LOAD TERMINATE 命令。

已知载入的输入文件 staffdata.del 拥有大量数据 ( 例如 20 000 或更多条记录 ):

update db cfg for sample using logretain recovery; 
 backup db sample; 
 connect to sample; 
 create tablespace data_space managed by database 
             using (file 'c:\prod\data_container1' 256); 
 create table newstaff like staff in ts1; 
 connect reset; 
 backup db sample;

该备份镜像的时间戳为 20080629205935:

connect to sample; 
 load from staffdata.del of del insert into newstaff copy yes to 'c:\prod\load_backup'; 
 connect reset; 
 restore db sample taken at 20080629205935; 
 rollforward db sample to end of logs and stop; 
 connect to sample; 
 load query table newstaff;

LOAD QUERY 命令返回的信息显示,NEWSTAFF 表处于 Not Load Restartable 和 Load Pending 状态。

connect to sample; 
 load from staffdata.del of del terminate into newstaff 
         copy yes to 'c:\prod\load_backup'; 
 load query table newstaff;

LOAD QUERY 命令返回的信息显示,NEWSTAFF 表现在处于 Normal 状态。

15.4.5 Read Access Only

在执行载入操作时,如果指定了 ALLOW READ ACCESS 选项,那么表就处于这种状态。 Read Access Only 是一个临时状态,它允许其他应用程序和实用程序读访问在执行载入操作之前就存在的数据 . 已知载入的输入文件 staffdata.del 拥有大量数据 ( 例如 200 000 或更多条记录 ):

connect to sample; 
 export to st_data.del of del select * from staff; 
 create table newstaff like staff; 
 import from st_data.del of del insert into newstaff; 
 load from staffdata.del of del insert into newstaff allow read access;

在执行载入操作时,从另一会话执行下列脚本:

connect to sample; 
 load query table newstaff; 
 select * from newstaff;

LOAD QUERY 命令返回的信息显示,NEWSTAFF 表处于 Read Access Only 和 Load in Progress 状态。该查询返回 STAFF 表所导出的内容,以及在执行载入操作之前就存在于 NEWSTAFF 表中的数据。

15.4.6 Unavailable

当前滚一个无法恢复的载入操作时,表就处于这种状态;这样的表只能被删除,或者从备份镜像恢复它。

已知载入的输入文件 staff_data.del 拥有下列内容:“ 11 , "Melnyk" , 20 , "Sales" , 10 , 70000 , 15000 ”。执行:

update db cfg for sample using logretain recovery; 
 backup db sample;

该备份镜像的时间戳为 20080629182012:

connect to sample; 
 load from staff_data.del of del insert into staff nonrecoverable; 
 connect reset; 
 restore db sample taken at 20080629182012; 
 rollforward db sample to end of logs and stop; 
 connect to sample; 
 load query table staff;

LOAD QUERY 命令返回的信息显示,STAFF 表处于 Unavailable 状态。

15.5 锁相关问题

15.5.1 锁升级

如果 DB2 数据库中发生锁升级现象,那么会影响数据库的并发性能。我们可以增加 locklist 和 maxlocks 来消除锁升级。但是很多时候单纯地调整这两个参数并不能从根本上消除锁升级。所以更多的是调整业务逻辑,创建最合理的索引,编写最高效的 SQL 。使 SQL 语句持有锁的时间尽可能地短。对于 X 锁升级来说通常发生在一张表上,S 锁升级则通常发生在多张表上。关于锁升级的详细内容,我们在“第 10 章:锁和并发”中有详细的讲解,读者可以参考相关内容。

15.5.2 锁等待问题解决流程

对于数据库中出现的锁等待问题,我们可以参考以下步骤来解决:

(1) 可以执行下列 SQL 语句,找出引起锁等待的 SQL 语句:

select AGENT_ID ,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS 
from table(SNAPSHOT_STATEMENT('sample',-1)) as B where AGENT_ID in 
(select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT('sample',-1)) 
as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY ) 
order by STMT_ELAPSED_TIME_MS DESC

------- 用你的数据库名称替换 SAMPLE 数据库

(2) 对引起锁等待的 SQL 语句,用解释工具分析其执行计划。从执行计划中分析该 SQL 语句的索引是否合理,以及能否对该 SQL 语句进行调优。

(3) 尝试使用 db2advis 工具为引起锁等待的 SQL 语句创建最合理的索引。尝试调优引起锁等待的 SQL 语句。

(4) 如果创建索引和调优 SQL 语句仍然不能解决问题,考虑能否根据业务逻辑选择 UR 隔离级别。

(5) 最后考虑能否对引起锁等待的 SQL 语句关联的表采用数据归档、业务分离等手段。

15.5.3 死锁

死锁是数据库中的一种正常现象,每个数据库中都存在死锁情况。死锁是一种特殊情况的锁等待。死锁问题通常和业务逻辑有关,通常我们可以设置 dlchktime 死锁检测时间间隔,把该参数调小,使 db2dlock 后台死锁检测进程能快速地检测到死锁然后打破死锁平衡。关于死锁的详细信息读者可以参考“第 10 章:锁和并发”中相关内容。

15.6 内存常见问题

15.6.1 bufferpool 设置过大数据库无法启动

一定要根据系统的内存资源情况设置 bufferpool,曾经有个客户把 bufferpool 设置为机器内存的 80%,结果导致数据库无法启动。如何解决呢?最后把数据库的配置参数 database_memory 由 automiac 更改为一个比较小的值,例如 100MB 后才能正常启动。然后再连接数据库,调用 db2 alter bufferpool bp_8k size num_of_pages 来更改缓冲池大小。

15.6.2 排序溢出

已分配的专用排序堆总数 = 20000000 
已分配的共享排序堆总数 = 0 
共享排序堆高水位标记 = 0 
后阈值排序 ( 共享内存 = 0 
总计排序 = 24504 
总计排序时间 ( 毫秒 = 653400 
排序溢出 = 3420 
活动排序数 = 324 
内存池类型 = 共享排序堆

从上面我们可以看到数据库中有很多排序溢出,排序特别消耗资源,合理地设置排序堆 sortheap 非常重要;所以如果数据库中有大量排序溢出,那么考虑增加 sortheap 大小。在 DB2 V9 以后考虑把该参数设置为 automiac,让 DB2 自动决定排序堆的大小。

15.6.3 锁内存不足

如果数据库中报告 SQL0912N,那么表明已经达到数据库的锁定请求的最大数目。因为分配给锁定列表的内存不足。这种情况下可考虑增加锁内存 locklist 的大小。建议在提交其他 SQL 语句前,应用程序应该提交 COMMIT 或 ROLLBACK 语句。更多时候我们要考虑调整应用,例如如果我们一次删除 1 千万条记录,那么肯定会产生大量锁,而如果我们分成 10 次删除,每删除 100 万条记录后 COMMIT 释放锁,那么占用锁的资源就会大大减少。

15.7 备份恢复常见问题

在由备份恢复一个数据库时,遇到 SQL2522 错误,如图 15-8 所示。

图 15-8 SQL2522 错误
图 15-8  SQL2522 错误
图 15-8 SQL2522 错误

 此错误一般是由于未能提供正确的时间戳造成的。如果有多个数据库的备份,那么在做数据库恢复时,就需要提供正确的路径和时间戳。如果是用 DB2 命令行来执行恢复操作,那么在 Windows NT 操作系统中可参照如下命令:

RESTORE DATABASE SAMPLE FROM D:\backups TAKEN AT 20081117125141

此命令中要注意路径和时间戳。时间戳可以通过 list history 命令得到:

LIST HISTORY BACKUP ALL FOR SAMPLE Op Obj Timestamp+sequence Type Dev 
Earliest log Current log BackupID B D 20081117125141001 F D 
S0000000.LOG S0000000.LOG Contains 2 tablespace(s): 00001 
SYSCATSPACE 00002 USERSPACE1 
 00003 DATA_SPACE 00004 INDEX_SPACE

此命令的输出列出了备份的时间戳加上一个 3 位的数字序列:

时间戳 +3 位的数字序列 =20081117125141001

所以,可以在 restore 命令中使用时间戳:20081117125141 。如果能有多于一个备份,那么可以使用 list history 命令显示所有备份记录的信息。

15.8 数据移动常见问题总结

如果我们只是在同一个数据库中进行数据移动,那么这将是非常简单的事情。但是实际生活中我们的数据源可能来自异构数据库,平台、操作系统也不同,数据移动过程中会涉及到代码页转换问题、标识列、生成列、大对象、XML、日期格式、空值处理、定界符和 PC/IXF 格式问题。本节我们主要讲解这些内容。

15.8.1 标识列

当表中有标识列时,由于标识列由数据库自动维护,所以导入导出时需要特别注意。

1. 标识列导出注意事项

可使用 EXPORT 实用程序从包含标识列的表中导出数据。但是,标识列会限制您输出文件格式的选择。

如果对导出操作指定的 SELECT 语句的格式为 SELECT * FROMtablename,并且未使用 METHOD 选项,那么支持将标识列属性导出至 IXF 文件。然后可使用 IMPORT 命令的 REPLACE_CREATE 和 CREATE 选项重新创建该表,包括其标识列属性。如果通过包含 GENERATED ALWAYS 类型标识列的表创建已导出 IXF 文件,那么成功导入数据文件的唯一方法就是在导入操作期间指定identityignore文件类型修饰符,否则会拒绝所有行 ( 发出 SQL3550W) 。

2. 标识列导入 (IMPORT) 和装入 (LOAD) 注意事项

无论输入数据是否具有标识列值,都可以使用 IMPORT/LOAD 实用程序将数据导入 / 装入到包含标识列的表中。

如果未使用与标识相关的文件类型修饰符,那么该实用程序会遵循下列规则来工作:

  • 如果标识列是 GENERATED ALWAYS 列,那么每当输入文件中的相应行缺少标识列值,或者显式指定了 NULL 值时,会为表行生成标识值。如果对标识列指定了非空值,那么会拒绝该行 (SQL3550W) 。
  • 如果标识列是 GENERATED BY DEFAULT 列,那么 IMPORT/LOAD 实用程序会使用用户提供的值 ( 如果提供了这些值 ) ;如果缺少数据或者显式指定了 NULL,那么会生成值。

除了通常对标识列数据类型 ( 即 SMALLINT、INT、BIGINT 或 DECIMAL) 的值执行的验证操作以外,IMPORT/LOAD 实用程序不会对用户提供的标识值执行任何其他的验证操作。不报告重复值。此外,在将数据导入 (import) 到带有标识列的表中时,不能使用compound=x 修饰符。

有 3 种文件类型修饰符可用来简化将数据导入到包含标识列的表中的操作:identitymissing、identityignore 和 identityoverride,如表 15-1 所示。

表 15-1 用来简化将数据导入到包含标识列的表中的 3 个文件类型修饰符
文件修饰符支持 IMPORT支持 LOAD描 述
identityignoreyesyes指定忽略导入或载入输入文件中的任何标识列的值,并为每一行生成一个新的标识值
identitymissingyesyes指定导入或载入输入文件不包含任何目标表中标识列 的值
identityoverridenoyes指定在将数据载入一个带有 GENERATED ALWAYS 标识列的表中时,要使用的载入输入文件中标识列的值;任何标识列上值为空 ( 或为 NULL 值 ) 的行都会被拒绝

通过表 15-1 我们可以看到, IMPORT 和 LOAD 都支持identitymissing 和 identityignore 文件类型修饰符,但是 LOAD 支持identityoverride 文件修饰符而 IMPORT 不支持。了解了上述文件类型修饰符以后,下面我们举一些例子。

例 15-1 在没有标识列的情况下导入数据。

如果输入数据文件未包含任何标识列值 ( 甚至未包含 NULL 值 ),那么identitymissing文件类型修饰符可以使您更方便地导入带有标识列的表。例如,考虑使用以下 SQL 语句定义的表:

create table table1 ( c1 char(30), c2 int generated by default as identity, c3 real, 
 c4 char(1))

用户可能想要将数据从文件 (import.del)导入到 TABLE1 中,并且此数据可能是从没有标识列的表中导出的。下面是此类文件的一个示例:

Robert, 45.2, J 
 Mike, 76.9, K 
 Leo, 23.4, I

导入此文件的一种方法是通过 IMPORT 命令显式列示所要导入的列,如下所示:

db2 import from import.del of del replace into table1 (c1, c3, c4)

但是,对于包含许多列的表来说,此语法难以使用并且容易出错。导入该文件的另一种方法是使用identitymissing文件类型修饰符,如下所示:

db2 import from import.del of del modified by identitymissing replace into table1

例 15-2 在带有标识列的情况下装入数据。

identityignore文件类型修饰符在某些方面与identitymissing相反。它指示 LOAD 实用程序:即使输入数据文件包含标识列数据,也应该忽略该数据,并且应该为每一行生成标识值。例如,用户可能想将以下数据按照例 15-1 中所示定义从文件 (load.del)导入到 TABLE1 中:

Robert, 1, 45.2, J 
 Mike, 2, 76.9, K 
 Leo, 3, 23.4, I

如果用户提供的值1、2 和 3未用于标识列,那么该用户可以发出以下 LOAD 命令:

db2 load from import.del of del method P(1, 3, 4) replace into table1 (c1, c3, c4)

同样,如果该表包含许多列,那么此方法可能难以使用并且容易出错。identityignore 文件类型修饰符可以将语法简化为:

db2 load from import.del of del modified by identityignore replace into table1

当带有标识列的表导出至 IXF 文件时,可使用 IMPORT 命令的 REPLACE_CREATE 和 CREATE 选项来重新创建该表,包括其标识列属性。如果这种 IXF 文件是从包含类型为 GENERATED ALWAYS 的标识列的表创建的,那么只有通过指定identityignore文件类型修饰符才能成功导入数据文件,否则会拒绝所有行 (SQL3550W) 。

例 15-3 装入包含用户提供的值的数据 (identityoverride) 。

这个文件类型修饰符 IMPORT 不支持,identityoverride 文件类型修饰符用来将用户提供的值装入到包含 GENERATED ALWAYS 标识列的表中。当从另一数据库系统迁移数据并且必须将表定义为 GENERATED ALWAYS 时,或者在将使用 ROLLFORWARD DATABASE 命令 DROPPED TABLE RECOVERY 选项恢复的数据装入到表中时,此文件类型修饰符非常有用。当使用了此文件类型修饰符时,将拒绝任何未包含标识列数据 ( 或者包含 NULL 数据 ) 的行 (SQL3116W) 。还应该注意,使用此文件类型修饰符时,可能会违反 GENERATED ALWAYS 列的唯一性属性。在这种情况下,执行 LOAD TERMINATE 操作,然后接着执行 LOAD INSERT 或 REPLACE 操作。下面我们举个例子。

考虑载入输入文件 custdata.del,它的内容为“ 3,"Shrek" ”:

create table customers (custno smallint not null generated always 
as identity (start with 500, increment by 1), custname varchar(16));

--- 该表具有标识列,开始值是 500,而我们导入的值是 3 。

load from custdata.del of del modified by identityoverride messages 
load.msg insert into customers; 
 select * from customers;

--- 我们可以看到由于我们指明了 identityoverride,所以我们提供的值 3 覆盖了 500 。

3. LOAD 标识列注意事项

在大多数情况下,LOAD 实用程序无法保证对各行指定标识列值的顺序与这些行在数据文件中的出现顺序相同。由于 LOAD 实用程序以并行方式对标识列值的指定进行管理,所以这些值按任意顺序指定。它的例外情况如下所示:

  • 在单一分区数据库中,当 CPU_PARALLELISM 设置为 1 时,不以并行方式处理行。在此情况下,将按照各行在数据文件参数中的出现顺序来隐式地指定标识列值。
  • 在多分区数据库中,如果标识列位于分布键中且存在单分区代理程序 ( 即,未指定多个分区代理程序或anyorder文件类型修饰符 ),那么将按照各行在数据文件中的出现顺序来指定标识列值。

将表装入到分区数据库中时,如果该表在数据库的分区键中具有标识列并且未指定identityoverride 文件类型修饰符,那么不能指定 SAVECOUNT 选项。当分区键中存在标识列并且正在生成标识值时,在至少一个数据库分区上从装入阶段重新启动装入操作时,需要从装入阶段开始时就重新启动整个装入操作,这意味着不可能有任何一致点。

注意:

如果符合下列所有条件,那么不允许执行 LOAD RESTART 操作,应改为发出 LOAD TERMINATE 或 REPLACE 操作。

  • 要装入的表位于分区数据库环境中,并且它包含至少一个标识列,该标识列位于分区键中或由分区键中的生成列引用。
  • 未指定identityoverride 文件类型修饰符。
  • 失败的上一个装入操作包括装入在装入阶段后失败的数据库分区。

15.8.2 生成列

当表中有生成列时 ( 生成列是由数据库自动维护的 ) ,对于导出没有特别注意的,但是导入时需要特别注意。

1. 生成列 IMPORT/LOAD 注意事项

无论输入数据是否具有生成列值,都可以将数据装入到包含 ( 非标识 ) 生成列的表中。 IMPORT/LOAD 实用程序生成列值。

如果未使用任何与生成列相关的文件类型修饰符,IMPORT/LOAD 实用程序就会依照下列规则工作:

  • 当数据文件中相应的行缺少生成列的值或提供了 NULL 值时,将创建生成列值。如果为生成列提供了非空值,那么将拒绝该行 (SQL3550W) 。
  • 如果为不可空生成列创建了 NULL 值,那么将拒绝整行数据 (SQL0407N) 。例如,如果将不可空生成列定义为两个表列之和,但这两个表列在数据文件中包含 NULL 值,那么就会发生这种情况。

表有 3 种相互排斥的方法可用来简化将数据装入到包含生成列的表中的操作:generated missing、generatedignore 和 generatedoverride 文件类型修饰符,如表 15-2 所示。

表 15-2 简化将数据装入到包含生成列的表中的方法
文件修饰符支持 IMPORT支持 LOAD描 述
generatedignoreyesYes指定导入或载入输入文件中的任何生成列的值都会被忽略,并且会为每一行生成一个新的值
generatedmissingyesYes指定导入或载入输入文件不包含任何目标表中生成列的值
generatedoverridenoyes指定在将数据载入一个带有 GENERATED ALWAYS 列的表中时,要使用的载入输入文件中生成列的值。如果您使用了该文件类型修饰符,您的表在执行载入操作之后,将处于检查暂挂状态,以便给您机会验证新数据的完整性。这里的完整性是指与生成列规范的一致性。如果要使表脱离该状态,且不验证输入值,那么就在执行载入操作之后发出下列命令:set integrity for < table-name> generated column immediate unchecked 。如果要解除该表的检查暂挂状态,且验证输入值,那么就发出下列命令:set integrity for < table-name> immediate checked

通过表 15-2 我们可以看到, IMPORT 和 LOAD 都支持generatedmissing 和 generatedignore文件类型修饰符,但是 LOAD 支持generatedoverride 文件修饰符而 IMPORT 不支持。了解了上述文件类型修饰符以后,下面我们举一些例子。

例 15-4 在没有生成列的情况下导入数据。

如果输入数据文件不包含表中的所有生成列的任何值 ( 甚至未包含 NULL 值 ),那么generatedmissing文件类型修饰符会使您能够更方便地将数据导入到包含生成列的表中。例如,考虑使用以下 SQL 语句定义的表:

create table table1 ( c1 int, c2 int, g1 int generated always as (c1 + c2), 
           g2 int generated always as (2 * c1), 
 c3 char(1))

用户可能想将数据从文件 (load.del) 导入到 TABLE1 中,此数据可能是从没有任何生成列的表中导出的。下面是此类文件的一个示例:

1, 5, J

2, 6, K 3, 7, I

导入此文件的一种方法是通过 IMPORT 或 LOAD 命令显式列示所要导入的列,如下所示:

db2 import/load from import.del of del replace into table1 (c1, c2, c3)

但是,对于包含许多列的表来说,此语法难以使用并且容易出错。另一种导入此文件的方法是使用generatedmissing文件类型修饰符,如下所示:

db2 import/load from import.del of del modified by generatedmissing replace into table1

例 15-5 在具有生成列的情况下导入数据。

Generatedignore 文件类型修饰符在某些方面与generatedmissing相反。它向 IMPORT/LOAD 实用程序指示:即使输入数据文件包含所有生成列的数据,也应该忽略该数据,并且应该为每一行生成值。例如,用户可能想将以下数据按照上述定义从文件 (load.del)导入到 TABLE1 中:

1, 5, 10, 15, J 2, 6, 11, 16, K 3, 7, 12, 17, I

用户提供的非空值10、11 和 12( 用于 g1) 以及15、16 和 17( 用于 g2) 导致拒绝该行 (SQL 3550W) 。为了避免这种情况,用户可以发出以下 IMPORT/LOAD 命令:

db2 import/load from import.del of del method P(1, 2, 5) replace into table1 (c1, c2, c3)

同样,如果该表包含许多列,那么此方法可能难以使用并且容易出错。generatedignore文件类型修饰符可以将语法简化为:

db2 import/load from import.del of del modified by generatedignore replace into table1

对于 INSERT_UPDATE,如果生成列同时充当主键并且指定了generatedignore 文件类型修饰符,那么 IMPORT 命令会采用generatedignore 文件类型修饰符。 IMPORT 命令不会在 UPDATE 的 WHERE 子句中用用户提供的值替换此列。

例 15-6 装入包含用户提供的值的数据。

Generatedoverride 文件类型修饰符只支持 LOAD 实用程序,它用来将用户提供的值装入到包含生成列的表中。当从另一个数据库系统迁移数据,或者在将使用 ROLLFORWARD DATABASE 命令 RECOVER DROPPED TABLE 选项恢复的数据装入到表中时,此文件类型修饰符非常有用。当使用了此文件类型修饰符时,将拒绝任何未包含不可空生成列数据 ( 或者包含 NULL 数据 ) 的行 (SQL3116W) 。使用此文件类型修饰符时,装入操作完成后将使表处于“设置完整性暂挂”状态。要使该表脱离“设置完整性暂挂”状态,而不验证用户提供的值,请执行以下命令:

SET INTEGRITY FOR table-name GENERATED COLUMN IMMEDIATE UNCHECKED

要使该表脱离“设置完整性暂挂”状态并强制验证用户提供的值,请执行以下命令:

SET INTEGRITY FOR table-name IMMEDIATE CHECKED

下面我们举个例子,假如载入输入文件 staffdata.del 的内容为:“ "Jack" , 500000.00 , 50000 ”:

create table newstaff (name varchar(16) not null, salary decimal(9,2), 
bonus decimal(9,2) generated always as (salary/10)); 
 load from staffdata.del of del modified by generatedoverride messages 
load.msg insert into newstaff; 
 set integrity for newstaff immediate checked; 
 select * from newstaff;

如果生成列在任何分区、维或分布键中,那么会忽略generatedoverride 文件类型修饰符并且 LOAD 实用程序会生成值,就像指定了generatedignore 文件类型修饰符一样。这样做是为了避免用户提供的生成列值与它的生成列定义相冲突,在这种情况下,会将生成的记录放置在错误的物理位置,例如错误的数据分区、MDC 块或数据库分区。

注意:

如果一个生成列表达式包含受防护的 (FENCED) 用户定义的函数,那么尝试装入到这样的表中时装入操作会失败。但是,通过使用generatedoverride文件类型修饰符,可以为这些类型的生成列提供您自己的值。

15.8.3 大对象

LOB 导出注意事项

导出包含大对象 (LOB) 列的表时,默认操作是对每个 LOB 值导出最多 32 KB,以便将其与列数据的余下部分放在同一文件中。如果要导出超过 32 KB 的 LOB 值,那么应将 LOB 数据写至单独的文件以避免截断。

要指定应将 LOB 写至自己的文件,请使用 lobsinfile 文件类型修饰符。此文件类型修饰符指示 EXPORT 实用程序将 LOB 数据放在 LOBS TO 子句指定的目录中。使用 LOBS TO 或 LOBFILE 会隐式激活 lobsinfile 文件类型修饰符。默认情况下,LOB 值与导出的关系数据将写至同一路径。如果使用 LOBS TO 选项指定了一个或多个路径,那么 EXPORT 实用程序将循环使用这些 LOB 路径,以便将每个成功的 LOB 值写入相应的 LOB 文件。还可使用 LOBFILE 选项对输出 LOB 文件指定名称。如果指定了 LOBFILE 选项,那么 lobfilename 的格式为 lobfilespec.xxx.lob,其中 lobfilespec 是为 LOBFILE 选项指定的值,而 xxx 是 EXPORT 实用程序生成的 LOB 文件的序号。否则,lobfilename 的格式为:exportfilename.xxx.lob,其中 exportfilename 是为 EXPORT 命令指定的已导出输出文件的名称,而 xxx 是 EXPORT 实用程序生成的 LOB 文件的序号。

默认情况下,多个 LOB 将写至单个文件,但您也可指定将各个 LOB 存储在不同文件中。 EXPORT 实用程序会生成 LOB 位置说明符 (LLS),以允许将多个 LOB 存储在一个文件中。写至导出输出文件的 LLS 是一个指示 LOB 数据在文件中存储位置的字符串。 LLS 的格式为 lobfilename.ext.nnn.mmm/,其中 lobfilename.ext 是包含 LOB 的文件的名称,nnn 是该文件内 LOB 的偏移量 ( 以字节为单位计量 ),而 mmm 是 LOB 的长度 ( 以字节为单位计量 ) 。例如,db2exp.001.123.456/ 的 LLS 表示 LOB 位于文件 db2exp.001 中,以 123 字节的偏移量开始进入文件,并且长度为 456 字节。如果 LLS 中指示的大小为 0,那么 LOB 被视为长度是 0 。如果长度为– 1,那么 LOB 被视为 NULL,并且忽略偏移量和文件名。

如果不希望个别 LOB 数据并置于同一文件,请使用 lobsinsepfiles 文件类型修饰符以将每个 LOB 写至单独文件。

大对象 LOAD/IMPORT 注意事项

因为 IMPORT/LOAD 实用程序将单个列的大小限制为 32KB,所以导入 LOB 时有一些额外注意事项。

默认情况下,IMPORT /LOAD 实用程序将输入文件中的数据视为要装入列中的数据。但是,如果大对象 (LOB) 数据存储在主要输入数据文件中,那么数据大小被限制为 32KB 。因此,为避免丢失数据,应将 LOB 数据存储在主要数据文件以外的位置,并且应在导入 LOB 时指定lobsinfile文件类型修饰符。

LOBS FROM 子句会隐式激活lobsinfile。导入数据时,LOBS FROM 子句会将导入数据时用于搜索 LOB 文件的路径列表传递至 IMPORT/LOAD 实用程序。如果未指定 LOBS FROM 选项,那么假定要导入的 LOB 文件与输入关系数据文件位于同一路径中。

指示存储 LOB 数据的位置

导入 LOB 信息时,可以使用 LOB 位置说明符 (LLS) 将多个 LOB 存储在单个文件中。指定lobsinfile后,EXPORT 实用程序会生成 LLS 并将其存储在导出输出文件中,并且 LLS 会指示 LOB 数据的位置。导入使用指定的lobsinfile选项修饰的数据时,数据库要求每个对应 LOB 列都有对应的 LLS 。如果 LOB 列遇到的不是 LLS,那么数据库会将其视为 LOB 文件,并且将把整个文件作为 LOB 装入。

对于 CREATE 方式下的 IMPORT,可通过使用 LONG IN 子句指定将创建 LOB 数据并将其存储在单独的表空间中。

以下示例显示如何导入 LOB 并将其存储在不同文件中的 DEL 文件:

IMPORT/LOAD FROM inputfile.del OF DEL  LOBS FROM /tmp/data 
MODIFIED BY lobsinfile INSERT INTO newtable

15.8.4 空值处理

在数据移动中,NULL 值是最难处理的,表 15-3 所示的文件修饰符可以用于处理 NULL 值。

表 15-3 用于处理 NULL 值的文件修饰符
文件修饰符描 述
nullindchar= x指定 x 的值 ( 单个字符 ) 将用于替换空值
striptnulls当将一个数据装入到一个变长字段时,将截断任何尾部的 NULL(0x00) 字符,如果未指定此选项则会保留 NULL
不能将此选项与 striptblanks 同时指定。它们是互斥的。此选项会取代过时的 padwithzero 选项
striptblanks当将一个数据装入到一个变长字段时,将截断任何尾部的空格。如果未指定此选项则会保留空格
不能将此选项与 striptnulls 同时指定。它们是互斥的。此选项会取代过时的 t 选项

( 续表 )

文件修饰符描 述
keepblanks指定在执行导入或载入操作时,要保留 CHAR、VARCHAR、LONG VARCHAR 或 CLOB 等类型列中开头或结尾的空白字符 ( 不包含在字符定界符之中 ) 。如果您所定义的非空 (NOT NULL) 列包含一个或多个空白字符,且这些空白字符表示有效数据,那么该修饰符会极其有用。如果您在导入或载入这些数据时未指定 keepblanks 修饰符,实用工具将试图用一个空 (NULL) 值来替换空白字符,但因为该列是不可为空的,所以实用工具会返回一个错误。请注意,在载入 CHAR 列时,总是会在结尾的空白中填入该列长度。但是,keepblanks 修饰符对于保留 CHAR 列中开头的空白是有必要的

下面我们举一个使用这些文件修饰符的的例子。

例 15-7 nullindchar 文件修饰符使用示例。

对于 IXF 格式的文件来说,导入空值非常方便,因为里面已经记录了空值的信息。但是,对于 ASC 格式文件就有一定的难度了,因为 DB2 会直接插入空格,而不是空值。为此,DB2 提供了一个文件修饰符进行控制—— NULL INDICATORS 。

考虑导入数据文件 null.txt 的内容为—— test++++++++ N+++(+ 表示空格 ) 。

C:\>db2 create table names (firstname varchar(12), lastname varchar(12)) 
 DB20000I SQL 命令成功完成。
 C:\> db2 import from null.txt of asc modified by nullindchar=N 
              method l (1 12,13 24) null indicators(0,13) insert into names 
读取行数 = 1 
跳过行数 = 0 
插入行数 = 1 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 1 
 C:\>db2 select * from names 
 FIRSTNAME LASTNAME 
 ------------ ------------ 
 test - 1 条记录已选择。

上述 IMPORT 语句中表示 13 字符开始的列值为 N 的时候替换为空值。

striptnulls

考虑载入输入文件 orgdata.asc 的内容为:++++++10Head Office++++++++160Corporate+ New York+---- 。

db2 create table orgtemp like org 
 db2 load from orgdata.asc of asc modified by striptnulls 
        method l (1 8, 9 22, 23 30, 31 40, 41 53) messages load.msg insert into orgtemp 
读取行数 = 1 
跳过行数 = 0 
装入行数 = 1 
拒绝行数 = 0 
删除行数 = 0 
落实行数 = 1 
 db2 select location concat division from orgtemp 
 1 
 ----------------------- 
 New York Corporate

在上面的例子中,我们看到在使用了 striptnulls 之后,将输入文件尾部的 null 删除了。

keepblanks

db2 create table names (lastname char(16), firstname char(16)) 
 DB20000I SQL 命令成功完成。
 db2 create table newnames like names 
 DB20000I SQL 命令成功完成。
 db2 insert into names values (' Tallerico ', ' Teresa ') 
 DB20000I SQL 命令成功完成。
 db2 export to names.del of del modified by nochardel 
messages export.msg select * from names 
导出的行数:1 
 db2 load from names.del of del modified by keepblanks 
messages load.msg insert into newnames 
读取行数 = 1 
跳过行数 = 0 
装入行数 = 1 
拒绝行数 = 0 
删除行数 = 0 
落实行数 = 1 
 db2 select firstname concat lastname from newnames 
 1 
 -------------------------------- 
 Teresa Tallerico 1 条记录已选择。

在上述例子中,我们看到在使用了 keepblanks 之后,导入的数据中的空格都保留了下来。

striptblanks

考虑载入输入文件 orgdata.asc 的内容为:++++++10Head Office++++++++160Corporate+ New York+++++ 。

db2 create table orgtemp like org 
 DB20000I SQL 命令成功完成。
 db2 load from orgdata.asc of asc modified by striptblanks 
method l (1 8, 9 22, 23 30, 31 40, 41 53) messages load.msg insert into orgtemp 
读取行数 = 1 
跳过行数 = 0 
装入行数 = 1 
拒绝行数 = 0 
删除行数 = 0 
落实行数 = 1 
 db2 select location concat division from orgtemp 
 1 
 ----------------------- 
 New YorkCorporate

在上述例子中,我们看到在使用了 striptblanks 之后,头部和尾部的所有空格都被删除了。

15.8.5 定界符注意问题

1. 定界符格式移动数据时的注意事项

移动定界 ASCII(DEL) 文件时,一定要确保移动的数据不会因为定界字符识别问题而导致无意中发生改变。为帮助避免发生这些错误,DB2 会强制实施若干限制并提供了许多文件类型修饰符。有许多限制可帮助避免所选定界字符被视为移动数据的一部分。首先,定界符是互斥的。其次,定界符不能是二进制零、换行符、回车符或空格。而且默认小数点 (.) 不能是字符串定界符。最后,在 DBCS 环境中,不支持竖线 (|) 字符定界符。下面是一些定界符的使用限制:

  • 空格 (X'20') 永远不能作为有效定界符。
  • 在导入期间将删除第一个字符前面的空格或者单元格值中最后一个字符后面的空格。不会删除单元格值中间嵌入的空格。
  • 由于句点 (.) 与时间戳记值中的句点冲突,所以它不是有效的字符串定界符。
  • 对于纯 DBCS( 图形 )、混合 DBCS 和 EUC 来说,定界符的范围是 x00 到 x3F 。
  • 对于使用 EBCDIC 代码页指定的 DEL 数据来说,定界符可能与 shift-in 和 shift-out DBCS 字符不一致。
  • 在 Windows 操作系统上,字符定界符外部第一次出现的文件结束符 (X'1A') 指示文件结束。不会导入任何后续数据。
  • 空值表示通常应该赋值的单元格缺少单元格值,也可以表示一串空格。
  • 由于某些产品将字符字段的长度限制为 254 或 255 个字节,所以,每当选择导出最大长度超过 254 个字节的字符列时,EXPORT 实用程序就会生成警告消息。 IMPORT 实用程序可以接受与最长的 LONG VARCHAR 和 LONG VARGRAPHIC 列等长的字段。
  • 如果服务器的代码页与客户机的代码页不同,那么建议指定非默认定界符的十六进制表示法。例如:
db2 load from ... modified by chardel0x0C coldelX1e ...

2. 数据移动期间的定界符问题

双字符定界符

默认情况下,对于 DEL 文件的基于字符的字段来说,字段中的任何字符定界符实例都用双字符定界符表示。例如,假定字符定界符是双引号,如果导出文本“ I am 6" tall. ”,那么 DEL 文件中的输出文本显示为“ "I am 6"" tall." ”相反,如果 DEL 文件中的输入文本为“ "What a ""nice"" day!" ”,那么导入的文本为“ What a "nice" day! ”。

nodoubledel

可通过指定 nodoubledel 文件类型修饰符对 IMPORT、EXPORT 和 LOAD 实用程序禁用双字符定界符行为。但要注意的是,双字符定界符行为是为了避免解析错误。对导出使用 nodoubledel 时,字符定界符出现在字符字段中时不会显示为双字符。对导入和装入使用 nodoubledel 时,双字符定界符不会解释为字符定界符的字面值实例。

nochardel

对导出使用 nochardel 文件类型修饰符时,字符字段不会用字符定界符括起来。对导入和装入使用 nochardel 时,字符定界符不会被视作特殊字符并且会解释为实际数据。

db2 create table stafftemp like staff 
 DB20000I SQL 命令成功完成。
 db2 export to staffdata.del of del modified by nochardel 
messages export.msg select * from staff

导出的行数:35

db2 import from staffdata.del of del modified by nochardel 
messages import.msg insert into stafftemp 
读取行数 = 35 
跳过行数 = 0 
插入行数 = 35 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 35

上述脚本中 staffdata.del 文件的内容如下所示:

10,Sanders,20,Mgr ,7,+98357.50, 
 20,Pernal,20,Sales,8,+78171.25,+00612.45 
 30,Marenghi,38,Mgr ,5,+77506.75, 
 40,O'Brien,38,Sales,6,+78006.00,+00846.55 
 50,Hanes,15,Mgr ,10,+80659.80, 
 60,Quigley,38,Sales,,+66808.30,+00650.25 
 70,Rothman,15,Sales,7,+76502.83,+01152.00

如果不使用 nochardel 文件修饰符,那么导出的文件内容如下所示:

10,"Sanders",20,"Mgr ",7,+98357.50, 
 20,"Pernal",20,"Sales",8,+78171.25,+00612.45 
 30,"Marenghi",38,"Mgr ",5,+77506.75, 
 40,"O'Brien",38,"Sales",6,+78006.00,+00846.55 
 50,"Hanes",15,"Mgr ",10,+80659.80, 
 60,"Quigley",38,"Sales",,+66808.30,+00650.25 
 70,"Rothman",15,"Sales",7,+76502.83,+01152.00

chardel

可使用其他文件类型修饰符,以通过手动方式避免默认定界符与数据之间混淆。 chardel 文件类型修饰符将单字符 x 指定为要使用的字符串定界符,以替代默认情况下使用的双引号。

db2 export to orgdata.del of del modified by chardel'' messages export.msg select * from org

coldel x

同样,要避免将默认情况下使用的逗号用作列定界符,可使用 coldel,它会将单字符 x 指定为列数据定界符。

db2 create table orgtemp like org 
 DB20000I SQL 命令成功完成。
 db2 export to orgdata.del of del modified by coldel; 
messages export.msg select * from org;

导出的行数:35

db2 import from orgdata.del of del modified by coldel; 
messages import.msg insert into orgtemp; 
读取行数 = 35 
跳过行数 = 0s 
插入行数 = 35 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 35

delprioritychar

移动 DEL 文件时另一个需要注意的问题就是保留定界符的正确优先顺序。定界符的默认优先级为:行,字符,列。但是,某些应用程序依赖于以下优先级:字符,行,列。例如,如果使用默认优先级,那么 DEL 数据文件

"Vincent <row delimiter> is a manager",<row delimiter>

将解释为两行:“ Vincent ”和“ is a manager ”,原因是 <row delimiter> 优先于字符定界符 (") 。如果使用 delprioritychar,那么字符定界符 (") 优先于行定界符 (<row delimiter>),这意味着同一 DEL 文件将正确地解释为一行:“ Vincent is a manager ”。

假设载入输入文件 contactsdata.del 的内容为:"Tallerico","123 Anyplace Street Ourtown H0H 0H0":

db2 create table contacts (lastname varchar(16), address varchar(64)) 
 DB20000I SQL 命令成功完成。
 db2 load from contactsdata.del of del modified by delprioritychar 
messages load.msg insert into contacts 
读取行数 = 1 
跳过行数 = 0 
装入行数 = 1 
拒绝行数 = 0 
删除行数 = 0 
落实行数 = 1 
 db2 select * from contacts 
 LASTNAME ADDRESS 
 -------------- --------------------------------------------------------- 
 Tallerico 123 Anyplace Street Ourtown H0H 0H0

15.8.6 PC/IXF 注意问题

PC/IXF 格式注意事项

典型导出操作包括插入或装入到现有表中的所选数据的输出。但是,也可导出整个表,以便以后使用 IMPORT 实用程序重新创建。

要导出表,必须指定 PC/IXF 文件格式。然后可通过 CREATE 方式使用 IMPORT 实用程序以重新创建已保存表 ( 包括其索引 ) 。但是,如果出现下列任一情况,那么某些信息不会保存至已导出 IXF 文件:

  • 索引列名包含十六进制值 0x2B 或 0x2D
  • 该表包含 XML 列
  • 该表是多维集群表 (MDC)
  • 该表包含表分区键
  • 由于代码页转换而导致索引名长度超过 128 个字节
  • 该表是受保护的
  • EXPORT 命令包含 SELECT * FROMtablename以外的操作字符串
  • 对 EXPORT 实用程序指定了 METHODN 参数

注意:

不建议使用导入的 CREATE 方式。请使用 db2look 实用程序来捕获并重新创建表。

索引信息

如果索引中指定的列名包含“-”或“+”字符,那么不会收集索引信息,并且将返回警告 SQL27984W 。 EXPORT 实用程序完成处理,并且不会影响已导出的数据。但是,索引信息未保存在 IXF 文件中。因此,您必须使用 db2look 实用程序来单独创建索引。

空间局限性

如果导出的数据超过创建导出文件所在文件系统的可用空间量,那么导出操作会失败。在这种情况下,应该通过在 WHERE 子句中指定条件来对选择的数据量进行限制,以使已导出文件能够存放在目标文件系统中。可以多次调用 EXPORT 实用程序以导出所有数据。表 15-4 列示了可用于 PC/ IXF 文件类型的文件类型修饰符。

表 15-4 可用于 PC/IXF 文件类型的文件类型修饰符
修 饰 符IMPORTLOAD描 述
indexixfyesno指定导入实用工具将删除表上所定义的所有索引,并由 PC/IXF 文件中的索引定义创建一个新的。该文件类型修饰符只能在表中内容要被替换时使用
nochecklengthsyesyes指定即使输入数据超出了目标表列的大小,也应该尝试导入或载入每一行。如果您知道输入数据将适合所有情况,那么就可以使用该文件类型修饰符

indexixf 示例:

db2 create table newemp like employee 
 DB20000I SQL 命令成功完成。
 db2 export to empdata.ixf of ixf messages export.msg select * from employee

导出的行数:42

db2 import from empdata.ixf of ixf modified by indexixf 
messages import.msg replace_create into newemp 
读取行数 = 42 
跳过行数 = 0 
插入行数 = 42 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 42

nochecklengths 示例:

db2 create table resumes like emp_resume 
 DB20000I SQL 命令成功完成。
 db2 export to emp_resumedata.ixf of ixf messages export.msg select * from emp_resume

导出的行数:8

db2 load from emp_resumedata.ixf of ixf modified by nochecklengths 
messages load.msg insert into resumes 
读取行数 = 8 
跳过行数 = 0 
装入行数 = 8 
拒绝行数 = 0 
删除行数 = 0 
落实行数 = 8

15.8.7 代码页不同注意事项

如果我们导入或装入数据时,表的数据页和输入文件的数据页不匹配,那么数据将无法导入,而且会报一个 SQL0332N 的错误。这种情况下我们可以使用下面的文件类型修饰符。

forcein 文件类型修饰符

forcein 文件类型修饰符允许导入 PC/IXF 文件,指定导入或载入实用工具不会由于代码页不匹配而拒绝数据,并取消代码页之间的转换。该文件类型修饰符在使用时必须谨慎,但是当处理包含了其他情况下无法导入或载入的数据类型或值时,该文件类型修饰符,十分有用。此选项不会理会 PC/IXF 文件与目标数据库中的数据之间的代码页差别。它允许更加灵活地定义兼容列。

db2 create table resumes like emp_resume 
 DB20000I SQL 命令成功完成。
 db2 export to emp_resumedata.ixf of ixf messages export.msg select * from emp_resume

导出的行数:8

db2 load from emp_resumedata.ixf of ixf modified by forcein 
messages load.msg insert into resumes; 
读取行数 = 8 
跳过行数 = 0 
装入行数 = 8 
拒绝行数 = 0 
删除行数 = 0 
落实行数 = 8

codepage 文件类型修饰符

指定一个 ASCII 字符串,用于表示要导入或载入数据的源代码页。如果您需要在运行于不同代码页的系统之间移动数据时避免讹误,那么该文件类型修饰符十分有用。首先会将输入文件中的字符数据从该文件类型修饰符所指定的代码页转换成当前的系统代码页,然后从当前系统代码页转换成数据库代码页。请记住,如果输入文件包含了当前系统代码页不能识别的字符,那么就无法将该字符导入或载入数据库。而且,在代码页转换中占用空间变大的数据可能会被截掉。

db2 create table stafftemp like staff 
 DB20000I SQL 命令成功完成。
 db2 export to staffdata.del of del messages export.msg select * from staff

导出的行数:35

db2 load from staffdata.del of del modified by codepage=850 
messages load.msg insert into stafftemp 
读取行数 = 35 
跳过行数 = 0 
插入行数 = 35 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 35

15.8.8 日期格式

在不同的平台和系统上进行数据移动时,日期格式容易出问题。下面我们讲解几个和日期格式有关的文件类型修饰符。这几个文件类型修饰符仅适用于 ASC 和 DEL 格式。

dateformat

dateformat=" x" 指定一个字符串,用于表示导入或载入数据的日期格式。每个未指定的元素将被赋值为 1 。

考虑导入数据文件 salesdata.asc 的内容为“ 23.04.2004LUCCHESSI++++++Ontario- South+++++++++++10 2 ”:

db2 create table salestemp like sales 
 DB20000I SQL 命令成功完成。
 db2 import from salesdata.asc of asc modified by dateformat="DD.MM.YYYY" 
method l (1 10, 11 25, 26 40, 41 51) messages import.msg insert into salestemp 
读取行数 = 1 
跳过行数 = 0 
插入行数 = 1 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 1

datesiso

指定以 ISO 格式导出所有日期格式:

db2 export to salesdata.del of del modified by datesiso 
messages export.msg select * from sales

timeformat

timeformat="x" 指定一个字符串,用于表示导入或载入数据的时间格式。每个未指定的元素将被赋值为 0 。

考虑载入输入文件 timedata.asc 的内容为“ 10.56 PM ”:

db2 create table times (timeofday time) 
 DB20000I SQL 命令成功完成。
 db2 load from timedata.asc of asc modified by timeformat="HH.MM TT" 
method l (1 8) messages load.msg insert into times 
读取行数 = 1 
跳过行数 = 0 
插入行数 = 1 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 1

timestampformat

timestampformat="x" 指定一个字符串,用于表示导入或载入数据的时间戳格式。在指定 month 和 minute 元素时,一定要避免二义性,因为两者都使用了字母“ m ”。 month 元素必须邻接其他日期元素,而 minute 元素必须邻接其他时间元素:

db2 create table in_traytemp like in_tray 
 DB20000I SQL 命令成功完成。
 db2 insert into in_tray values ('2008-04-16-17.12.30.000000', 
'db2inst1', 'Any subject', 'Any note text') 
 DB20000I SQL 命令成功完成。
 db2 export to in_traydata.del of del modified by 
timestampformat="yyyy.mm.dd hh:mm tt" 
messages export.msg select * from in_tray

导出的行数:4

db2 load from in_traydata.del of del modified by 
timestampformat="yyyy.mm.dd hh:mm tt" 
messages load.msg insert into in_traytemp 
读取行数 = 4 
跳过行数 = 0 
插入行数 = 4 
更新行数 = 0 
拒绝行数 = 0 
落实行数 = 4

15.8.9 XML 问题

随着 DB2 V9.1 中引入了本地 XML 支持,导出 (EXPORT) 实用程序也被扩展,以支持 XML 。如果没有指定任何与 XML 相关的选项而导出一个表 ( 用 XML 数据定义的 ),那么相关的 XML 数据将被写入到与导出的其他关系数据分开的一个或多个文件中。让我们看一个例子。下面的 EXPORT 命令是在 PRODUCT 表上发出的,该表中定义了一个 XML 列:

EXPORT TO prodexport.del DEL MESSAGES msg.out SELECT * FROM product

在这个例子中,导出实用程序将生成两个输出文件。其中一个输出文件是 prodexport.del( 如图 15-9 所示 ),该文件将包含表中的关系数据和 XML Data Specifier(XDS) 。

图 15-9 输出文件 prodexport.del 的内容
图 15-9  输出文件 prodexport.del 的内容
图 15-9 输出文件 prodexport.del 的内容

XDS 是用名为 "XDS" 的一个 XML 标记表示的字符串。它具有一些属性,用于描述关于列中实际的 XML 数据的信息。下面是 XDS 字符串中可能出现的一些属性:

  • FIL:指定包含 XML 数据的文件的文件名。
  • OFF:指定 XML 数据在 FIL 属性指定的文件中的字节偏移量。
  • LEN:指定 FIL 属性中指定的文件中的 XML 数据的字节长度。
  • SCH:指定用于验证 XML 文档的 XML 模式的全限定 SQL 标识符。下一节中将讨论这个属性。

从图 15-9 所示的 prodexport.del 的内容中可以看出,第一个 XML 数据存储在 prodexport.del.001.xml 中,从 0 字节偏移位置开始,长度为 252 个字节。

在这个例子中,导出实用程序生成的另一个文件是 prodexport.del.001.xml( 图 15-10 所示 ),该文件包含 XML 内容。导出的每个 XML 数据都被连接在一起写入到这个文件中。

图 15-10 输出文件 prodexport.del.001.xml 的内容
图 15-10  输出文件 prodexport.del.001.xml 的内容
图 15-10 输出文件 prodexport.del.001.xml 的内容

使用 XML 选项和修饰符导出 XML 数据

与导出大型对象一样,您可以指定被导出 XML 文档的存储路径,还可以指定输出文件的文件名。考虑下面的例子:

EXPORT TO prodexport.del DELXMLTO d:\xmlpath XMLFILE proddesc 
MODIFIED BY XMLINSEPFILES XMLNODECLARATION XMLCHAR 
XMLSAVESCHEMA MESSAGES msg.out SELECT * FROM product

在这个例子中,PRODUCT 表的关系数据被导出到 prodexport.del 文件中。然后,所有 XML 数据都被写入到 XML TO 子句指定的目录 d:\xmlpath 中。包含 XML 数据的文件被命名为 proddesc.ext.xml,其中 ext 是一个序列号 ( 例如 proddesc.001.xml、proddesc.002.xml、proddesc.003.xml 等 ) 。基本文件名是用 XMLFILE 选项定义的。

您也许还注意到,这个例子中使用了一些修饰符。下面对所有与 XML 相关的修饰符作一个总结:

  • XMLINSEPFILES:导致导出实用程序将导出的每个 XML 文档写入到不同的 XML 文件中。
  • XMLNODECLARATION:表明导出 XML 数据无需使用 XML 声明标记。默认情况下,XML 标记被写在 XML 文档的开头,并包括一个编码属性。
  • XMLCHAR:表明 XML 数据以字符码页编码。默认情况下,XML 数据是以 UNICODE 编码的。当使用这个修饰符时,使用的是 codepage 文件类型修饰符或应用程序码页。
  • XMLGRAPHIC:表明无论是 codepage 文件类型修饰符还是应用程序码页,导出的 XML 数据都将以 UTF-16 码页编码。注意,这个例子中没有使用 XMLGRAPHIC 。

我们要介绍的最后一个选项是 XMLSAVESCHEMA 。当插入一个 XML 文档时,可以用一个 XML 模式对其进行验证。 XMLSAVESCHEMA 选项导致导出实用程序保存用于每个导出的 XML 数据的 XML 模式。该模式的全限定 SQL 标识符将被存储为相应的 XML Data Specifier(XDS) 中的一个 SCH 属性。注意,如果没有用 XML 模式验证导出的 XML 文档,或者该模式对象不再存在于数据库中,那么相应的 XDS 中将不包括 SCH 属性。图 15-11 显示了前面的导出例子的结果。

图 15-11 导出结果
图 15-11  导出结果
图 15-11 导出结果

15.9 安全常见问题总结

安全性是一个宽泛的主题,可应用于系统架构的各个不同级别。系统管理员必须持续监控他们的系统,确保系统中采取了适当的安全预防措施。安全性可以应用在系统架构的不同级别上。例如,可以通过安装防火墙来防止外部网络对服务器的未经授权的访问。可以使用一些安全网络协议技术,例如 IPSec,来保证网络上计算机间通信信道的安全性。又如,可以实行严格的密码策略,要求用户选择一个强密码,并经常更换密码。

下面我们总结了一些 DB2 安全使用过程中的最佳安全实践,数据库管理员 (DBA) 和开发人员如果遵循这些最佳实践,可以确保在 DB2 for LUW 中取得最高级别的安全性。这些最佳实践注重于可以通过数据库管理和编程进行控制的一些安全性因素,但是不包括其他可应用于系统中更大范围内的安全技术或策略。本节中列出的最佳实践不分先后顺序,它们的重要性相当,都有助于提高 DB2 数据库服务器的总体安全级别。

15.9.1 从 PUBLIC 撤销隐式的权限和特权

DB2 在内部使用一个名为 PUBLIC 的伪组,对于 PUBLIC 来说,可以为之授予特权,也可以撤销它的特权。 PUBLIC 实际上不是在外部安全设施中定义的一个组,但通过它可以为通过 DB2 认证的用户授予特权。

当创建一个新数据库时,某些数据库权限和特权就会自动授予 PUBLIC,如表 15-5 所示。

表 15-5 创建数据库后被授予 PUBLIC 的权限和特权
权限或特权描 述
BINDADD允许用户在数据库中创建新的包
CREATETAB允许用户在数据库中创建新的表
CONNECT允许用户连接到数据库
IMPLICIT_SCHEMA允许用户在不存在的模式中创建对象 ( 动态地创建模式 )
USERSPACE1 上的 USE 特权允许用户在 USERSPACE1 表空间中创建表或索引
NULLID 模式上的 CREATEIN允许用户在 NULLID 模式中创建对象
模式 SQLJ 上的 CREATEIN允许用户在 SQLJ 模式中创建对象
SYSPROC 模式上所有函数和过程上的 EXECUTE WITH GRANT 特权允许用户调用 SYSPROC 模式中的存储过程和执行该模式中的函数,并且可以将该许可授予其他用户
SQLJ 模式中所有过程上的 EXECUTE WITH GRANT 特权允许用户调用 SQLJ 模式中的存储过程

( 续表 )

权限或特权描 述
NULLID 模式中创建的所有包上的 BIND 和 EXECUTE 特权允许用户绑定 (BIND) 和执行 (EXECUTE)NULLID 模式中的包
SYSIBM 模式中表上的 SELECT 特权允许用户查看系统编目表中的信息
SYSCAT 模式中视图上的 SELECT 特权允许用户查看系统编目视图中的信息
SYSIBMADM 模式中管理视图上的 SELECT 特权允许用户查看这些管理视图中的信息
SYSSTAT 模式中编目视图上的 SELECT
特权
允许用户查看系统编目视图中的信息
SYSTAT 模式中视图上的 UPDATE 特权允许用户更新这些系统编目视图中的统计信息

作为一项最佳实践,在创建一个新的数据库之后,强烈建议您应立即撤销这些被授给PUBLIC的隐式特权。

例如,您可以执行例 15-8 中显示的语句来撤销系统编目视图上的特权和其他被授予 PUBLIC 的隐式特权。不过这个示例还不是最完整的。

例 15-8 创建数据库后撤销 PUBLIC 的隐式特权。
CREATE DATABASE testdb; 
 CONNECT TO testdb; 
 REVOKE BINDADD ON DATABASE FROM PUBLIC; 
 REVOKE CREATETAB ON DATABASE FROM PUBLIC; 
 REVOKE CONNECT ON DATABASE FROM PUBLIC; 
 REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC; 
 REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.COLAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.DBAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.INDEXAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.PACKAGEAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.PASSTHRUAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.ROUTINEAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.SCHEMAAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.SECURITYLABELACCESS FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.SECURITYPOLICYEXEMPTIONS FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.SEQUENCEAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.SURROGATEAUTHIDSFROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.TBSPACEAUTH FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.XSROBJECTAUTHFROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.AUTHORIZATIONIDS FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.OBJECTOWNERS FROM PUBLIC; 
 REVOKE SELECT ON TABLE SYSCAT.PRIVILEGES FROM PUBLIC;

从 DB2 V9.1 开始,CREATE DATABASE 命令语法增加了 RESTRICTIVE 选项。如果该命令中包括了 RESTRICTIVE 选项,那么会导致 RESTRICT_ACCESS 数据库配置参数被设置为 YES,同时不自动授予 PUBLIC 任何特权。如果忽略了 RESTRICTIVE 选项,那么 RESTRICT_ACCESS 数据库配置参数被设置为 NO,前述所有特权都将自动授予 PUBLIC 。

如果已使用 RESTRICTIVE 选项创建数据库,并且想要检查是否限制了授予 PUBLIC 的许可权,那么可以发出以下查询来验证 PUBLIC 可以访问哪些模式:

SELECT DISTINCT OBJECTSCHEMA FROM SYSIBMADM.PRIVILEGES 
WHERE AUTHID='PUBLIC' 

 OBJECTSCHEMA 
 ------------ 
 SYSFUNSYSIBMSYSPROC

注意:

从 DB2 V9.1 开始,SYSIBMADM.PRIVILEGES 管理视图就可用了。

15.9.2 保护系统编目视图

由于系统目录视图描述数据库中的每个对象,因此如果您的数据库中有敏感数据,那么您可能想要限制对它们的访问。

对于 DB2 V9.1 之前的版本来说,在创建数据库期间,会将对系统目录视图的 SELECT 特权授予 PUBLIC 。大多数情况下,这样做不会引起任何安全性问题。但是,对于特别敏感的数据来说,这可能不恰当,因为这些表描述数据库中的每个对象。如果是这种情况,那么就需要考虑从 PUBLIC 撤销 SELECT 特权;然后按需要将 SELECT 特权授予特定用户。授予和撤销对系统目录视图的 SELECT 特权与对任何其他视图授予和撤销权限的方式相同,但是必须具有 SYSADM 或 DBADM 权限,才可执行此操作。

假如,如果您不想让任何用户知道其他用户有权访问哪些对象,就应考虑限制对系统目录和管理视图中相关权限的编目视图的访问权。这将防止有关用户特权的信息对可访问该数据库的任何人可用。

还应检查对其收集统计信息的列。记录在系统目录中的某些统计信息可能包含您环境中的敏感信息的数据值。如果这些统计信息包含敏感数据,那么您可能希望从 PUBLIC 撤销对 SYSCAT.COLUMNS、SYSSTAT.TABLES 和 SYSCAT.COLDIST 目录视图的 SELECT 特权。

如果希望限制对系统目录视图的访问,那么您可以定义视图,来让每个用户检索和它自己相关特权的信息。

例如,视图 MYSELECTS 包括每个特定的表的所有者和名称,已将该表的 SELECT 特权直接授予了一个用户:

CREATE VIEW MYSELECTS AS SELECT TABSCHEMA, TABNAME 
FROM SYSCAT.TABAUTH WHERE GRANTEETYPE = 'U' 
AND GRANTEE = USER AND SELECTAUTH = 'Y'

此语句中的关键字 USER 为当前会话的用户。

如下语句使此视图可供每个授权用户使用:

GRANT SELECT ON TABLE MYSELECTS TO PUBLIC

最后,应记住要通过发出下列两条语句来撤销对视图和基本表的 SELECT 特权:

REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC 
 REVOKE SELECT ON TABLE SYSIBM.SYSTABAUTH FROM PUBLIC

作为一项最佳实践,在创建一个新的数据库之后,强烈建议您保护系统编目视图。

15.9.3 创建实例用户显式指定组

在安装完 DB2 后,我们需要创建实例。而在 Linux/UNIX 上创建实例需要创建一个和实例同名的用户。我们在创建实例用户时,一定要做好规划,显式地指定实例组。在有的操作系统上,如果创建实例时不指定组,那么操作系统就隐含地把这个用户指定到一个组中。然后这个实例用户所在的组隐含地具有该实例最高的 SYSADM 权限。这样这个组中所有用户都具有最高的 SYSADM 权限。这非常危险。所以,创建实例用户时必须显式地指定组。下面我们举一个例子。

例如在 AIX 上我要创建一个 myinst 的实例,如果我在创建这个 myinst 用户时没有创建组,那么这个 myinst 用户会自动加入到 staff 组中。实例创建后,staff 组就具有这个实例的 SYSADM 权限。我们都知道 staff 中包含操作系统几乎所有用户。这样是非常危险的。

作为一项最佳实践,建议您创建实例时,做好用户组规划,显式指定实例组。

15.9.4 为 SYSxxx_GROUP 参数使用显式值

DB2 定义了一个超级用户权限层次结构 (SYSADM、SYSCTRL、SYSMAINT 和 SYSMON),每个权限可以执行一个子集的管理操作,例如创建数据库、强制使用户离开系统、进行数据库备份。与它们相关联的实例级参数 (SYSADM_GROUP、SYSCTRL_GROUP、SYSMAIN_GROUP 和 SYSMON_GROUP) 用于控制哪些用户可以继承哪些权限。

每个参数可以被设置为拥有该权限的一个用户组 ( 在外部安全设施中定义 ) 的名称。设置好之后,指定组中的所有用户就继承了该权限。

例如,如果有一个名为 DBAGRP1 的操作系统组,所有 DBA 用户都是这个组的成员,如果使用例 15-9 中所示的命令将 SYSADM_GROUP 实例参数的值设为 DBAGRP1,那么这个组中的所有用户都将继承 SYSADM 权限。

例 15-9 更新 SYSADM_GROUP 实例参数。

UPDATE DBM CFG USING SYSADM_GROUP dbagrp1

在 Windows 上进行默认的 DB2 安装时,这些参数的值被默认设置为 NULL 。这意味着超级用户权限被授给属于本地 Administrators 组的所有有效用户账户。在 Linux 和 UNIX 平台上,NULL 值被默认地赋给实例所有者的主组,完成安装后,默认情况下这个组只包含用户 ID 和实例所有者。

作为一项最佳实践,应该将每个实例级权限参数的默认值改为一个显式的组名,以防止不可控的超级用户访问。

在很多企业中,一个 DBA 扮演着多种角色,因而这些参数可以设置为相同的组名。而在大型环境中,由多个 DBA 负责一个系统,因此可以使用不同的组名。除了确保这些参数具有显式值以外,还应该尽量确保参数值所指定的组中的所有用户都确实有必要成为这个组的成员。如果没有这个必要,那么应该从这个组中删除!由于用户和组账户的管理是在 DB2 之外处理的,因此 DB2 不会仔细检查用户应不应该成为一个组的成员。

15.9.5 跟踪隐式的特权

如前所述,创建一个新的数据库时,PUBLIC 被隐式地授予一些特权。实际上,并不是只有此时才会授予隐式的特权。在某些情况下,当一个用户创建一个数据库对象 ( 例如一个表或者包 ) 或授予 DBADM 权限级别时,数据库管理器会隐式地将一些特权授给用户。理解被隐式授予的特权有哪些,以及这些隐式特权所蕴涵的安全意义 ( 表 15-6 所示 ),这一点非常重要。

表 15-6 授予不同动作的隐式特权
动 作授予执行该动作的用户的隐式特权
创建新的
数据库
将 GRANT of DBADM 权限以及 BINDADD、CONNECT、CREATETAB、CREATE_EXTERNAL_ROUTINE、CREATE_NOT_FENCED_ROUTINE、IMPLICIT_ SCHEMA、LOAD 和 QUIESCE_CONNECT 权限授予创建者 (SYSADM 或 SYSCTRL)
将 GRANT of BINDADD、CREATETAB、CONNECT 和 IMPLICIT_SCHEMA 授予 PUBLIC
将 USERSPACE1 表空间上的 USE 特权授予 PUBLIC
将 SYSPROC 模式中所有过程和函数的 EXECUTE WITH GRANT 特权授予 PUBLIC
将 SQLJ 模式中所有过程上的 EXECUTE with GRANT 特权授予 PUBLIC
将 NULLID 模式中所有包上的 BIND 和 EXECUTE 特权授予 PUBLIC
将 SQLJ 和 NULLID 模式上的 CREATEIN 授予 PUBLIC
将 SYSIBM 编目上的 SELECT 授予 PUBLIC
将 SYSCAT 编目视图上的 SELECT 特权授予 PUBLIC
将 SYSIBMADM 管理视图上的 SELECT 特权授予 PUBLIC
将 SYSSTAT 编目视图上的 SELECT 特权授予 PUBLIC
将 SYSSTAT 编目视图上的 UPDATE 特权授予 PUBLIC
授予 DBADM
权限
将 GRANT of BINDADD、CONNECT、CREATETAB、CREATE_EXTERNAL_ ROUTINE、CREATE_NOT_FENCED_ROUTINE、IMPLICIT_SCHEMA、LOAD 和 QUIESCE_CONNECT 授予目标用户
模式当显式地创建模式时,CREATEIN、ALTERIN、DROPIN 权限被授予创建模式的用户
当隐式地创建模式时,另外还有 CREATEIN 权限被授予 PUBLIC
创建对象 ( 表、索引、包 )将 GRANT of CONTROL 授予对象创建者
创建视图仅当用户对视图定义中引用的所有表、视图和昵称均有 CONTROL 特权时,才为其授予 Grant of CONTROL 特权

作为一项最佳实践,应该仔细检查和跟踪执行某动作时所授予的隐式特权。如果以后撤销这个动作,那么应撤销任何隐式的特权。

例如,假设您一开始将 DBADM 权限授予用户 niuniu,而随后您又决定撤销此权限。为了撤销 niuniu 的 DBADM 权限,可以使用以下语句:

REVOKE DBADM ON DATABASE FROM USER niuniu

执行该语句之后,niuniu 将不再拥有 DBADM 权限;然而,他仍然拥有数据库上的 GRANT、BINDADD、CONNECT、CREATETAB、CREATE_EXTERNAL_ROUTINE、CREATE_NOT_FENCED_ROUTINE、IMPLICIT_SCHEMA、LOAD 和 QUIESCE_CONNECT 权限,这些权限是在一开始授予 niuniu 权限时隐式地授给该用户的。这些权限需要显式地从 niuniu 那里撤销。

15.9.6 不授予不必要的特权

开发一个应用程序时,通常开发人员很少一开始就考虑安全性问题。例如,开发人员通常会用一个超级用户账户 (DBADM 或 SYSADM) 来开发和测试他们的应用程序,以免在运行代码时不断碰到安全错误消息。通过使用“控制中心”,可以很容易为一个用户授予所有数据库许可和权限,如图 15-12 所示。

图 15-12 通过“控制中心”授予特权
图 15-12   通过“控制中心”授予特权
图 15-12 通过“控制中心”授予特权

通常,完成了应用程序的开发和测试阶段后,在开发过程中为解决安全错误消息而授予的特权仍然保留在那里,而实际上它们已经没有存在的必要了。

作为一项最佳实践,应仔细检查在安装和配置应用程序的过程中授予每个用户的特权。确保所有被授出的许可和特权都确实是有必要的。

对于不熟悉 DB2 安全模型的开发人员来说,他们往往因为贪图简单而通过“控制中心” ( 见图 15-12) 为自己授予所有可用的特权,以避免安全错误消息。您应该确保所有被授予的特权和权限都确实是有必要的。

15.9.7 使用加密的 AUTHENTICATION 模式

在本书的第 13 章我们已经讲过认证机制,身份认证是指使用一种安全机制对用户 ID 和密码进行认证的过程。用户和组的身份认证是在 DB2 外部的一个设施 ( 例如操作系统、域控制器或 Kerberos 安全系统 ) 中进行管理的。实际的身份认证位置由实例参数 AUTHENTICATION 的值来决定。为了防止用户名和密码以及数据通过网络在客户机与服务器之间传输的时候被窃取,建议采用加密的认证方式。表 15-7 总结了各种加密的身份认证选项。

表 15-7 加密的 AUTHENTICATION 模式总结
AUTHENTICATION 模式描 述
SERVER_ENCRYPT规定在服务器上定义的安全设施上进行身份认证。如果在连接尝试期间指定了一个用户 ID 和密码,那么该用户 ID 和密码将与服务器上定义的有效用户 ID 和密码组合相比较,以确定是否允许该用户访问实例或数据库。在这种模式下,在网络上传输用户 ID 和密码时,它们将被加密
KRB_SERVER_ENCRYPT规定服务器接受 KERBEROS 认证或加密的 SERVER 身份认证模式
DATA_ENCRYPT规定服务器接受加密的 SERVER 身份认证模式和对用户数据的加密。
使用这种身份认证类型时,以下用户数据也将被加密:
SQL 和 XQuery* 语句
SQL 程序变量数据
完成对 SQL 或 XQuery* 语句的处理后,来自服务器的包括对数据的描述的输出数据
一个查询的部分或全部结果集数据
Large object(LOB) 数据流
SQLDA 描述
DATA_ENCRYPT_CMP规定服务器接受 SERVER 身份认证模式和对用户数据的加密。
这种身份认证类型可以与不支持 DATA_ENCRYPT 身份认证类型的下级产品相兼容。这些产品可以使用 SERVER_ENCRYPT 身份认证类型,而不必加密用户数据。支持 DATA_ENCRYPT 身份认证类型的产品则必须使用它
GSS_SERVER_ENCRYPT规定服务器接受基于 GSS API 插件的身份认证或加密的服务器身份认证模式

注意:

从 DB2 V9 开始,XQuery 是官方支持的查询语言,我们会在《 DB2 应用开发实战指导》一书中讲解 XQuery 。

作为一项最佳实践,为了防止用户名和密码以及数据通过网络在客户机与服务器之间传输的时候被窃取,建议采用加密的认证方式。

应该为您的环境选择什么样的身份认证模式,这是由数据的敏感级别决定的。如果所有数据都是敏感的,那么应该选择 DATA_ENCRYPT 身份认证模式,这种身份认证模式会对客户机和服务器之间传输的很多数据进行加密。如果只有一小部分数据是敏感的,那么可以选择使用 SERVER_ENCRYPT 模式,这样可以保证密码得到加密,而敏感数据则可以通过不同的机制来得到保护。

注意,AUTHENTICATION 参数是在实例级上设置的,这意味着在相同实例中创建的数据库将使用共同的身份认证模式。如果有两个数据库,每个数据库需要不同的身份认证模式,那么就需要在不同的实例中创建这两个数据库。

15.9.8 使用独立 ID 创建和拥有对象

创建一个数据库对象时,执行 DDL 语句创建它的那个用户 ID 会拥有该对象。如果那个用户 ID 后来不用了 ( 例如这位用户离开了公司 ),或者该用户不再需要数据库对象上的数据库访问或权限,那么 DBA 必须撤销该用户的特权。这将导致其他有依赖关系的数据库对象或包失效 ( 或者不起作用 ) 。

一旦成功创建了数据库对象或程序包,只要对象的创建者或程序包的绑定者继续持有它所引用的数据库对象上必要的特权,那么这个数据库对象或包就被认为是有效的 ( 而不是不起作用 ) 。因此,当对象创建者或包的绑定者的特权被撤销时,包含静态 SQL 语句的对象和包将失效。

这里简要描述一下这个过程:

(1) 在操作系统中创建一个新的用户 niuniu,并使这个用户无效,使之不能被使用。

(2) 从所有操作系统组中删除这个用户 ID,并确认已将该用户所属的用户或组的 CONNECT 特权撤销,以确保该用户 ID 不具有 CONNECT 权限。

(3) 当需要创建新的数据库对象时,或者必须执行其他的 DDL 语句时,再通过 GRANT 语句将执行该动作所需的必要特权授给这个新的用户 ID 。例如,为了创建表 T1 上的一个视图,必须将表 T1 上的 SELECT 特权授给新的用户 niuniu:

GRANT SELECT ON TABLE T1 TO USER niuniu

将当前会话授权 ID 暂时设置为新的用户 niuniu:

SET SESSION_USER = NIUNIU

在这个授权 ID 下,创建数据库对象和绑定包。例如,为了创建表 T1 上的视图,可以执行以下语句:

CREATE VIEW V1 AS SELECT * FROM T1

(4) 创建好所有必需的数据库对象和包之后,使用组成员关系和组特权来控制对所创建的数据库对象和包的访问:

GRANT SELECT ON VIEW V1 TO GROUP1 
 GRANT EXECUTE ON PKG TO GROUP1

(5) 完成上述操作之后,执行以下两条语句,将当前会话授权 ID 重新设置成常规授权 ID:

SET SESSION_USER = SYSTEM_USER 
 or 
 SET SESSION_USER = NIUNIU
作为一项最佳实践,使用独立的ID来创建和拥有对象。

这种方法可以确保使用一个独立的用户 ID 与创建数据库对象、绑定包和授予特权的角色相关联。随着时间的推移,面对着用户的来来去去,这样将大大简化数据库模式和特权的管理。这种方法虽然比较麻烦,但是可以有效地避免一些安全问题的出现。

15.9.9 使用视图控制数据访问

控制表数据访问的一种常见方法是使用视图。您不必将整个表数据都公开给应用程序用户,而是可以基于表中的部分列创建一个视图。例如,假设例 15-9 中定义的表包含保险单信息:

例 15-9 包含保险数据的示例表定义。
CREATE TABLE INSURANCE ( CUSTID INTEGER NOT NULL PRIMARY KEY、 
SALARY FLOAT、 RENEWAL_MONTH VARCHAR(3)、---- 年龄 SEX CHAR(1)、 
MARITAL_STATUS CHAR(1)、 NUM_DEPENDENTS INTEGER、 
YEAR_1ST_POLICY INTEGER、 NUM_CLAIMS INTEGER、 
----- 索赔历史 CYCLES INTEGER、 COMMUTE_DIST FLOAT);

假设某家保险公司的代理公司希望可以访问客户数据,以便分析客户数据,为客户提供更合适的产品。然而,假设根据法律该保险公司不能泄漏个人的年龄或他们索赔的金额。为了满足这些需求,可以在这个表上按照例 15-10 所示方式定义一个视图,视图中不包括客户的 RENEWAL_MONTH 和索赔历史:

例 15-10 在包含保险数据的表上定义视图。
CREATE VIEW ins_v1_sis_comp_1 AS  SELECT custid、salary、sex、marital_status、
num_dependents、year_1st_policy、cycles、commute_dist FROM insurance);

于是,通过这个视图就可以控制对数据的访问,而不必使用基表。使用 GRANT 语句可以控制对视图的访问,以免所有用户都能查看数据。例如,您可以控制谁能对该视图执行 SELECT、INSERT、UPDATE 和 DELETE 操作。

作为一项最佳实践,在您想隐藏表中的部分列或行的时候,应该使用视图来控制对表的访问。底层表定义发生变化时,使用视图还有助于使应用程序不受影响。和表一样,也可以授予视图上的特定特权。当然除了使用视图,我们还可以使用LBAC

15.9.10 使用存储过程控制数据访问

控制对表数据的访问的另一种方法是使用存储过程。存储过程是一组 SQL 语句,这组 SQL 语句形成一个逻辑单元,用于执行特定的任务。存储过程是在数据服务器上创建和运行的,用于封装一组经常要运行的操作或查询。例如,一个雇员数据库上的操作 ( 雇用、解雇、升职、查找 ) 可以编写成存储过程,由应用程序来调用,而不是直接编写在应用程序中。存储过程可以带不同的参数和结果来编译和执行,它们可以有输入、输出和输入 / 输出参数的任意组合。例 15-11 展示了一个存储过程,该存储过程根据业绩评分评定雇员新的工资和奖金。

例 15-11 根据业绩评分评定雇员的工资和奖金。
CREATE PROCEDURE UPDATE_SALARY ( IN empNum CHAR(6)、 IN rating SMALLINT) 
LANGUAGE SQL BEGIN IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10、
bonus = 1500 WHERE empno = empNum; ELSE UPDATE employee 
SET salary = salary * 1.05、bonus = 1000 WHERE empno = empNum; 
 END IF; END

该存储过程接收两个输入参数,即雇员号和一个评分,然后根据给定的评分更新该雇员的工资和奖金。对于获得评分“ 1 ”的雇员,为他加薪 10%,并提供 $1500 的奖金。对于所有其他评分,为雇员加薪 5%,并提供 $1000 的奖金。

作为一项最佳实践,应考虑使用存储过程来控制对数据的访问。通过对存储过程的调用,将直接允许对表的访问,因此,限制一个用户可以在表上执行的动作,也将同时控制什么用户可以调用存储过程。

如今,很多应用程序的数据库层都设计为存储过程模块化。也就是说,所有数据库访问都可通过存储过程调用来执行。想要执行某个事务 ( 例如更新订单或者购买某个产品 ) 的应用程序,只需从应用程序中调用存储过程即可。这种方法的一个附带好处是,所有逻辑都集中放在一个地方,这使得管理和维护更加容易,而且也使其他应用程序可以重用其功能。这种方法与目前市场上比较流行的面向服务的架构 (Service Oriented Architecture,SOA) 非常吻合。

还可以通过 GRANT 和 REVOKE 语句控制对存储过程的访问。想要调用存储过程的用户需要被授予 EXECUTE 权限。根据绑定选项和 SQL 语句是静态还是动态,存储过程引用到的各对象可能需要更多的特权。

15.9.11 使用 LBAC 控制数据访问

DB2 V9 中一个新的、令人激动的特性是基于标签的访问控制 (Label Based Access Control,LBAC) 。 LBAC 使您可以决定谁拥有不同行和列上的写访问权限,谁拥有读访问权限。

一种特殊的新的安全管理员权限 (SECADM) 被用于配置 LBAC,具体做法是创建安全策略,安全策略实际上定义了用于决定谁可以访问什么数据的标准。创建好安全策略后,安全管理员创建安全标签,安全标签也是安全策略的一部分。标签可以基于任何标准,例如工作名称、用户是否是管理人员或者用户是否属于某个特定的部门。创建好安全标签之后,便可以将安全标签与表中的行和列相关联,以保护其中保存的数据。安全管理员通过为用户授予安全标签来允许用户访问受保护的数据。当一个用户试图访问受保护的数据时,该用户的安全标签将与用于保护该数据的安全标签相比较。

安全管理员还可以为用户授予豁免权 (exemption) 。豁免权使用户可以访问其安全标签不允许其访问的受保护数据。如果一个用户试图访问一个受保护的列,而他们的 LBAC 凭证又不允许他们访问该列,那么这样的访问将失败,用户会收到一条错误消息。

作为一项最佳实践,应考虑使用LBAC作为控制对敏感数据访问的一种方法。LBAC很容易配置,您可以对它进行定制,以满足特定的安全环境。

使用这种非常易于定制的新安全特性可以让开发人员将精力集中在业务逻辑的开发上,而不用关心太多的安全问题。通过使用 LBAC,我们可以在数据服务器实现特定的安全需求。

15.9.12 对重要敏感数据加密

数据库已经能够阻止未经授权的人看到其中的数据,这通常是通过数据库管理器中的特权和权限来实现的。我们都知道 DBA 对表中的数据有完全的访问权限,但是在有些环境下,表中可能还有数据拥有者不希望任何其他人看到的某些信息,例如病人姓名等。特别是基于 Web 的应用程序,这一问题就更加明显了。在这种应用程序中,用户输入的数据 ( 比如信用卡号 ) 需要保存起来,以备同一用户以后使用该应用程序。同时,用户拥有者希望能够确保任何其他人不能访问这种数据。

为了实现这种功能,DB2 内置了一些 SQL 函数,这些函数允许应用程序加密和解密数据。当将数据插入到数据库中时,可以使用用户提供的加密密码对其加密。当检索该数据的时候,必须提供相同的密码才能解密数据。对于要多次使用同一个密码的情况,可以使用一个赋值语句设置 ENCRYPTION PASSWORD 值,并令其在某次连接期间内有效。

下面显示了这些常用的加密函数:

  • Encrypt (StringDataToEncrypt,PasswordOrPhrase,PasswordHint)
  • Decrypt_Char(EncryptedData,PasswordOrPhrase)
  • GetHint(EncryptedData)
  • Set Encryption Password

用于对数据加密的算法是一个 RC2 分组密码 (block cipher),它带有一个 128 位的密钥。这个 128 位的密钥是通过消息摘要从密码得来的。加密密码与 DB2 认证无关,仅用于数据的加密和解密。这里可以提供一个可选的参数 PasswordHint,这是一个字符串,可以帮助用户记忆用于对数据加密的 PasswordOrPhrase( 例如,可以使用 'George' 作为记忆 'Washington' 的提示 ) 。

1. 列级加密

列级加密 (column level encryption) 意味着对一个给定列中的所有值都使用相同的密码进行加密。这种类型的加密可以在视图中使用,也可以在使用了一个公共密码的情况下使用。当对一个或多个表中所有的行使用相同的密钥时,ENCRYPTION PASSWORD 专用寄存器将十分有用。

例 15-12 这个例子使用 ENCRYPTION PASSWORD 值来保存加密密码。它对雇员的社会保险号进行加密,并以经过加密的形式将其存储在 EMP 表中。
db2 create table emp (ssn varchar(124) for bit data); 
 db2 set encryption password = 'Ben123'; 
 db2 insert into emp (ssn) values(encrypt('289-46-8832')); 
 db2 insert into emp (ssn) values(encrypt('222-46-1904')); 
 db2 insert into emp (ssn) values(encrypt('765-23-3221')); 
 db2 select decrypt_char(ssn) from emp;
例 15-13 这个例子在结合使用视图的情况下使用 ENCRYPTION PASSWORD 值来保存加密密码。下面的语句声明了 EMP 表的一个视图:
create view clear_ssn (ssn) as select decrypt_char(ssn) from emp;

在应用程序代码中,我们将 ENCRYPTION PASSWORD 设置为 'Ben123',现在可以使用 clear_ssn 视图了。

set encryption password = 'Ben123'; select ssn from clear_ssn;

行 - 列 ( 单元格 ) 或集合 - 列级加密

行 - 列 ( 单元格 ) 或集合 - 列 (Set-Column) 级加密意味着在一个加密数据列内使用多个不同的密码。例如,Web 站点可能需要保存客户信用卡号 (ccn) 。在这个数据库中,每个客户可以使用他自己的密码或短语来加密 ccn 。

例 15-14 Web 应用程序收集关于客户的用户信息。这种信息包括客户名称 ( 存储在宿主变量 custname 中 )、信用卡号 ( 存储在宿主变量 cardnum 中 ) 和密码 ( 存储在宿主变量 userpswd 中 ) 。应用程序像下面这样执行客户信息的插入操作:
insert into customer (ccn, name) values(encrypt(:cardnum, :userpswd), :custname)

当应用程序需要重新显示某客户的信用卡信息时,客户要输入密码,同样该密码也要存储在宿主变量 userpswd 中。之后,可以像下面这样检索该 ccn:

select decrypt_char(ccn, :userpswd) from customer where name = :custname;

15-15这个例子使用提示来帮助客户记忆他们的密码。这里使用与例 15-14 相同的应用程序,该应用程序将提示保存到宿主变量 pswdhint 中。假设 userpswd 的值是 'Chamonix',pswdhint 的值是 'Ski Holiday' 。

insert into customer (ccn, name) 
 values(encrypt(:cardnum, :userpswd, :pswdhint), :custname)

如果客户请求关于所使用的密码的提示,那么可以使用下面的查询:

select gethint(ccn) into :pswdhint from customer where name = :custname; 
 pswdhint 的值被设置为 "Ski Holiday" 。

加密非字符值

数值和日期 / 时间数据类型的加密通过强制类型转换得到间接的支持。非字符的 SQL 类型通过强制转换为 "varchar" 或 "char" 就可以被加密了。

例 15-16 加密和解密 TIMESTAMP 数据时用到的强制类型转换函数。
-- Create a table to store our encrypted value 
 create table etemp (c1 varchar(124) for bit data); 
 set encryption password 'next password'; 
 -- Store encrypted timestamp 
 insert into etemp values encrypt(char(CURRENT TIMESTAMP)); 
 -- Select & decrypt timestamp 
 select timestamp(decrypt_char(c1)) from etemp;
例 15-17 加密 / 解密 double 数据。
set encryption password 'next password'; 
 insert into etemp values encrypt(char(1.11111002E5)); 
 select double(decrypt_char(c1)) from etemp;
作为一项最佳实践,建议对表中的重要敏感数据加密。

加密,就其本质而言,会使大部分 SQL 语句慢下来。但是如果多加注意,多加判断,还是可以将大量的额外开销降至最低。而且,加密数据对于数据库的设计来说有着很大的影响。通常,您需要对一个模式中的一些敏感数据元素进行加密,例如社会保险号、信用卡号、病人姓名等等。

15.10 SQL0805 和 SQL0818 错误

SQL0818错误

SQL0818 错误是由于绑定文件中头部的时间戳和应用程序本身的时间戳及数据库应用程序包内部的时间戳不一致而造成的。

在静态 SQL 嵌入 C/C++ 编程中,存在 3 个 SQL 时间戳,分别如下:

第 1 个在绑定文件的头部,可以通过 db2bfd 参看:

/home/switch$ db2bfd -s -b -v fkapp.bnd 
 fkapp.bnd: Header Contents 
 Header Fields: 
 Field Value 
 ----- ----- 
 releaseNum 0x700 
 Endian 0x4c 
 numHvars 2 
 maxSect 1 
 numStmt 12 
 optInternalCnt 4 
 optCount 9 
 Name Value 
 ------------------ ----- 
 Isolation Level Cursor Stability 
 Creator 
				 "ORACLE " 
 App Name  "FKAPP " 
 Timestamp 
				 "QAqjTJDV:2005/03/09 19:35:42:16" 
 Cnulreqd 
				 Yes 
 Sql Error No package 
 Validate 
				 Bind 
 Date Default/local 
 Time 
				 Default/local 
 *** All other options are using default settings as specified by the server ***

第 2 个时间戳在数据库的系统表中,执行如下命令:

db2 select unique_id from syscat.packages where pkgname='FKAPP'

得出应用程序的时间戳,如下所示:

/home/switch$ db2 select unique_id from syscat.packages where pkgname='FKAPP' 
 UNIQUE_ID 
 --------- 
 QAqjTJDV

第 3 个时间戳包含在程序 fkapp.c 的头部,在程序编译后自动包含在可执行应用程序的头部,所以这 3 个时间戳必须一致,否则就会出现 SQL0818 的错误。

在清楚了产生 SQL0818 错误的原因后,下面我们先把数据库中时间戳不一致的应用程序包 (package) 删除掉:

db2 drop package pkgname

然后重新绑定生成新的应用程序包 (package)db2 bind *.bnd 即可。

SQL0805 错误

SQL0805N 错误是指应用程序在执行时在数据库中找不到应用程序包 "pkgname" 。不能完成语句,因为未在 syscat.packages 系统表目录中找到必要的程序包。 "<pkgname>" 的格式为:

  • 'pkgschema.pkgname 0Xcontoken',其中一致性标记以十六进制给出。
  • 'pkgschema.pkgname.pkgversion',如果程序包版本为空字符串,则名称省略 '.pkgversion' 。
  • '%.pkgname',如果设置了 CURRENT PACKAGE PATH,那么 CURRENT PACKAGE PATH 中模式名的设置是由百分比字符 ('%') 来指示的。

造成 SQL0805 错误的可能原因是:

  • 程序包未绑定或已删除。
  • 若试图运行 DB2 实用程序,则 DB2 实用程序可能需要重新绑定至数据库。
  • '%.pkgname',虽然设置了 CURRENT PACKAGE PATH,但是在 CURRENT PACKAGE PATH 中的任何模式中都找不到名称为 'pkgname' 的程序包。

注意,当对给定的 package-schema.package-name 使用版本标识时,可能有以相同的程序包模式和程序包名定义的程序包,但是未找到正确的程序包,原因是现有程序包与请求的版本或一致性标记不一致。程序包必须与程序包名的所有 3 个部分相匹配。当正在使用多个版本时,导致出现此消息的附加原因为:

  • 正在执行的应用程序的版本已预编译、编译和链接,但是未绑定,或已绑定但是后来删除了该版本的程序包。
  • 应用程序已预编译和绑定,但是未编译或链接,所以正在执行的应用程序不是最新的。
  • 程序包由与生成编译并链接至应用程序可执行文件的已修改源文件的预编译不同的源文件预编译生成的绑定文件绑定。
  • 新应用程序同与现有的程序包相同的名称 ( 和版本 ) 绑定,这样就替换了现有的程序包。如果运行与替换的程序包相关联的应用程序,则导致此错误。

在所有上述情况下,请求的一致性标记与现有版本的一致性标记不匹配,因此认为未找到程序包。

那么如何解决呢?可以采用以下方法:

指定正确的程序包名或绑定该程序。若正在运行的应用程序未与数据库绑定,则与数据库管理员联系以执行必需的绑定。确保正在执行的应用程序或对象模块是与程序包的预编译和绑定相关联的已编译和链接的修改源代码。

如果设置了 CURRENT PACKAGE PATH,则确保在 CURRENT PACKAGE PATH 中指定了包括程序包的模式。

可以使用下列 SQL 语句来查询目录,以确定是否有程序包的不同版本:

SELECT PKGSCHEMA, PKGNAME, PKGVERSION, UNIQUE_ID 
FROM SYSCAT.PACKAGES 
WHERE PKGSCHEMA='pkgschema' and PKGNAME='pkgname'.

注意 UNIQUE_ID 列与一致性标记相对应。

若 DB2 实用程序需要重新绑定至数据库,则当连接至数据库时,数据库管理员可以通过从实例的 bnd 子目录发出下列 CLP 命令之一来完成此操作: 

  • 对于 DB2 实用程序,发出“ DB2 bind @db2ubind.lst blocking all grant public ”。
  • 对于 CLI,发出“ DB2 bind @db2cli.lst blocking all grant public ”。

相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=405928
ArticleTitle=developerWorks 图书频道: 循序渐进 DB2 —— DBA 系统管理、运维与应用案例,第 15 章
publish-date=06302009