DB2 UDFs Top Interview Questions

You know that DB2 functions and stored procedures store in the Database server.

When you invoke them they will trigger. You can write UDFs using any language like SQL, C or COBOL.

You can access user defined functions from DB2 local or remote server.

DB2 scalar user defined function.
UDF: Function is basically something repeatedly you are using in your SQL query. Then, you can call your function in main SQL query to avoid complexity

DB2 scalar user defined function using SQL.

Example

create function emp_current_votes
(employee_id char(6))
returns integer
specific emp_curr_votes_int
not deterministic
language sql
return select count(*)from emp_votes where empno = emp

In the above function you are getting votes from votes table. Here the function is emp_currrent_votes.

How to run user defined function in DB2

db2 => select firstnme, lastname, emp_current_votes(empno)
from employee; 

FIRSTNME LASTNAME 3
------------ ----------
CHRISTINE HAAS 1
MICHAEL THOMPSON 0
SALLY KWAN 0
...
WING LEE 0
JASON GOUNOT 0
DIAN HEMMINGER 2
GREG ORLANDO 0
KIM NATZ 0
KIYOSHI YAMAMOTO 0

Interview Question

• One important limitation is the use of user-defined scalar functions in an order by clause. To provide a usable ordering function, the UDF must be deterministic. If you attempt to order by a nondeterministic scalar function, you’ll get error code SQL0583N.

Also read

• DB2 SQL Query Performance Tuning (2 of 3)
• Real logic you need to connect DB2 using PHP engine
• Top DB2 privileges really useful for certification seekers
• Left Vs Right Outer Join Which is Better