create db2 index on non-partitioned table in different tablespace

Question

I have created Non partitined table in db2.

create table test (name varchar (22), cell# integer);

table created succesffully.

Now I want to create index on test table in tablespace TEST_IDX.

I execute following query CREATE INDEX test1 ON test (cell#) in TEST_IDX.

It give me following error:

[CREATE - 0 row(s), 0.000 secs] [Error Code: -109, SQL State: 42601] DB2 SQL Error: SQLCODE=-109, SQLSTATE=42601, SQLERRMC=IN

db2 database version is DB2/LINUXZ64 9.7.3

Answer 1

I think you will have to specify that for the table, i.e.:

create table test ( ... ) in <tblspc> index in TEST_IDX

See ADMIN_MOVE_TABLE ( http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dm.doc/doc/t0054864.html?resultof=%22%6d%6f%76%65%5f%74%61%62%6c%65%22%20 ) for more information.

Answer 2

Index tablespace should be defined during table creation, if not mentioned index will be created in same tablespace where table is exist.