IBM Cognos 最佳实践: 在 Adaptive Warehouse Projects 中使用 DB2 Row Compression

产品:Adaptive Analytics Framework、Adaptive Warehouse、Adaptive Analytics、DB2 LUW;关注领域:性能

本文介绍了一个 Adaptive Warehouse 生成的数据仓库中的 DB2 使用行压缩的实现。

Greg Harris, 软件工程师, IBM

Greg Harris 是一名 Database Administrator 和 ERP 软件专家,他从 2004 年起成为 Adaptive Applications Framework 开发团队的一员。



2011 年 7 月 22 日

免费下载:IBM® Cognos® Express V9.5 或者 Cognos® 8 Business Intelligence Developer Edition V8.4 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

简介

目的

本文介绍了一个 Adaptive Warehouse 生成的数据仓库中的 DB2 行压缩(row compression)的实现。DB2 行压缩功能可以减少磁盘存储需求,同时改进执行全表扫描的大量查询的顺序读取访问。

适用性

Adaptive Warehouse 所有版本都使用 DB2 ESE LUW 9.1 版本,这些版本带有行压缩选项,作为一个目标数据仓库存备份存储。

例外与除外责任

无。


关于 DB2 Row Compression

DB2 行压缩被设计用来减少数据库对象的磁盘存储空间需求。实现方法是创建一个压缩的符号字典,取代行级重复数据模式。在执行字典翻译时有一些 CPU 开销,但是在磁盘 IO 效率上也有一个提高,在每个 IO 单元更多数据可被检索。在 DB2 缓冲池中数据页面也保持压缩的,为了潜在的重用允许更多的数据缓存在内存中。

在 DB2 上使用 Adaptive Warehouse (AW)(包含大量事实表)实现的应用程序是提高 DB2 行压缩最合适的候选者。当行压缩被应用时,执行全表扫描的报表查询在查询性能上可以得到显著的改善。


没有启用和启用 Row Compression 时的 Query Performance 示例

以下是 DB2BATCH 运行样例摘要,说明启动行压缩之前和之后的查询执行时间。这些示例涉及到 GOSales 样例数据仓库应用程序中的事实表 ORDER_ACTIVITY_MEASURES。这个报表查询需要该事实表的一个全表扫描。

* Timestamp: Mon Jun 29 2009 12:08:05 Eastern Daylight Time
--------------------------------------------- 

* SQL Statement Number 1:
 
SELECT "T0"."C0" "Yearkey"           , 
       "T0"."C1" "C__memberCaption"  , 
       "T0"."C0" "Ordinal0"          , 
       "T0"."C2" "Quarterkey"        , 
       "T0"."C3" "C__memberCaption0" , 
       "T0"."C4" "Ordinal1"          , 
       "T0"."C5" "Monthkey"          , 
       "T0"."C6" "C__memberCaption1" , 
       "T0"."C7" "Ordinal"           , 
       "T0"."C8" "C___PMT"           , 
       "T0"."C9" "C___SMT1" 
