Dear Sir,

I can create a user-defined function and deployed successsfully, but error message "The function "SF_MASKID" resolved to specific function "SQL111026095035700" that is not valid in the context where it is used .. SQLCODE=390, SQLSTATE=42887,DRIVER=4.12.55. while I call it.

The function as below.

CREATE OR REPLACE FUNCTION SF_MASKID (INOUT P_ID VARCHAR(17))
RETURNS VARCHAR(17)
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
SET P_ID = SUBSTR(P_ID,1,4) CONCAT TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****');
RETURN P_ID;
END

So, please show in what context did you used the function.
In other words, show the SQL statement in which you used the function.

Apart from your issue, you might want to use
TRANSLATE( SUBSTR(P_ID,5) , '**********' , '1234567890' )
or
TRANSLATE( SUBSTR(P_ID,5) , '' , '1234567890' , '*' )
than
TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****')

Because, your original use of TRANSLATE function translates '*' into '1'.
(Second parameter and third parameter of TRANSLATE function of DB2 are reversed to Oracle.)

I test it in DB2 editor. By the way, I set the P_ID is 'G12345678' and run below for testing, the return result is G123***** that is my expected result.

select SUBSTR(P_ID,1,4) concat TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****') from sysibm.dual;

Is it wrong ?

Thanks for your help.

I think that you must be setting compatibility vector to Oracle, like I wrote in another thread.
I don't know much about bahavior of DB2 setting compatibility vector.
So, I would not comment any more.

Sorry, I might confused you.

Assume the SQL is valid, is it something I still missing when create the common function ? I tried to add the schemas name together with the function name like "DEVDB.SF_MASKID", but I still get the same error message. Anyone can help ?

Thanks !