ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用DB2 9多种系统的行压缩功能压缩数据库

使用DB2 9多种系统的行压缩功能压缩数据库

原创 Linux操作系统 作者:izhonglin 时间:2019-07-26 16:39:01 0 删除 编辑
IBM® DB2® 9 for Linux®, UNIX®, and Windows® 包括了一项新的基于字典的行压缩特性,该特性可用于压缩数据对象。当压缩数据时,通过使用更少的数据库页表示相同的数据,可以节省磁盘存储空间。一些具有包含重复模式的行的大型表将从该特性中获益匪浅。在本教程中,您将通过一系列的练习来熟悉这项新特性。理解该特性的优点,然后在您自己的环境中实现行压缩。
开始之前

关于本教程

    数据行压缩用较短的符号字符串替换数据行中公共的字节模式。与值压缩相比,行压缩能节省更多的存储空间。不过,实现行压缩需要一定的附加成本,并且每当需要压缩或解压缩一个行时,都需要付出相应的处理成本。DB2 9 中的数据行压缩基于 Lempel-Ziv (LZ) 算法。LZ 算法使用一个静态字典存储所需的信息。在 Data-compression.com 上可以找到 关于该算法的详细信息。这种字典是存储在表本身中的,因而也需要一点开销(大约 74KB)。如果不能节省存储空间,那么 DB2 9 是不会压缩行的。

    本教程通过一系列的练习让您熟悉这项新的基于字典的行压缩特性。本教程面向的读者是 DB2 技术专家、数据库管理员和程序员。您应该对 DB2 on Linux, UNIX, and Windows(本文后面简称为 DB2 LUW)有一个基本的理解。您还应该熟悉 DB2 Command Window,并知道如何运行 DB2 LUW 脚本。 

目标

    本教程将帮助您熟悉 DB2 9 中数据压缩的概念和功能。在这些练习中,您将学习如何:

-启用/禁用行压缩。
-使用 Tablespace 创建中的新选项以使一个页可以包含更多的行。
-使用各种 SQL 查询和工具来分析压缩的优点。

先决条件

    本教程是针对那些拥有初级到中级技能和经验的 Linux 或 UNIX 程序员而编写的。您应该大致熟悉如何使用 UNIX 命令行 shell,并具有 C 语言方面的实用知识。

系统需求

    要运行本教程中的例子,需要:

-DB2 9 数据服务器。
-Microsoft Windows 2000 或更高版本,以及一个拥有管理员权限的账户,或者具有 root 访问权限的 Linux(Validated edition)。
-Java Runtime Environment 1.4.2 或更高版本。
-请参考 DB2 9 系统需求页面,以确保您的硬件满足需求。

    上面的链接里还提供了 DB2 9 Express-C。请参阅教程 “DB2 XML 评估指南” (developerWorks,2006 年 6 月),了解安装 DB2 的步骤。 除非修改了 DB2 配置,否则 DB2 在安装之后将自动启动。

-使用 compression.zip 文件中提供的示例脚本和文件来演示本教程中的概念。将其中的内容解压到一个名为 DB2compression 的子目录中(C:\DB2compression 或 home/userid/DB2compression)。该目录在本教程中被简称为 DB2compression。本教程假设您将 DB2 安装在默认目录下,并且所有 DB2 练习都是以数据库管理员 id 执行的。

第1页

{$PageTitle=构建环境并在其中测试}

构建一个数据库受管存储环境并在其中进行测试

    在本节中,将构建一个环境,在此环境中可以使用数据库受管存储(DMS)表空间比较对表中的数据进行压缩和不进行压缩这两种不同的情况。用于填充表的数据放在一个定界 ASCII 文件中,该文件名为 data.del,位于 DB2compression 子目录下。所有脚本都在子目录 Section2 中。

    要在 db2 命令窗口中运行这些脚本,可使用:

db2 –tvf scriptname.sql
            

    可以通过输入名称来运行批处理脚本(后缀为 .bat)。