FROM 
       (SELECT  "ALL_TIME"."GREGORIAN_CALENDAR_YEAR"                      "C0" , 
                "ALL_TIME"."GREGORIAN_YEAR_CAPTION"                       "C1" , 
                "ALL_TIME"."GREGORIAN_CALENDAR_QUARTER"                   "C2" , 
                "ALL_TIME"."GREGORIAN_QUARTER_CAPTION"                    "C3" , 
                "ALL_TIME"."GREGORIAN_CALENDAR_YEAR" * 4 
                  + "ALL_TIME"."GREGORIAN_CALENDAR_QUARTER"               "C4" , 
                "ALL_TIME"."GREGORIAN_CALENDAR_MONTH"                     "C5" , 
                "ALL_TIME"."GREGORIAN_MONTH_CAPTION"                      "C6" , 
                "ALL_TIME"."GREGORIAN_CALENDAR_YEAR" * 12
                  + "ALL_TIME"."GREGORIAN_CALENDAR_MONTH"                 "C7" , 
                SUM("Order_Activity_Measures"."ORDER_NET_AMOUNT")         "C8" , 
                SUM("Order_Activity_Measures"."ORDER_MARGIN_AMOUNT")      "C9" 
       FROM     "DB2ADMIN"."ALL_TIME" "ALL_TIME", 
                "DB2ADMIN"."ORDER_ACTIVITY_MEASURES" "Order_Activity_Measures" 
       WHERE    "ALL_TIME"."GREGORIAN_CALENDAR_YEAR" * 12
                  + "ALL_TIME"."GREGORIAN_CALENDAR_MONTH" BETWEEN 24062 AND 24073 
            AND ( 
                         'Rolling Periods' = 'Rolling Periods' 
                      OR 'Rolling Periods' = 'YTD' 
                ) 
            AND "ALL_TIME"."CALENDAR_TYPE_NAME" IN ('Gregorian', 
                                                    '-'        , 
                                                    'Late Date', 
                                                    'Early Date') 
            AND NOT "ALL_TIME"."GREGORIAN_CALENDAR_YEAR" IS NULL 
            AND "ALL_TIME"."CALENDAR_TYPE_NAME" IN ('Gregorian', 
                                                    '-'        , 
                                                    'Late Date', 
                                                    'Early Date') 
            AND NOT "ALL_TIME"."GREGORIAN_CALENDAR_QUARTER" IS NULL 
            AND "ALL_TIME"."CALENDAR_TYPE_NAME" IN ('Gregorian', 
                                                    '-'        , 
                                                    'Late Date', 
                                                    'Early Date') 
            AND NOT "ALL_TIME"."GREGORIAN_CALENDAR_MONTH" IS NULL 
            AND "Order_Activity_Measures"."ORDER_DATE_ALL_TIME_SID"
                  = "ALL_TIME"."ALL_TIME_SID" 
       GROUP BY "ALL_TIME"."GREGORIAN_CALENDAR_YEAR"                      , 
                "ALL_TIME"."GREGORIAN_YEAR_CAPTION"                       , 
                "ALL_TIME"."GREGORIAN_CALENDAR_QUARTER"                   , 
                "ALL_TIME"."GREGORIAN_QUARTER_CAPTION"                    , 
                "ALL_TIME"."GREGORIAN_CALENDAR_YEAR" * 4
                  + "ALL_TIME"."GREGORIAN_CALENDAR_QUARTER", 
                "ALL_TIME"."GREGORIAN_CALENDAR_MONTH"                     , 
                "ALL_TIME"."GREGORIAN_MONTH_CAPTION"                      , 
                "ALL_TIME"."GREGORIAN_CALENDAR_YEAR" * 12
                  + "ALL_TIME"."GREGORIAN_CALENDAR_MONTH" 
       ) "T0" FOR 
FETCH ONLY; 
 
Yearkey     C__memberCaption     Ordinal0    Quarterkey  C__memberCaption0    Ordinal1   
 Monthkey    C__memberCaption1    Ordinal     C___PMT                           C___SMT1
----------- -------------------- ----------- ----------- -------------------- -----------
 ----------- -------------------- ----------- --------------------------------- ---------
------------------------ 
       2006 2006                        2006           1 Q1/2006                     8025
           1 Jan/2006                   24073                     2704738640.30          
           1129361652.90 

* 1 row(s) fetched, 1 row(s) output.

* Elapsed Time is:      12.079891 seconds

* Summary Table:
 
Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)  
 Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output 
--------- ----------- ----------- -------------- -------------- --------------
 --------------- -------------- -------------- ------------- 
Statement           1           1      12.079891      12.079891      12.079891
       12.079891      12.079891              1             1 

* Total Entries:              1
* Total Time:                12.079891 seconds
* Minimum Time:              12.079891 seconds
* Maximum Time:              12.079891 seconds
* Arithmetic Mean Time:      12.079891 seconds
* Geometric Mean Time:       12.079891 seconds
--------------------------------------------- 
* Timestamp: Mon Jun 29 2009 12:08:18 Eastern Daylight Time

启用压缩后,查询执行速度快了近 30%,如下所示:

* 1 row(s) fetched, 1 row(s) output.

* Elapsed Time is:       8.547579 seconds

* Summary Table:
 
Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)  
 Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output 
--------- ----------- ----------- -------------- -------------- --------------
 --------------- -------------- -------------- ------------- 
Statement           1           1       8.547579       8.547579       8.547579
        8.547579       8.547579              1             1 

* Total Entries:              1
* Total Time:                 8.547579 seconds
* Minimum Time:               8.547579 seconds
* Maximum Time:               8.547579 seconds
* Arithmetic Mean Time:       8.547579 seconds
* Geometric Mean Time:        8.547579 seconds
--------------------------------------------- 
* Timestamp: Mon Jun 29 2009 12:36:24 Eastern Daylight Time

改变 Adaptive Warehouse Fact Tables 启动 Row Compression

如何实现行压缩在 DB2 version 9.1 和更高版本上有一些不同。

