【IT168 专稿】DB2 V9新增了可用来压缩数据对象的基于字典的行压缩功能｡在压缩数据时,通过使用较少的数据库页来表示相同数据,从而达到节省磁盘存储空间的目的｡对于那些行中包含重复模式的大型表,将能从此功能中受益｡数据行压缩(COMPRESS子句)可与现有的空间值压缩(VALUE COMPRESS子句)一起使用｡由于数据行压缩是DB2 V9新增的功能,所以很多用户对其特性不是很了解,本文将重点介绍DB2 V9数据行压缩功能,并通过实际的例子来帮助大家理解和提高｡
简介
在版本9之前,DB2有三种方式的压缩,分别是空间值压缩(VALUE COMPRESS子句)､索引压缩(MDC技术)和数据库备份压缩｡
使用空间值压缩(VALUE COMPRESS子句)时,不会将变长数据类型(VARCHAR､VARGRAPHICS､LONG VARCHAR､LONG VARGRAPHIC､BLOB､CLOB和DBCLOB)的 空值(NULL)和零长度数据存储在磁盘上｡只有与这些数据类型相关联的开销值才会占用磁盘空间｡如果使用了VALUE COMPRESS,那么还可以使用可选COMPRESS SYSTEM DEFAULT选项来进一步减少磁盘空间的使用量｡如果插入的或更新的值等于列的数据类型的系统缺省值,那么使用的磁盘空间最少｡缺省值将不会存储在磁盘上｡支持COMPRESS SYSTEM DEFAULT的数据类型包括所有数字类型列､定长字符和定长图形字符串数据类型｡这表示零和空格可以压缩｡
通过在创建表的时候指定ORGANIZE BY DIMENSIONS选项,将对表的启用MDC功能,可以指定一个或多个键作纬｡MDC是使用基于块的索引, 块索引指向记录块或记录组,而不是指向单个记录｡通过从物理上根据群集值将MDC表中的数据组织成块,然后使用块索引来存取这些块,能显著地改善性能｡在MDC中,群集索引是基于块的｡这些索引比常规的基于记录的索引要小很多,因此,占用的磁盘空间更少,并且扫描时速度会更快｡
如果数据库中的数据量比较大,可以在备份数据库的时候通过指定COMPRESS选项使用备份压缩功能,从而是备份映像文件需要的存储空间最小｡
DB2 V9新增了可用来压缩数据对象的基于字典的行压缩功能｡在压缩数据时,通过使用较少的数据库页来表示相同数据,从而达到节省磁盘存储空间的目的｡对于那些行中包含重复模式的大型表,将能从此功能中受益｡数据行压缩(COMPRESS子句)可与现有的空间值压缩(VALUE COMPRESS子句)一起使用｡对于使用行压缩的表,查询性能可能有所提高｡可能需要更少的 I/O 操作来访问压缩数据,并且在压缩后,可以将更多数据高速缓存在缓冲池中｡由于用户数据压缩在日志记录内,因此日志记录可能会变小｡对于 UPDATE 日志记录,则可能不会出现这种情况｡与行压缩关联的成本取决于压缩和解压缩数据所需的额外 CPU 周期｡在访问行中的数据时,压缩和解压缩是以行为单位执行的｡要评估使用行压缩后存储器的节省情况,可使用 DB2 INSPECT 联机实用程序的 ROWCOMPESTIMATE 选项｡在启用了表的COMPRESS 属性并创建了压缩字典之后,才可压缩行｡可通过 CREATE 或 ALTER TABLE 语句来设置 COMPRESS 属性｡可使用 REORG TABLE 命令来创建压缩字典｡在处理 REORG
命令时,现有的所有表行都要被压缩｡数据行压缩不适用于索引､LOB､LF 或 XML 对象｡
在将表存储在磁盘上时,如果对数据行､空值和系统缺省值使用诸如压缩之类的功能,则表可能占用较少的空间｡通过数据压缩,可以使用较少的数据库页来存储数据,从而节省磁盘存储空间｡由于每页可以存储更多的逻辑数据,因此访问同样多的逻辑数据时需要读取的页数将会少一些｡这意味着压缩还可以节省磁盘I/O｡I/O速度也会加快,因为可以将更多的逻辑数据高速缓存在缓冲池中｡
由于数据行压缩是DB2 V9新增的功能,所以很多用户对其特性不是很了解,本文将重点介绍DB2 V9数据行压缩功能,并通过实际的例子来帮助大家理解和提高｡
我们将按照下列顺序介绍表压缩:
1. 创建示例数据库DB2TEST1:在此章节将一步步讲解如何创建示例数据库DB2TEST1,并创建示例表空间TABLESPACE1｡
2. 对新表使用数据行压缩(表压缩):
a) 创建使用表压缩功能的示例表TEST1,插入多条记录;通过运行脱机重组创建压缩字典后,再插入剩余记录;
b) 通过INSPECT ROWCOMPESTIMATE语句进行估计压缩的效果,注意,这个只是估计,和实际的压缩可能会有出入;
c) 查询系统表SYSIBM.SYSTABLES,看一下实际的压缩效果;
d) 取消示例表TEST1的压缩属性,运行脱机重组,对示例表TEST1进行解压缩｡
3. 对已经存在的表使用数据行压缩(表压缩):
a) 创建不使用表压缩功能的示例表TEST2,插入数据;
b) 发出ALTER TABLE命令对示例表TEST2指定COMPRESS属性,运行脱机重组命令,给示例表TEST2创建压缩字典并压缩数据｡
c) 表压缩总结:总结第二步和第三步中用到的命令,以及表的压缩属性和压缩字典在各种情况下表是否进行压缩进行总结｡
4. 对表启用空值､系统缺省值压缩:
a) 创建示例表TEST3,使用空值压缩和系统缺省值压缩,插入数据;
b) 对示例表TEST3取消使用空值压缩和系统缺省值压缩｡
创建示例数据库DB2TEST1
首先我们在WINDOWS XP环境下安装DB2 ESE V9.1,安装完成后,打开一个DB2CLP窗口,发出CREATE DATABASE语句,创建示例数据库DB2TEST1,具体如清单1所示:
命令执行成功,这样我们创建了一个示例数据库DB2TEST1｡ 下面我们继续在DB2CLP窗口中,连上示例数据库DB2TEST1,发出GETDB CFG命令,查看示例数据库的配置参数,在返回结果中可以看到,数据库代码页是1386,数据库地域是CN,数据库代码集是GBK,部分结果如清单2所示:- - 清单1. 创建示例数据库DB2TEST1 C:\> DB2 CREATE DATABASE DB2TEST1 DB20000I CREATE DATABASE命令成功完成｡
- - 清单2. 查看示例数据库DB2TEST1配置参数 C:\> db2 connect to db2test1 数据库连接信息 数据库服务器 = DB2 / NT 9.1.0 SQL 授权标识 = RHETTE 本地数据库别名 = DB2TEST1 C:\> db2 get db cfg for db2test1 数据库 db2test1 的数据库配置 数据库配置发行版级别 = 0x0b00 数据库发行版级别 = 0x0b00 数据库地域 = CN 数据库代码页 = 1386 数据库代码集 = GBK 数据库国家/地区代码 = 86 数据库整理顺序 = UNIQUE 备用整理顺序 ( ALT_COLLATE ) = 数据库页大小 = 4096 . . . . . . . . . . . . . . . . . .
下面我们在示例数据库DB2TEST1中创建1个4K页大小的DMS表空间,用来存放示例表的数据,名称为TABLESPACE1｡
在DB2CLP窗口中,发出CREATE TABLESPACE命令,创建4K页大小的示例表空间TABLESPACE1,对应的缓冲池使用默认创建的IBMDEFAULTBP,具体如清单3所示:
- - 清单3 . 创建DMS示例表空间 C:\> DB2 CREATE REGULAR TABLESPACE TABLESPACE1 PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL IBMDEFAULTBP DB20000I SQL命令成功完成｡
命令成功完成｡注意,我们在MANAGED BY后面跟的是AUTOMATIC STORAGE,表示新创建的表空间将使用自动存储｡如果新建的表空间使用DB2管理存储器(自动存储器),根据要创建的表空间类型不同,其空间管理会有所区别,当其表空间类型是常规或者大型时,将自动创建成数据库管理空间(DMS), 当其表空间类型是系统临时或者用户临时时,将自动创建成系统管理空间(SMS)｡使用自动存储,就不再需要担心如何添加容器以及监控容器的增长等,自动存储会自动增加表空间在磁盘和文件系统上的大小｡在DB2CLP窗口中输入LIST TABLESPACE命令,你可以看到示例表空间TABLESPACE1已经创建成功,表空间标识是3,其空间管理类型是数据库管理空间,另外,数据库默认创建的3个表空间也在结果集中,分别是SYSCATSPACE､TEMPSPACE1和USERSPACE1,具体如清单4所示:
- - 清单4 . 查看示例表空间 C:\> db2 list tablespaces show detail 当前数据库的表空间 表空间标识 = 0 名称 = SYSCATSPACE 类型 = 数据库管理空间 内容 = 所有持久数据｡常规表空间｡ 状态 = 0x0000 详细解释: 正常 总计页数 = 8192 可用页数 = 8188 已用页数 = 7924 可用页数 = 264 高水位标记(页) = 7924 页大小(以字节计) = 4096 扩展数据块大小(页) = 4 预取大小(页) = 4 容器数 = 1 表空间标识 = 1 名称 = TEMPSPACE1 类型 = 系统管理空间 内容 = 系统临时数据 状态 = 0x0000 详细解释: 正常 总计页数 = 1 可用页数 = 1 已用页数 = 1 可用页数 = 不适用 高水位标记(页) = 不适用 页大小(以字节计) = 4096 扩展数据块大小(页) = 32 预取大小(页) = 32 容器数 = 1 表空间标识 = 2 名称 = USERSPACE1 类型 = 数据库管理空间 内容 = 所有持久数据｡大型表空间｡ 状态 = 0x0000 详细解释: 正常 总计页数 = 8192 可用页数 = 8160 已用页数 = 96 可用页数 = 8064 高水位标记(页) = 96 页大小(以字节计) = 4096 扩展数据块大小(页) = 32 预取大小(页) = 32 容器数 = 1 表空间标识 = 3 名称 = TABLESPACE1 类型 = 数据库管理空间 内容 = 所有持久数据｡常规表空间｡ 状态 = 0x0000 详细解释: 正常 总计页数 = 8192 可用页数 = 8160 已用页数 = 96 可用页数 = 8064 高水位标记(页) = 96 页大小(以字节计) = 4096 扩展数据块大小(页) = 32 预取大小(页) = 32 容器数 = 1
对新表使用数据行压缩(表压缩):
数据行压缩的目标是节省磁盘存储空间｡它还可以减少磁盘I/O｡另外,可以在缓冲池中高速缓存更多数据,这样就可以提高缓冲池命中率｡但是,关联的成本以压缩和解压缩数据所需的额外CPU周期形式出现｡数据行压缩节省的存储量和对性能的影响与数据库中数据的特征､数据库的布局和调整以及应用程序工作负载相关｡仅压缩数据页上的数据或日志记录中的数据｡数据行压缩使用基于静态字典的压缩算法来逐行压缩数据｡在行级别压缩数据允许将一行中跨多个列值的重复模式替换为较短的符号字符串｡为了压缩表数据,表COMPRESS属性必须设置为YES,且该表必须有压缩字典｡要把表设置成压缩方式,使用下列两个命令之一:
CREATE TABLE table_name . . . COMPRESS YES OR ALTER TABLE tablename COMPRESS YES
我们接下来创建示例表TEST1,表数据将存储在表空间TABLESPACE1中,其将启用表压缩功能(数据行压缩)｡在DB2CLP窗口中,连上示例数据库DB2TEST1,发出CREATE TABLE命令创建带COMPRESS属性的示例表TEST1,具体如清单5所示:
- - 清单5 .创建示例表TEST1 C:\> DB2 CREATE TABLE TEST1 ( ID INTEGER , NAME VARCHAR ( 10 ) , DEPARID VARCHAR ( 10 ) , NOTE VARCHAR ( 100 ) ) IN TABLESPACE1 COMPRESS YES DB20000I SQL命令成功完成｡
命令执行成功,这样我们就创建了示例表TEST1,其启用了数据行压缩方式｡要使压缩生效,我们需要要构建压缩字典并接着压缩表(表中需要有数据,不能是空表),然后DB2将扫描表中的数据,找出相同的字符串替换为较短的符号字符串,并放入到压缩字典中｡表中的所有数据行都将参与构建压缩字典｡该字典将与表数据行一起存储在表数据对象部分｡为了构建压缩字典,我们可以使用REORG命令,执行脱机重组｡第一次压缩一个表(或者你需要重新构建压缩字典)你可以使用如下命令:
REORG table table_name resetdictionary
这个命令将扫描整个表,创建压缩字典,然后执行实际表的重组,并在重组的过程中压缩数据｡需要注意此时REORG使用的是RESETDICTIONARY选项｡如果表的COMPRESS属性为YES并且字典存在,则可能会压缩插入到页中的数据行｡此情况适用于任何插入行操作,包括通过导入或装入操作来插入｡压缩是对整个表启用的,但却是单独地压缩每行｡因此,一个表可以同时包含已压缩的行和未压缩的行｡以后如果你需要运行一个正常的表重组,但是有不希望重新构建压缩字典,可以运行下面的命令:
REORG table table_name keepdictionary
需要注意此时REORG使用的是KEEPDICTIONARY选项｡每个表都拥有自己的压缩字典｡只能压缩永久数据对象｡数据行压缩不适用于索引､长整型数据对象､LOB 数据对象和XML数据对象｡行压缩与表数据复制支持不兼容｡
下面我们将为清单5中创建的示例表TEST1创建一个压缩字典｡
在DB2CLP窗口中,发出REORG命令,脱机执行表重组命令｡当示例表TEST1是空表时,执行REORG命令时会报SQL2220W错误,构建压缩字典失败,这是因为示例表TEST1中需要有部分数据时创建压缩字典才会成功,否则压缩字典将无法构建｡具体如清单6所示:
- - 清单6 .对示例表TEST1创建压缩字典 C:\> db2 REORG TABLE test1 resetdictionary SQL2220W 没有为一个或多个数据对象构建压缩字典｡ C:\> db2 ? sql2220w SQL2220W 没有为一个或多个数据对象构建压缩字典｡ 解释: 未能为一个或多个数据对象构建压缩字典｡这些对象不包含任何记录,或者不包含大于适合于此页大小的最小记录长度的任何记录｡未构建新字典｡将继续完成该操作｡如果在执行该操作之前已经有一个字典,则会保留该字典,而行数将取决于压缩程度｡ 用户响应: 参阅“管理日志”以确保是那些数据对象导致了警告｡
为了构建压缩字典,我们需要先为示例表TEST1插入部分数据｡
在DB2CLP窗口中,连上示例数据库DB2TEST1,在示例表TEST1插入部分数据,部门编号都是‘001’,备注信息都是‘TEST’,编号和姓名都不相同,具体如清单7所示:
- - 清单7 .对示例表TEST1插入部分数据 C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 1 , ' AA ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 2 , ' BB ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 3 , ' CC ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 4 , ' DD ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 5 , ' EE ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 6 , ' FF ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 7 , ' GG ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 8 , ' HH ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 9 , ' II ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡
命令成功完成,这样我们为示例表TEST1插入了9条记录｡部门编号都是‘001’,备注信息都是‘TEST’,编号和姓名都不相同｡
下面我们对示例表TEST1创建一个压缩字典,由于是第一次构建压缩字典,所以需要执行带RESETDICTIONARY选项的REORG命令｡
在DB2CLP窗口中,对示例表TEST1发出REORG命令,执行脱机重组,为示例表TEST1构建压缩字典,具体如清单8所示:
- - 清单8 .对示例表TEST1创建压缩字典 C:\> db2 REORG TABLE test1 resetdictionary DB20000I REORG命令成功完成｡
命令成功完成,这个命令将扫描整个表,创建压缩字典,然后执行实际表的重组,并在重组的过程中压缩数据｡由于部门编号和备注信息各行的数据都相同,所以将DB2通过分析与获取数据中出现的重复模式,生成压缩字典｡
接下来我们继续对示例表TEST1插入数据,由于示例表TEST1的COMPRESS属性为YES并且字典存在,所以可能会压缩插入到页中的数据行｡此情况适用于任何插入行操作,包括通过导入或装入操作来插入｡
在DB2CLP窗口中,对示例表TEST1通过INSERT INTO命令插入剩余10条数据,具体如清单9所示:
- - 清单9 .对示例表TEST1插入数据 C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 10 , ' JJ ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 11 , ' KK ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 12 , ' LL ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 13 , ' MM ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 14 , ' NN ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 15 , ' OO ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 16 , ' PP ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 17 , ' QQ ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 18 , ' RR ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST1( ID , NAME , DEPARID , NOTE ) VALUES ( 19 , ' SS ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡
命令成功完成,新插入的10条记录在数据库中也将以压缩的方式存储｡
如果想估计对示例表TEST1使用表压缩(数据行压缩)比不使用表压缩节省了多少空间,可以通过INSPECT ROWCOMPESTIMATE语句进行评估分析｡记住,这个命令只是估计压缩的效果,而不是查看最终实际的压缩效果,如果想查看实际的压缩效果,需要查看系统表SYSIBM.SYSTABLES｡带压缩估计选项(ROWCOMPESTIMATE)的INSPECT命令,将生成一份报告,描述节省了多少页｡语法如下:
DB2 INSPECT ROWCOMPESTIMATE TABLE NAME table_name RESULTS KEEP file_name
由于INSPECT命令生成的文件是二进制的,无法直接查看,所以我们需要使用DB2INSPF命令将此文件格式转成可读模式才能查看,具体语法如下:
DB2INSPF file_name output_file_name
下面我们看看我们使用表压缩方式节省了多少空间,在DB2CLP窗口中发出DB2 INSPECT命令,具体如清单10所示:
- - 清单10 .查看示例表TEST1,看一下表压缩方式节省了多少空间 C:\> DB2 " INSPECT ROWCOMPESTIMATE TABLE NAME test1 RESULTS KEEP test1.resp " DB20000I INSPECT命令成功完成｡
命令成功完成,这样我们就在C:\Program Files\IBM\SQLLIB\DB2下生成了一个文件test1.resp,由于这个文件是二进制的,我们需要使用DB2INSPF将此文件格式转成可读模式才能查看,继续在DB2CLP窗口中执行DB2INSPF命令,具体如清单11所示:
- - 清单11 .查看示例表TEST1,看一下表压缩方式节省了多少空间 C:\> cd C:\Program Files\IBM\SQLLIB\DB2 C:\Program Files\IBM\SQLLIB\DB2> DB2INSPF test1.resp output_test1.resp
此时我们打开output_test1.resp文件,可以看到通过压缩而节省的页数所占的百分比是56,通过压缩而节省的字节数所占的百分比是56,由于行大小太小而不适合压缩的行数所占的百分比是0,压缩字典大小是8192个字节,扩充字典大小是3336 个字节,具体如清单12所示:
- - 清单12 .查看示例表TEST1,看一下表压缩方式节省了多少空间 DATABASE: DB2TEST1 VERSION : SQL09010 2007-10-25-21.14.03.500000 操作:ROWCOMPESTIMATE TABLE 模式名:RHETTE 表名:TEST1 表空间标识:3 对象标识:4 结果文件名:test1.resp 表阶段开始(有符号的标识:4,无符号的:4;表空间标识:3):RHETTE.TEST1 数据阶段开始｡对象:4 表空间:3 行压缩估计结果: 通过压缩而节省的页数所占的百分比:56 通过压缩而节省的字节数所占的百分比:56 由于行大小太小而不适合压缩的行数所占的百分比:0 压缩字典大小:8192 个字节｡ 扩充字典大小:3336 个字节｡ 数据阶段结束｡ 表阶段结束｡ 处理已完成｡2007-10-25-21.14.03.562000
我们可以使用INSPECT ROWCOMPESTIMATE语句对压缩的效果进行评估分析,如果想查看实际的压缩效果,需要查看系统表SYSIBM.SYSTABLES,这系统表中,有几个新增的列是和压缩相关的:
COMPRESSION:此参数表示对于表来说,是否使用了压缩,其有4个值,含义分别是:
-N:没有设置任何压缩
-V:仅仅设置了空间值压缩
-R:仅仅设置了数据行压缩
-B:即设置了空间值压缩,也设置了数据行压缩
AVGROWSIZE:新增列,用来表示表的平均物理行长,包括所有压缩和没有压缩的数据行,此参数用来决定每页能存放的最大行数(对常规表空间来说,单页最大能放255行,对于大型表空间来说,单页最大行数会超过255行｡)｡当值为-1的时候,表示统计信息没有收集｡
PCTPAGESSAVED:新增列,表示使用压缩节省空间页的百分比｡当值为-1的时候,表示统计信息没有收集｡
PCTROWSCOMPRESSED:新增列,表示表中压缩的行数占总行数的百分比｡这个参数用来决定解压缩时CPU的开支｡当值为-1的时候,表示统计信息没有收集｡
AVGROWCOMPRESSIONRATIO:新增列,表示所有压缩的行的平均压缩比例(是压缩前的总页数和压缩后的总页数的比例)｡当值为-1的时候,表示统计信息没有收集｡
AVGCOMPRSSEDROWSIZE:新增列,表示所有压缩行在物理磁盘上的平均物理行长,当值为-1的时候,表示统计信息没有收集｡
另外,需要注意的是,如果想查看SYSIBM.SYSTABLES表中某个表的压缩情况,需要要查询都是表运行RUNSTATS,只有这样,上述几个列的值才不会是-1｡
下面,我们在DB2CLP窗口中,发出RUNSTATS命令对示例表TEST1运行统计信息,具体如清单13所示:
- - 清单13 .对示例表TEST1运行统计信息 C:\> DB2 RUNSTATS ON TABLE RHETTE.TEST1 DB20000I RUNSTATS命令成功完成｡
接下来我们就可以通过系统表SYSIBM.SYSTABLES查看示例表TEST1的压缩情况了｡
我们继续在DB2CLP窗口中,发出SELECT命令查看示例表TEST1的压缩情况,具体如清单14所示:
- - 清单14 .查看示例表TEST1压缩情况 C:\> DB2 SELECT NAME , COMPRESSION , AVGROWSIZE , PCTPAGESSAVED , PCTROWSCOMPRESSED ,AVGROWCOMPRESSIONRATIO , AVGCOMPRESSEDROWSIZE FROM SYSIBM.SYSTABLES WHERE NAME = 'TEST1' NAME COMPRESSION AVGROWSIZE PCTPAGESSAVED PCTROWSCOMPRESSED AVGROWCOMPRESSIONRATIO AVGCOMPRESSEDROWSIZE - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - TEST1 R 19 56 100 2.29223 19 1 条记录已选择｡
可以看到示例表TEST1启用了数据行压缩,压缩后的所有行的平均行长是19,压缩后节省空间的百分比是56%和用INSPECT命令估计的一样,压缩的行数占总行数的百分比是100%,压缩前和压缩后页数的百分比是2.29223,压缩行的平均行长是19｡
如果想查看压缩字典的大小,可以使用ADMIN_GET_TAB_INFO表函数查看其DICTIONARY_SIZE列的值,比如我们想查看示例表TEST1的压缩字典大小,可以在DB2CLP窗口中这么查看,具体如清单15所示:
- - 清单15 .查看示例表TEST1压缩字典大小 C:\>db2 describe "select * from table(sysproc.admin_get_tab_info('rhette','test1')) as t" SQLDA 信息 sqldaid : SQLDA sqldabc: 1204 sqln: 27 sqld: 27 列信息 sqltype sqllen sqlname.data sqlname.length - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 449 VARCHAR 128 TABSCHEMA 9 449 VARCHAR 128 TABNAME 7 453 CHARACTER 1 TABTYPE 7 501 SMALLINT 2 DBPARTITIONNUM 14 497 INTEGER 4 DATA_PARTITION_ID 17 453 CHARACTER 1 AVAILABLE 9 493 BIGINT 8 DATA_OBJECT_L_SIZE 18 493 BIGINT 8 DATA_OBJECT_P_SIZE 18 493 BIGINT 8 INDEX_OBJECT_L_SIZE 19 493 BIGINT 8 INDEX_OBJECT_P_SIZE 19 493 BIGINT 8 LONG_OBJECT_L_SIZE 18 493 BIGINT 8 LONG_OBJECT_P_SIZE 18 493 BIGINT 8 LOB_OBJECT_L_SIZE 17 493 BIGINT 8 LOB_OBJECT_P_SIZE 17 493 BIGINT 8 XML_OBJECT_L_SIZE 17 493 BIGINT 8 XML_OBJECT_P_SIZE 17 501 SMALLINT 2 INDEX_TYPE 10 453 CHARACTER 1 REORG_PENDING 13 449 VARCHAR 10 INPLACE_REORG_STATUS 20 449 VARCHAR 12 LOAD_STATUS 11 453 CHARACTER 1 READ_ACCESS_ONLY 16 453 CHARACTER 1 NO_LOAD_RESTART 15 501 SMALLINT 2 NUM_REORG_REC_ALTERS 20 453 CHARACTER 1 INDEXES_REQUIRE_REBUILD 23 453 CHARACTER 1 LARGE_RIDS 10 453 CHARACTER 1 LARGE_SLOTS 11 493 BIGINT 8 DICTIONARY_SIZE 15
如果觉得压缩的效果不能满意,可以对表进行解压缩,也就是把表的COMPRESS 属性设置为 NO,然后执行传统脱机表重组,这样就把压缩表变成了不压缩的表了｡
比如,我们现在想对示例表TEST1进行解压缩,可以继续在当前的DB2CLP窗口中,先发出ALTER TABLE命令,把示例表TEST1的COMPRESS属性设置为NO,再运行REORG命令执行脱机重组,具体如清单16所示:
- - 清单16 .对示例表TEST1进行解压缩 C:\Program Files\IBM\SQLLIB\DB2>cd \ C:\> db2 alter table test1 compress no DB20000I SQL命令成功完成｡ C:\> db2 reorg table test1 resetdictionary DB20000I REORG命令成功完成｡
命令成功完成,此时示例表TEST1将不再使用表压缩功能,并且压缩字典将被删除｡此时我们再次在DB2CLP窗口中查看示例表TEST1的实际压缩情况,可以发现,没有使用压缩,有关压缩的各个列都和压缩的时候值不一样了,具体如清单17所示:
- - 清单17 .查看示例表TEST1压缩情况 C:\> DB2 SELECT NAME , COMPRESSION , AVGROWSIZE , PCTPAGESSAVED , PCTROWSCOMPRESSED ,AVGROWCOMPRESSIONRATIO , AVGCOMPRESSEDROWSIZE FROM SYSIBM.SYSTABLES WHERE NAME = 'TEST1' NAME COMPRESSION AVGROWSIZE PCTPAGESSAVED PCTROWSCOMPRESSED AVGROWCOMPRESSIONRATIO AVGCOMPRESSEDROWSIZE - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - TEST1 N 45 0 0 0 0 1 条记录已选择｡
总结一下,如果对一个新表启用表压缩:
-创建一个表,将表的COMPRESS属性设置为 YES
-对表装入一部分数据
-执行带有RESETDICTIONARY选项的脱机重组,创建一个新的压缩字典
-装入剩余的数据到这个表中(装入时将考虑压缩字典并在装入时压缩数据)
对已经存在的表使用数据行压缩(表压缩)
下面我看一下如何对一个已经存在的表(表中有数据存在)启用数据行压缩(表压缩),首先我们需要创建一个示例表 TEST2,并插入部分数据｡接下来发出ALTER TABLE命令对示例表TEST2指定COMPRESS属性,运行脱机重组命令,给示例表TEST2创建压缩字典并压缩数据｡
在DB2CLP窗口中,连上示例数据库DB2TEST1,发出CREATE TABLE命令,创建示例表TEST2,初始不使用表压缩功能(不指定COMPRESS关键字),具体如清单18所示:
- - 清单18 .创建示例表TEST2 C:\> db2 connect to db2test1 数据库连接信息 数据库服务器 = DB2 / NT 9.1.0 SQL 授权标识 = RHETTE 本地数据库别名 = DB2TEST1 C:\> DB2 CREATE TABLE TEST2 ( ID INTEGER , NAME VARCHAR ( 10 ) , DEPARID VARCHAR ( 10 ) , NOTE VARCHAR ( 100 ) ) IN TABLESPACE1 DB20000I SQL命令成功完成｡
命令执行成功,这样我们就创建了示例表TEST2,其不使用表压缩功能,接下来我们对示例表TEST2插入数据,部门编号都是‘001’,备注信息都是‘TEST’,编号和姓名都不相同,具体如清单19所示:
- - 清单19 .对示例表TEST2插入部分数据 C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 1 , ' AA ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 2 , ' BB ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 3 , ' CC ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 4 , ' DD ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 5 , ' EE ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 6 , ' FF ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 7 , ' GG ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 8 , ' HH ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡ C:\> DB2 INSERT INTO TEST2( ID , NAME , DEPARID , NOTE ) VALUES ( 9 , ' II ' , ' 001 ' , ' TEST ') DB20000I SQL命令成功完成｡
命令成功完成,这样我们为示例表TEST2插入了9条记录｡部门编号都是‘001’,备注信息都是‘TEST’,编号和姓名都不相同｡
如果想对示例表TEST2启用表压缩功能,需要把表的COMPRESS选项改成YES｡下面我们在DB2CLP窗口中,发出ALTER TABLE命令,把示例表TEST2的COMPRESS属性设置为YES,具体如清单20所示:
- - 清单20 .对示例表TEST2启用COMPRESS属性 C:\> db2 alter table test2 compress yes DB20000I SQL命令成功完成｡
命令成功完成,此时示例表TEST2启用了表压缩功能,但是由于没有压缩字典的存在,所以示例表TEST2中的数据还不是以压缩的方式存储的｡直到显式的发出了脱机REORG命令,创建了压缩字典后,示例表TEST2中的数据才会真正的压缩存储｡
下面我们对示例表TEST2创建一个压缩字典,由于是第一次构建压缩字典,所以需要执行带RESETDICTIONARY选项的REORG命令｡
在DB2CLP窗口中,对示例表TEST2发出REORG命令,执行脱机重组,为示例表TEST2构建压缩字典,具体如清单21所示:
- - 清单21 .对示例表TEST2创建压缩字典 C:\> db2 REORG TABLE test2 resetdictionary DB20000I REORG命令成功完成｡
命令成功完成,这个命令将扫描整个表,创建压缩字典,然后执行实际表的重组,并在重组的过程中压缩数据｡由于部门编号和备注信息各行的数据都相同,所以将DB2通过分析与获取数据中出现的重复模式,生成压缩字典｡
如果想查看对示例表TEST2使用表压缩(数据行压缩)比不使用表压缩节省了多少空间,可以通过INSPECT ROWCOMPESTIMATE语句进行评估分析,也可以查看系统表SYSIBM.SYSTABLES查看实际的压缩效果,具体如前面所述｡
下面我们来总结一下表压缩的相关内容｡
如果想对一个新表启用表压缩,可以按照以下步骤:
-创建一个表,将表的COMPRESS属性设置为 YES
-对表装入一部分数据
-执行带有RESETDICTIONARY选项的脱机重组,创建一个新的压缩字典
-装入剩余的数据到这个表中(装入时将考虑压缩字典并在装入时压缩数据)
如果想对一个已经存在的表(表中已经有数据)启用表压缩,可以按照以下步骤:
-通过ALTER TABLE命令将表的COMPRESS属性设置为 YES
- 执行带有RESETDICTIONARY选项的脱机重组,创建一个新的压缩字典
如果想查看对表使用表压缩(数据行压缩)比不使用表压缩节省了多少空间,可以通过INSPECT ROWCOMPESTIMATE语句进行评估分析｡带压缩估计选项(ROWCOMPESTIMATE)的INSPECT命令,将生成一份报告,描述节省了多少页｡语法如下:
DB2 INSPECT ROWCOMPESTIMATE TABLE NAME table_name RESULTS KEEP file_name
由于INSPECT命令生成的文件是二进制的,无法直接查看,所以我们需要使用DB2INSPF命令将此文件格式转成可读模式才能查看,具体语法如下:
DB2INSPF file_name output_file_name
数据行压缩是基于字典的压缩,每个字典是一个压缩/解压缩数据记录的符号表｡算法是基于LEMPEL-ZIV(LZ)算法,使用静态字典,每张表一个字典,存放在永久表对象中｡使用数据行压缩的数据无论是在磁盘还是在内存都是压缩的,只有在计算前才会被解压缩,因此极大地节约了I/O带宽和内存,但是增加了CPU消耗｡对于压缩的数据行其日志也是压缩的｡对于经压缩后不能节省存储的行,DB2不对其进行压缩处理,数据(每一行)中重复的串是高压缩比的关键｡文本数据一般压缩得比较好,因为其中往往包括重复字串以及大量的重复字符､前导或后缀空格｡
需要注意的是,通过执行CREATE TABLE或ALTER TABLE语句打开表压缩开关时,并不压缩数据,数据仅在创建压缩字典表后才能真正实现压缩｡压缩字典表将在临时内存中创建,大约需临时占用10M左右内存,临时内存从UTILITIES HEAP中申请｡字典的分析与生成基于表中已有的数据,是离线进行的,在以后的版本中将会支持在线进行,也就是说现在只能是在脱机重组时创建压缩字典,在将来的版本中将支持联机重组时创建压缩字典｡
需要注意的是,执行脱机重组(REORG)的时候,有两个重要的选项,分别是RESETDICTIONARY和KEEPDICTIONARY,两个含义和动作都是不一样的｡我们来分别来看一下:
对表启用空值､系统缺省值压缩
除了数据行压缩以外,以前的空值压缩在DB2 V9中继续可以使用｡创建表时,可使用可选 VALUE COMRESSION 子句来指定表在使用表级别也可能是列级别的节省空间的行格式｡使用 VALUE COMRESSION 时,不会将已指定给已定义的变长数据类型(VARCHAR､VARGRAPHICS､LONG VARCHAR､LONG VARGRAPHIC､BLOB､CLOB 和 DBCLOB)的 NULL 和零长度数据存储在磁盘上｡只有与这些数据类型相关联的开销值才会占用磁盘空间｡如果使用了VALUE COMRESSION,那么还可以使用可选 COMPRSS SYSTEM DEFAULT 选项来进一步减少磁盘空间的使用量｡如果插入的或更新的值等于列的数据类型的系统缺省值,那么使用的磁盘空间最少｡缺省值将不会存储在磁盘上｡支持COMPRSS SYSTEM DEFAULT的数据类型包括所有数字类型列､定长字符和定长图形字符串数据类型｡这表示零和空格可以压缩｡要确定是否应考虑对表进行空间压缩,应了解大多数值等于系统缺省值或 NULL 值的表将受益于新的行格式｡例如,假设有一个 INTEGER 列且列的 90% 的列值为 0 (INTEGER数据类型的缺省值)或 NULL,压缩此表和此列将受益于新的行格式并节省大量的磁盘空间｡
下面我们创建示例表TEST3,其使用VALUE COMRESSION对整个表的空值进行压缩,对每个列使用COMPRESS SYSTEM DEFAULT选项对数据类型默认的系统缺省值进行压缩｡
在DB2CLP窗口连上示例数据库,发出CREATE TABLE命令创建示例表TEST3,并插入部分数据,具体如清单22所示:
- -清单22 .创建示例表TEST3 C:\> db2 connect to db2test1 数据库连接信息 数据库服务器 = DB2 / NT 9.1.0 SQL 授权标识 = RHETTE 本地数据库别名 = DB2TEST1 C:\> DB2 CREATE TABLE TEST3 ( ID INTEGER COMPRESS SYSTEM DEFAULT , NAME VARCHAR ( 10 ) COMPRESS SYSTEM DEFAULT , NOTE VARCHAR ( 100 ) COMPRESS SYSTEM DEFAULT ) IN TABLESPACE1 VALUE COMPRESSION DB20000I SQL命令成功完成｡ C:\> db2 insert into test3 ( id , name ) values ( 0 , ' aa ') DB20000I SQL命令成功完成｡ C:\> db2 insert into test3 ( id , name ) values ( 1 , ' bb ') DB20000I SQL命令成功完成｡
命令成功完成,这样我们就创建了示例表TEST3,其使用空值和默认值压缩｡
COMPRESS SYSTEM DEFAULT指定对系统默认值存储时使用最小空间,如果VALUE COMRESSION子句没有被指定,将返回一个警告SQLSTATE01648,并且系统默认值也不会在压缩存储｡允许系统默认值压缩将对INSERT和UPDATE操作额外增加一些性能开支,因为需要额外检查是否需要默认值压缩｡COMPRESS SYSTEM DEFAULT子句不支持DATE,TIME,TIMESTAMP,XML或者结构化数据类型,否则会报SQLSTATE 42842错误｡如果基本数据类型是变长字符串,则本语句将自动忽略｡
可以使用 DEACTIVATE VALUE COMPRESSION 来指定表将不再对表中数据使用节省空间技术｡如果使用DEACTIVATE VALUE COMPRESSION,这将显式禁用与该表中的列相关联的所有 COMPRESS SYSTEM DEFAULT 选项｡
下面我们对示例表TEST3的ID列禁用系统默认值压缩,在DB2CLP窗口中,发出带COMPRESS OFF选项的ALTER TABLE命令,具体如清单23所示:
- -清单23 .对示例表TEST3一个列禁用系统默认值压缩 C:\> DB2 ALTER TABLE TEST3 ALTER COLUMN ID COMPRESS OFF DB20000I SQL命令成功完成｡
下面我们对示例表TEST3使用 DEACTIVATE VALUE COMPRESSION 来指定表将不再对表中数据使用节省空间技术,其各个列上的COMPRESS SYSTEM DEFAULT也将同时禁用｡
在DB2CLP窗口中发出DEACTIVATE VALUE COMPRESSION命令,具体如清单24所示:
- -清单24 .对示例表TEST3禁用空间节省技术 C:\> DB2 ALTER TABLE TEST3 DEACTIVATE VALUE COMPRESSION DB20000I SQL命令成功完成｡
如果想对没有启用空值压缩的旧表启用空值压缩,可以是带ACTIVATE VALUE COMPRESSION选项的ALTER TABLE命令显式地启用空值压缩,比如我们对示例表TEST3还想启用空值压缩,可以在DB2CLP窗口中发出ALTER TABLE命令,具体如清单25所示:
- - 清单25 .对示例表TEST3启用空值压缩 C:\> DB2 ALTER TABLE test3 ACTIVATE VALUE COMPRESSION DB20000I SQL命令成功完成｡
如果对某个列想启用系统默认值压缩,可以在DB2CLP窗口中发出ALTER TABLE命令,具体如清单26所示:
- - 清单26 .对示例表TEST3启用系统默认值压缩 C:\> DB2 ALTER TABLE test3 ALTER id COMPRESS SYSTEM DEFAULT DB20000I SQL命令成功完成｡