操作说明

    首先需要使用数据库管理员 id 和密码登录到系统。确认 “Sample” 数据库已经存在,并且已经创建了 EXPLAIN 表。如果其中一项不存在,或者两者都不存在,那么必须先创建,因为本教程后面一直要用到它们。

    现在来修改 DBM 和 DB 配置,以进行这组练习。

  1. 使用以下命令更新数据库管理器配置:

    update dbm cfg using MAXAGENTS 200;
    update dbm cfg using NUM_POOLAGENTS 100;
    update dbm cfg using HEALTH_MON OFF;
    update dbm cfg using MON_HEAP_SZ 250;
    update dbm cfg using DFT_MON_TABLE ON;

    或者使用 udbmcfg.sql 脚本来设置数据库管理器参数。


  2. 使用以下命令更新 SAMPLE 数据库配置:

    update db cfg for sample  using SELF_TUNING_MEM OFF IMMEDIATE;
    update db cfg for sample using LOCKLIST 500 IMMEDIATE;
    update db cfg for sample using NUM_IOCLEANERS 2 IMMEDIATE;
    update db cfg for sample using NUM_IOSERVERS 5 IMMEDIATE;
    update db cfg for sample using MAXFILOP 256 IMMEDIATE;
    update db cfg for sample using LOGSEC OND 40 IMMEDIATE;
    update db cfg for sample using AUTO_TBL_MAINT OFF IMMEDIATE;
    update db cfg for sample using AUTO_RUNSTATS OFF IMMEDIATE;
    update db cfg for sample using AUTO_MAINT OFF IMMEDIATE;

    或者使用 udbcfg.sql 脚本来设置数据库参数。


  3. 使用以下命令修改缓冲池空间,以便能够将整个表装入内存:

    alter bufferpool IBMDEFAULTBP IMMEDIATE SIZE 10000;

    或者使用 alterbp.cfg 脚本来设置缓冲池。


  4. 现在创建两个 DMS 表空间 —— 一个用于数据,一个用于索引。

        为大小为 10MB 的容器创建一个页大小为 4KB 并由数据库管理的普通表空间 TS1,为其提供一个文件名(Windows 上为 C:\db2\node0000\TS1,Linux 上为 /home/db2inst1/db2inst1/NODE0000/TS1):

                    CREATE REGULAR TABLESPACE TS1
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TS1' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TS1' 10M );

    创建另一个具有相同维的表空间 TSI1,用于存放索引。

    CREATE REGULAR TABLESPACE TSI1
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TSI1' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TSI1' 10M );

    或者使用 crts1.sql 脚本来创建表空间。


  5. 现在,创建表 T1,并指定不压缩(这个表是从 TCPH 基准中使用的一个表摘录出来的)。

    使用脚本 crt1.sql 创建包含下述列的表 T1 和相关的索引:

    "L_ORDERKEY"        INTEGER     NOT NULL ,
    "L_PARTKEY" INTEGER NOT NULL ,
    "L_SUPPKEY" INTEGER NOT NULL ,
    "L_LINENUMBER" INTEGER NOT NULL ,
    "L_QUANTITY" DOUBLE NOT NULL ,
    "L_EXTENDEDPRICE" DOUBLE NOT NULL ,
    "L_DISCOUNT" DOUBLE NOT NULL ,
    "L_TAX" DOUBLE NOT NULL ,
    "L_RETURNFLAG" CHAR(1) NOT NULL ,
    "L_LINESTATUS" CHAR(1) NOT NULL ,
    "L_SHIPDATE" DATE NOT NULL ,
    "L_COMMITDATE" DATE NOT NULL ,
    "L_RECEIPTDATE" DATE NOT NULL ,
    "L_SHIPINSTRUCT" CHAR(25) NOT NULL ,
    "L_SHIPMODE" CHAR(10) NOT NULL ,
    "L_COMMENT" VARCHAR(44) NOT NULL

        添加 “L_ORDERKEY” 和 “L_LINENUMBER” 上的一个惟一键以及 “L_PARTKEY” 和 “L_SUPPKEY” 上的另一个键。

        现在可以开始就表空间的使用情况进行第一个系列的分析。多次重复执行一组相同的动作,首先从少量的行开始,然后逐渐加大数量,直到完成整个表:

    1. 将所需的行从 data.del(在子目录 DB2Compression 中)导入到 T1 中,替换已有的数据。
    2. 在带分布和索引的表 T1 上执行 RUNSTATS。
    3. 收集信息表 syscat.tables。
    4. 列出表空间的详细信息。

  6. 针对以下数量的行运行 loadT1.sql:
    • 100 行
    • 1000 行
    • 10000 行
    • 整个表

        对于这些测试,只有 NPAGES 是有用的,因为压缩标志为 N,其他参数都为 0。


  7. 在分析过程中,使用 INSPECT 命令估计如果压缩 TS1 中的 T1,可以节省多少空间:

    INSPECT ROWCOMPESTIMATE TABLE
    NAME T1
    SCHEMA db2inst1
    RESULTS KEEP T1.raw;

    可以使用以下批处理文件和脚本文件:

    • insp1.bat 删除检查文件。
    • inspT1.sql 运行 INSPECT 命令。
    • insp2.bat 格式化和显示检查数据。

    注意: 需要为 INSPECT 命令提供一个文件名(例如 t1.raw),用于存储结果。输出文件是一个二进制文件,与 db2diag.log 存储在相同的目录中。在不同操作系统上该路径可能有所不同:Linux 上为 db2dump,Windows 上为 …sqllib\instancename。需要使用 db2inspf 命令格式化该文件。

