You are aware that all DB2 functions and stored procedures stored in Database server, and whenever you invoke them they will be triggered. I want to share with you that UDFs you can write using any language SQL, C or COBOL.
UDFs you can access from DB2 local or remote server.
I have explained with 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
Below function is DB2 scalar user defined function.
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 execute 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
- 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.
- DB2 UDF sample function read now with awesome interview question
- Real reason why Left outer JOIN only you need to use in DB2 SQL
- 3 top rules to write alias instead of Table column name in SQL query
- 4 top DB2 host variable points you need to know before write program