Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    MN, USA
    Posts
    4

    Angry Unanswered: Create Index using function Syntax Help

    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.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    indexes can be created on real data
    add the lower when executing the select
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Dec 2004
    Location
    MN, USA
    Posts
    4
    This link seems to suggest that functions are available for creating indexes:

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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is a command specific for the Net Search Extender and must be executed in the "db2text" command - not the regular "db2" CLP.
    function-schema.function-name
    The schema and the name of a user-defined function used to access text documents that are in a column of an unsupported type. The function performs a column type conversion, using as input parameter an arbitrary column type. It returns the value as one of the Net Search Extender supported types.
    The regular CREATE INDEX statement is described here: http://publib.boulder.ibm.com/infoce...c/r0000919.htm
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2004
    Location
    MN, USA
    Posts
    4
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can create such an index with the help of generated columns:
    Code:
    ALTER TABLE ... ADD COLUMN column1_lcase VARCHAR(...) GENERATED ALWAYS AS ( LCASE(column1) );
    CREATE INDEX ... ON ... ( column1_lcase );
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •