SQL warehousing — Creating the partitioned table and the source table

Create a partitioned table that contains four partitions. Each partition stores data for one month of the year 2005. You also create a source table that contains data for the twelfth month of the year 2006.

Before you begin

Ensure that the GSDB sample database is created on your computer.

About this task

You will create the following tables in the GOSALES schema of the GSDB database:

INVENTORY_LEVEL_2005_PARTITIONED: This table is the partitioned version of the INVENTORY_LEVEL table. However, the table contains only inventory levels data for the year 2005, and only rows with PRODUCT_NUMBER equal to 1110 are considered for this example.
INVENTORY_LEVEL_2006: This table contains the source data that you will roll into the INVENTORY_LEVEL_2006_PARTITIONED table. The column definitions in this table are similar to the column definitions in the INVENTORY_LEVEL_2006_PARTITIONED table.

Procedure

To set up the partitioned table and source table:

From the DB2 command prompt, run the following SQL statements to create and insert data in the partitioned table:

Create table GOSALES. INVENTORY_LEVEL_2005_PARTITIONED ("INVENTORY_YEAR" SMALLINT 
NOT NULL,"INVENTORY_MONTH" SMALLINT NOT NULL,"WAREHOUSE_BRANCH_CODE" INTEGER 
NOT NULL, "PRODUCT_NUMBER" INTEGER NOT NULL,"OPENING_INVENTORY" INTEGER,"QUANTITY_SHIPPED" 
INTEGER,"ADDITIONS" INTEGER,"UNIT_COST" DECIMAL(19 , 2),"CLOSING_INVENTORY" INTEGER 
NOT NULL,"AVERAGE_UNIT_COST" DECIMAL(19 , 2)) DATA CAPTURE NONE IN "GOSALES_TS" 
PARTITION BY RANGE (INVENTORY_MONTH NULLS LAST) (PARTITION 
MONTH1 STARTING FROM (1) ENDING AT (2) EXCLUSIVE, PARTITION
MONTH2 STARTING FROM (2) ENDING AT (3) EXCLUSIVE, PARTITION
MONTH3 STARTING FROM (3) ENDING AT (4) EXCLUSIVE, PARTITION
MONTH4 STARTING FROM (4) ENDING AT (5) EXCLUSIVE, PARTITION 
MONTH5 STARTING FROM (5) ENDING AT (6) EXCLUSIVE, PARTITION
MONTH6 STARTING FROM (6) ENDING AT (7) EXCLUSIVE, PARTITION
MONTH7 STARTING FROM (7) ENDING AT (8) EXCLUSIVE, PARTITION
MONTH8 STARTING FROM (8) ENDING AT (9) EXCLUSIVE, PARTITION 
MONTH9 STARTING FROM (9) ENDING AT (10) EXCLUSIVE, PARTITION
MONTH10 STARTING FROM (10) ENDING AT (11) EXCLUSIVE, PARTITION
MONTH11 STARTING FROM (11) ENDING AT (12) EXCLUSIVE, PARTITION
MONTH12 STARTING FROM (12) ENDING AT (13) EXCLUSIVE)


INSERT INTO GOSALES.INVENTORY_LEVEL_2005_PARTITIONED (INVENTORY_YEAR,
  INVENTORY_MONTH, WAREHOUSE_BRANCH_CODE, PRODUCT_NUMBER, OPENING_INVENTORY,
  QUANTITY_SHIPPED, ADDITIONS, UNIT_COST, CLOSING_INVENTORY, AVERAGE_UNIT_COST)
  SELECT INVENTORY_YEAR AS INVENTORY_YEAR,
         INVENTORY_MONTH AS INVENTORY_MONTH,
         WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE,
         PRODUCT_NUMBER AS PRODUCT_NUMBER,
         OPENING_INVENTORY AS OPENING_INVENTORY,
         QUANTITY_SHIPPED AS QUANTITY_SHIPPED,
         ADDITIONS AS ADDITIONS,
         UNIT_COST AS UNIT_COST,
         CLOSING_INVENTORY AS CLOSING_INVENTORY,
         AVERAGE_UNIT_COST AS AVERAGE_UNIT_COST
    FROM GOSALES.INVENTORY_LEVELS 
    WHERE (INVENTORY_YEAR = 2005) AND (PRODUCT_NUMBER = 1110)

The INVENTORY_LEVEL_2005_PARTITIONED table is created.

In the Data Source Explorer view, refresh the GSDB database connection to confirm that the INVENTORY_LEVEL_2005_PARTITIONED table was created correct.
- View the number of rows in the INVENTORY_LEVEL_2005_PARTITIONED table. The table must contain 120 rows.
- Select the INVENTORY_LEVEL_2005_PARTITIONED table, click the Data Partitions tab in the Properties view, and check that the partitions in the INVENTORY_LEVEL_2005_PARTITIONED table look like this:
Figure 1. Partitions of the INVENTORY_LEVEL_2005_PARTITIONED table

Run the following SQL statements to create and insert data in the source table:

CREATE TABLE "GOSALES"."INVENTORY_LEVEL_2006" (
		"INVENTORY_YEAR" SMALLINT NOT NULL,
		"INVENTORY_MONTH" SMALLINT NOT NULL,
		"WAREHOUSE_BRANCH_CODE" INTEGER NOT NULL,
		"PRODUCT_NUMBER" INTEGER NOT NULL,
		"OPENING_INVENTORY" INTEGER,
		"QUANTITY_SHIPPED" INTEGER,
		"ADDITIONS" INTEGER,
		"UNIT_COST" DECIMAL(19 , 2),
		"CLOSING_INVENTORY" INTEGER NOT NULL,
		"AVERAGE_UNIT_COST" DECIMAL(19 , 2)
	)
	DATA CAPTURE NONE 
	IN "GOSALES_TS"

INSERT INTO GOSALES.INVENTORY_LEVEL_2006 (INVENTORY_YEAR,
  INVENTORY_MONTH, WAREHOUSE_BRANCH_CODE, PRODUCT_NUMBER, OPENING_INVENTORY,
  QUANTITY_SHIPPED, ADDITIONS, UNIT_COST, CLOSING_INVENTORY, AVERAGE_UNIT_COST)
  SELECT INVENTORY_YEAR AS INVENTORY_YEAR,
         INVENTORY_MONTH AS INVENTORY_MONTH,
         WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE,
         PRODUCT_NUMBER AS PRODUCT_NUMBER,
         OPENING_INVENTORY AS OPENING_INVENTORY,
         QUANTITY_SHIPPED AS QUANTITY_SHIPPED,
         ADDITIONS AS ADDITIONS,
         UNIT_COST AS UNIT_COST,
         CLOSING_INVENTORY AS CLOSING_INVENTORY,
         AVERAGE_UNIT_COST AS AVERAGE_UNIT_COST
    FROM GOSALES.INVENTORY_LEVELS 
    WHERE (INVENTORY_YEAR = 2006) AND (PRODUCT_NUMBER = 1110) AND (INVENTORY_MONTH = 12)

The INVENTORY_LEVEL_2006 table is created.

In the Data Source Explorer view, refresh the GSDB database connection, and ensure that the INVENTORY_LEVEL_2006 table contains 10 rows.