Hello everyone!!
I'm trying to create one index using follow statement:

CREATE UNIQUE INDEX XAK1TABLE ON TABLE ("CNAME" ASC) IN TB16

but the answer is:
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 "IN" clause is not allowed. SQLSTATE=42601

System used is DB2 Exp-C UDB v9.7.1 64bit on Linux.

Here some examples that I found on Infocenter (http://publib.boulder.ibm.com/infoce...r7/index.jsp):
Example 8: Create a unique index named A_IDX on a partitioned table named MYNUMBERDATA in table space IDX_TBSP.

CREATE UNIQUE INDEX A_IDX ON MYNUMBERDATA (A) IN IDX_TBSP

Example 9: Create a non-unique index named B_IDX on a partitioned table named MYNUMBERDATA in table space IDX_TBSP.

CREATE INDEX B_IDX ON MYNUMBERDATA (B)
NOT PARTITIONED IN IDX_TBSP



Have you idea how fix it??

Thank you in advance!
Fabio

petitof, I believe it depends on the type of table space being used. With a DMS table space you can put tables and indexes in separate table spaces. With a SMS table space, the table and index must be in the same table space.

In your statement

Is your table name = TABLE? If not, then try using an actual table name.

And your table has to be partitioned in order for this clause to work.

Hi!! Thank you for the answers!

X Stealth_DBA: I tried to do it with DMS (thanks for the tip, I didn't know) but the output doesn't change:
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 "IN" clause is not allowed. SQLSTATE=42601

Here the statement that I have used to create DMS tablespace (sql command from Control Center client 9.7.1):
CREATE REGULAR TABLESPACE <name tablespace> PAGESIZE 4 K MANAGED BY DATABASE USING ( DEVICE '/home/db2inst1/db2/tablespaces/database/<name tablespace>' 25600 ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BF4GR DROPPED TABLE RECOVERY ON;


X Cougar8000: Yes TABLE is the name of the table, and it's not partitioned! I have to do it???

Thank you!
Fabio

Could it be a limitation of DB2 Express-C?

You wouldn't be so surprised if you had a chance to read the fine manual:

Thank you, n_i!
But you should see the Infocenter of the correct release, in fact:

"This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601)"

so, is "temporary table" equal to partitioned tables? Just to confirm, can I know the source of the quote that you gave me? Maybe is it relative oldest release of DB2?

From: http://publib.boulder.ibm.com/infoce.../r0000919.html

that is:

IN tablespace-name
The IN clause is supported only for nonpartitioned indexes. Specifying the IN clause for partitioned indexes results in SQLSTATE 42601.

Specifies the table space in which the index is to be created. This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601). You can specify this clause even if the INDEX IN clause was specified when the table was created. This will override that clause.

The table space specified by tablespace-name must be in the same database partition group as the data table spaces for the table and manage space in the same way as the other table spaces of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege.

If the IN clause is not specified, the index is created in the table space that was specified by the INDEX IN clause on the CREATE TABLE statement. If no INDEX IN clause was specified, the table space of the first visible or attached data partition of the table is used. This is the first partition in the list of data partitions that are sorted on the basis of range specifications. If the IN clause is not specified, the authorization ID of the statement is not required to have the USE privilege on the default table space.



So: Is "This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601)" equal to


Thank you in advance!
Fabio

I believe this is not accurate.


Please take a look at Note #11 here:
IBM DB2 9.7 for Linux, UNIX and Windows Information Center


....If the table is a partitioned table, the index table space for a nonpartitioned index can be specified with the IN clause of the CREATE INDEX statement.

db2 "create table test (c1 int, c2 int) in dmst partition by range(c1) (starting 1 ending 10)"
DB20000I The SQL command completed successfully.

db2 "create index testi on test (c2) not partitioned in dmsi"
DB20000I The SQL command completed successfully.

thank you, db2girl!!
I also made a test specifying "AUTOMATIC STORAGE NO" during the creation of the database, but nothing is changed.

thanks to all!!

Fabio

Do you have Tables with Range Partitioning or temporary Tables?

You have to specify the Tablespace for the Indexes with the 'create table' Statement. The tablespace-type has to be DMS and it does not matter if you use AUTOMATIC STORAGE or not.