Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Unanswered: UPPER Function not working

    Hi guys , i am using DB2 UDB 9.1 trail version, when i rollbacked my OS date , the Upper Function does not works,
    it says SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: UPPER;FUNCTION
    ,when i go through old archives of this forums i have found out when the OS date is rollbacked before the DataBase creation date ,this problem occurs ,
    i can any please explain why this problem occurs, with any specific reason.

    thankyou,
    sivaraj

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    This does not seem to be your date problem.... this is something else wherein you are not using the UPPER function correctly..... kindly post the SQL state,ment which you were trying to execute.... Also post the complete error code which was generated.

    SQL0440N No authorized routine named "<routine-name>" of type
    "<routine-type>" having compatible arguments was
    found.

    Explanation:

    This occurs in a reference to routine "<routine-name>", when the
    database manager cannot find a routine it can use to implement
    the reference. There are several reasons why this could occur:

    o "<routine-name>" was either incorrectly specified or does not
    exist in the database.

    o A qualified reference was made, and the qualifier was
    incorrectly specified.

    o The user's SQL path does not contain the schema to which the
    desired function or method belongs, and an unqualified
    reference was used.

    o The wrong number of arguments were included.

    o The right number of arguments were included in the function
    or method reference, but the data types of one or more of the
    arguments is incorrect.

    o The routine does not exist in the database with the same
    function id that was used when the package was bound (applies
    to static statements).

    o The mutator method corresponding to an attribute assignment
    used in an UPDATE statement could not be found. The data
    type of the new value for the attribute is not a data type
    that is the same or promotable to the data type of the
    attribute.

    o The routine invoker is not authorized to execute the
    routine.
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Feb 2009
    Posts
    5
    this is the query i have execute :

    select GP_C_GRP_ID from group_profile where UPPER(GP_C_GRP_ID)=UPPER('mktg')

    This is the error message :
    SQL0440N No authorized routine named "UPPER" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    SQL0440N No authorized routine named "UPPER" of type "FUNCTION

    plus ,
    1. this query has been working fine till few days ago , as we are using trail version ,the system administrator rollback's the OS date frequently ,

    2. more over i am just a developer , and i was just interested know the specific reason , why rollback of OS date affects the DataBase. (Which i think is the root cause of this problem).

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    I remember something called "function resolution"

    A precondition for using a function is, that it is created before the last BIND or REBIND of the application that wants to use it.
    The creation timestamp of the function is stored in the catalog and if the current date is older ( due to the OS-date rollback), the function is no valid candidate
    Last edited by umayer; 03-03-09 at 04:14.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You should never mess around with the system time. Fix the time setting and DB2 should find its functions again.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2009
    Posts
    5
    thank you guys for your reply , and giving a detailed reason why this is happening,

    plus , i have a another question which came up when i was searching for above problem.

    - when in queried the tables like syscat.FUNCTIONS ,sysibm.sysfunctions ,syscat.CASTFUNCTIONS , any where i cannot find the infomation about UPPER function (but details about UCASE is present in syscat.functions) , but still i can execute the function , and according to IBM website the functionality of UCASE and UPPER functions are same.
    what i want to know is is UPPER a alias name for UCASE or other way around.
    please clear this out for me.
    thankyou ...

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    According to the DB2 LUW V9.5 manual, UCASE is a synomym for UPPER.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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