|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hello there,
I'd like to create a clustered index that contains the in upper cases transformed data of a column (table / tablespace in encoding schema Unicode, column item_id VARCHAR(20) FOR SBCS DATA, DB2 v10 z/OS).
| Code: |
CREATE INDEX my_clust_fct_ix
ON my_table
( UCASE( item_name
, 'UNI' ) ASC
, item_id ASC)
USING STOGROUP teststog
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED
CLUSTER
COMPRESS NO
BUFFERPOOL BP30
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G |
But the result is SQL-Code -628 / SQL-State 42613
Because the Column is VarChar but FOR SBCS DATA, I tried to create the index using the UPPER function without the locale-name option. But the result was the same.
There are other indexes created, especially a unique index for the pk col item_id. But all of them are defined as NOT CLUSTER.
I was able to find several restrictions using key-expressions. And I know there must be max. one clustered index.
But I did not find any hints saying the index option CLUSTER is not allowed to be combined with key-expressions.
Can you please give me some information if I made some mistakes in the create statement above or if this is a not allowed (and a not very well documented) combination or in which other way I should try to create this index. |
|
| Back to top |
|
 |
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
| Auryn wrote: |
Hello there,
I'd like to create a clustered index that contains the in upper cases transformed data of a column (table / tablespace in encoding schema Unicode, column item_id VARCHAR(20) FOR SBCS DATA, DB2 v10 z/OS).
(...) |
Sorry, made a mistake discribing the column(s):
item_name VARCHAR(20) FOR SBCS DATA,
item_id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
"A CREATE INDEX or ALTER INDEX statement attempted to specify either CLUSTER, PARTITION BY or both for an XML index or an extended index."
an index on a scalar function is an extended index! |
|
| Back to top |
|
|
|
|