现在对压缩数据重复上述过程。

  1. 再创建两个 DMS 表空间 —— 一个用于数据,另一个用于索引。

    为大小为 10MB 的容器创建一个页大小为 4KB 并由数据库管理的普通表空间 TS2,为其提供一个文件名(Windows 上为 C:\db2\node0000\TS2,Linux 上为 /home/db2inst1/db2inst1/NODE0000/TS2):

    CREATE REGULAR TABLESPACE TS2
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TS2' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TS2' 10M );

    创建另一个具有相同维的表空间 TSI2,用于存放索引。

    CREATE REGULAR TABLESPACE TSI2
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TSI2' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TSI2' 10M );

    或者使用 crts2.sql 脚本来创建表空间。

  2. 现在创建表 T2 并指定压缩。

  3. 使用脚本 crt2.sql 创建表 T2 和相关的索引,使它们与 T1 及其索引一样。

        对表空间的使用情况重复上述分析过程。多次重复执行一组相同的动作,首先从少量的行开始,然后逐渐加大数量,直到完成整个表:

    1. 将所需的行从 data.del(在子目录 DB2Compression 中)导入到 T2 中,替换已有的数据。
    2. 重组数据,并重新构建压缩字典。
    3. 在带分布和索引的表 T2 上执行 RUNSTATS。
    4. 收集信息表 syscat.tables。
    5. 列出表空间的详细信息。

  4. 针对以下数量的行运行 loadT2.sql:
    • 100 行
    • 1000 行
    • 10000 行
    • 整个表

    比较问题 6 和 10 的结果。

第2页
{$PageTitle=比较查询的执行}

比较查询的执行

    在本节中,将对相同表中压缩的和不压缩的数据运行一系列的查询。用于填充表的数据放在子目录 DB2compression 下名为 data.del 的一个定界 ASCII 文件中。所有脚本都在子目录 Section3 中。

操作说明

    使用数据库管理员 id 和密码登录到系统。将当前目录改为 Section3。

  1. 对整个 T1 表运行一个解释:

    db2expln -d sample -o res1.txt -statement
                    "select * from db2inst1.t1"

    查看输出文件。也可以使用名为 T1_exp.sql 的示例脚本。

  2. 对整个 T2 表运行一个解释:

    db2expln -d sample -o res2.txt -statement
                    "select * from db2inst1.t2"

    查看输出文件。也可以使用名为 T2_exp.sql 的示例脚本。

        比较以上两个步骤中的输出文件,应该可以看到,压缩表 T2 上的查询的成本估计大约是完整表 T1 上的查询的 41%(脚本附带有示例输出)。

    现在使用 db2batch 命令运行查询。


  3. 为了对表 T1 运行查询,可使用以下命令:

    db2batch -d sample -f T1_run.sql -cli -r res3.txt

    该命令在数据库 sample 中运行 T1_run.sql 文件中的命令,并将输出(包括详细的执行时间)放在 res3.txt 中。

    也可以使用示例脚本 T1_run.bat。


  4. 为了对表 T2 运行查询,可使用以下命令:

    db2batch -d sample -f T2_run.sql -cli -r res4.txt

    该命令在数据库 sample 中运行 T2_run.sql 文件中的命令,并将输出(包括详细的执行时间)放在 res4.txt 中。

    也可以使用示例脚本 T2_run.bat。

    比较上述两个步骤中的输出文件,应该可以看到,两个查询的耗时非常相近(脚本附带有示例输出)。

第3页
{$PageTitle=构建一个环境并在其中测试}

构建一个 System Managed Storage 环境并在其中进行测试

    在本节中,您将构建一个环境,在此环境中可以使用 System Managed Storage(SMS)表空间 比较对表中的数据进行压缩和不进行压缩这两种不同的情况。用于填充表的数据放在一个定界 ASCII 文件中,该文件名为 data.del,位于子目录 DB2compression 下。所有脚本放在子目录 Section4 中。

