Search code examples
sqldb2-luw

Why can my function not use same keywords on DB2 LUW?

I have following function on IBM i DB2 and want to use it on DB2 LUW (Linux). The error I receive is "Multiple or conflicting keywords involving the "FENCED" clause are present.. SQLCODE=-628, SQLSTATE=42613". Why can I not use it? The documentation lists the keywords, so I do not understand...

CREATE OR REPLACE FUNCTION getHex2Char(IN_HEXCHAR VARCHAR(8))
RETURNS INT
DETERMINISTIC
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION 
BEGIN
-- Do some SQL stuff and return result
END
Solution

  • The currently supported Db2-LUW versions allow the clause called on null input ( the syntax null call has the same meaning ) with the create function statement.

    But the function can return a NULL or a NOT NULL value , when the input is null.

    The Db2 for i implementation of returns null on null input is not currently in the Db2-LUW implementation, although it was supported in older versions.

    That returns null on null input clause was present in Db2-LUW versions 7.x, 8.x, 9.1, 9.5, 9.7, 10.1, 10.5, but was removed from the documentation (and presumably removed from the product) from Db2-LUW v11.1 onwards.

    Reference Db2-LUW documentation link.