Results 1 to 6 of 6

Thread: partition index

  1. #1
    Join Date
    Dec 2011
    Posts
    30

    Unanswered: partition index

    when i create the partition table like:

    create table Parent(id integer,range date)

    INDEX IN gl_index
    PARTITION BY (range)
    (PARTITION jan2010 STARTING ('1/1/2010') ENDING '1/31/2010'
    IN tbsp1
    INDEX IN i_tbsp1,
    PARTITION feb2010 STARTING ('2/1/2010') ENDING '2/28/2010'
    IN tbsp2
    INDEX IN i_tbsp2);

    after that i have created the partition index like:

    CREATE INDEX partidx ON parent(id) PARTITIONED;

    but that partition index created in the tablespace gl_index,tbsp1,tbsp2
    not in the i_tbsp1 and i_tbsp2.

    after that i tried to create the index spcifying IN cluase and INDEX IN clause
    but it gives syntax error.


    why this is so,can't we create the partition index in the separate tablespace
    rather than getting created in data partition tablespace?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2 9.5 or 9.7 ??
    and the error you get while creating the index. with in clause
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    according the doc
    The table space placement for an index partition of the partitioned index is determined by the following rules:

    If the table being indexed was created using the partition-tablespace-options INDEX IN clause of the CREATE TABLE statement, the index partition is created in the table space specified in that INDEX IN clause.
    If the CREATE TABLE statement for the table being indexed did not specify the partition-tablespace-options INDEX IN clause, the index partition partitioned index is created in the same table space as the corresponding data partition that it indexes.

    The IN clause of the CREATE INDEX statement is not supported for partitioned indexes (SQLSTATE 42601).

    The tablespace-clauses INDEX IN clause of the CREATE TABLE statement is ignored for partitioned indexes.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Dec 2011
    Posts
    30
    it seems really contradictory statement .

    according to doc:

    if we use index in clause then partition index created in the TBSP mentioned in the index in clause.


    and your last statement states that it is ignored the index in clause.


    Do you have any link that states the same.?

    thanks for your reply.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the quoted text was copied from infocenter
    CREATE INDEX - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Dec 2011
    Posts
    30
    Thank you very much

Posting Permissions

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