Composite index creation in DB2

Question

I used to create composite index for my Oracle table with the following script.

CREATE INDEX index_name ON my_table (column1,column2,column3) TABLESPACE tablespace_name;

When I tried the same in DB2, got some errors at tablespace and I tried using the following script simply,

CREATE INDEX index_name ON my_table (column1,column2,column3);

Is this fine in DB2? As I am new to DB2 and no syntax found when searched for a composite index in DB2, not sure about this. Need advice.

AngocA · Accepted Answer · 2014-02-06 08:49:09Z

1

For the CREATE INDEX documentation, you can check the InfoCenter: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000919.html

The DB2 CREATE INDEX sentence you provided is correct. Remember that in DB2, you do not provide the tablespace at index creation. The tablespace for indexes is specified when you create the table

CREATE TABLE XX
 (
 ...
 )
IN ts_table
INDEX IN ts_indexes

For more information about the create table, please take a look at this: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html

answered Feb 6, 2014 at 8:49

AngocA

7,7046 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WarrenT Over a year ago

This is a platform specific answer for DB2 LUW. For those who have DB2 for i see pic.dhe.ibm.com/infocenter/iseries/v7r1m0/…

AngocA Over a year ago

@WarrenT we should ask the platform, and re-tag the questions. I found that there is a tag called db2400, I think it should be called db2i. Also, I did not see any tag for DB2 for zOS.

Ian Bjorhovde Over a year ago

Also note that you can specify a tablespace in the CREATE INDEX statement if you are using range partitioned tables. Some have noted that you can create a table with a single range partition specifically so that you can create each index in a separate tablespace.

Collectives™ on Stack Overflow

Composite index creation in DB2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related