PRODUCT_DIM DATE_DIM
/
/
SALES_FACT
|
|
STORE_DIM 其中的表的定义如下：| 表名 | 类型 | 列名 | 数据类型 | 列描述 |
| SALES_FACT | FACT TABLE | DATE_ID | DATE | 产品售出日期 |
| PRODUCT_ID | INT | 所购买产品的标识符 | ||
| STORE_ID | INT | 出售产品的商店的标识符 | ||
| QUANTITY | INT | 这次交易中售出产品的数量 | ||
| PRICE | INT | 产品购买价格。[为了简单起见，该字段为整型，但是使用小数型更符合实际] | ||
| TRANSACTION_DETAILS | CHAR(100) | 关于此次交易的描述/具体信息 | ||
| DATE_DIM | DIMENSION TABLE | DATE_ID NOT NULL | DATE | 惟一标识符 |
| MONTH | INT | 日期记录所属的月份 | ||
| QUARTER | INT | 日期记录所属的季度（第 1、第 2、第 3 或第 4 季度） | ||
| YEAR | INT | 日期记录所属的年份 | ||
| PRODUCT_DIM | DIMENSION TABLE | PRODUCT_ID NOT NULL | INT | 产品惟一标识符 |
| PRODUCT_DESC | CHAR(20) | 对产品的描述 | ||
| MODEL | CHAR(200) | 产品型号 | ||
| MAKE | CHAR(50) | 产品的质地 | ||
| STORE_DIM | DIMENSION TABLE | STORE_ID NOT NULL | INT | 商店惟一标识符 |
| LOCATION | CHAR(15) | 商店位置 | ||
| DISTRICT | CHAR(15) | 商店所属街区 | ||
| REGION | CHAR(15) | 商店所属区域 |
| 数据库分区组名 | 数据库分区数 |
| FACT_GROUP | 0,1,2,3 |
| DATE_GROUP | 1 |
| PRODUCT_GROUP | 2 |
| STORE_GROUP | 3 |
| 表空间名 | 数据库分区组 | 表 |
| FACT_SMS | FACT_GROUP | SALES_FACT |
| DATE_SMS | DATE_GROUP | DATE_DIM |
| PRODUCT_SMS | PRODUCT_GROUP | PRODUCT_DIM |
| STORE_SMS | STORE_GROUP | STORE_DIM |
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
D.MONTH AS MONTH,
S.STORE_ID AS STORE_ID,
S.DISTRICT AS DISTRICT,
S.REGION AS REGION,
SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT
FROM
SKAPOOR.SALES_FACT F1,
SKAPOOR.DATE_DIM D,
SKAPOOR.PRODUCT_DIM P,
SKAPOOR.STORE_DIM S
WHERE
P.MODEL LIKE '%model%' AND
F1.DATE_ID=D.DATE_ID AND
F1.PRODUCT_ID=P.PRODUCT_ID AND
F1.STORE_ID=S.STORE_ID AND
F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
D.MONTH = 1
GROUP BY
S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
( SELECT
D1.MONTH AS MONTH,
S1.STORE_ID AS STORE_ID,
S1.DISTRICT AS DISTRICT,
S1.REGION AS REGION,
SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
FROM
SKAPOOR.SALES_FACT F2,
SKAPOOR.DATE_DIM D1,
SKAPOOR.PRODUCT_DIM P1,
SKAPOOR.STORE_DIM S1
WHERE
P1.MODEL LIKE '%model%' AND
F2.DATE_ID=D1.DATE_ID AND
F2.PRODUCT_ID=P1.PRODUCT_ID AND
F2.STORE_ID=S1.STORE_ID AND
F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
D1.MONTH=11
GROUP BY
S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)
SELECT
A.*,
B.*
FROM
TMP1 A LEFT OUTER JOIN TMP2 B ON
(A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC; 环境设置 本文的测试是使用以下环境执行的： 清单 3. db2levelDB2 9 Enterprise Edition:
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:PROGRA~1IBMSQLLIB" with DB2 Copy Name "DB2COPY1". 清单 4. 操作系统System: WIN32_NT SKAPOOR Service Pack 2 5.1 x86 Family 15, model 4, stepping 3 清单 5. 硬件CPU: total:2 online:2 Cores per socket:1 Threading degree per core:2
Physical Memory(MB): total:2551 free:1988 available:1949
Virtual Memory(MB): total:4950 free:6575
Swap Memory(MB): total:2399 free:4587
1 Physical disk Size 100GB 空间需求 为了重新创建本文中描述的所有测试用例，需要高达 20Gb 的磁盘空间来存放数据和日志文件。其中将近 13Gb 的空间要分配给日志文件。我们要使用循环日志记录，分配 100 个主日志： 清单 6. 用于日志的数据库配置Log file size (4KB) (LOGFILSIZ) = 8192
Number of primary log files (LOGPRIMARY) = 100
Number of secondary log files (LOGSECOND) = 150 略加修改为事实表填充数据的脚本，即可减少日志文件所需的磁盘空间。本文的后面将对此进行讨论。 设置数据库 第一步是创建一个测试数据库。 在本文的测试中，创建了 4 个逻辑数据分区。在 etcservices 文件中，应确保有足够的端口用于创建 4 个数据分区。在我们的测试环境中，文件 C:WINDOWSsystem32driversetcservices 中包含关于实例 "DB2" 的以下内容： 清单 7. services 文件的内容DB2_DB2 60000/tcp
DB2_DB2_1 60001/tcp
DB2_DB2_2 60002/tcp
DB2_DB2_END 60003/tcp
DB2c_DB2 50000/tcp 为向实例添加数据库分区，可使用 DB2 CLP 执行以下命令： 清单 8. 使用 db2ncrt 命令创建数据库分区db2stop
db2ncrt /n:1 /u:username,passWord /i:DB2 /m:machine /p:1
db2ncrt /n:2 /u:username,password /i:DB2 /m:machine /p:2
db2ncrt /n:3 /u:username,password /i:DB2 /m:machine /p:3 其中 /u 选项所表示的用户名和密码，/m 选项所表示的计算机名，以及 /i 选项所表示的实例名应该根据您自己的环境加以修改。 创建数据库 创建数据库 DSS_DB。这里使用 D: 盘存储该数据库。请根据您自己的环境进行调整。 清单 9. 创建数据库的命令db2 create database dss_db on D:; 数据库和数据库治理器是使用下面的设置来配置的。db2_all 工具用于设置所有数据库分区上的数据库配置和数据库治理器配置。 清单 10. 更新数据库治理器配置的语句db2_all update dbm cfg
using cpuspeed 2.282997e-007 intra_parallel NO comm_bandwidth 100.000000
db2_all update db cfg for DSS_DB
using locklist 2450 dft_degree 1 maxlocks 60
avg_appls 1 stmtheap 16384 dft_queryopt 5 创建数据库分区组和表空间 使用以下语句创建数据库分区组和表空间。可以将这些语句复制到一个名为 STORAGE.ddl 的文件中，然后使用下面的命令执行它们：db2 -tvf STORAGE.ddl -z storage.log清单 11. 创建数据库分区组和表空间的语句CONNECT TO DSS_DB;
--------------------------------------------------
-- DDL Statements for DATABASE PARTITION GROUPS --
--------------------------------------------------
CREATE DATABASE PARTITION GROUP "FACT_GROUP" ON DBPARTITIONNUMS
(0,
1,
2,
3);
CREATE DATABASE PARTITION GROUP "DATE_GROUP" ON DBPARTITIONNUMS
(1);
CREATE DATABASE PARTITION GROUP "PRODUCT_GROUP" ON DBPARTITIONNUMS
(2);
CREATE DATABASE PARTITION GROUP "STORE_GROUP" ON DBPARTITIONNUMS
(3);
COMMIT WORK;
------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE REGULAR TABLESPACE FACT_SMS IN DATABASE PARTITION GROUP FACT_GROUP
PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:databasefact_tbsp0') ON DBPARTITIONNUMS (0)
USING ('d:databasefact_tbsp1') ON DBPARTITIONNUMS (1)
USING ('d:databasefact_tbsp2') ON DBPARTITIONNUMS (2)
USING ('d:databasefact_tbsp3') ON DBPARTITIONNUMS (3)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE REGULAR TABLESPACE DATE_SMS IN DATABASE PARTITION GROUP
DATE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:databasedate_group') ON DBPARTITIONNUMS (1)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE REGULAR TABLESPACE PRODUCT_SMS IN DATABASE PARTITION GROUP
PRODUCT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:databaseproduct_group') ON DBPARTITIONNUMS (2)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE REGULAR TABLESPACE STORE_SMS IN DATABASE PARTITION GROUP
STORE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:databasestore_group') ON DBPARTITIONNUMS (3)
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
COMMIT WORK;
-- Mimic tablespace
ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
TRANSFERRATE 0.060000;
ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
TRANSFERRATE 0.060000;
ALTER TABLESPACE USERSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
TRANSFERRATE 0.060000;
COMMIT WORK;
------------------------------------------------
-- Update the bufferpool to use 1000 4K pages --
------------------------------------------------
ALTER BUFFERPOOL IBMDEFAULTBP SIZE 1000;
COMMIT WORK;
CONNECT RESET; 注重：表空间被定义为 "NO FILE SYSTEM CACHING"，以避免文件系统缓存歪曲测试各种方法时得到的评测结果。 使用 db2batch 工具评测性能 db2batch 程序用于运行 清单 2 中的主查询。为了使用 db2batch 命令运行该查询，需要将查询保存在一个以分号结尾的文件中，并使用以下选项，使 db2batch 工具查看计时情况： 清单 12. 使用 db2batch 评测查询的性能 db2batch -d <dbname> -f <input_file> -i <elapsed_time> -iso <isolation level>
-o p <perf_detail> o <optlevel> r <rows_out> -r <result_file> 其中 <dbname> 是数据库名称，<input_file> 是以分号结尾、包含查询的文件。 -iso <isolation level>: 在我们的测试中，默认隔离级别是 CS，但是默认情况下 db2batch 工具使用隔离级别 RR。假如使用隔离级别 RR 执行一个查询，那么使用隔离级别 CS 创建的 MQT 不会被考虑。为了解决这个问题，可以在 db2batch 命令中使用 -iso 选项和隔离级别 CS，以便查询选择 MQT。而且，应用程序可使用默认的 CS 隔离级别，不带 -iso 选项运行 db2batch 会导致它使用 RR 隔离级别，并可能导致锁争用。 -o - options options: p <perf_detail>: 性能具体信息。返回数据库治理器、数据库、应用程序和语句的快照（只有在自动提交关闭，且处理的是单个语句，而非语句块时，才返回语句快照）。另外还返回缓冲池、表空间和 FCM的快照（只有在多数据库分区环境中才会返回 FCM 快照）。 对于例子 p 5，我们使用最具体的输出，但是也可以使用不同级别的性能输出。 o <optlevel>: 查询优化级别。（本文使用优化级别 5，这里不需要显式地指定这个优化级别，因为它是数据库的默认优化级别，如 清单 10 所示。） r <rows_out>: 所获取且将发送到输出的行数。我们的例子 r 0 不发送行。 -r <result_file>: 结果文件。在我们的例子中，results.txt 是输出文件名，db2batch 将结果输出到该文件中。 在本文中，我们使用：db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r <output filename> 提高查询性能的方法 在本节中，让我们逐步了解用于提高 清单 2 中描述的查询的性能的各种不同方法。在讨论任何方法之前，必须创建基本的事实表和维度表。 步骤 A：创建好表空间之后，就要创建事实表和维度表。可以将 SKAPOOR 改为符合您自己环境的模式名。这样做时，务必更新 清单 2 中的查询，以反映适当的模式名。可以将下面的语句复制到一个名为 TEST1.ddl 的文件中，然后使用以下命令来执行该文件：db2 -tvf TEST1.ddl -z test1.log 清单 13. TEST1.ddl 的内容CONNECT TO DSS_DB;
---------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT"
---------------------------------------------------
CREATE TABLE "SKAPOOR "."SALES_FACT" (
"DATE_ID" DATE ,
"PRODUCT_ID" INTEGER ,
"STORE_ID" INTEGER ,
"QUANTITY" INTEGER ,
"PRICE" INTEGER ,
"TRANSACTION_DETAILS" CHAR(100) )
DISTRIBUTE BY HASH("DATE_ID")
IN "FACT_SMS" ;
-------------------------------------------------
-- DDL Statements for table "SKAPOOR "."DATE_DIM"
-------------------------------------------------
CREATE TABLE "SKAPOOR "."DATE_DIM" (
"DATE_ID" DATE NOT NULL ,
"MONTH" INTEGER ,
"QUARTER" INTEGER ,
"YEAR" INTEGER )
IN "DATE_SMS" ;
-- DDL Statements for primary key on Table "SKAPOOR "."DATE_DIM"
-- DATE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."DATE_DIM"
ADD PRIMARY KEY
("DATE_ID");
----------------------------------------------------
-- DDL Statements for table "SKAPOOR "."PRODUCT_DIM"
----------------------------------------------------
CREATE TABLE "SKAPOOR "."PRODUCT_DIM" (
"PRODUCT_ID" INTEGER NOT NULL ,
"PRODUCT_DESC" CHAR(20) ,
"MODEL" CHAR(10) ,
"MAKE" CHAR(10) )
IN "PRODUCT_SMS" ;
-- DDL Statements for primary key on Table "SKAPOOR "."PRODUCT_DIM"
-- PRODUCT_ID is the unique identifier
ALTER TABLE "SKAPOOR "."PRODUCT_DIM"
ADD PRIMARY KEY
("PRODUCT_ID");
--------------------------------------------------
-- DDL Statements for table "SKAPOOR "."STORE_DIM"
--------------------------------------------------
CREATE TABLE "SKAPOOR "."STORE_DIM" (
"STORE_ID" INTEGER NOT NULL ,
"LOCATION" CHAR(15) ,
"DISTRICT" CHAR(15) ,
"REGION" CHAR(15) )
IN "STORE_SMS" ;
-- DDL Statements for primary key on Table "SKAPOOR "."STORE_DIM"
-- STORE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."STORE_DIM"
ADD PRIMARY KEY
("STORE_ID");
COMMIT WORK;
CONNECT RESET; 步骤 B：创建好表后，将数据插入到三个维度表中，并根据您自己的环境调整模式： 清单 14. 填充 DATE_DIM 表db2 -td@ -vf date_insert.txt -z date_insert.log 清单 15. 填充 PRODUCT_DIM 表db2 -td@ -vf product_insert.txt -z product_insert.log 清单 16. 填充 STORE_DIM 表db2 -td@ -vf store_insert.txt -z store_insert.log 这三个文件的内容是： DATE_DIM 表被填入 2006 年所有 365 天的值。 清单 17. date_insert.txt 的内容connect to dss_db@
begin atomic
declare cnt INT default 1;
declare dat DATE default '01/01/2006';
declare yer INT default 2006;
declare quart INT default 1;
while (cnt <= 365) do
if (int(dat + cnt DAYS)/100) between 200601 and 200603 then
set quart=1;
elseif (int(dat + cnt DAYS)/100) between 200604 and 200606 then
set quart=2;
elseif (int(dat + cnt DAYS)/100) between 200607 and 200609 then
set quart=3;
elseif (int(dat + cnt DAYS)/100) between 200610 and 200612 then
set quart=4;
end if;
insert into SKAPOOR.DATE_DIM values (
dat + cnt DAYS,
(int(dat + cnt DAYS)/100) - 200600,
quart,
yer
);
set cnt=cnt+1;
end while;
end@
connect reset@ PRODUCT_DIM 表被填入 60,000 种产品。 清单 18. product_insert.txt 的内容connect to dss_db@
drop sequence seq1@
drop sequence seq2@
create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@
begin atomic
declare cnt INT default 1;
while (cnt < 60001) do
insert into SKAPOOR.PRODUCT_DIM values (
nextval for SEQ2,
'product desc' concat char(nextval for SEQ1),
'model ' concat char(integer(rand()*1000)),
'maker ' concat char(integer(rand()*500))
);
set cnt=cnt+1;
end while;
end@
drop sequence seq1@
drop sequence seq2@
connect reset@ STORE_DIM 表被填入 201 家商店。 清单 19. store_insert.txt 的内容connect to dss_db@
drop sequence seq2@
create sequence seq2 as integer start with 0 increment by 1@
begin atomic
declare cnt INT default 1;
while (cnt < 202) do
insert into SKAPOOR.STORE_DIM values (
nextval for SEQ2,
'location' concat char(integer(rand()*500)),
'district' concat char(integer(rand()*10)),
'region' concat char(integer(rand()*5))
);
set cnt=cnt+1;
end while;
end@
drop sequence seq2@
connect reset@ 步骤 C：将数据插入到 SALES_FACT 表中。根据您自己的环境调整模式。在我们的测试环境中，将数据插入到事实表花了约一个半小时的时间。 清单 20. 填充 SALES_FACT 表db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log 清单 21. sales_fact_insert.ddl 的内容connect to dss_db@
VALUES (CURRENT TIMESTAMP)@
begin atomic
declare cnt INT default 1;
declare cnt1 INT default 1;
declare dat DATE default '01/01/2006';
while (cnt <= 365) do
INSERT INTO SKAPOOR.SALES_FACT
with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
(values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
select DATE_ID, int(rand()*59999) + 1, int(rand()*200), int(rand()*50) + 1,
INT(RAND()*200 + 1), RESERVE, U_ID + 1
from v
where U_ID < 60000)
select date_id, product_id, store_id, quantity, price, transaction_details from v;
set cnt1 = cnt1 + 1;
set cnt = cnt + 1;
end while;
end@
VALUES (CURRENT TIMESTAMP)@
connect reset@ 注重：在 清单 21 中，SALES_FACT 表是在一次事务处理中填充的，这需要大量的磁盘空间来作日志记录。为了降低日志记录的影响，可以创建一个存储过程，并分步提交插入内容： 清单 22. 填充 SALES_FACT 表的另一种方法connect to dss_db@
VALUES (CURRENT TIMESTAMP)@
-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging
create procedure salesFactPopulate()
specific salesFactPopulate
language sql
begin
declare cnt INT default 1;
declare cnt1 INT default 1;
declare dat DATE default '01/01/2006';
while (cnt <= 365) do
INSERT INTO SKAPOOR.SALES_FACT
with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
(
values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
union all
select DATE_ID, int(rand()*59999) + 1, int(rand()*200), int(rand()*50) + 1,
INT(RAND()*200 + 1), RESERVE, U_ID + 1
from v
where U_ID < 60000
)
select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS from v;
commit work;
set cnt1 = cnt1 + 1;
set cnt=cnt+1;
end while;
end@
-- populate the SALES_FACT table
invoke salesFactPopulate@
VALUES (CURRENT TIMESTAMP)@
connect reset@ 步骤 D：为了理解各种不同的方法对所选查询访问计划的有怎样的影响，我们需要解释（Explain）查询，以查看 DB2 查询优化器选择的访问计划。为此，可使用 EXPLAIN 工具，这要求存在 EXPLAIN 表。为了创建 EXPLAIN 表，执行以下步骤： 1、进入 sqllibmisc 目录所在的位置。 2、在我们的测试环境中，这个位置为 "C:Program FilesIBMSQLLIBMISC"。 3、执行 db2 connect to dss_db。 4、执行 db2 -tvf EXPLAIN .DDL。 新闻热点
疑难解答