操作说明

    使用数据库管理员 id 和密码登录到系统。

    现在创建两个 SMS 表空间 —— 一个用于数据,另一个用于索引。

  1. 为大小为 10MB 的容器创建一个页大小为 4KB 并由数据库管理的普通表空间 TSA,为其提供一个文件名(Windows 上为 C:\db2\node0000\TSA,Linux 上为 /home/db2inst1/db2inst1/NODE0000/TSA):

                    CREATE REGULAR TABLESPACE TSA
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TSA' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TSA' 10M );

    创建另一个具有相同维的表空间 TSIA,用于存放索引:

    CREATE REGULAR TABLESPACE TSIA
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TSIA' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TSIA' 10M );

    或者使用 crtsa.sql 脚本来创建表空间。

    现在创建表 TA 并指定不压缩(这个表是从 TCPH 基准中使用的一个表摘录出来的)。

  2. 使用脚本 crta.sql 创建包含下述列的表 TA 和相关的索引:

    "L_ORDERKEY"        INTEGER     NOT NULL ,
    "L_PARTKEY" INTEGER NOT NULL ,
    "L_SUPPKEY" INTEGER NOT NULL ,
    "L_LINENUMBER" INTEGER NOT NULL ,
    "L_QUANTITY" DOUBLE NOT NULL ,
    "L_EXTENDEDPRICE" DOUBLE NOT NULL ,
    "L_DISCOUNT" DOUBLE NOT NULL ,
    "L_TAX" DOUBLE NOT NULL ,
    "L_RETURNFLAG" CHAR(1) NOT NULL ,
    "L_LINESTATUS" CHAR(1) NOT NULL ,
    "L_SHIPDATE" DATE NOT NULL ,
    "L_COMMITDATE" DATE NOT NULL ,
    "L_RECEIPTDATE" DATE NOT NULL ,
    "L_SHIPINSTRUCT" CHAR(25) NOT NULL ,
    "L_SHIPMODE" CHAR(10) NOT NULL ,
    "L_COMMENT" VARCHAR(44) NOT NULL

        添加 “L_ORDERKEY” 和 “L_LINENUMBER” 上的一个惟一键以及 “L_PARTKEY” 和 “L_SUPPKEY” 上的另一个键。

        现在可以开始就表空间的使用情况进行第一个系列的分析。多次重复执行一组相同的动作,首先从少量的行开始,然后逐渐加大数量,直到完成整个表:

    1. 将所需的行从 data.del(在子目录 DB2Compression 中)导入到 TA 中,替换已有的数据。
    2. 在带分布和索引的表 TA 上执行 RUNSTATS。
    3. 收集信息表 syscat.tables。
    4. 列出表空间的详细信息。

  3. 针对以下数量的行运行 loadTA.sql:
    • 100 行
    • 1000 行
    • 10000 行
    • 整个表

    对于这些测试,只有 NPAGES 是有用的,因为压缩标志为 N,其他参数都为 0。

  4. 在分析过程中,使用 INSPECT 命令估计如果压缩 TSA 中的 TA,可以节省多少空间:

    INSPECT ROWCOMPESTIMATE TABLE
    NAME TA
    SCHEMA db2inst1
    RESULTS KEEP ta.raw;

    可以使用以下批处理文件和脚本文件:

    • insp1.bat 删除检查文件。
    • inspTA.sql 运行 INSPECT 命令。
    • insp2.bat 格式化和显示检查数据。

    注意:需要为 INSPECT 命令提供一个文件名(例如 ta.raw),用于存储结果。输出文件是一个二进制文件,与 db2diag.log 存储在相同的目录中。在不同操作系统上该路径可能有所不同:Linux 上为 db2dump,Windows 上为 …sqllib\instancename。需要使用 db2inspf 命令格式化该文件。

    现在对压缩数据重复上述过程。再创建两个 SMS 表空间 —— 一个用于数据,另一个用于索引。

  5. 为大小为 10MB 的容器创建一个页大小为 4KB 并由数据库管理的普通表空间 TSB,为其提供一个文件名(Windows 上为 C:\db2\node0000\TSB,Linux 上为 /home/db2inst1/db2inst1/NODE0000/TSB):

    CREATE REGULAR TABLESPACE TSB
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TSB' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TSB' 10M );

  6. 创建另一个具有相同维的表空间 TSI2,用于存放索引:

    CREATE REGULAR TABLESPACE TSIB
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TSIB' 10M );
    or USING ( FILE '/home/db2inst1/db2inst1/NODE0000/TSIB' 10M );

    或者使用 crtsb.sql 脚本来创建表空间。

    现在创建表 TB 并指定压缩。


  7. 使用脚本 crtb.sql 创建表 TB 和相关的索引,使它们与 TA 及其索引一样。

        对表空间的使用情况重复上述分析过程。多次重复执行一组相同的动作,首先从少量的行开始,然后逐渐加大数量,直到完成整个表:

    1. 将所需的行从 data.del(在子目录 DB2Compression 中)导入到 TB 中,替换已有的数据。
    2. 重组数据并重新构建压缩字典。
    3. 在带分布和索引的表 TB 上执行 RUNSTATS。
    4. 收集信息表 syscat.tables。
    5. 列出表空间的详细信息。

  8. 针对以下数量的行运行 loadTB.sql:
    • 100 行
    • 1000 行
    • 10000 行
    • 整个表

    比较问题 4 和 6 的结果。

