CODEWITHSUNDEEP
db2user-defined-functions
the.unknowing
the.unknowing

Reputation: 117

Identify schema inside a UDF in Db2

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.

Upvotes: 0

Views: 173

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

Use the ROUTINE_SCHEMA global variable.

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

Upvotes: 1

Related Questions