Повышение производительности запросов DB2 в среде интеллектуального бизнес-анализа

                		
                                  PRODUCT_DIM             DATE_DIM
                                            \            /
                                             \          /
                                              SALES_FACT
                                                  |
                                                  |
		                             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;

                
DB2 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~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
			

                
System: WIN32_NT SKAPOOR Service Pack 2 5.1 x86 Family 15, model 4, stepping 3
			

                
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
			

                
Log file size (4KB)                         (LOGFILSIZ) = 8192
Number of primary log files                (LOGPRIMARY) = 100
Number of secondary log files               (LOGSECOND) = 150
			

                
DB2_DB2           60000/tcp
DB2_DB2_1         60001/tcp
DB2_DB2_2         60002/tcp
DB2_DB2_END       60003/tcp
DB2c_DB2          50000/tcp
			

                
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
			

                
db2 create database dss_db on D:\;

                
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

db2 -tvf STORAGE.ddl -z storage.log
                        

                
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:\database\fact_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:\database\fact_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:\database\fact_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:\database\fact_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:\database\date_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:\database\product_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:\database\store_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;

                
				db2batch -d <dbname> -f <input_file> -i <elapsed_time> -iso <isolation level>
				-o p <perf_detail> o <optlevel> r <rows_out> -r <result_file> 

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r <output filename>

			
db2 -tvf TEST1.ddl -z test1.log
			

                
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;
			

                
db2 -td@ -vf date_insert.txt -z date_insert.log
			

                
db2 -td@ -vf product_insert.txt -z product_insert.log
			

                
db2 -td@ -vf store_insert.txt -z store_insert.log
			

                
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@
			

                
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@
			

                
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@

                
db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log
			

                
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@
			

                
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@
			

                
db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log
			

                
CONNECT TO DSS_DB;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CREATE INDEX SKAPOOR.IND_DATE_STORE ON SKAPOOR.SALES_FACT (DATE_ID, STORE_ID);
CONNECT RESET;
			

                
db2 -tvf runstats.ddl -z runstats.log
			

                
CONNECT TO DSS_DB;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;
			

                
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
		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)
			

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf JOIN_ELIM_QUERY.SQL -z JOIN_ELIM_QUERY.log
db2 set current explain mode no
db2 connect reset
			

 
db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o join_elim.txt
			

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf QUERY1.SQL -z QUERY1.log
db2 set current explain mode no
db2 connect reset
			

db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o test1.txt
			

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r test1.results.txt	

* Prepare Time is:       7.278206 seconds
* Execute Time is:     107.729436 seconds
* Fetch Time is:         0.000102 seconds
* Elapsed Time is:     115.007744 seconds (complete)
			

db2 -tvf replicated.ddl
			

                
connect to dss_db;

drop table skapoor.store_dim_rep;
drop table skapoor.product_dim_rep;
drop table skapoor.date_dim_rep;

