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.

returns

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?

Thanks,
Mark.

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.

Andy

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?