第4页
{$PageTitle=构建另一种环境并在其中进行测试}构建另一种环境并在其中进行测试(可选)

    在本节中,您将构建一个环境, 在此环境中可以使用 System Managed Storage (SMS)表空间比较对表中的数据进行压缩和不进行压缩这两种不同的情况。用于填充表的数据放在一个定界 ASCII 文件中,该文件名为 data.del,位于子目录 DB2compression 下。

大型表空间环境

从 “构建一个数据库受管存储环境并在其中进行测试” 小节中的脚本开始:

  1. 使用新的 LARGE 选项重新创建 DMS 表空间:

    DROP TABLESPACE TS1;
    CREATE LARGE TABLESPACE TS1
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TS1' 10M );

  2. 对 TS2 重复此过程。
  3. 重新创建表,并重复执行分析脚本。
  4. 比较 NPAGES 与 syscat.tables 中其他的压缩值。

具有不同的范围大小的大型表空间环境

    从 “构建一个数据库受管存储环境并在其中进行测试” 小节中的脚本开始:

  1. 使用新的 LARGE 选项重新创建 DMS 表空间:

    DROP TABLESPACE TS1;
    CREATE LARGE TABLESPACE TS1
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TS1' 10M )
    EXTENT 2 ;

  2. 对 TS2 重复此过程。
  3. 重新创建表,并重复执行分析脚本。
  4. 比较 NPAGES 与 syscat.tables 中的其他的压缩值。

具有不同页大小的大型表空间环境

    从 “构建一个数据库受管存储环境并在其中进行测试” 小节中的脚本开始:

  1. 创建包含 1250 个 32KB 页的缓冲池 BP32:

    CONNECT TO SAMPLE;
    CREATE BUFFERPOOL BP32 IMMEDIATE SIZE 1250 PAGESIZE 32 K ;
    CONNECT RESET;

  2. 使用新的 LARGE 选项重新创建 DMS 表空间:

    DROP TABLESPACE TS1;
    CREATE LARGE TABLESPACE TS1
    PAGESIZE 4K
    MANAGED BY DATABASE
    USING ( FILE 'C:\DB2\NODE0000\TS1' 10M )
    BUFFERPOOL BP32 ;

  3. 对 TS2 重复此过程。
  4. 重新创建表,并重复执行分析脚本。
  5. 比较 NPAGES 与 syscat.tables 中的其他的压缩值。

静态字典环境

    从 “构建一个数据库受管存储环境并在其中进行测试” 小节中的脚本开始:

  1. 重新创建具有原来那么多维的 DMS 表空间 TS2。
  2. 重新创建表 T2 及其索引。
  3. 运行装载步骤,从 10000 行开始。
  4. KEEPDICTIONARY 选项替换 REORG 语句中的 RESETDICTIONARY 选项:

    REORG TABLE DB2INST1.T2 INDEX DB2INST1.L_OK_LN2 ALLOW
                    READ ACCESS KEEPDICTIONARY
                    ;
                    

  5. 现在分别对 50000 行和整个表重复 T2 的装载步骤。
  6. 比较 NPAGES 与 syscat.tables 中的其他的压缩值。

第5页
{$PageTitle=结束语}

结束语

    本教程通过一系列的练习让您熟悉了 DB2 9 的行压缩特性。您现在应该了解了 DB2 9 中数据压缩的概念和功能,并且应该知道如何:

  • 启用/禁用行压缩。
  • 使用表空间创建中的新选项以使一个页可以包含更多的行。
  • 使用不同的 SQL 查询和工具来分析压缩的优点。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9403012/viewspace-456/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-08-16

  • 博文量
    101
  • 访问量
    69635