Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Range partitioning in db2?

    Hi,

    In our setup db2 V9.7 with fixpack 4

    I am newbie for database partitioning, Under db2inst1 we have six partitioned databases.

    Planning to set the range partitioning for some of the tables under one database.

    Tables records is going to be huge max 50GB.. Already tables are created under the database.

    How to add the range partitioning?

    DDL for the table:
    -----------------

    CREATE TABLE "db2DWH"."TIS_DW_METRIC_AXLE_LOOP" (
    "SITE_ID" INTEGER NOT NULL,
    "TFM_ID" INTEGER NOT NULL,
    "ITM_ID" INTEGER NOT NULL,
    "SRVY_TMS" TIMESTAMP NOT NULL,
    "ROW_NBR" INTEGER NOT NULL,
    "DTCTR_ID" INTEGER NOT NULL,
    "SRC_DATETIME" VARCHAR(50) NOT NULL,
    "LANE_ID" INTEGER NOT NULL,
    "ETL_BATCH_ID" CHAR(14) NOT NULL,
    "DATA_TYP_CD" VARCHAR(20),
    "LOOP_ON_TM" VARCHAR(50),
    "LOOP_MIN_HGHT" DECIMAL(22 , 5),
    "LOOP_NBR" VARCHAR(20),
    "LOOP_THRSHLD" VARCHAR(50),
    "AXLE_CNT" VARCHAR(20),
    "AXLE_WGHT" DECIMAL(22 , 5),
    "AXLE_SPCNG" VARCHAR(50),
    "AXLE_GRP_CNT" INTEGER,
    "AXLE_PATTERN" VARCHAR(100),
    "AXLE_OVERFLOW" VARCHAR(50),
    "GRP_OVERFLOW" VARCHAR(50),
    "AXLE_WGHT_S2" DECIMAL(22 , 5),
    "G1_MASS_G8_MASS" DECIMAL(22 , 5),
    "G1_TYP_G8_TYP" VARCHAR(20),
    "DATA_INTRVL_IN_SECS" INTEGER,
    "IS_LATEST_FLG" CHAR(1),
    "DATA_SRC_CD" CHAR(5) NOT NULL,
    "SRC_FILE_NM" VARCHAR(50) NOT NULL,
    "INS_TMS" TIMESTAMP NOT NULL,
    "INS_USR" VARCHAR(30) NOT NULL,
    "UPD_TMS" TIMESTAMP,
    "UPD_USR" VARCHAR(30)
    )
    DATA CAPTURE NONE
    IN "METAXLELOOP_TAB"
    DISTRIBUTE BY HASH ("SITE_ID", "TFM_ID", "ITM_ID", "ROW_NBR", "DTCTR_ID", "LANE_ID", "ETL_BATCH_ID", "SRVY_TMS")
    COMPRESS NO;

    ALTER TABLE "db2DWH"."TIS_DW_METRIC_AXLE_LOOP" ADD CONSTRAINT "TIS_DW_METRIC_AXLE_LOOP_PK" PRIMARY KEY
    ("SITE_ID",
    "TFM_ID",
    "ITM_ID",
    "ROW_NBR",
    "DTCTR_ID",
    "LANE_ID",
    "ETL_BATCH_ID",
    "SRVY_TMS");

    How to add the range partition for this table?

    Thanks,
    laxman..

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Add it right after DISTRIBUTE BY HASH.

    You have too many columns in your hash partitioning key for a good design. I don't think you understand how it works.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •