DB2 LOWER() bug

Case insensitive search in a table with a foreign key

The goal is to execute a case insensitive Hibernate search in a DB2 table which has a foreign key linked to another DB2 table. This should be easily done using the LOWER() function of DB2.

The relevant DDL of the DB2 table to execute the search on, MyTable.ddl:

CREATE TABLE MYTABLE (
    ID BIGINT GENERATED ALWAYS AS IDENTITY CONSTRAINT MYTABLE_PK PRIMARY KEY,
    ANOTHERTABLE_ID BIGINT NOT NULL,
    FIELD1 VARCHAR (255) NOT NULL,
    FIELD2 VARCHAR (255) NOT NULL,
    CONSTRAINT MYTABLE_FK FOREIGN KEY (ANOTHERTABLE_ID) REFERENCES ANOTHERTABLE (ID)
);

The relevant DTO (Data Transfer Object) code of the DB2 table, MyTable.java:

public class MyTable {

    private Long id;
    private Long anotherTableId;
    private String field1;
    private String field2;

    // Implement public getters and setters here.
}

The relevant java code of the backing bean MyBean.java:

public class MyBean {

    public void actionSearch() {
        Map params = new HashMap();
        params.put("anotherTableId", getAnotherTableId());
        params.put("field1", "%" + getSearchFilter().toLowerCase() + "%");
        params.put("field2", "%" + getSearchFilter().toLowerCase() + "%");
        Command command = new QueryCommand(params, "MyFilter", MyTable.class);
        CommandInvoker.execute(command);
    }

}

The getSearchFilter() method is just a getter for the search string entered by the browser user.

The relevant code of the Hibernate mapping file mybean.hbm.xml including the case insensitive search query using the DB2 LOWER() function:

 package="mypackage.mywebapp">
     name="MyTable" table="MYTABLE">
         column="ID" name="id" type="long">
             class="identity" />
        
         column="ANOTHERTABLE_ID" name="anotherTableId" type="long" />
         column="FIELD1" name="field1" />
         column="FIELD2" name="field2" />
    

     name="MyFilter">
        FROM
            MyTable table 
        WHERE
            table.anotherTableId = :anotherTableId
        AND
            ( LOWER(table.field1) LIKE :field1 OR LOWER(table.field2) LIKE :field2 )
    

OK, the code here above looks fine, but after executing the query command in MyBean.java, Hibernate will throw a SQLGrammarException, followed by a SQLException from the IBM DB2 wrapper:

org.hibernate.exception.SQLGrammarException: could not execute query

Caused by: com.ibm.db2.jcc.a.SqlException: No authorized routine named "LOWER" of type "FUNCTION" having compatible arguments was found.

Troubleshooting

The following Hibernate queries works correctly, the first query is without the LOWER() function and the second query is without the WHERE clause on the foreign key:

 name="MyFilter">
    FROM
        MyTable table 
    WHERE
        table.anotherTableId = :anotherTableId
    AND
        ( table.field1 LIKE :field1 OR table.field2 LIKE :field2 )

 name="MyFilter">
    FROM
        MyTable table 
    WHERE
        LOWER(table.field1) LIKE :field1 OR LOWER(table.field2) LIKE :field2

The conslusion is that the LOWER() function conflicts with the WHERE clause on the foreign key somehow.

The Solution

Use LCASE() instead of LOWER().

It's really weird, the LCASE() function is an alias of the LOWER() function, which should behave identical as the LOWER() function. BalusC spent 3 days on finding the solution. Blame IBM.

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair.

(C) June 2006, BalusC

Source:http://balusc.blogspot.com/2006/06/db2-lower-bug.html