OS sol2.8
UDB V8.1fp5


Getting the following error

Still get this error:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0440N No
authorized routine named "INTEGER" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884

when i run this query.

SELECT * FROM ENROLLEE_STATE WHERE INTEGER(ENROLLEE_ID)=?


I gave the rights on type ID , using the following stat.
grant execute on function schema.id(integer) to public/schema.

Still it did not work.
What is the solution for this.

What datatype is ENROLLEE_STATE.ENROLLEE_ID?

Andy

It's typed type ID . which is of type integer

What is the schema of the UDT ID?

Andy

The schema is inst01

When you create a UDT, the cast functions that are generated are in the same schema as the type. so you need to execute you SQL like this:

SELECT * FROM ENROLLEE_STATE WHERE inst01.INTEGER(ENROLLEE_ID)=?

HTH

Andy

My queries are like this...

SELECT * FROM case WHERE INTEGER(case_id) = ?
and
SELECT * FROM enrollee_state WHERE INTEGER(enrollee_id) = ?


And the error i am getting is

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0440N No
authorized routine named "INTEGER" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884


I gave the rights by using the following stmt.

grant execute on function integer(enrollee_id) to inst01;

I thought it will work. But not.

Your problem is that you are using a UDT. When you create a UDT, DB2 will automatically create the casting functions to the base datatype (integer if your example). These functions are in the same schema as the UDT, so to use them you need to fully qualify the function. Unfully qualified functions are searched in the "PATH" which, by default, is SYSIBM,SYSFUNC,SYSPROC,<CURRENT USER>.
Change your queries to fully qualify the cast function, like this:

SELECT * FROM case WHERE inst01.INTEGER(case_id) = ?
and
SELECT * FROM enrollee_state WHERE inst01.INTEGER(enrollee_id) = ?

HTH

Andy

ARWinner , thanks for the info.

is there any other workaround?
My developer were not interested to change the code.
There are lot of queries like this.

It was working with V7.1 . But failing with V8.1 and FP5

The only thing I can think of is to set the path:

SET CURRENT PATH = CURRENT PATH,inst01

HTH

Andy

AR ,

can you be little more clear.

Is there any special register like CURRENT PATH .
or Is taht PATH env solaris variable.

thanks

It is a special register. That was SQL that I gave you.

Andy