create table skapoor.store_dim_rep as (select * from skapoor.store_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.product_dim_rep as (select * from skapoor.product_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.date_dim_rep as (select * from skapoor.date_dim)
data initially deferred refresh deferred in FACT_SMS replicated;

refresh table skapoor.store_dim_rep;
refresh table skapoor.product_dim_rep;
refresh table skapoor.date_dim_rep;

create index skapoor.store_dim_id_rep on skapoor.store_dim_rep (store_id);
create index skapoor.product_dim_id_rep on skapoor.product_dim_rep (product_id);
create index skapoor.date_dim_id_rep on skapoor.date_dim_rep (date_id);

runstats on table skapoor.store_dim_rep with distribution and indexes all;
runstats on table skapoor.product_dim_rep with distribution and indexes all;
runstats on table skapoor.date_dim_rep with distribution and indexes all;

connect reset;
			

                
db2_all db2 update db cfg for DSS_DB using dft_refresh_age any
db2 terminate
			

                
db2 -tvf mdc_tablespace.ddl -z mdc_tablespace.log
			

                
CREATE REGULAR TABLESPACE FACT_SMS_MDC_EX IN DATABASE PARTITION GROUP
FACT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:\database\fact_tbsp_mdc_EX120') ON DBPARTITIONNUMS (0)
USING ('d:\database\fact_tbsp_mdc_EX121') ON DBPARTITIONNUMS (1)
USING ('d:\database\fact_tbsp_mdc_EX122') ON DBPARTITIONNUMS (2)
USING ('d:\database\fact_tbsp_mdc_EX123') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 12
	 PREFETCHSIZE 24
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;
			

                
db2 -tvf sales_fact_mdc.ddl -z sales_fact_mdc.log
			

                
CONNECT TO DSS_DB;

---------------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT_MDC_1"
---------------------------------------------------------
 

CREATE TABLE "SKAPOOR "."SALES_FACT_MDC_1"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "RESERVE" CHAR(100) )   
		 DISTRIBUTE BY HASH("DATE_ID")   
		   IN "FACT_SMS_MDC_EX"  
		 ORGANIZE BY ( 
		  ( "DATE_ID" ) , 
		  ( "STORE_ID" ) ) 
		 ; 

COMMIT WORK;
CONNECT RESET;
			

                
db2 -tvf sales_fact_mdc_insert_alter.ddl -z sales_fact_mdc_insert.log
			

                
CONNECT TO DSS_DB;

VALUES(CURRENT TIMESTAMP);

-----------------------------------
-- SET OPTLEVEL 0 TO FAVOUR INDEX ACCESS TO IMPROVE PERFORMANCE OF INSERT.
SET CURRENT QUERY OPTIMIZATION 0;
-----------------------------------

-- INSERTING THE DATA IN THE ORDER OF THE MDC COLUMNS IMPROVES 
-- THE PERFORMANCE OF THE INSERT.
INSERT INTO SKAPOOR.SALES_FACT_MDC_1 SELECT *
FROM SKAPOOR.SALES_FACT ORDER BY DATE_ID,STORE_ID;

ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES SKAPOOR.DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY (STORE_ID) REFERENCES SKAPOOR.STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY (PRODUCT_ID)
REFERENCES SKAPOOR.PRODUCT_DIM;

VALUES(CURRENT TIMESTAMP);

RUNSTATS ON TABLE SKAPOOR.SALES_FACT_MDC_1 WITH DISTRIBUTION AND INDEXES ALL;
			

                
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_MDC_1 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_MDC_1 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;
			

                
db2 -tvf tablepart.ddl -z tablepart.log
                        

                
CONNECT TO DSS_DB;

CREATE REGULAR TABLESPACE FACT_TPART_SMS IN DATABASE PARTITION GROUP FACT_GROUP
PAGESIZE 4096 MANAGED BY SYSTEM 
	 USING ('d:\database\fact_tpart_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:\database\fact_tpart_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:\database\fact_tpart_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:\database\fact_tpart_tbsp3') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING;

-- CREATE THE SALES_FACT TABLE PARTITIONED ON DATE_ID
-- WITH A SINGLE PARTITION TO START WITH, CONTAINING
-- ALL SALES FROM JANUARY
CREATE TABLE "SKAPOOR "."SALES_FACT_TPART"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")   
                   IN "FACT_TPART_SMS"
                 PARTITION BY ("DATE_ID")
                    (PART Jan STARTING ('1/1/2006') ENDING ('1/31/2006'))
;

VALUES (CURRENT TIMESTAMP);

-- POPULATE THE SALES FROM JAN
INSERT INTO "SKAPOOR"."SALES_FACT_TPART"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '1/1/2006' AND '1/31/2006';

commit work;

VALUES (CURRENT TIMESTAMP);

-- CREATE TABLES FOR SALES FROM EACH MONTH
-- WHICH WILL THEN BE ATTACHED TO SALES_FACT_TPART TABLE
CREATE TABLE "SKAPOOR"."SALES_FEB" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_MAR" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_APR" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_MAY" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_JUN" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_JUL" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_AUG" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_SEP" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_OCT" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_NOV" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_DEC" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";

VALUES (CURRENT TIMESTAMP);

-- POPULATE EACH TABLE WITH SALES FOR THE CORRESPONDING MONTH
INSERT INTO "SKAPOOR"."SALES_FEB"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '2/1/2006' AND '2/28/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_MAR"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '3/1/2006' AND '3/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_APR"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '4/1/2006' AND '4/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_MAY"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '5/1/2006' AND '5/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_JUN"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '6/1/2006' AND '6/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_JUL"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '7/1/2006' AND '7/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_AUG"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '8/1/2006' AND '8/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_SEP"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '9/1/2006' AND '9/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_OCT"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '10/1/2006' AND '10/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_NOV"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '11/1/2006' AND '11/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_DEC"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '12/1/2006' AND '12/31/2006';

commit work;

VALUES (CURRENT TIMESTAMP);

-- Attach SALES from February and March
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Feb STARTING FROM '2/1/2006' ENDING AT '2/28/2006' 
  FROM "SKAPOOR"."SALES_FEB";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Mar STARTING FROM '3/1/2006' ENDING AT '3/31/2006'
  FROM "SKAPOOR"."SALES_MAR";

-- Make the partitions visible
SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;

commit work;

-- Attach SALES from April to June
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Apr STARTING FROM '4/1/2006' ENDING AT '4/30/2006'
  FROM "SKAPOOR"."SALES_APR";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION May STARTING FROM '5/1/2006' ENDING AT '5/31/2006'
  FROM "SKAPOOR"."SALES_MAY";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Jun STARTING FROM '6/1/2006' ENDING AT '6/30/2006'
  FROM "SKAPOOR"."SALES_JUN";

SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;

commit work;

-- Attach SALES from July to Dec
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Jul STARTING FROM '7/1/2006' ENDING AT '7/31/2006' 
  FROM "SKAPOOR"."SALES_JUL";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Aug STARTING FROM '8/1/2006' ENDING AT '8/31/2006' 
  FROM "SKAPOOR"."SALES_AUG";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Sep STARTING FROM '9/1/2006' ENDING AT '9/30/2006' 
  FROM "SKAPOOR"."SALES_SEP";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Oct STARTING FROM '10/1/2006' ENDING AT '10/31/2006'
  FROM "SKAPOOR"."SALES_OCT";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Nov STARTING FROM '11/1/2006' ENDING AT '11/30/2006'
  FROM "SKAPOOR"."SALES_NOV";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Dec STARTING FROM '12/1/2006' ENDING AT '12/31/2006'
  FROM "SKAPOOR"."SALES_DEC";

SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;
yo
commit work;

VALUES(CURRENT TIMESTAMP);

RUNSTATS ON TABLE SKAPOOR.SALES_FACT_TPART WITH DISTRIBUTION;

commit work;

CONNECT RESET;
                        

                
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_TPART 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_TPART F2,
		SKAPOOR.DATE_DIM D1,
		SKAPOOR.PRODUCT_DIM P1,
		SKAP