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?

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).

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?

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.

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 ?

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

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.

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