Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Partitioned Tables in db2

    Hi Folks,

    I want to create DPSI on orders table's customer_id column having following table definition

    CREATE TABLE orders(order_no INT, shipdate DATE,customer_id int)
    PARTITION BY RANGE(shipdate)
    (
    PARTITION q206 STARTING '1/1/2006',
    PARTITION q306 STARTING '4/1/2006',
    PARTITION q406 STARTING '7/1/2006',
    PARTITION q506 STARTING '10/1/2006'
    ENDING '12/31/2006'
    )
    ORGANIZE BY (shipdate)


    Do i need to create partitioned table space for the same, if yes what would be the ddl for the same?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ankurgoyal9, You don't mention the version of DB2 or the operating system. However, since you are talking about DPSI (Data Partitioned Secondary Index), I will assume z/OS and V8 (although it should be the same in V9).

    To answer your question, No you do not create a Table space for a DPSI. It is handled like any other Index and the Index space is created automatically by DB2 (for Stogroups).

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Hi,

    I am using DB2 ver 9.5, I was confused when i saw a IBM documentation on DPSI

    http://publib.boulder.ibm.com/infoce...aryindexes.htm

    where it mentions that table space should be a partitioned table space.

    Also when i am trying to create partitioned index on the mentioned table

    create unique index ind_test_orders on test_orders(XX) partitioned

    i get

    Function not supported. SQLSTATE=42997 error

    Am i missing something here?

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The link you provided goes to the Info center for DB2 V9.1 z/OS. If your version of DB2 is V9.5, it is probably LUW.
    This link will take you to the CREATE INDEX statement in th DB2 LUW V9.5 Info center.

    There isn't any ability to create a DPSI that I can find.

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    DB2 9.5/AIX 6.5
    I had an impression that indexes are not partitioned even if they are on partitioned table. But now I checked 'create index' command and found 'NOT PARTITIONED' clause in it:
    NOT PARTITIONED
    Indicates that a single index should be created that spans all of the data partitions defined for the table. The table-name must identify a table defined with data partitions (SQLSTATE 53036).

    Does it mean that when I create 2nd (3rd, etc) indexes without 'not partitioned' clause on partitioned table the indexes will be partioned ?
    Last edited by MarkhamDBA; 06-02-09 at 11:12.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Nope .. In 9.5, the NOT PARTITIONED clause is the only option. In a later release, you will have the option to use PARTITIONED clause.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Google does not know... Always check the "source" before reading. When you're on LUW and get a "z" hit, just ignore or you'll only get confused.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I believe that version 9.7 (due out in a few weeks) has PARTITIONED indexes. If it does not make it into the first release of 9.7 it will probably be in the first fixpack later this year.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A
    I believe that version 9.7 (due out in a few weeks) has PARTITIONED indexes. If it does not make it into the first release of 9.7 it will probably be in the first fixpack later this year.
    good to know this. I am taking DB2 DBA training at IBM next week and will ask them abt it. thx Marcus
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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