Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011

    Unanswered: User defined functions and CURRENT_PATH

    Running DB2 Express-C 9.7.2 and DB2 LUW Enterprise 9.7

    I am trying to create and use a user defined function that mirrors one in the database that I am porting from.

    The function is created within the currently active schema (CURRENT_SCHEMA - a synonmym of CURRENT SQLID).

    Whereas the function search path CURRENT {FUNCTION} PATH doesn't contain the current schema but contains the current user.


    where DB2ADMIN is the value of the special register [I]USER{/I].

    This seems counterintuitive to me; Functions and procedures are created in schemas and the search path to find them contains the logged on user rather than the current schema.

    If I create the FUNCTION under the userid we are going to be running as...

    This creates a schema of A_USER and puts A_FUNCTION in there.

    But if I issue

    This correctly updates the [I]USER{/I] special register but the CURRENT_PATH special register still refers to the original user DB2ADMIN.

    Again this seems like a bug in that the CURRENT_PATH special register is not updated when USER special register changes.

    I could create the function in DB2ADMIN but I don't want to be logged on as that all of the time so DB2ADMIN wouldn't be in the path when logged on as someone else.

    I can update the currrent path special register using...

    But this is only for the current transaction and I'd rather not have to change hundreds of SQL scripts to add a SET PATH or qualify the function calls.

    How do I get the correct schema added to the PATH?


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    Why not just do the following:

    1) Create UDF fully qualified (with a schema)
    2) GRANT EXECUTE ON FUNCTION schema.* to GROUP ...
    3) Whenever you use the UDF, just fully qualify it.


  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    I agree with Andy. If you work with database objects, you should fully qualify them to make sure you are working with the right objects.

    Regarding the idea to implicitly change the value of the CURRENT PATH special register, here are a few questions you may want to think of:
    • Would you want to remove the DB2ADMIN schema name when A_USER is added? That would potentially make functions unavailable after the user switch
    • Would you leave DB2ADMIN in there and just add A_USER? How would you deal with many user switches and a very long path?
    • How would you determine the sequence in the path? Would A_USER be added at the end or the front or somewhere in the middle? For functions with the same unqualified name in different schemas, this can have a significant impact on the semantics.
    • How would you handle paths that were explicitly set by the application? Modify them or leave them as-is?
    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