SnowConvert AI - IBM DB2 - CREATE FUNCTION¶
Description¶
Creates a new user defined function or replaces an existing function for the current database. (IBM DB2 SQL Language Reference Create Function).
DB2 User Defined Functions (UDFs) allow developers to extend the built-in functionality of the database by creating custom functions that can be invoked in SQL statements. DB2 supports several types of SQL UDFs:
Scalar Functions: Return a single value and can be used wherever an SQL expression is valid.
Table Functions: Return a table result set and can be used in the FROM clause of SELECT statements.
SnowConvert AI Translation Support: SnowConvert AI supports translation of Inline UDFs, SQL Scalar Functions, and SQL Table Functions with the following migration approaches:
Inline Functions: Will be kept as Inline Functions in Snowflake.
Non-inline UDFs: If they are Snowflake Scripting compliant, they will be transformed into Snowflake Scripting UDFs
Complex UDFs: Any UDFs that don’t fit the above two categories will be migrated to Stored Procedures instead.
In cases where UDFs are converted to procedures, an EWI message will be added to inform the user why the UDF could not be directly migrated to a Snowflake UDF and was converted to a procedure instead.
Grammar Syntax¶
The following is the SQL syntax to create a user defined function in IBM DB2. Click here to go to the DB2 specification for this syntax.
CREATE [ OR REPLACE ] FUNCTION function_name
[ ( [ { IN | OUT | INOUT } ] parameter_name data_type [ DEFAULT default_clause ]... ) ]
RETURNS { data_type
| ROW ( column_name data_type [, column_name data_type ]... )
| TABLE ( column_name data_type [, column_name data_type ]... )
| row_type_name
| anchored_row_data_type
| ELEMENT OF array_type_name }
[ LANGUAGE SQL ]
[ PARAMETER CCSID { ASCII | UNICODE } ]
[ SPECIFIC specific_name ]
[ { DETERMINISTIC | NOT DETERMINISTIC } ]
[ { EXTERNAL ACTION | NO EXTERNAL ACTION } ]
[ { READS SQL DATA | CONTAINS SQL | MODIFIES SQL DATA } ]
[ { ALLOW PARALLEL | DISALLOW PARALLEL } ]
[ STATIC DISPATCH ]
[ CALLED ON NULL INPUT ]
[ INHERIT SPECIAL REGISTERS ]
[ PREDICATES ( predicate_specification ) ]
[ { INHERIT ISOLATION LEVEL [ { WITHOUT LOCK REQUEST | WITH LOCK REQUEST } ] } ]
[ { SECURED | NOT SECURED } ]
RETURN { expression
| SELECT statement
| BEGIN [ ATOMIC ]
[ DECLARE declarations ]
statement...
END }
UDF Option List¶
Description¶
DB2 CREATE FUNCTION statements support various options that control the behavior, performance, and security characteristics of the function. These options specify how the function should be executed, what SQL operations it can perform, whether it’s deterministic, and how it handles parallel execution, among other settings.
Migration Support Table¶
The following table shows the DB2 to Snowflake UDF option equivalencies:
DB2 Option |
Snowflake Equivalent |
Notes |
|---|---|---|
|
|
Translated to Snowflake’s equivalent syntax |
|
Not Needed |
Snowflake doesn’t support specific names for UDFs |
|
|
Preserved in Snowflake UDF definition |
|
Not Needed |
Snowflake doesn’t have equivalent option |
|
|
Snowflake UDFs can read data by default |
|
|
Basic SQL support is default in Snowflake |
|
Not Needed |
UDFs that modify data are converted to stored procedures |
|
Not Needed |
Snowflake handles parallelization automatically |
|
Not Needed |
Not applicable in Snowflake’s architecture |
|
Default Behavior |
Snowflake UDFs handle NULL inputs by default |
|
Not Needed |
Snowflake doesn’t have equivalent special registers |
|
Not Needed |
Snowflake doesn’t support predicate pushdown specifications |
|
Not Needed |
Snowflake uses different transaction isolation model |
|
Not Needed |
Snowflake uses different security model |
|
Not Needed |
Character set handling differs in Snowflake |
Note
Options marked as “Not Needed” will be removed during migration because Snowflake either handles the functionality automatically (e.g., parallelization, NULL input handling) or the option is not applicable in Snowflake’s architecture (e.g., special registers, isolation levels).
INLINE UDF¶
Description¶
Inline UDFs in DB2 are simple functions that contain a single SQL expression or return statement without complex procedural logic. These functions are typically defined with a direct RETURN clause followed by a simple expression, calculation, or query.
SnowConvert AI preserves these as Inline Functions in Snowflake, maintaining their simplicity and performance characteristics.
Note
Some parts in the output code are omitted for clarity reasons.
Sample Source Patterns¶
Input Code:¶
Db2 - Inline UDF with expression¶
CREATE FUNCTION CALCULATE_TAX (price DECIMAL(10,2), tax_rate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN price * tax_rate;
Output Code:¶
Snowflake¶
CREATE FUNCTION CALCULATE_TAX (price DECIMAL(10,2), tax_rate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "10/31/2025", "domain": "no-domain-provided", "migrationid": "CzuaAR6Mu3GfenyLFPxUGw==" }}'
AS
$$
price * tax_rate
$$;
Input Code:¶
Db2 - Inline UDF with Select¶
CREATE FUNCTION GET_EMPLOYEE_COUNT (dept_id INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
RETURN SELECT COUNT(*) FROM employees WHERE department_id = dept_id;
Output Code:¶
Snowflake¶
CREATE FUNCTION GET_EMPLOYEE_COUNT (dept_id INTEGER)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "10/31/2025", "domain": "no-domain-provided", "migrationid": "HjuaARhyBn6VZ1Uyctr5Ag==" }}'
AS
$$
SELECT
COUNT(*) FROM
employees
WHERE department_id = :dept_id
$$;
Known Issues¶
There are no known issues.
Scalar UDF¶
Description¶
Scalar UDFs in DB2 are functions that return a single value and can contain more complex logic than inline functions. These functions may include procedural constructs such as variable declarations, conditional statements, loops, and multiple SQL statements. Unlike inline UDFs, scalar UDFs with complex logic use a BEGIN...END block structure to encapsulate their functionality.
SnowConvert AI Migration: If the scalar UDF logic is compatible with Snowflake Scripting syntax, it will be translated to a Snowflake Scripting UDF. This preserves the function’s behavior while adapting it to Snowflake’s UDF implementation. Functions that contain unsupported constructs will be converted to stored procedures instead.
Note
Some parts in the output code are omitted for clarity reasons.
Sample Source Patterns¶
Input Code:¶
Db2 - Scalar UDF with IF ELSE Statement¶
CREATE FUNCTION CALCULATE_DISCOUNT (purchase_amount DECIMAL(10,2), customer_type VARCHAR(20))
RETURNS DECIMAL(10,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN
DECLARE discount_rate DECIMAL(5,4);
DECLARE final_discount DECIMAL(10,2);
IF customer_type = 'PREMIUM' THEN
SET discount_rate = 0.15;
ELSIF customer_type = 'GOLD' THEN
SET discount_rate = 0.10;
ELSIF customer_type = 'SILVER' THEN
SET discount_rate = 0.05;
ELSE
SET discount_rate = 0.02;
END IF;
SET final_discount = purchase_amount * discount_rate;
RETURN final_discount;
END;
Output Code:¶
Snowflake¶
CREATE FUNCTION CALCULATE_DISCOUNT (purchase_amount DECIMAL(10,2), customer_type VARCHAR(20))
RETURNS DECIMAL(10,2)
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "11/26/2025", "domain": "no-domain-provided", "migrationid": "usGaAajDzniYFKtk+Fvnzg==" }}'
AS
$$
DECLARE
discount_rate DECIMAL(5,4);
final_discount DECIMAL(10,2);
BEGIN
IF (:customer_type = 'PREMIUM') THEN
discount_rate := 0.15;
ELSEIF (:customer_type = 'GOLD') THEN
discount_rate := 0.10;
ELSEIF (:customer_type = 'SILVER') THEN
discount_rate := 0.05;
ELSE
discount_rate := 0.02;
END IF;
final_discount := purchase_amount * discount_rate;
RETURN final_discount;
END
$$;
Input Code:¶
Db2 - Scalar UDF with WHILE Loop¶
CREATE FUNCTION CALCULATE_COMPOUND_INTEREST (principal DECIMAL(15,2), rate DECIMAL(5,4), years INTEGER)
RETURNS DECIMAL(15,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN
DECLARE counter INTEGER DEFAULT 1;
DECLARE amount DECIMAL(15,2);
SET amount = principal;
WHILE counter <= years DO
SET amount = amount * (1 + rate);
SET counter = counter + 1;
END WHILE;
RETURN amount;
END;
Output Code:¶
Snowflake¶
CREATE FUNCTION CALCULATE_COMPOUND_INTEREST (principal DECIMAL(15,2), rate DECIMAL(5,4), years INTEGER)
RETURNS DECIMAL(15,2)
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "11/26/2025", "domain": "no-domain-provided", "migrationid": "usGaAajDzniYFKtk+Fvnzg==" }}'
AS
$$
DECLARE
counter INTEGER DEFAULT 1;
amount DECIMAL(15,2);
BEGIN
amount := principal;
WHILE (:counter <= :years) DO
amount := amount * (1 + rate);
counter := counter + 1;
END WHILE;
RETURN amount;
END
$$;
Input Code:¶
Db2 - Scalar UDF with simple select into for variable assignment.¶
CREATE OR REPLACE FUNCTION CalculatePrice
(
p_BasePrice DECIMAL(10, 2),
p_Quantity INT
)
RETURNS DECIMAL(10, 2)
LANGUAGE SQL
BEGIN
DECLARE v_Discount DECIMAL(5, 2);
DECLARE v_Subtotal DECIMAL(10, 2);
DECLARE v_FinalPrice DECIMAL(10, 2);
SELECT CASE
WHEN p_Quantity >= 10 THEN 0.15
WHEN p_Quantity >= 5 THEN 0.10
ELSE 0.05
END,
p_BasePrice * p_Quantity
INTO v_Discount, v_Subtotal
FROM SYSIBM.SYSDUMMY1;
SET v_FinalPrice = v_Subtotal * (1 - v_Discount);
RETURN v_FinalPrice;
END;
Output Code:¶
Snowflake¶
CREATE OR REPLACE FUNCTION CalculatePrice
(
p_BasePrice DECIMAL(10, 2),
p_Quantity INT
)
RETURNS DECIMAL(10, 2)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "11/26/2025", "domain": "no-domain-provided", "migrationid": "o8GaAaDmH3aONT/SIuOCFw==" }}'
AS
$$
DECLARE
v_Discount DECIMAL(5, 2);
v_Subtotal DECIMAL(10, 2);
v_FinalPrice DECIMAL(10, 2);
BEGIN
v_Discount := CASE
WHEN :p_Quantity >= 10 THEN 0.15
WHEN :p_Quantity >= 5 THEN 0.10
ELSE 0.05
END;
v_Subtotal := :p_BasePrice * :p_Quantity;
v_FinalPrice := v_Subtotal * (1 - v_Discount);
RETURN v_FinalPrice;
END
$$;
Input Code:¶
Db2 - Scalar UDF with Values statement for variable assignment.¶
CREATE OR REPLACE FUNCTION CalculatePrice
(
p_BasePrice DECIMAL(10, 2),
p_Quantity INT
)
RETURNS DECIMAL(10, 2)
LANGUAGE SQL
BEGIN
DECLARE v_Discount DECIMAL(5, 2);
DECLARE v_Subtotal DECIMAL(10, 2);
DECLARE v_FinalPrice DECIMAL(10, 2);
VALUES (CASE
WHEN p_Quantity >= 10 THEN 0.15
WHEN p_Quantity >= 5 THEN 0.10
ELSE 0.05
END,
p_BasePrice * p_Quantity)
INTO v_Discount, v_Subtotal;
SET v_FinalPrice = v_Subtotal * (1 - v_Discount);
RETURN v_FinalPrice;
END;
Output Code:¶
Snowflake¶
CREATE OR REPLACE FUNCTION CalculatePrice
(
p_BasePrice DECIMAL(10, 2),
p_Quantity INT
)
RETURNS DECIMAL(10, 2)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "11/26/2025", "domain": "no-domain-provided", "migrationid": "usGaAajDzniYFKtk+Fvnzg==" }}'
AS
$$
DECLARE
v_Discount DECIMAL(5, 2);
v_Subtotal DECIMAL(10, 2);
v_FinalPrice DECIMAL(10, 2);
BEGIN
v_Discount := CASE
WHEN :p_Quantity >= 10 THEN 0.15
WHEN :p_Quantity >= 5 THEN 0.10
ELSE 0.05
END;
v_Subtotal :=
p_BasePrice * p_Quantity;
v_FinalPrice := v_Subtotal * (1 - v_Discount);
RETURN v_FinalPrice;
END
$$;
Known Issues¶
Warning
SnowConvert AI will not translate UDFs containing the following elements into SnowScripting UDFs, as these features are unsupported in SnowScripting UDFs:
Access database tables
Use cursors
Call other UDFs
Contain aggregate or window functions
Perform DML operations (INSERT/UPDATE/DELETE)
Return result sets
Related EWIs¶
There are no related EWIs.
Table UDF¶
Description¶
Table UDFs (Table-Valued Functions) in DB2 are functions that return a table result set rather than a single value. These functions can be used in the FROM clause of SELECT statements. Table UDFs are defined with a RETURNS TABLE clause that specifies the structure of the returned table.
SnowConvert AI Migration: Table UDFs that are compatible with Snowflake’s table function syntax will be translated to Snowflake Table Functions. This preserves their functionality while adapting them to Snowflake’s implementation. Functions that contain unsupported Snowflake Scripting elements will be converted to stored procedures that return result sets instead.
Note
Some parts in the output code are omitted for clarity reasons.
Sample Source Patterns¶
Input Code:¶
Db2 - Simple Table UDF¶
CREATE FUNCTION GET_EMPLOYEES_BY_DEPT (dept_id INTEGER)
RETURNS TABLE (
employee_id INTEGER,
employee_name VARCHAR(100),
salary DECIMAL(10,2),
hire_date DATE
)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
RETURN
SELECT emp_id, emp_name, emp_salary, emp_hire_date
FROM employees
WHERE department_id = dept_id
ORDER BY emp_name;
Output Code:¶
Snowflake¶
CREATE FUNCTION GET_EMPLOYEES_BY_DEPT (dept_id INTEGER)
RETURNS TABLE (
employee_id INTEGER,
employee_name VARCHAR(100),
salary DECIMAL(10,2),
hire_date DATE
)
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "10/31/2025", "domain": "no-domain-provided", "migrationid": "3TuaAdyRFHCJuSE7bnMCGg==" }}'
AS
$$
SELECT emp_id, emp_name, emp_salary, emp_hire_date
FROM
employees
WHERE department_id = dept_id
ORDER BY emp_name
$$;
Input Code:¶
Db2 - Complex Table UDF with Multiple Parameters¶
CREATE FUNCTION GET_SALES_REPORT (start_date DATE, end_date DATE, min_amount DECIMAL(10,2))
RETURNS TABLE (
sales_id INTEGER,
customer_name VARCHAR(100),
product_name VARCHAR(100),
sale_amount DECIMAL(10,2),
sale_date DATE,
region VARCHAR(50)
)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
RETURN
SELECT s.sale_id, c.customer_name, p.product_name,
s.amount, s.sale_date, c.region
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id
WHERE s.sale_date BETWEEN start_date AND end_date
AND s.amount >= min_amount
ORDER BY s.sale_date DESC, s.amount DESC;
Output Code:¶
Snowflake¶
CREATE FUNCTION GET_SALES_REPORT (start_date DATE, end_date DATE, min_amount DECIMAL(10,2))
RETURNS TABLE (
sales_id INTEGER,
customer_name VARCHAR(100),
product_name VARCHAR(100),
sale_amount DECIMAL(10,2),
sale_date DATE,
region VARCHAR(50)
)
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "10/31/2025", "domain": "no-domain-provided", "migrationid": "3TuaAdyRFHCJuSE7bnMCGg==" }}'
AS
$$
SELECT s.sale_id, c.customer_name, p.product_name,
s.amount, s.sale_date, c.region
FROM
sales s
JOIN
customers c ON s.customer_id = c.customer_id
JOIN
products p ON s.product_id = p.product_id
WHERE s.sale_date BETWEEN start_date AND end_date
AND s.amount >= min_amount
ORDER BY s.sale_date DESC, s.amount DESC
$$;
Known Issues¶
There are no known issues.
Related EWIs.¶
There are no related EWIs.
UDF Converted to Stored Procedure¶
Description¶
Some DB2 UDFs cannot be directly migrated as Snowflake UDFs due to limitations in Snowflake Scripting UDFs. When a DB2 UDF contains elements that are not supported in Snowflake Scripting UDFs (such as SQL DML statements, cursors, result sets, or calls to other UDFs), SnowConvert AI will migrate these functions as Stored Procedures instead.
SnowConvert AI Migration: These UDFs are converted to stored procedures with an EWI message explaining why the direct UDF migration was not possible.
Note
Some parts in the output code are omitted for clarity reasons.
Sample Source Patterns¶
Input Code:¶
Db2 - UDF with DML Statement¶
CREATE FUNCTION LOG_AUDIT_EVENT (event_type VARCHAR(50), event_details VARCHAR(500))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
INSERT INTO audit_log (event_type, event_details, log_timestamp)
VALUES (event_type, event_details, CURRENT_TIMESTAMP);
RETURN 1;
END;
Output Code:¶
Snowflake¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE BECAUSE IT CONTAINS THE FOLLOWING: VALUES CLAUSE, INSERT STATEMENT ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "audit_log" **
CREATE OR REPLACE PROCEDURE LOG_AUDIT_EVENT (event_type VARCHAR(50), event_details VARCHAR(500))
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "db2", "convertedOn": "10/31/2025", "domain": "no-domain-provided", "migrationid": "9DuaAVemZHCpeN/qzhiOkg==" }}'
AS
$$
BEGIN
INSERT INTO audit_log (event_type, event_details, log_timestamp)
VALUES (event_type, event_details, CURRENT_TIMESTAMP);
RETURN 1;
END
$$;
Known Issues¶
The main limitation is that the resulting converted procedure must be invoked using the CALL syntax, preventing its use directly within standard SQL expressions like the original UDF.
Related EWIs¶
SSC-EWI-0068: User defined function was transformed to a Snowflake procedure.