Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Question Answered: DB2 Table Partition

    Hi DB2 Gurus,

    I am currently working on a task to partition a DB2 table. The table size in term of records is 20 million rows and over 10GB of disk space consumption.

    The details regarding the environment:

    OS: AIX 6
    DB2: 9.7.6

    The partition is to be done on two fields, consider the following script:

    create table t (col1 integer, col2 integer);

    insert into t (0,0);
    insert into t (0,1);
    insert into t (0,3);
    insert into t (1,0);
    insert into t (1,1);
    insert into t (1,3);
    insert into t (2,0);
    insert into t (2,1);
    insert into t (2,3);

    my requirement is to have the following partitions

    partition by range (col1, col2)

    PAR1 --> values of col1 = 0 and col2 = 1 in one partition
    PAR2 --> values of col1 = 1 and col2 = 1 in one partition
    PAR3 --> values of col1 not in (0,1) and col2 <> 1 in one partition

    I don't want to have one partition for each value and just want the groups as above, I have tried various methods and searched like crazy but cant get it working

    Any help is appreciated.

    Cheers,
    Faisal.
    Last edited by faisalee; 07-20-16 at 02:33.

  2. Best Answer
    Posted by mark.bb

    "Hi Faisal,

    try this:
    Code:
    create table t (
      col1 integer
    , col2 integer
    , col3 generated always as (
    case 
      when (col1,col2)=(0,1) then 1
      when (col1,col2)=(1,1) then 2
      else 3
    end
    )
    )
    partition by (col3) (
      partition part1 starting 1 ending 1 in userspace1
    , partition part2 starting 2 ending 2 in userspace1
    , partition part3 starting 3 ending 3 in userspace1
    );
    
    
    insert into t (col1, col2) values (0,1), (1,1), (0,2), (null, null);
    select datapartitionnum(col1), col1, col2 from t;
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    72
    Provided Answers: 9
    Hi Faisal,

    try this:
    Code:
    create table t (
      col1 integer
    , col2 integer
    , col3 generated always as (
    case 
      when (col1,col2)=(0,1) then 1
      when (col1,col2)=(1,1) then 2
      else 3
    end
    )
    )
    partition by (col3) (
      partition part1 starting 1 ending 1 in userspace1
    , partition part2 starting 2 ending 2 in userspace1
    , partition part3 starting 3 ending 3 in userspace1
    );
    
    
    insert into t (col1, col2) values (0,1), (1,1), (0,2), (null, null);
    select datapartitionnum(col1), col1, col2 from t;
    Regards,
    Mark.

  4. #3
    Join Date
    Sep 2004
    Posts
    15
    Thank you Mark! You are Awesome

    This is exactly what I was looking for.

Tags for this Thread

Posting Permissions

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