Identify schema inside a UDF in Db2

Question

I have the same user defined function in Db2 defined in several schemas. How can I retrieve the schema used for the current invocation within the UDF ?

CURRENT_SCHEMA special register does not work in my case, as it only works after SET CURRENT SCHEMA '...' (which is not used).

Are there any other possibilities ?

Example UDFs:

--#SET TERMINATOR #
CREATE FUNCTION SCHEMA1.TEST_UDF ( )
   RETURNS VARCHAR(100) LANGUAGE SQL
BEGIN
   RETURN CURRENT_SCHEMA;
END#
CREATE FUNCTION SCHEMA2.TEST_UDF ( )
   RETURNS VARCHAR(100) LANGUAGE SQL
BEGIN
   RETURN CURRENT_SCHEMA;
END#
--#SET TERMINATOR ;

Invocation:

SELECT SCHEMA1.TEST_UDF() FROM SYSIBM.SYSDUMMY1;

I'd like to see 'SCHEMA1' as output for this invocation.

Uh, why do you have the same function defined in multiple places? Why does it even matter? What are you planning on doing with this information? What do you mean by "current invocation"? CURRENT_SCHEMA is going to give you the "default" schema where objects are being resolved, but this isn't going to be the only schema that's looked at (that's handled by the SQL path/library list). Normally you'd pick a canonical copy and only ever call that. — Clockwork-Muse
@Clockwork-Muse: I deliver a UDF. Customers might deploy this procedure several times to different schemas (especially on Db2 z/OS), e.g. for dev, test, QA, pre-prod, ... So I might end up with having a single UDF logic deployed into several schemas, and need to find out the schema under which I currently run to query further information from exactly that schema. — the.unknowing
Poor use case: deploying to different schemas for different environments. Normally you're going to want identically named schemas on different databases - otherwise you have a decent chance of grabbing something from the wrong schema. (For that matter, having a dedicated box for dev/test/QA should probably be swapped for a dockerized version anyways, which also eliminates the multiple schemas) And knowing what your 'current' schema is in such a case is problematic anyways, because from a deployment perspective the other deployments should not exist. — Clockwork-Muse

Mark Barinstein Mark Barinstein · Accepted Answer · 2018-12-12T15:38:07

Use the ROUTINE_SCHEMA global variable.

--#SET TERMINATOR #
CREATE OR REPLACE FUNCTION SCHEMA1.TEST_UDF ( )
   RETURNS VARCHAR(100) 
BEGIN
   RETURN ROUTINE_SCHEMA;
END#

Identify schema inside a UDF in Db2

1 Answers