Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    36

    Unanswered: Problems with UDF (User Defined Functions)

    Hi folks,

    We are currently migrating from MSSQL to DB2. I have successfully migrated a database to DB2 and now I am experiencing a kind of weird problem when trying to deploy a UDF (User Defined Functions) from my DB2 DEV server to my DB2 PROD server. Both servers are running on Windows 2008 Server Standard Edition. My DEV server is Express-C 9.5 and my PROD server is DB2 9.5 Workgroup Edition.

    When I try to execute the DDL in order to create the UDF on the PROD server, I am getting the following error message :
    SQL0440N No authorized routine named "VARCHAR_FORMAT" of type "FUNCTION"
    having compatible arguments was found. LINE NUMBER=1. SQLSTATE=42884
    Here is the code :
    Code:
    [...]
    VARCHAR_FORMAT(SD007.Sortie.datSortie,'YYYY-MM-DD HH24:MI')
    [...]
    Could anyone here explain how routines works in DB2 please? I'm having the same error message for another UDF regarding the TIMESTAMP function.

    Regards

    Charles
    Last edited by CharleyDC5; 09-07-10 at 11:20.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the DB2 version and OS of the Development and production environments?

    What does the system think is data type of the column on both systems?

    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    36
    Quote Originally Posted by ARWinner View Post
    What is the DB2 version and OS of the Development and production environments?

    What does the system think is data type of the column on both systems?

    Andy
    Good morning Andy,

    I was about to edit my previous post. My DEV server is Express-C 9.5, and my PROD server is 9.5 Workgroup Edition.

    Both servers are running on Windows 2008 Server Standard Edition.

    Thank you very much

    Charles

  4. #4
    Join Date
    Jun 2010
    Posts
    36
    Quote Originally Posted by CharleyDC5 View Post
    What does the system think is data type of the column on both systems?
    What do you mean?
    Charles M.
    DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
    DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    berjeng same

    Quote Originally Posted by CharleyDC5 View Post
    What do you mean?
    I want to know if there is any difference in syscat.columns for the column in question.

    Andy

  6. #6
    Join Date
    Jun 2010
    Posts
    36
    Good morning Andy,

    I just ran the following query :
    select * from syscat.columns

    There is not row having "VARCHAR_FORMAT" in the COLNAME column.

    Is it normal?

    Looks like VARCHAR_FORMAT is unknown on my PROD server and recognized on my DEV server.

    Any thoughts?

    Thanks

    C
    Charles M.
    DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
    DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    VARCHAR_FORMAT is a function. You should not find it in syscat.columns. What I want you to run on each system is "select * from syscat.columns where tabschema = 'SD007' and tabname = 'SORTIE' order by colno" Then compare the results, especialaly for the column "DATSORTIE".

    Andy

  8. #8
    Join Date
    Jun 2010
    Posts
    36
    There is a difference.
    DEV : Timestamp
    PROD : Date

    VARCHAR_FORMAT expects a timestamp expression, so that's why it was not working.

    Thanks Andy and sorry for the newbie question...

    Have a great day. Your help is much appreciated.
    Charles M.
    DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
    DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition

Posting Permissions

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