Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    36

    Angry Unanswered: getting errors while using user -defined tables.

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What datatype is ENROLLEE_STATE.ENROLLEE_ID?

    Andy

  3. #3
    Join Date
    Apr 2004
    Posts
    36
    It's typed type ID . which is of type integer

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the schema of the UDT ID?

    Andy

  5. #5
    Join Date
    Apr 2004
    Posts
    36
    The schema is inst01

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Apr 2004
    Posts
    36
    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.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  9. #9
    Join Date
    Apr 2004
    Posts
    36
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The only thing I can think of is to set the path:

    SET CURRENT PATH = CURRENT PATH,inst01

    HTH

    Andy

  11. #11
    Join Date
    Apr 2004
    Posts
    36
    AR ,

    can you be little more clear.

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

    thanks

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is a special register. That was SQL that I gave you.

    Andy

Posting Permissions

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