ibm db2 create index in tablespace does not work

fauzimh 0
Viewed 28

I created a database on db2 11.5, then tablespace, then create a table. Everything okay so far. However, when i tried to create index in the newly created TABLESPACE, it complains syntax error:

CREATE INDEX SCH.TBL_PMT_ERR_NIX01 ON SCH.TBL_PMT_ERR (PMT_NO ASC, PMT_ERR_ID ASC) in TBS_EC_SINDEX;

with error:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0109N  The statement or command was not processed because the following
clause is not supported in the context where it is used: "IN".  SQLSTATE=42601

i tried these 2 but still they don't work, complaining the "PARTITION" clause is not supported

CREATE INDEX SCH.TBL_PMT_ERR_NIX01 ON SCH.TBL_PMT_ERR (PMT_NO ASC, PMT_ERR_ID ASC) partitioned in TBS_EC_SINDEX;

CREATE INDEX SCH.TBL_PMT_ERR_NIX01 ON SCH.TBL_PMT_ERR (PMT_NO ASC, PMT_ERR_ID ASC) not partitioned in TBS_EC_SINDEX;

Could you help me pointing out what I'm missing?

db2
1 Answers

When you run your create table statement, you can optionally specify the index in clause at that time to allow indexes to use a specific tablespace that you pre-created. Additional capabilities are available for PARTITIONED tables.

The key detail you omitted from your question was whether or not your table is itself partitioned.

The documentation for create index states that the IN tablespapace-name clause can only be specified or a nonpartitioned index on a partitioned table. So if your table is not itself partitioned then you cannot use this IN clause when creating an index, and you should consider using the create table statement to identify an index tablespace for that table instead.

mao0
Related