对于 DB2 9.1:

  1. 运行 AW 负载管理来传递一些初始数据进行一个事实表构建。单击运行按钮之前,检查同步表并注意以 “_MEASURES” 结束的表对象的名称。这将是在数据仓库中创建的事实表的物理名称。完成数据加载。
  2. 使用 DB2cmd SQL 工具,改变(a)中标注的 AW 事实表来启动行压缩。例如,要为表 MYSCHEMA.JOB_ACTION_MEASURES 启动行压缩,输入命令:
    ALTER TABLE MYSCHEMA.JOB_ACTION_MEASURES COMPRESS YES;
  3. 重组表来创建一个压缩字典然后压缩现有内容,例如:
    REORG TABLE MYSCHEMA.JOB_ACTION_MEASURES RESETDICTIONARY;
  4. 如往常一样继续负载管理操作。所有添加到表中的后续数据将被压缩。

对于更高版本 DB2:

  1. 对于 DB2 9.5,当数据被加载到事实表时,数据库可以自动创建压缩字典。使用负载管理检查事实构建表对象同步选项卡,使用以 “_MEASURES” 结束的名称定位它们,并标记名称和状态。
  2. 如果同步状态是 “will be created”,选择 “Script”,并选择 “Create a separate script for database synchronisation” 选项然后单击 OK。编辑生成的 SQL 脚本,对于标记的事实表添加 COMPRESS YES 子句到 CREATE TABLE 语句。在数据仓库模式上运行 SQL 脚本。
  3. 如果同步状态是 “same as db”,使用 DB2cmd SQL 编辑器改变此表,例如:
    ALTER TABLE MYSCHEMA.JOB_ACTION_MEASURES COMPRESS YES;
  4. 如往常一样继续负载管理操作。所有添加到该表的后续数据将被压缩。

跨 AW Fact Tables 启动 Row Compression 的脚本

以下脚本可被编辑并在您的数据仓库数据库上运行,来生成一个脚本跨所有 AW 创建的事实表启动行压缩。以下脚本将使用通配符表达式 %_MEASURES 以及脚本 ALTER TABLE 和 REORG 命令扫描指定的 AW 事实表。注意脚本立即运行生成的命令,这样可以审查相应脚本。

如果运行 DB2 9.1,您将不得不运行负载管理来初步传递一些数据到您的事实表。对于更高版本的 DB2,您可以删除或忽略 REORG 语句或它们生成的 SQL,这并不是运行所必需的。

在安装 Adaptive Application Framework 时,这个脚本的一个副本被安装在 <AW root >\PW\scripts 文件夹,见文件 example_row_compression.db2。

db2 -- example_row_compression.db2
db2 --
db2 -- Description:  	This script enables db2 row compression 
db2 -- 			on all AAF described fact tables. 
db2 -- 			Note that DB2 Enterprise Edition 
db2 -- 			with the row compresson option or 
db2 --			DB2 Data Warehouse Edition is 
db2 -- 			require to support this.
db2 -- 			Ensure DB is idle before using and 
db2 -- 			warehouse is populated with data. 
db2 -- 
db2 -- Usage:		Edit for proper <database_name> and 
db2 -- 			<schema_name> . Run from DB2 CLIP window.
db2 --

db2 connect to <database_name>

db2 -x -z comp1.sql "select distinct 'ALTER TABLE <schema_name>.'||a.name||'
 COMPRESS YES;' from <schema_name>.pwd_physical_object a,
 <schema_name>.pwd_physical_object_type b where a.type_code=b.type_code
 and trim(b.type_name)='fact_detail'"

db2 -tvf comp1.sql

db2 commit

db2 -x -z comp2.sql "select distinct 'REORG TABLE <schema_name>.'||a.name||'
 RESETDICTIONARY;' from <schema_name>.pwd_physical_object a,
 <schema_name>.pwd_physical_object_type b where a.type_code=b.type_code
 and trim(b.type_name)='fact_detail'"

db2 -tvf comp2.sql

db2 commit

db2 terminate

样例脚本输出创建:

ALTER TABLE WH.ORDER_ACTIVITY_MEASURES COMPRESS YES;
ALTER TABLE WH.RETURN_ACTIVITY_MEASURES COMPRESS YES;
ALTER TABLE WH.SHIPPED_ORDERS_SUMM_MEASURES COMPRESS YES;

REORG TABLE WH.ORDER_ACTIVITY_MEASURES RESETDICTIONARY;
REORG TABLE WH.RETURN_ACTIVITY_MEASURES RESETDICTIONARY;
REORG TABLE WH.SHIPPED_ORDERS_SUMM_MEASURES RESETDICTIONARY;

参考资料

学习

获得产品和技术

讨论

  • 参与 developerWorks 博客 并加入 developerWorks 中文社区,developerWorks 社区是一个面向全球 IT 专业人员,可以提供博客、书签、wiki、群组、联系、共享和协作等社区功能的专业社交网络社区。

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=677651
ArticleTitle=IBM Cognos 最佳实践: 在 Adaptive Warehouse Projects 中使用 DB2 Row Compression
publish-date=07222011