Hi,

I am trying to create an index with a function and am getting stuck on the syntax. I am on V9 on AIX. I checked the online manuals, but I am still doing something wrong.

Here is my command:
CREATE INDEX INDEX_NAME ON TABLE_NAME
(
LOWER(column1) ASC
) PCTFREE 20 MINPCTUSED 10 COLLECT STATISTICS;


and the error:

CREATE INDEX INDEXNAME TABLENAME( LOWER(column1) ASC ) PCTFREE 20 MINPCTUSED 10 COLLECT STATISTICS
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "(column1)" was found following "TABLENAME (
LOWER". Expected tokens may include: "<space>". SQLSTATE=42601


Thanks for your comments.

indexes can be created on real data
add the lower when executing the select

This link seems to suggest that functions are available for creating indexes:

http://publib.boulder.ibm.com/infoce...c/r0052232.htm

This is a command specific for the Net Search Extender and must be executed in the "db2text" command - not the regular "db2" CLP.
The regular CREATE INDEX statement is described here: http://publib.boulder.ibm.com/infoce...c/r0000919.htm

Thanks for the replies.

- I did a little more research and discovered the meaning of the 'net search extender'. I will get back to my developer and let him know a lcase index in not available.

You can create such an index with the help of generated columns:
DB2 will automatically reroute to COLUMN1_LCASE if the query contains a predicate like "LCASE(column1) = '...'". Then you may see that the index is exploited. All that depends on the usual optimizer decisions regarding the best execution plan.

Just wanted to pointed out..

Index on the generated column LCASE(..) will not be useful if a predicate uses LOWER(though LCASE and LOWER do the same functionality).

Need to use the correct subfunction(LCASE or LOWER) in the SQL statements based on the table design.

--Naga